Uploaded image for project: 'Percona Server'
  1. Percona Server
  2. PS-4911

 MyRocks crashes when TTL is defined on table with composite PK with varchar column in utf8_bin or latin_bin and query uses index on another column

    XMLWordPrintable

    Details

      Description

      Create table with Composite PK and one of PK columns is varchar and has utf8_bin collation and it also has an index on some other column:

      CREATE TABLE `test` (
      
        `a` bigint(20) NOT NULL,
        `b` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
        `u` bigint(20) unsigned NOT NULL,
        `d` bigint(20) DEFAULT NULL,
        PRIMARY KEY (`a`,`b`),
        KEY `d` (`d`)
      ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='ttl_duration=1000;ttl_col=u';
      

      insert some data to table

      mysql> insert into test values (100, 'aaabbb', unix_timestamp(), 200);
      

      According to explain, it uses index on column d
       

      mysql> explain select count(*) from test;
      +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
      | id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
      +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
      |  1 | SIMPLE      | test  | NULL       | index | NULL          | d    | 9       | NULL |    0 |     0.00 | Using index |
      +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
      1 row in set, 1 warning (0.00 sec)
      

       Try to execute SELECT - MySQL crashes

      mysql> select count(*) from test;
      ERROR 2013 (HY000): Lost connection to MySQL server during query
      

      And that's what is in logs: 

      17:38:39 UTC - mysqld got signal 11 ;
      This could be because you hit a bug. It is also possible that this binary
      or one of the libraries it was linked against is corrupt, improperly built,
      or misconfigured. This error can also be caused by malfunctioning hardware.
      Attempting to collect some information that could help diagnose the problem.
      As this is a crash and something is definitely wrong, the information
      collection process might fail.
      Please help us make Percona Server better by reporting any
      bugs at http://bugs.percona.com/
       
      key_buffer_size=134217728
      read_buffer_size=6291456
      max_used_connections=2
      max_threads=2049
      thread_count=3
      connection_count=1
      It is possible that mysqld could use up to
      key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 23228750 K  bytes of memory
      Hope that's ok; if not, decrease some variables in the equation.
       
      Thread pointer: 0x2ad4c80d7000
      Attempting backtrace. You can use the following information to find out
      where mysqld died. If you see no messages after this, something went
      terribly wrong...
      stack_bottom = 2ad4265cacf0 thread_stack 0x40000
      /usr/sbin/mysqld(my_print_stacktrace+0x3b)[0xf0077b]
      /usr/sbin/mysqld(handle_fatal_signal+0x471)[0x7a5161]
      /lib64/libpthread.so.0(+0xf5e0)[0x2ad42689e5e0]
      /usr/lib64/mysql/plugin/ha_rocksdb.so(_ZNK7myrocks11Rdb_key_def39unpack_binary_or_utf8_varchar_space_padEPNS_17Rdb_field_packingEP5FieldPhPNS_17Rdb_string_readerES7_+0x43)[0x2ad4976c8f93]
      /usr/lib64/mysql/plugin/ha_rocksdb.so(_ZNK7myrocks11Rdb_key_def13unpack_recordEP5TABLEPhPKN7rocksdb5SliceES7_RKb+0x212)[0x2ad4976ccba2]
      /usr/lib64/mysql/plugin/ha_rocksdb.so(_ZN7myrocks10ha_rocksdb20secondary_index_readEiPh+0x289)[0x2ad4976a45e9]
      /usr/lib64/mysql/plugin/ha_rocksdb.so(_ZN7myrocks10ha_rocksdb25index_next_with_directionEPhb+0xa9)[0x2ad4976a60b9]
      /usr/lib64/mysql/plugin/ha_rocksdb.so(_ZN7myrocks10ha_rocksdb18index_first_internEPh+0x133)[0x2ad4976a6303]
      /usr/lib64/mysql/plugin/ha_rocksdb.so(_ZN7myrocks10ha_rocksdb11index_firstEPh+0x65)[0x2ad4976a6565]
      /usr/sbin/mysqld(_ZN7handler14ha_index_firstEPh+0x11f)[0x8104bf]
      /usr/sbin/mysqld(_Z15join_read_firstP7QEP_TAB+0x82)[0xc9afc2]
      /usr/sbin/mysqld(_Z10sub_selectP4JOINP7QEP_TABb+0x11b)[0xca177b]
      /usr/sbin/mysqld(_ZN4JOIN4execEv+0x267)[0xc9a4a7]
      /usr/sbin/mysqld(_Z12handle_queryP3THDP3LEXP12Query_resultyy+0x17d)[0xd0b93d]
      /usr/sbin/mysqld[0x7625d8]
      /usr/sbin/mysqld(_Z21mysql_execute_commandP3THDb+0x44e1)[0xccd191]
      /usr/sbin/mysqld(_Z11mysql_parseP3THDP12Parser_state+0x5e5)[0xcd06f5]
      /usr/sbin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0xabd)[0xcd12bd]
      /usr/sbin/mysqld(_Z10do_commandP3THD+0x1df)[0xcd2cff]
      /usr/sbin/mysqld(handle_connection+0x2b8)[0xd9c8f8]
      /usr/sbin/mysqld(pfs_spawn_thread+0x1b4)[0xf18904]
      /lib64/libpthread.so.0(+0x7e25)[0x2ad426896e25]
      /lib64/libc.so.6(clone+0x6d)[0x2ad4286fd34d]
       
      Trying to get some variables.
      Some pointers may be invalid and cause the dump to abort.
      Query (2ad4c800e030): select count(*) from test
      Connection ID (thread ID): 8
      Status: NOT_KILLED
      

       
       Now try the same query, but force using PK - everything is OK 

      mysql> select count(*) from test use index(primary);
      +----------+
      | count(*) |
      +----------+
      |        1 |
      +----------+
      1 row in set (0.00 sec) 
      

       
       
       
       The same thing but on table with composite PK with 2 bigint - everything is OK:
       
       
       

      mysql> CREATE TABLE `test2` (
        `a` bigint(20) NOT NULL,
        `b` bigint(20) NOT NULL,
        `u` bigint(20) unsigned NOT NULL,
        `d` bigint(20) DEFAULT NULL,
        PRIMARY KEY (`a`,`b`),
        KEY `d` (`d`)
      ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='ttl_duration=1000;ttl_col=u';
      
      mysql> insert into test2 values (100, 200, unix_timestamp(), 200);
      
      mysql> explain select count(*) from test;
      +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
      | id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
      +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
      |  1 | SIMPLE      | test  | NULL       | index | NULL          | d    | 9       | NULL |    0 |     0.00 | Using index |
      +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
      1 row in set, 1 warning (0.00 sec)
      
      mysql> select count(*) from test2;
      +----------+
      | count(*) |
      +----------+
      |        1 |
      +----------+
      1 row in set (0.01 sec)
      

       The same thing but on table with composite PK if PK column had COLLATE utf8mb4_bin - everything is OK
       
       
       

      mysql> CREATE TABLE `test3` (
        `a` bigint(20) NOT NULL,
        `b` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
        `u` bigint(20) unsigned NOT NULL,
        `d` bigint(20) DEFAULT NULL,
        PRIMARY KEY (`a`,`b`),
        KEY `d` (`d`)
      ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='ttl_duration=1000;ttl_col=u';
      

       

      mysql> insert into test3 values (100, 'aaabbb', unix_timestamp(), 200);
      
      mysql> explain select count(*) from test3;
      +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
      | id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
      +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
      |  1 | SIMPLE      | test3 | NULL       | index | NULL          | d    | 9       | NULL |    0 |     0.00 | Using index |
      +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
      1 row in set, 1 warning (0.00 sec)
      
      mysql> select count(*) from test3;
      +----------+
      | count(*) |
      +----------+
      |        1 |
      +----------+
      1 row in set (0.00 sec)
      

       
       
       
       
       
       
       

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                george.lorch George Lorch
                Reporter:
                mfedotov Maksim Fedotov
              • Votes:
                1 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - Not Specified
                  Not Specified
                  Logged:
                  Time Spent - 4 hours, 15 minutes
                  4h 15m