[MDEV-14433] RocksDB may show empty or incorrect output with rocksdb_strict_collation_check=off - Jira

XML

Word

Printable

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.2(EOL)
Fix Version/s: 10.2.12
Component/s: Storage Engine - RocksDB
Labels:
None

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

includes

MDEV-14563 Wrong query plan for query with no PK

Closed

relates to

MDEV-14293 MyRocks lacks basic functionality

Closed

Activity

People

Assignee:: Sergei Petrunia

Reporter:: Andrii Nikitin (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2017-11-17 13:54

Updated:: 2017-12-06 09:45

Resolved:: 2017-12-06 09:45

Git Integration

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