Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.10
-
Centos 7
Description
As it is right now, it is useless 100% for one reason: the only collation available is binary and that does not allow to case-insensitive searches. It breaks any application that was working with innodb or tokudb.
Can somebody fix this so may start using this fantastic engine?
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Fix Version/s | 10.2 [ 14601 ] | |
| Assignee | Sergei Petrunia [ psergey ] |
Where do you see that "the only collation available is binary and that does not allow to case-insensitive searches."?
> SELECT @@version;
---------------------
| @@version |
---------------------
| 10.2.10-MariaDB-log |
---------------------
1 row in set (0.00 sec)
> CREATE TABLE t(x CHAR(3)) ENGINE=RocksDB;
Query OK, 0 rows affected (0.06 sec)
> INSERT INTO t VALUES('foo'),('Foo');
Query OK, 2 rows affected (0.19 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [bugs]> SELECT x FROM t WHERE x = 'foo';
------
| x |
------
| foo |
| Foo |
------
2 rows in set (0.00 sec)
> SHOW CREATE TABLE t\G
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`x` char(3) DEFAULT NULL
) ENGINE=ROCKSDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
- 1. row ***************************
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
Same with explicit case insensitive collation:
> ALTER TABLE t COLLATE utf8_unicode_ci;
Query OK, 2 rows affected (0.32 sec)
Records: 2 Duplicates: 0 Warnings: 0
> SELECT x FROM t WHERE x = 'foo';
------
| x |
------
| foo |
| Foo |
------
2 rows in set (0.00 sec)
philip_38, I assume this bug was filed because you saw an error like this:
create table t24 ( col1 varchar(100) character set utf8 primary key) ;
|
ERROR 4063 (HY000): Unsupported collation on string indexed column test.t24.col1 Use binary collation (latin1_bin, binary, utf8_bin).
|
The wording is misleading there. MyRocks actually supports any collation now (although some are more space-efficient than others). You can make this warning go away by setting (either in console or my.cnf):
set global rocksdb_strict_collation_check=off
|
You can also enable it for individual tables/indexes by setting rocksdb_strict_collation_exceptions.
Keeping the bug open and assigned to me as:
- Need better wording error message
- Need better docs for this topic
This option should be set by default.
99% of the time people use varchar(X) fields for primary and not binary or varbinary, but it turns out that you may not use varchar as part of a primary key unless you set this option off.
I found another issue with Rocksdb. Please correct me if I am wrong
This select without limit brings in 16 rows
SELECT class, state,account_id, flagv from userbusy where account_id='16356' and class='W' and state='NJ' and not flagv;
---------------------------+
| class | state | account_id | flagv |
---------------------------+
| W | NJ | 16356 | 0 |
| W | NJ | 16356 | 0 |
| W | NJ | 16356 | 0 |
| W | NJ | 16356 | 0 |
| W | NJ | 16356 | 0 |
| W | NJ | 16356 | 0 |
| W | NJ | 16356 | 0 |
| W | NJ | 16356 | 0 |
| W | NJ | 16356 | 0 |
| W | NJ | 16356 | 0 |
| W | NJ | 16356 | 0 |
| W | NJ | 16356 | 0 |
| W | NJ | 16356 | 0 |
| W | NJ | 16356 | 0 |
| W | NJ | 16356 | 0 |
| W | NJ | 16356 | 0 |
---------------------------+
But the same select with a limit of 11 brings only one record
SELECT class, state,account_id, flagv from userbusy where account_id='16356' and class='W' and state='NJ' and not flagv limit 11;
---------------------------+
| class | state | account_id | flagv |
---------------------------+
| W | NJ | 16402 | 0 |
---------------------------+
Please tell me I am confused.
I invite Elena to login and run the queries.
the table is very simple>
describe userbusy;
------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
------------------------------------------+
| number | varchar(10) | NO | PRI | NULL | |
| class | char(1) | YES | X | ||
| state | char(2) | NO | ?? | ||
| account_id | varchar(10) | NO | PRI | NULL | |
| flagv | int(11) | YES | MUL | 0 |
------------------------------------------+
I just created the same tables with create table userbusy_in engine=innodb as select * from userbusy, and IT WORKS fine. So the implementation of RocksDB is not working.
philip_38 While 10.2 is GA, particular plugins/Engines may have non-GA status as described at https://mariadb.com/kb/en/library/list-of-plugins/
MyRocks it is still alpha, so it is expected that it may have some problems and we appreciate your assistance in finding bugs.
I've tried to reproduce the problem with simplest SELECT .. LIMIT, but everything I've tried worked properly.
Could you provide outputs of queries below, so I have chance to reproduce the problem and create dedicated bug for LIMIT query:
show create table userbusy\G |
show index from userbusy; |
explain select class, state,account_id, flagv from userbusy where account_id='16356' and class='W' and state='NJ' and not flagv; |
| Comment |
[ show create table userbusy\G
*************************** 1. row *************************** Table: userbusy Create Table: CREATE TABLE `userbusy` ( `number` varchar(10) NOT NULL, `class` char(1) DEFAULT 'X', `state` char(2) NOT NULL DEFAULT '??', `account_id` varchar(10) NOT NULL, `flagv` int(11) DEFAULT 0, PRIMARY KEY (`number`,`account_id`), KEY `IDX_userbusy_flagv` (`flagv`) ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) MariaDB [asterisk]> show index from userbusy; +----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | userbusy | 0 | PRIMARY | 1 | number | A | 171518 | NULL | NULL | | LSMTREE | | | | userbusy | 0 | PRIMARY | 2 | account_id | A | 171518 | NULL | NULL | | LSMTREE | | | | userbusy | 1 | IDX_userbusy_flagv | 1 | flagv | A | 19 | NULL | NULL | YES | LSMTREE | | | +----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec) MariaDB [asterisk]> explain select class, state,account_id, flagv from userbusy where account_id='16356' and class='W' and state='NJ' and not flagv; +------+-------------+----------+------+--------------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------+------+--------------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | userbusy | ALL | IDX_userbusy_flagv | NULL | NULL | NULL | 171518 | Using where | +------+-------------+----------+------+--------------------+------+---------+------+--------+-------------+ 1 row in set (0.00 sec) ] |
| Comment |
[ show create table userbusy\G
*************************** 1. row *************************** Table: userbusy Create Table: CREATE TABLE `userbusy` ( `number` varchar(10) NOT NULL, `class` char(1) DEFAULT 'X', `state` char(2) NOT NULL DEFAULT '??', `account_id` varchar(10) NOT NULL, `flagv` int(11) DEFAULT 0, PRIMARY KEY (`number`,`account_id`), KEY `IDX_userbusy_flagv` (`flagv`) ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) MariaDB [asterisk]> show index from userbusy; +----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | userbusy | 0 | PRIMARY | 1 | number | A | 171518 | NULL | NULL | | LSMTREE | | | | userbusy | 0 | PRIMARY | 2 | account_id | A | 171518 | NULL | NULL | | LSMTREE | | | | userbusy | 1 | IDX_userbusy_flagv | 1 | flagv | A | 19 | NULL | NULL | YES | LSMTREE | | | +----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec) MariaDB [asterisk]> explain select class, state,account_id, flagv from userbusy where account_id='16356' and class='W' and state='NJ' and not flagv; +------+-------------+----------+------+--------------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------+------+--------------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | userbusy | ALL | IDX_userbusy_flagv | NULL | NULL | NULL | 171518 | Using where | +------+-------------+----------+------+--------------------+------+---------+------+--------+-------------+ 1 row in set (0.00 sec) ] |
| Comment |
[ show create table userbusy\G
*************************** 1. row *************************** Table: userbusy Create Table: CREATE TABLE `userbusy` ( `number` varchar(10) NOT NULL, `class` char(1) DEFAULT 'X', `state` char(2) NOT NULL DEFAULT '??', `account_id` varchar(10) NOT NULL, `flagv` int(11) DEFAULT 0, PRIMARY KEY (`number`,`account_id`), KEY `IDX_userbusy_flagv` (`flagv`) ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) MariaDB [asterisk]> show index from userbusy; +----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | userbusy | 0 | PRIMARY | 1 | number | A | 171518 | NULL | NULL | | LSMTREE | | | | userbusy | 0 | PRIMARY | 2 | account_id | A | 171518 | NULL | NULL | | LSMTREE | | | | userbusy | 1 | IDX_userbusy_flagv | 1 | flagv | A | 19 | NULL | NULL | YES | LSMTREE | | | +----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec) MariaDB [asterisk]> explain select class, state,account_id, flagv from userbusy where account_id='16356' and class='W' and state='NJ' and not flagv; +------+-------------+----------+------+--------------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------+------+--------------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | userbusy | ALL | IDX_userbusy_flagv | NULL | NULL | NULL | 171518 | Using where | +------+-------------+----------+------+--------------------+------+---------+------+--------+-------------+ 1 row in set (0.00 sec) ] |
|
|
{code:java}
|
show create table userbusy
|
*************************** 1. row *************************** |
Table: userbusy
|
Create Table: CREATE TABLE userbusy (
|
number varchar(10) NOT NULL, |
class char(1) DEFAULT 'X', |
state char(2) NOT NULL DEFAULT '??', |
account_id varchar(10) NOT NULL, |
flagv int(11) DEFAULT 0, |
PRIMARY KEY (number,account_id),
|
KEY IDX_userbusy_flagv (flagv)
|
) ENGINE=ROCKSDB DEFAULT CHARSET=utf8
|
1 row in set (0.00 sec) |
MariaDB [asterisk]> show index from userbusy;
|
+----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
|
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
|
Index_comment |
|
+----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| userbusy | 0 | PRIMARY | 1 | number | |
A | 171518 | NULL | NULL | | LSMTREE | |
| |
|
| userbusy | 0 | PRIMARY | 2 | account_id | |
A | 171518 | NULL | NULL | | LSMTREE | |
| |
|
| userbusy | 1 | IDX_userbusy_flagv | 1 | flagv | |
A | 19 | NULL | NULL | YES | LSMTREE | |
| |
|
+----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
3 rows in set (0.00 sec) |
MariaDB [asterisk]> explain select class, state,account_id, flagv from |
userbusy where account_id='16356' and class='W' and state='NJ' and not |
flagv;
|
+------+-------------+----------+------+--------------------+------+---------+------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key |
|
key_len | ref | rows | Extra |
|
+------+-------------+----------+------+--------------------+------+---------+------+--------+-------------+
|
| 1 | SIMPLE | userbusy | ALL | IDX_userbusy_flagv | NULL | |
NULL | NULL | 171518 | Using where | |
+------+-------------+----------+------+--------------------+------+---------+------+--------+-------------+
|
1 row in set (0.00 sec) |
|
|
|
| Comment |
[ As you can see by looking at the output of
show index from userbusy; the index on the field flagv has the wrong number of records. It should as many records as the primary key. Maybe that is why the query gets the wrong results. ] |
Let me save some time. If I do not have an index on the field, then limit works fine, bit it is of course slow. If I add an index on that BOOL field, then limit does not work.
This does not happen with Innodb. I had to go back.
The reason I stopped using Tokudb was that you need to disconnect Anonymous Large Pages on the box, and that affects perfomance dramatically wheen you have a huge box, with 500+ GB of RAM.
So basically there is no alternative now to Innodb. This is a sad state of affairs.
| Link |
This issue relates to |
philip_38 Thank you for requested outputs. Indeed behavior is very strange and I suspect that it is compound primary key (on varchar?) which triggers the problem.
I've opened MDEV-14433 which is not directly touches LIMIT problem, but I believe its fix will fix LIMIT query as well. (Or at least it will give more chances to verify LIMIT case)
Hello,
Ok the fix for MDEV-14433 is now pushed. I'm not able to give a 100% warranty, but it is highly likely that the issue with wrong query result is fixed by it.
Sorry to ask, but how do I get the code to test?
Can you supply instructions with as much detail as possible?
philip_38, it is currently available as source code on github, in the 10.2 branch: https://github.com/MariaDB/server/tree/10.2 .
You can download that and compile from source.
The fix will also be in the next release of MariaDB 10.2 (which should be 10.2.12). I am not sure about when that release will happen, though.
Also added https://mariadb.com/kb/en/library/myrocks-and-index-only-scans/
Closing as everything reported has been addressed.
| issue.field.resolutiondate | 2017-12-15 15:02:43.0 | 2017-12-15 15:02:43.065 |
| Fix Version/s | 10.2.12 [ 22810 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 83635 ] | MariaDB v4 [ 153121 ] |
Assigned to psergey for commenting, if not fixing.