Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.21
-
x86_64, openSUSE Tumbleweed, SLE15
Description
rocksdb.index_merge_rocksdb2 is failing in MariaDB 10.2.21 (but the fail was also seen in previous versions) for x86_64 architecture. See logs below for more information.
Logging: ./mysql-test-run rocksdb.index_merge_rocksdb2
|
vardir: /usr/share/mysql-test/var
|
Removing old var directory...
|
Creating var directory '/usr/share/mysql-test/var'...
|
Checking supported features...
|
Can't exec "patch": No such file or directory at ./mysql-test-run line 1979.
|
MariaDB Version 10.2.21-MariaDB
|
- SSL connections supported
|
Collecting tests...
|
Installing system database...
|
|
|
==============================================================================
|
|
|
TEST RESULT TIME (ms) or COMMENT
|
--------------------------------------------------------------------------
|
|
|
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019
|
worker[1] mysql-test-run: WARNING: running this script as _root_ will cause some tests to be skipped
|
rocksdb.index_merge_rocksdb2 'write_prepared' [ fail ]
|
Test ended at 2019-01-25 22:26:51
|
|
|
CURRENT_TEST: rocksdb.index_merge_rocksdb2
|
--- /usr/share/mysql-test/plugin/rocksdb/rocksdb/r/index_merge_rocksdb2.result 2018-12-31 00:29:21.000000000 +0100
|
+++ /usr/share/mysql-test/plugin/rocksdb/rocksdb/r/index_merge_rocksdb2.reject 2019-01-25 22:26:51.250437614 +0100
|
@@ -26,7 +26,7 @@
|
test.t0 analyze status OK
|
explain select * from t0 where key1 < 3 or key1 > 1020;
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t0 range i1 i1 4 NULL 2 Using index condition; Using where
|
+1 SIMPLE t0 range i1 i1 4 NULL 4 Using index condition; Using where
|
explain
|
select * from t0 where key1 < 3 or key2 > 1020;
|
id select_type table type possible_keys key key_len ref rows Extra
|
@@ -260,33 +260,33 @@
|
explain
|
select * from t0,t1 where (t0.key1=t1.key1) and ( t0.key1=3 or t0.key2=4);
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL # Using union(i1,i2); Using where
|
-1 SIMPLE t1 ref i1 i1 4 test.t0.key1 #
|
+1 SIMPLE t1 ALL i1 NULL NULL NULL #
|
+1 SIMPLE t0 ref i1,i2 i1 4 test.t1.key1 # Using where
|
explain
|
select * from t0,t1 where (t0.key1=t1.key1) and
|
(t0.key1=3 or t0.key2<4) and t1.key1=2;
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 SIMPLE t0 ref i1,i2 i1 4 const 2 Using where
|
-1 SIMPLE t1 ref i1 i1 4 const 1
|
+1 SIMPLE t1 ref i1 i1 4 const 2
|
explain select * from t0,t1 where t0.key1 = 5 and
|
(t1.key1 = t0.key1 or t1.key8 = t0.key1);
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t0 ref i1 i1 4 const 1
|
-1 SIMPLE t1 index_merge i1,i8 i1,i8 4,4 NULL 2 Using union(i1,i8); Using where; Using join buffer (flat, BNL join)
|
+1 SIMPLE t1 ALL i1,i8 NULL NULL NULL 0 Using where
|
+1 SIMPLE t0 ref i1 i1 4 const 2
|
explain select * from t0,t1 where t0.key1 < 3 and
|
(t1.key1 = t0.key1 or t1.key8 = t0.key1);
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t0 range i1 i1 4 NULL # Using index condition
|
-1 SIMPLE t1 ALL i1,i8 NULL NULL NULL # Range checked for each record (index map: 0x81)
|
+1 SIMPLE t1 ALL i1,i8 NULL NULL NULL #
|
+1 SIMPLE t0 range i1 i1 4 NULL # Using index condition; Using where; Using join buffer (flat, BNL join)
|
explain select * from t1 where key1=3 or key2=4
|
union select * from t1 where key1<4 or key3=5;
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 PRIMARY t1 index_merge i1,i2 i1,i2 4,4 NULL 4 Using union(i1,i2); Using where
|
-2 UNION t1 index_merge i1,i3 i1,i3 4,4 NULL 4 Using sort_union(i1,i3); Using where
|
+1 PRIMARY t1 ALL i1,i2 NULL NULL NULL 0 Using where
|
+2 UNION t1 ALL i1,i3 NULL NULL NULL 0 Using where
|
NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
|
explain select * from (select * from t1 where key1 = 3 or key2 =3) as Z where key8 >5;
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t1 index_merge i1,i2,i8 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where
|
+1 SIMPLE t1 range i1,i2,i8 i8 4 NULL 2 Using index condition; Using where
|
create table t3 like t0;
|
insert into t3 select * from t0;
|
alter table t3 add key9 int not null, add index i9(key9);
|
@@ -392,10 +392,10 @@
|
8704
|
explain select * from t1 WHERE cola = 'foo' AND colb = 'bar';
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t1 index_merge cola,colb cola,colb 3,3 NULL # Using intersect(cola,colb); Using where
|
+1 SIMPLE t1 ref cola,colb cola 3 const # Using index condition; Using where
|
explain select * from t1 force index(cola,colb) WHERE cola = 'foo' AND colb = 'bar';
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t1 index_merge cola,colb cola,colb 3,3 NULL # Using intersect(cola,colb); Using where
|
+1 SIMPLE t1 ref cola,colb cola 3 const # Using index condition; Using where
|
drop table t1;
|
CREATE TABLE t1(a INT);
|
INSERT INTO t1 VALUES(1);
|
@@ -517,9 +517,8 @@
|
where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1));
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY t1 ALL NULL NULL NULL NULL #
|
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func #
|
-2 MATERIALIZED t2 ALL NULL NULL NULL NULL #
|
-2 MATERIALIZED t3 ALL a,b NULL NULL NULL # Range checked for each record (index map: 0x3)
|
+1 PRIMARY t2 ALL NULL NULL NULL NULL # Using where
|
+1 PRIMARY t3 ALL a,b NULL NULL NULL # Range checked for each record (index map: 0x3); FirstMatch(t1)
|
select * from t1
|
where exists (select 1 from t2, t3
|
where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1));
|
@@ -603,7 +602,7 @@
|
64801
|
explain select key1,key2 from t1 where key1=100 and key2=100;
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t1 index_merge key1,key2 key2,key1 5,5 NULL # Using intersect(key2,key1); Using where; Using index
|
+1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL # Using intersect(key1,key2); Using where; Using index
|
select key1,key2 from t1 where key1=100 and key2=100;
|
key1 key2
|
100 100
|
@@ -789,7 +788,7 @@
|
Level Code Message
|
explain select pk from t1 where key1 = 1 and key2 = 1;
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t1 ref key1,key2 key1 5 const 2 Using where
|
+1 SIMPLE t1 index_merge key1,key2 key2,key1 4,5 NULL 1 Using intersect(key2,key1); Using where; Using index
|
select pk from t1 where key2 = 1 and key1 = 1;
|
pk
|
26
|
@@ -1057,7 +1056,7 @@
|
FROM t1
|
WHERE c = 1 AND b = 1 AND d = 1;
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t1 ref c,bd c 5 const 2 Using where
|
+1 SIMPLE t1 index_merge c,bd c,bd 5,10 NULL 1 Using intersect(c,bd); Using where; Using index
|
CREATE TABLE t2 ( a INT )
|
SELECT a
|
FROM t1
|
@@ -1110,7 +1109,7 @@
|
(((pk BETWEEN 141 AND 141) OR col_varchar_key <> 'l')
|
AND ((pk BETWEEN 141 AND 141) OR (col_int_key > 141)));
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t1 index PRIMARY,col_int_key,col_varchar_key col_varchar_key 7 NULL 2 Using where; Using index
|
+1 SIMPLE t1 index PRIMARY,col_int_key,col_varchar_key col_varchar_key 7 NULL 4 Using where; Using index
|
SELECT col_int_key
|
FROM t1
|
WHERE col_varchar_key >= 'l' OR
|
@@ -1288,7 +1287,7 @@
|
);
|
explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t1 range PRIMARY,key1 key1 12 NULL ROWS Using index condition
|
+1 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL ROWS Using where
|
select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
|
pk1 pk2 key1 key2 pktail1ok pktail2ok pktail3bad pktail4bad pktail5bad pk2copy badkey filler1 filler2
|
1 10 0 0 0 0 0 0 0 10 0 filler-data-10 filler2
|
@@ -1321,13 +1320,13 @@
|
1 SIMPLE t1 ref key1 key1 4 const ROWS Using where
|
explain select * from t1 where pk1 < 7500 and key1 = 10;
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t1 range PRIMARY,key1 key1 8 NULL ROWS Using index condition
|
+1 SIMPLE t1 range PRIMARY,key1 PRIMARY 4 NULL ROWS Using where
|
explain select * from t1 where pktail1ok=1 and key1=10;
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t1 index_merge key1,pktail1ok key1,pktail1ok 4,4 NULL 1 Using intersect(key1,pktail1ok); Using where
|
+1 SIMPLE t1 ref key1,pktail1ok key1 4 const 2 Using where
|
explain select * from t1 where pktail2ok=1 and key1=10;
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t1 index_merge key1,pktail2ok key1,pktail2ok 4,4 NULL 1 Using intersect(key1,pktail2ok); Using where
|
+1 SIMPLE t1 ref key1,pktail2ok key1 4 const 2 Using where
|
explain select * from t1 where (pktail2ok=1 and pk1< 50000) or key1=10;
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 SIMPLE t1 index_merge PRIMARY,key1,pktail2ok PRIMARY,key1 4,4 NULL ROWS Using union(PRIMARY,key1); Using where
|
@@ -1393,7 +1392,7 @@
|
WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2;
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index
|
-2 SUBQUERY t2 ref f2,f3 f2 5 const 1 Using where
|
+2 SUBQUERY t2 index_merge f2,f3 f3,f2 2,5 NULL 1 Using intersect(f3,f2); Using where; Using index
|
DROP TABLE t1,t2;
|
set global rocksdb_force_flush_memtable_now=1;
|
#
|
@@ -1412,5 +1411,5 @@
|
INSERT INTO t1 SELECT id + 16, 7, 0 FROM t1;
|
EXPLAIN SELECT SQL_NO_CACHE count(*) FROM t1 WHERE id2=7 AND id3=0;
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t1 ref id2,id3,covering_index covering_index 8 const,const 1 Using index
|
+1 SIMPLE t1 ref id2,id3,covering_index covering_index 8 const,const 2 Using index
|
DROP TABLE t1;
|
|
|
mysqltest: Result length mismatch
|
|
|
- saving '/usr/share/mysql-test/var/log/rocksdb.index_merge_rocksdb2-write_prepared/' to '/usr/share/mysql-test/var/log/rocksdb.index_merge_rocksdb2-write_prepared/'
|
|
|
Only 1 of 2 completed.
|
--------------------------------------------------------------------------
|
The servers were restarted 0 times
|
Spent 0.000 of 42 seconds executing testcases
|
|
|
Failure: Failed 1/1 tests, 0.00% were successful.
|
|
|
Failing test(s): rocksdb.index_merge_rocksdb2
|
Attachments
Activity
It is not entirely clear what is the source of changing query plans.
I assumed it is memtable statistics, so I've added "set global rocksdb_force_flush_memtable_now=1" at a few places in storage/rocksdb/mysql-test/rocksdb/include/index_merge1.inc and storage/rocksdb/mysql-test/rocksdb/include/index_merge2.inc.
This does cause query plans to change, but some rows estimates do not look like they are much better.
This doesnt look like the right approach to fix this.
Reproducible for me and monty on the current 10.2 branch
(for me it is:
commit 5e9b34191e395ced03fbbbe6aedc07b0a7293984 (HEAD -> 10.2, origin/10.2)
|
Author: Marko Mäkelä <marko.makela@mariadb.com>
|
Date: Thu Aug 29 08:21:54 2019 +0300
|
|
|
Clean up innodb.innodb-read-view
|
Note: the test includes other files:
--source include/index_merge1.inc
|
...
|
--source include/index_merge_ror.inc
|
...
|
--source include/index_merge2.inc
|
...
|
--source include/index_merge_2sweeps.inc
|
...
|
--source include/index_merge_ror_cpk.inc
|
These files are not specific to RocksDB. The upstream has them in:
mysql-test/include/index_merge1.inc
|
mysql-test/include/index_merge2.inc
|
mysql-test/include/index_merge_2sweeps.inc
|
mysql-test/include/index_merge_ror.inc
|
mysql-test/include/index_merge_ror_cpk.inc
|
MariaDB has two copies of each file, global one, and one for MyRocks:
mysql-test/include/index_merge1.inc
|
storage/rocksdb/mysql-test/rocksdb/include/index_merge1.inc
|
|
|
mysql-test/include/index_merge_ror.inc
|
storage/rocksdb/mysql-test/rocksdb/include/index_merge_ror.inc
|
|
|
./mysql-test/include/index_merge2.inc
|
./storage/rocksdb/mysql-test/rocksdb/include/index_merge2.inc
|
|
|
./mysql-test/include/index_merge_2sweeps.inc
|
./storage/rocksdb/mysql-test/rocksdb/include/index_merge_2sweeps.inc
|
|
|
./mysql-test/include/index_merge_ror_cpk.inc
|
./storage/rocksdb/mysql-test/rocksdb/include/index_merge_ror_cpk.inc
|
Diff'ing our files against upstream, I see many differences:
--- storage/rocksdb/mysql-test/rocksdb/include/index_merge1.inc 2019-06-02 20:17:39.576642580 +0300
|
+++ /home/psergey/dev-git/mysql-5.6-rocksdb/mysql-test/include/index_merge1.inc 2019-05-03 22:10:49.365581982 +0300
|
@@ -58,6 +58,10 @@
|
eval set @d=@d*2;
|
dec $1;
|
}
|
+if ($engine_type == RocksDB)
|
+{
|
+ set global rocksdb_force_flush_memtable_now=1;
|
+}
|
--enable_query_log
|
|
analyze table t0;
|
@@ -371,6 +375,10 @@
|
|
-- disable_query_log
|
-- disable_result_log
|
+if ($engine_type == RocksDB)
|
+{
|
+ set global rocksdb_force_flush_memtable_now=1;
|
+}
|
analyze table t4;
|
-- enable_result_log
|
-- enable_query_log
|
...
|
The changes were introduced into upstream by this patch:
https://github.com/facebook/mysql-5.6/commit/c3d6ffc2f96324cb0c436f1a6ed77441c8b3c16d
There were several FB/MySQL-5.6 -> MariaDB merges since that patch.
Could it occur that the dependent .inc files were not merged (as they are outside of `storage/rocksdb` directory? The merge process includes taking certain upstream files from outside `storage/rocksdb` but not necessarily these files?
Ok after taking the changes from FB upstream, the test passes most of the time.
If I run the test repeatedly, I get one failure for 20 runs:
psergey@blackbox:~/dev-git/10.2/mysql-test$ ./mysql-test-run --mem --parallel=4 --force --repeat=20
|
...
|
rocksdb.index_merge_rocksdb2 'write_committed' w1 [ pass ] 57264
|
rocksdb.index_merge_rocksdb2 'write_prepared' w2 [ pass ] 60034
|
rocksdb.index_merge_rocksdb2 'write_committed' w1 [ pass ] 56324
|
rocksdb.index_merge_rocksdb2 'write_prepared' w2 [ pass ] 60054
|
rocksdb.index_merge_rocksdb2 'write_committed' w1 [ pass ] 57217
|
rocksdb.index_merge_rocksdb2 'write_prepared' w2 [ pass ] 59879
|
rocksdb.index_merge_rocksdb2 'write_committed' w1 [ pass ] 56150
|
rocksdb.index_merge_rocksdb2 'write_prepared' w2 [ pass ] 60750
|
rocksdb.index_merge_rocksdb2 'write_committed' w1 [ pass ] 56759
|
rocksdb.index_merge_rocksdb2 'write_prepared' w2 [ pass ] 61263
|
rocksdb.index_merge_rocksdb2 'write_committed' w1 [ pass ] 56962
|
rocksdb.index_merge_rocksdb2 'write_prepared' w2 [ pass ] 61247
|
rocksdb.index_merge_rocksdb2 'write_committed' w1 [ pass ] 56937
|
rocksdb.index_merge_rocksdb2 'write_prepared' w2 [ pass ] 61699
|
rocksdb.index_merge_rocksdb2 'write_committed' w1 [ pass ] 57601
|
rocksdb.index_merge_rocksdb2 'write_prepared' w2 [ pass ] 62886
|
rocksdb.index_merge_rocksdb2 'write_committed' w1 [ pass ] 57110
|
rocksdb.index_merge_rocksdb2 'write_prepared' w2 [ pass ] 63536
|
...
rocksdb.index_merge_rocksdb2 'write_committed' w1 [ pass ] 57499
|
rocksdb.index_merge_rocksdb2 'write_prepared' w2 [ fail ]
|
Test ended at 2019-08-29 19:47:24
|
CURRENT_TEST: rocksdb.index_merge_rocksdb2
|
--- /home/psergey/dev-git/10.2/storage/rocksdb/mysql-test/rocksdb/r/index_merge_rocksdb2.result 2019-08-29 18:11:21.290143722 +0300
|
+++ /home/psergey/dev-git/10.2/storage/rocksdb/mysql-test/rocksdb/r/index_merge_rocksdb2.reject 2019-08-29 19:47:23.989962819 +0300
|
@@ -207,13 +207,13 @@
|
alter table t2 add index i321(key3, key2, key1);
|
explain select key3 from t2 where key1 = 100 or key2 = 100;
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t2 index_merge i1_3,i2_3 i1_3,i2_3 4,4 NULL # Using sort_union(i1_3,i2_3); Using where
|
+1 SIMPLE t2 index i1_3,i2_3 i321 12 NULL # Using where; Using index
|
explain select key3 from t2 where key1 <100 or key2 < 100;
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t2 index_merge i1_3,i2_3 i1_3,i2_3 4,4 NULL # Using sort_union(i1_3,i2_3); Using where
|
+1 SIMPLE t2 index i1_3,i2_3 i321 12 NULL # Using where; Using index
|
explain select key7 from t2 where key1 <100 or key2 < 100;
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t2 index_merge i1_3,i2_3 i1_3,i2_3 4,4 NULL # Using sort_union(i1_3,i2_3); Using where
|
+1 SIMPLE t2 ALL i1_3,i2_3 NULL NULL NULL # Using where
|
create table t4 (
|
key1a int not null,
|
key1b int not null,
|
So far, the failure has been at the same location, after the alter table t2 add index i321.
Note: Marko has encountered this test and disabled it in 10.4 and 10.3: https://github.com/mariadb/server/commit/3e6e843423595249a26b076f32ef9ab4e74ab0fd