Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-14433

RocksDB may show empty or incorrect output with rocksdb_strict_collation_check=off

    XMLWordPrintable

Details

    Description

      It is hard to find exact conditions which reliably trigger the problem, but following steps did demonstrate it (see outputs later):

      set global rocksdb_strict_collation_check=off;
      drop table t;
      CREATE TABLE t(
        a varchar(10) NOT NULL,
        b char(1) DEFAULT 'X',
        c char(2) NOT NULL DEFAULT '??',
        d varchar(10) NOT NULL,
        e int(11) DEFAULT 0,
        PRIMARY KEY (a,d),
        KEY (e)
      ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8;
       
      insert into t select 1,1,1,1,0;
      insert into t select 2,1,1,1,0;
      insert into t select 3,1,1,1,0;
       
      select a from t;
      explain select * from t where not e;
      select a from t;
      

      MariaDB [test]> drop table t;
      Query OK, 0 rows affected (0.04 sec)
       
      MariaDB [test]> CREATE TABLE t(
          ->   a varchar(10) NOT NULL,
          ->   b char(1) DEFAULT 'X',
          ->   c char(2) NOT NULL DEFAULT '??',
          ->   d varchar(10) NOT NULL,
          ->   e int(11) DEFAULT 0,
          ->   PRIMARY KEY (a,d),
          ->   KEY (e)
          -> ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8;
      Query OK, 0 rows affected (0.22 sec)
       
      MariaDB [test]> 
      MariaDB [test]> insert into t select 1,1,1,1,0;
      Query OK, 1 row affected (0.07 sec)
      Records: 1  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> insert into t select 2,1,1,1,0;
      Query OK, 1 row affected (0.03 sec)
      Records: 1  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> insert into t select 3,1,1,1,0;
      Query OK, 1 row affected (0.03 sec)
      Records: 1  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> 
      MariaDB [test]> select a from t;
      +---+
      | a |
      +---+
      | 1 |
      | 2 |
      | 3 |
      +---+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> explain select * from t where not e;
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------+
      |    1 | SIMPLE      | t     | ref  | e             | e    | 5       | const |    2 |       |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> select a from t;
      +---+
      | a |
      +---+
      |   |
      |   |
      |   |
      +---+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> \r
      Connection id:    11
      Current database: test
       
      MariaDB [test]> select a from t;
      +---+
      | a |
      +---+
      |   |
      |   |
      |   |
      +---+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> flush tables;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> select a from t;
      +---+
      | a |
      +---+
      |   |
      |   |
      |   |
      +---+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> select a from t;
      ERROR 2006 (HY000): MySQL server has gone away
      No connection. Trying to reconnect...
      Connection id:    9
      Current database: test
       
      +---+
      | a |
      +---+
      |   |
      |   |
      |   |
      +---+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> optimize table t;
      +--------+----------+----------+----------+
      | Table  | Op       | Msg_type | Msg_text |
      +--------+----------+----------+----------+
      | test.t | optimize | status   | OK       |
      +--------+----------+----------+----------+
      1 row in set (0.15 sec)
       
      MariaDB [test]> select a from t;
      +---+
      | a |
      +---+
      |   |
      |   |
      |   |
      +---+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> select a,b from t;
      +---+------+
      | a | b    |
      +---+------+
      | 1 | 1    |
      | 2 | 1    |
      | 3 | 1    |
      +---+------+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> select a from t;
      +---+
      | a |
      +---+
      |   |
      |   |
      |   |
      +---+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> alter table t drop primary key;
      Query OK, 3 rows affected (0.27 sec)               
      Records: 3  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select a from t;
      +---+
      | a |
      +---+
      | 1 |
      | 2 |
      | 3 |
      +---+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> select a from t;
      +---+
      | a |
      +---+
      | 1 |
      | 2 |
      | 3 |
      +---+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> explain select * from t where not e;
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------+
      |    1 | SIMPLE      | t     | ref  | e             | e    | 5       | const |    1 |       |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> select a from t;
      +---+
      | a |
      +---+
      | 1 |
      | 2 |
      | 3 |
      +---+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> alter table t add primary key(a,d);
      Query OK, 3 rows affected (0.27 sec)               
      Records: 3  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select a from t;
      +---+
      | a |
      +---+
      |   |
      |   |
      |   |
      +---+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> drop table t;
      Query OK, 0 rows affected (0.03 sec)
       
      MariaDB [test]> CREATE TABLE t(
          ->   a varchar(10) NOT NULL,
          ->   b char(1) DEFAULT 'X',
          ->   c char(2) NOT NULL DEFAULT '??',
          ->   d varchar(10) NOT NULL,
          ->   e int(11) DEFAULT 0,
          ->   PRIMARY KEY (a,d),
          ->   KEY (e)
          -> ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8;
      ERROR 4063 (HY000): Unsupported collation on string indexed column test.t.a Use binary collation (latin1_bin, binary, utf8_bin).
      MariaDB [test]> 
      MariaDB [test]> insert into t select 1,1,1,1,0;
      ERROR 1146 (42S02): Table 'test.t' doesn't exist
      MariaDB [test]> insert into t select 2,1,1,1,0;
      ERROR 1146 (42S02): Table 'test.t' doesn't exist
      MariaDB [test]> insert into t select 3,1,1,1,0;
      ERROR 1146 (42S02): Table 'test.t' doesn't exist
      MariaDB [test]> 
      MariaDB [test]> select a from t;
      ERROR 1146 (42S02): Table 'test.t' doesn't exist
      MariaDB [test]> set global rocksdb_strict_collation_check=off;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> drop table t;
      ERROR 1051 (42S02): Unknown table 'test.t'
      MariaDB [test]> CREATE TABLE t(
          ->   a varchar(10) NOT NULL,
          ->   b char(1) DEFAULT 'X',
          ->   c char(2) NOT NULL DEFAULT '??',
          ->   d varchar(10) NOT NULL,
          ->   e int(11) DEFAULT 0,
          ->   PRIMARY KEY (a,d),
          ->   KEY (e)
          -> ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8;
      Query OK, 0 rows affected (0.14 sec)
       
      MariaDB [test]> 
      MariaDB [test]> insert into t select 1,1,1,1,0;
      Query OK, 1 row affected (0.03 sec)
      Records: 1  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> insert into t select 2,1,1,1,0;
      Query OK, 1 row affected (0.03 sec)
      Records: 1  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> insert into t select 3,1,1,1,0;
      Query OK, 1 row affected (0.04 sec)
      Records: 1  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> 
      MariaDB [test]> select a from t;
      +---+
      | a |
      +---+
      | 1 |
      | 2 |
      | 3 |
      +---+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> select a from t;
      +---+
      | a |
      +---+
      | 1 |
      | 2 |
      | 3 |
      +---+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> select a,b from t;
      +---+------+
      | a | b    |
      +---+------+
      | 1 | 1    |
      | 2 | 1    |
      | 3 | 1    |
      +---+------+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> select a from t;
      +---+
      | a |
      +---+
      | 1 |
      | 2 |
      | 3 |
      +---+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> explain select a from t;
      +------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
      | id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
      +------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
      |    1 | SIMPLE      | t     | index | NULL          | PRIMARY | 64      | NULL |    0 | Using index |
      +------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> select a from t;
      +---+
      | a |
      +---+
      | 1 |
      | 2 |
      | 3 |
      +---+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> explain select a from t where not 1;
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------+
      |    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> select a from t;
      +---+
      | a |
      +---+
      | 1 |
      | 2 |
      | 3 |
      +---+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> explain select a from t where not a;
      +------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
      | id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
      +------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
      |    1 | SIMPLE      | t     | index | PRIMARY       | PRIMARY | 64      | NULL |    0 | Using where; Using index |
      +------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> select a from t;
      +---+
      | a |
      +---+
      | 1 |
      | 2 |
      | 3 |
      +---+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> explain select a from t where not e;
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
      |    1 | SIMPLE      | t     | ref  | e             | e    | 5       | const |    1 | Using index |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> select a from t;
      +---+
      | a |
      +---+
      | 1 |
      | 2 |
      | 3 |
      +---+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> explain select * from t where not e;
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------+
      |    1 | SIMPLE      | t     | ref  | e             | e    | 5       | const |    2 |       |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> select a from t;
      +---+
      | a |
      +---+
      |   |
      |   |
      |   |
      +---+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> alter table t engine=rocksdb;
      Query OK, 3 rows affected (0.27 sec)               
      Records: 3  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select a from t;
      +---+
      | a |
      +---+
      |   |
      |   |
      |   |
      +---+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> set global rocksdb_strict_collation_check=off;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> drop table t;
      Query OK, 0 rows affected (0.03 sec)
       
      MariaDB [test]> CREATE TABLE t(
          ->   a varchar(10) NOT NULL,
          ->   b char(1) DEFAULT 'X',
          ->   c char(2) NOT NULL DEFAULT '??',
          ->   d varchar(10) NOT NULL,
          ->   e int(11) DEFAULT 0,
          ->   PRIMARY KEY (a,d),
          ->   KEY (e)
          -> ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8;
      Query OK, 0 rows affected (0.20 sec)
       
      MariaDB [test]> 
      MariaDB [test]> insert into t select 1,1,1,1,0;
      Query OK, 1 row affected (0.04 sec)
      Records: 1  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> insert into t select 2,1,1,1,0;
      Query OK, 1 row affected (0.03 sec)
      Records: 1  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> insert into t select 3,1,1,1,0;
      Query OK, 1 row affected (0.03 sec)
      Records: 1  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> 
      MariaDB [test]> select a from t;
      +---+
      | a |
      +---+
      | 1 |
      | 2 |
      | 3 |
      +---+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> explain select a from t where not e;
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
      |    1 | SIMPLE      | t     | ref  | e             | e    | 5       | const |    1 | Using index |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> select a from t;
      +---+
      | a |
      +---+
      | 1 |
      | 2 |
      | 3 |
      +---+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> explain select * from t where not a;
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      |    1 | SIMPLE      | t     | ALL  | PRIMARY       | NULL | NULL    | NULL |    0 | Using where |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> select a from t;
      +---+
      | a |
      +---+
      | 1 |
      | 2 |
      | 3 |
      +---+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> explain select * from t where not e;
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------+
      |    1 | SIMPLE      | t     | ref  | e             | e    | 5       | const |    1 |       |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> select a from t;
      +---+
      | a |
      +---+
      | 1 |
      | 2 |
      | 3 |
      +---+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> explain select * from t where not e;
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------+
      |    1 | SIMPLE      | t     | ref  | e             | e    | 5       | const |    5 |       |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> select a from t;
      +---+
      | a |
      +---+
      |   |
      |   |
      |   |
      +---+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> flush query cache;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> select a from t;
      +---+
      | a |
      +---+
      |   |
      |   |
      |   |
      +---+
      3 rows in set (0.00 sec)
      

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              anikitin Andrii Nikitin (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.