diff options
Diffstat (limited to 'storage/rocksdb/mysql-test/rocksdb/r')
39 files changed, 3951 insertions, 153 deletions
diff --git a/storage/rocksdb/mysql-test/rocksdb/r/2pc_group_commit.result b/storage/rocksdb/mysql-test/rocksdb/r/2pc_group_commit.result index 06452a5437f..a9e80f1562e 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/2pc_group_commit.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/2pc_group_commit.result @@ -3,42 +3,87 @@ DROP DATABASE IF EXISTS mysqlslap; CREATE DATABASE mysqlslap; USE mysqlslap; CREATE TABLE t1(id BIGINT AUTO_INCREMENT, value BIGINT, PRIMARY KEY(id)) ENGINE=rocksdb; +SET @save_rocksdb_enable_2pc= @@rocksdb_enable_2pc; +SET @save_rocksdb_flush_log_at_trx_commit= @@rocksdb_flush_log_at_trx_commit; # 2PC enabled, MyRocks durability enabled -SET GLOBAL rocksdb_enable_2pc=0; +SET GLOBAL rocksdb_enable_2pc=1; SET GLOBAL rocksdb_flush_log_at_trx_commit=1; +## ## 2PC + durability + single thread -select variable_value into @c from information_schema.global_status where variable_name='rocksdb_wal_group_syncs'; -select case when variable_value-@c = 1000 then 'true' else 'false' end from information_schema.global_status where variable_name='rocksdb_wal_group_syncs'; -case when variable_value-@c = 1000 then 'true' else 'false' end -false +## +select variable_value into @b1 from information_schema.global_status where variable_name='Binlog_commits'; +select variable_value into @b2 from information_schema.global_status where variable_name='Binlog_group_commits'; +select variable_value into @b3 from information_schema.global_status where variable_name='Rocksdb_wal_synced'; +select IF(variable_value - @b1 = 1000, 'OK', 'FAIL') as Binlog_commits +from information_schema.global_status where variable_name='Binlog_commits'; +Binlog_commits +OK +select IF(variable_value - @b2 = 1000, 'OK', 'FAIL') as Binlog_group_commits +from information_schema.global_status where variable_name='Binlog_group_commits'; +Binlog_group_commits +OK +# Prepare operations sync, commits don't. We expect slightly more than 1K syncs: +select IF(variable_value - @b3 between 1000 and 1500, 'OK', 'FAIL') as Rocksdb_wal_synced +from information_schema.global_status where variable_name='Rocksdb_wal_synced'; +Rocksdb_wal_synced +OK +## ## 2PC + durability + group commit -select variable_value into @c from information_schema.global_status where variable_name='rocksdb_wal_group_syncs'; -select case when variable_value-@c > 0 and variable_value-@c < 10000 then 'true' else 'false' end from information_schema.global_status where variable_name='rocksdb_wal_group_syncs'; -case when variable_value-@c > 0 and variable_value-@c < 10000 then 'true' else 'false' end -false -# 2PC enabled, MyRocks durability disabled -SET GLOBAL rocksdb_enable_2pc=0; -SET GLOBAL rocksdb_flush_log_at_trx_commit=0; -select variable_value into @c from information_schema.global_status where variable_name='rocksdb_wal_group_syncs'; -select case when variable_value-@c = 0 then 'true' else 'false' end from information_schema.global_status where variable_name='rocksdb_wal_group_syncs'; -case when variable_value-@c = 0 then 'true' else 'false' end -true -select variable_value into @c from information_schema.global_status where variable_name='rocksdb_wal_group_syncs'; -select case when variable_value-@c = 0 then 'true' else 'false' end from information_schema.global_status where variable_name='rocksdb_wal_group_syncs'; -case when variable_value-@c = 0 then 'true' else 'false' end -true -# 2PC disabled, MyRocks durability enabled -SET GLOBAL rocksdb_enable_2pc=1; -SET GLOBAL rocksdb_flush_log_at_trx_commit=1; -select variable_value into @c from information_schema.global_status where variable_name='rocksdb_wal_group_syncs'; -select case when variable_value-@c = 0 then 'true' else 'false' end from information_schema.global_status where variable_name='rocksdb_wal_group_syncs'; -case when variable_value-@c = 0 then 'true' else 'false' end -false -select variable_value into @c from information_schema.global_status where variable_name='rocksdb_wal_group_syncs'; -select case when variable_value-@c = 0 then 'true' else 'false' end from information_schema.global_status where variable_name='rocksdb_wal_group_syncs'; -case when variable_value-@c = 0 then 'true' else 'false' end -false +## +select variable_value into @b1 from information_schema.global_status where variable_name='Binlog_commits'; +select variable_value into @b2 from information_schema.global_status where variable_name='Binlog_group_commits'; +select variable_value into @b3 from information_schema.global_status where variable_name='Rocksdb_wal_synced'; +select IF(variable_value - @b1 = 10000, 'OK', 'FAIL') as Binlog_commits +from information_schema.global_status where variable_name='Binlog_commits'; +Binlog_commits +OK +select IF(variable_value - @b2 between 100 and 5000, 'OK', 'FAIL') as Binlog_group_commits +from information_schema.global_status where variable_name='Binlog_group_commits'; +Binlog_group_commits +OK +select IF(variable_value - @b3 between 1 and 9000, 'OK', 'FAIL') +from information_schema.global_status where variable_name='Rocksdb_wal_synced'; +IF(variable_value - @b3 between 1 and 9000, 'OK', 'FAIL') +OK +## +# 2PC enabled, MyRocks durability disabled, single thread +## SET GLOBAL rocksdb_enable_2pc=1; -SET GLOBAL rocksdb_flush_log_at_trx_commit=1; +SET GLOBAL rocksdb_flush_log_at_trx_commit=0; +select variable_value into @b1 from information_schema.global_status where variable_name='Binlog_commits'; +select variable_value into @b2 from information_schema.global_status where variable_name='Binlog_group_commits'; +select variable_value into @b3 from information_schema.global_status where variable_name='Rocksdb_wal_synced'; +select IF(variable_value - @b1 = 1000, 'OK', 'FAIL') as Binlog_commits +from information_schema.global_status where variable_name='Binlog_commits'; +Binlog_commits +OK +select IF(variable_value - @b2 = 1000, 'OK', 'FAIL') as Binlog_group_commits +from information_schema.global_status where variable_name='Binlog_group_commits'; +Binlog_group_commits +OK +select IF(variable_value - @b3 < 10, 'OK', 'FAIL') +from information_schema.global_status where variable_name='Rocksdb_wal_synced'; +IF(variable_value - @b3 < 10, 'OK', 'FAIL') +OK +## +# 2PC enabled, MyRocks durability disabled, concurrent workload +## +select variable_value into @b1 from information_schema.global_status where variable_name='Binlog_commits'; +select variable_value into @b2 from information_schema.global_status where variable_name='Binlog_group_commits'; +select variable_value into @b3 from information_schema.global_status where variable_name='Rocksdb_wal_synced'; +select IF(variable_value - @b1 = 10000, 'OK', 'FAIL') as Binlog_commits +from information_schema.global_status where variable_name='Binlog_commits'; +Binlog_commits +OK +select IF(variable_value - @b2 < 8000, 'OK', 'FAIL') as Binlog_group_commits +from information_schema.global_status where variable_name='Binlog_group_commits'; +Binlog_group_commits +OK +select IF(variable_value - @b3 < 10, 'OK', 'FAIL') +from information_schema.global_status where variable_name='Rocksdb_wal_synced'; +IF(variable_value - @b3 < 10, 'OK', 'FAIL') +OK +SET GLOBAL rocksdb_enable_2pc= @save_rocksdb_enable_2pc; +SET GLOBAL rocksdb_flush_log_at_trx_commit= @save_rocksdb_flush_log_at_trx_commit; DROP TABLE t1; DROP DATABASE mysqlslap; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/add_index_inplace.result b/storage/rocksdb/mysql-test/rocksdb/r/add_index_inplace.result index b51680fa5fe..01fa9f1d35b 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/add_index_inplace.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/add_index_inplace.result @@ -289,12 +289,16 @@ set global rocksdb_bulk_load=1; connect con1,localhost,root,,; # Switch to connection con1 connection con1; -show global variables like 'rocksdb_bulk_load'; +show global variables like 'rocksdb_bulk_load%'; Variable_name Value rocksdb_bulk_load ON -show session variables like 'rocksdb_bulk_load'; +rocksdb_bulk_load_allow_unsorted OFF +rocksdb_bulk_load_size 1000 +show session variables like 'rocksdb_bulk_load%'; Variable_name Value rocksdb_bulk_load ON +rocksdb_bulk_load_allow_unsorted OFF +rocksdb_bulk_load_size 1000 CREATE TABLE t1 (i INT, j INT, PRIMARY KEY (i)) ENGINE = ROCKSDB; INSERT INTO t1 VALUES (1,1); # Disconnecting on con1 @@ -341,10 +345,11 @@ SET @prior_rocksdb_strict_collation_check= @@rocksdb_strict_collation_check; SET @prior_rocksdb_merge_buf_size = @@rocksdb_merge_buf_size; SET global rocksdb_strict_collation_check = off; SET session rocksdb_merge_combine_read_size = 566; -SET session rocksdb_merge_buf_size = 336; -show variables like '%rocksdb_bulk_load%'; +SET session rocksdb_merge_buf_size = 340; +show variables like 'rocksdb_bulk_load%'; Variable_name Value rocksdb_bulk_load OFF +rocksdb_bulk_load_allow_unsorted OFF rocksdb_bulk_load_size 1000 CREATE TABLE t1 (a VARCHAR(80)) ENGINE=RocksDB; INSERT INTO t1 (a) VALUES (REPEAT("a", 80)); diff --git a/storage/rocksdb/mysql-test/rocksdb/r/add_index_inplace_sstfilewriter.result b/storage/rocksdb/mysql-test/rocksdb/r/add_index_inplace_sstfilewriter.result index 2d1ba7ca1d8..0c3ad720194 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/add_index_inplace_sstfilewriter.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/add_index_inplace_sstfilewriter.result @@ -1,18 +1,18 @@ drop table if exists t1; CREATE TABLE t1(pk CHAR(5) PRIMARY KEY, a char(30), b char(30)) COLLATE 'latin1_bin'; set rocksdb_bulk_load=1; -set rocksdb_bulk_load_size=100000; +set rocksdb_bulk_load_size=10000; LOAD DATA INFILE <input_file> INTO TABLE t1; set rocksdb_bulk_load=0; select count(pk) from t1; count(pk) -3000000 +300000 select count(a) from t1; count(a) -3000000 +300000 select count(b) from t1; count(b) -3000000 +300000 ALTER TABLE t1 ADD INDEX kb(b), ALGORITHM=INPLACE; ALTER TABLE t1 ADD INDEX kb_copy(b), ALGORITHM=COPY; SELECT COUNT(*) as c FROM @@ -25,22 +25,22 @@ c 1 select count(*) from t1 FORCE INDEX(kb); count(*) -3000000 +300000 select count(*) from t1 FORCE INDEX(kb_copy); count(*) -3000000 +300000 select count(*) from t1 FORCE INDEX(PRIMARY); count(*) -3000000 +300000 ALTER TABLE t1 DROP INDEX kb, ALGORITHM=INPLACE; ALTER TABLE t1 DROP INDEX kb_copy, ALGORITHM=INPLACE; ALTER TABLE t1 ADD INDEX kb(b), ADD INDEX kab(a,b), ALGORITHM=INPLACE; SELECT COUNT(*) FROM t1 FORCE INDEX(kab); COUNT(*) -3000000 +300000 SELECT COUNT(*) FROM t1 FORCE INDEX(kb); COUNT(*) -3000000 +300000 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( diff --git a/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_errors.result b/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_errors.result index eced62bd043..f230b173892 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_errors.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_errors.result @@ -26,4 +26,30 @@ select @@rocksdb_bulk_load; @@rocksdb_bulk_load 0 call mtr.add_suppression('finalizing last SST file while setting bulk loading variable'); +TRUNCATE TABLE t1; +SET rocksdb_bulk_load_allow_unsorted=1; +SET rocksdb_bulk_load=1; +INSERT INTO t1 VALUES(100); +INSERT INTO t1 VALUES(101); +INSERT INTO t1 VALUES(99); +SET rocksdb_bulk_load=0; +SELECT * FROM t1; +pk +99 +100 +101 +TRUNCATE TABLE t1; +SET rocksdb_bulk_load=1; +INSERT INTO t1 VALUES(201); +INSERT INTO t1 VALUES(200); +INSERT INTO t1 VALUES(202); +INSERT INTO t1 VALUES(201); +ERROR 23000: Failed to insert the record: the key already exists +SET rocksdb_bulk_load=0; +SELECT * FROM t1; +pk +200 +201 +202 +SET rocksdb_bulk_load_allow_unsorted=DEFAULT; DROP TABLE t1; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_unsorted.result b/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_unsorted.result new file mode 100644 index 00000000000..2a7c7bd69fd --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_unsorted.result @@ -0,0 +1,103 @@ +DROP TABLE IF EXISTS t1; +SET rocksdb_bulk_load_size=3; +SET rocksdb_bulk_load_allow_unsorted=1; +CREATE TABLE t1(a INT, b INT, PRIMARY KEY(a) COMMENT "cf1"); +SET rocksdb_bulk_load=1; +SELECT * FROM t1; +a b +SET rocksdb_bulk_load=0; +SELECT * FROM t1; +a b +-3 5 +-1 3 +2 0 +4 -2 +6 -4 +DROP TABLE t1; +CREATE TABLE t1(a INT, b INT, PRIMARY KEY(a) COMMENT "cf1", KEY(b)); +SET rocksdb_bulk_load=1; +SELECT * FROM t1; +a b +6 -4 +4 -2 +2 0 +-1 3 +-3 5 +SET rocksdb_bulk_load=0; +DROP TABLE t1; +CREATE TABLE t1(a INT, b INT, PRIMARY KEY(a) COMMENT "cf1"); +CREATE TABLE t2(a INT, b INT, PRIMARY KEY(a) COMMENT "cf1"); +SET rocksdb_bulk_load=1; +INSERT INTO t1 VALUES (1,1); +INSERT INTO t2 VALUES (1,1); +SELECT * FROM t1; +a b +1 1 +INSERT INTO t1 VALUES (2,2); +SELECT * FROM t2; +a b +1 1 +SELECT * FROM t1; +a b +1 1 +SET rocksdb_bulk_load=0; +SELECT * FROM t1; +a b +1 1 +2 2 +DROP TABLE t1, t2; +CREATE TABLE t1(a INT, b INT, PRIMARY KEY(a) COMMENT "cf1"); +CREATE TABLE t2(a INT, b INT, PRIMARY KEY(b) COMMENT "cf1"); +CREATE TABLE t3(a INT, b INT, PRIMARY KEY(a) COMMENT "cf1") +PARTITION BY KEY() PARTITIONS 4; +connect other,localhost,root,,; +set session transaction isolation level repeatable read; +select * from information_schema.rocksdb_dbstats where stat_type='DB_NUM_SNAPSHOTS'; +STAT_TYPE VALUE +DB_NUM_SNAPSHOTS 0 +start transaction with consistent snapshot; +select * from information_schema.rocksdb_dbstats where stat_type='DB_NUM_SNAPSHOTS'; +STAT_TYPE VALUE +DB_NUM_SNAPSHOTS 1 +connection default; +set rocksdb_bulk_load=1; +set rocksdb_bulk_load_size=100000; +LOAD DATA INFILE <input_file> INTO TABLE t1; +LOAD DATA INFILE <input_file> INTO TABLE t2; +LOAD DATA INFILE <input_file> INTO TABLE t3; +set rocksdb_bulk_load=0; +SHOW TABLE STATUS WHERE name LIKE 't%'; +Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment +t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL +t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL +t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL partitioned +ANALYZE TABLE t1, t2, t3; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +test.t3 analyze status OK +SHOW TABLE STATUS WHERE name LIKE 't%'; +Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment +t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL +t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL +t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL partitioned +select count(a) from t1; +count(a) +5000000 +select count(b) from t1; +count(b) +5000000 +select count(a) from t2; +count(a) +5000000 +select count(b) from t2; +count(b) +5000000 +select count(a) from t3; +count(a) +5000000 +select count(b) from t3; +count(b) +5000000 +DROP TABLE t1, t2, t3; +SET rocksdb_bulk_load_allow_unsorted=0; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_unsorted_errors.result b/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_unsorted_errors.result new file mode 100644 index 00000000000..d8e5b92e897 --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_unsorted_errors.result @@ -0,0 +1,4 @@ +SET rocksdb_bulk_load=1; +SET rocksdb_bulk_load_allow_unsorted=1; +ERROR HY000: Error when executing command SET: Cannot change this setting while bulk load is enabled +SET rocksdb_bulk_load=0; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/bytes_written.result b/storage/rocksdb/mysql-test/rocksdb/r/bytes_written.result new file mode 100644 index 00000000000..d9d29e6ac69 --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/bytes_written.result @@ -0,0 +1,10 @@ +DROP TABLE IF EXISTS stats_test_table; +CREATE TABLE stats_test_table (a INT, b INT, PRIMARY KEY (a)) ENGINE=ROCKSDB; +SET GLOBAL rocksdb_perf_context_level=3; +INSERT INTO stats_test_table VALUES (7,1); +INSERT INTO stats_test_table VALUES (2,2); +SELECT io_write_bytes > 0 FROM INFORMATION_SCHEMA.TABLE_STATISTICS WHERE TABLE_NAME = "stats_test_table"; +io_write_bytes > 0 +1 +DROP TABLE stats_test_table; +SET GLOBAL rocksdb_perf_context_level=DEFAULT; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/col_opt_not_null.result b/storage/rocksdb/mysql-test/rocksdb/r/col_opt_not_null.result index 7f197f05b15..2ae965b6cfc 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/col_opt_not_null.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/col_opt_not_null.result @@ -1643,17 +1643,27 @@ r1_1 -0.9 r1_1 0.0 r1_1 0.9 r1_1 0.9 -SELECT MAX(f), MAX(f0), MAX(r1_1), MAX(f23_0), MAX(f20_3), MAX(d), MAX(d1_0), MAX(d10_10), MAX(d53), MAX(d53_10) FROM t1; -MAX(f) 9.999999680285692e37 -MAX(d) 1e81 -MAX(d10_10) 0.9999999999 -MAX(d1_0) 9 -MAX(d53) 100000000000000000000000000000000000000000000000000000 -MAX(d53_10) 10000000000000000000000000000000000000000000.0000000000 -MAX(f0) 9.999999680285692e37 -MAX(f20_3) 99999998430674940.000 -MAX(f23_0) 9.999999680285692e37 -MAX(r1_1) 0.9 +SELECT +CONCAT('', MAX(f)), +CONCAT('', MAX(f0)), +CONCAT('', MAX(r1_1)), +CONCAT('', MAX(f23_0)), +CONCAT('', MAX(f20_3)), +CONCAT('', MAX(d)), +CONCAT('', MAX(d1_0)), +CONCAT('', MAX(d10_10)), +CONCAT('', MAX(d53)), +CONCAT('', MAX(d53_10)) FROM t1; +CONCAT('', MAX(f)) 9.999999680285692e37 +CONCAT('', MAX(d)) 1e81 +CONCAT('', MAX(d10_10)) 0.9999999999 +CONCAT('', MAX(d1_0)) 9 +CONCAT('', MAX(d53)) 100000000000000000000000000000000000000000000000000000 +CONCAT('', MAX(d53_10)) 10000000000000000000000000000000000000000000.0000000000 +CONCAT('', MAX(f0)) 9.999999680285692e37 +CONCAT('', MAX(f20_3)) 99999998430674940.000 +CONCAT('', MAX(f23_0)) 9.999999680285692e37 +CONCAT('', MAX(r1_1)) 0.9 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10,pk) VALUES ( 9999999999999999999999999999999999999999999999999999999999999.9999, 9999999999999999999999999999999999999999999999999999999999999.9999, diff --git a/storage/rocksdb/mysql-test/rocksdb/r/col_opt_null.result b/storage/rocksdb/mysql-test/rocksdb/r/col_opt_null.result index fb0e2af4362..a834310417e 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/col_opt_null.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/col_opt_null.result @@ -1430,17 +1430,27 @@ r1_1 -0.9 r1_1 0.0 r1_1 0.9 r1_1 0.9 -SELECT MAX(f), MAX(f0), MAX(r1_1), MAX(f23_0), MAX(f20_3), MAX(d), MAX(d1_0), MAX(d10_10), MAX(d53), MAX(d53_10) FROM t1; -MAX(f) 9.999999680285692e37 -MAX(d) 1e81 -MAX(d10_10) 0.9999999999 -MAX(d1_0) 9 -MAX(d53) 100000000000000000000000000000000000000000000000000000 -MAX(d53_10) 10000000000000000000000000000000000000000000.0000000000 -MAX(f0) 9.999999680285692e37 -MAX(f20_3) 99999998430674940.000 -MAX(f23_0) 9.999999680285692e37 -MAX(r1_1) 0.9 +SELECT +CONCAT('', MAX(f)), +CONCAT('', MAX(f0)), +CONCAT('', MAX(r1_1)), +CONCAT('', MAX(f23_0)), +CONCAT('', MAX(f20_3)), +CONCAT('', MAX(d)), +CONCAT('', MAX(d1_0)), +CONCAT('', MAX(d10_10)), +CONCAT('', MAX(d53)), +CONCAT('', MAX(d53_10)) FROM t1; +CONCAT('', MAX(f)) 9.999999680285692e37 +CONCAT('', MAX(d)) 1e81 +CONCAT('', MAX(d10_10)) 0.9999999999 +CONCAT('', MAX(d1_0)) 9 +CONCAT('', MAX(d53)) 100000000000000000000000000000000000000000000000000000 +CONCAT('', MAX(d53_10)) 10000000000000000000000000000000000000000000.0000000000 +CONCAT('', MAX(f0)) 9.999999680285692e37 +CONCAT('', MAX(f20_3)) 99999998430674940.000 +CONCAT('', MAX(f23_0)) 9.999999680285692e37 +CONCAT('', MAX(r1_1)) 0.9 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10,pk) VALUES ( 9999999999999999999999999999999999999999999999999999999999999.9999, 9999999999999999999999999999999999999999999999999999999999999.9999, diff --git a/storage/rocksdb/mysql-test/rocksdb/r/col_opt_unsigned.result b/storage/rocksdb/mysql-test/rocksdb/r/col_opt_unsigned.result index ef85ad1c237..b931743d59a 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/col_opt_unsigned.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/col_opt_unsigned.result @@ -317,17 +317,27 @@ r1_1 0.0 r1_1 0.0 r1_1 0.9 r1_1 0.9 -SELECT MAX(f), MAX(f0), MAX(r1_1), MAX(f23_0), MAX(f20_3), MAX(d), MAX(d1_0), MAX(d10_10), MAX(d53), MAX(d53_10) FROM t1; -MAX(f) 9.999999680285692e37 -MAX(d) 1e81 -MAX(d10_10) 0.9999999999 -MAX(d1_0) 9 -MAX(d53) 100000000000000000000000000000000000000000000000000000 -MAX(d53_10) 10000000000000000000000000000000000000000000.0000000000 -MAX(f0) 9.999999680285692e37 -MAX(f20_3) 99999998430674940.000 -MAX(f23_0) 9.999999680285692e37 -MAX(r1_1) 0.9 +SELECT +CONCAT('', MAX(f)), +CONCAT('', MAX(f0)), +CONCAT('', MAX(r1_1)), +CONCAT('', MAX(f23_0)), +CONCAT('', MAX(f20_3)), +CONCAT('', MAX(d)), +CONCAT('', MAX(d1_0)), +CONCAT('', MAX(d10_10)), +CONCAT('', MAX(d53)), +CONCAT('', MAX(d53_10)) FROM t1; +CONCAT('', MAX(f)) 9.999999680285692e37 +CONCAT('', MAX(d)) 1e81 +CONCAT('', MAX(d10_10)) 0.9999999999 +CONCAT('', MAX(d1_0)) 9 +CONCAT('', MAX(d53)) 100000000000000000000000000000000000000000000000000000 +CONCAT('', MAX(d53_10)) 10000000000000000000000000000000000000000000.0000000000 +CONCAT('', MAX(f0)) 9.999999680285692e37 +CONCAT('', MAX(f20_3)) 99999998430674940.000 +CONCAT('', MAX(f23_0)) 9.999999680285692e37 +CONCAT('', MAX(r1_1)) 0.9 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10,pk) VALUES ( 9999999999999999999999999999999999999999999999999999999999999.9999, 9999999999999999999999999999999999999999999999999999999999999.9999, diff --git a/storage/rocksdb/mysql-test/rocksdb/r/col_opt_zerofill.result b/storage/rocksdb/mysql-test/rocksdb/r/col_opt_zerofill.result index 1f9bf16ecfc..f8fdfed86e7 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/col_opt_zerofill.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/col_opt_zerofill.result @@ -313,17 +313,27 @@ r1_1 0.0 r1_1 0.0 r1_1 0.9 r1_1 0.9 -SELECT MAX(f), MAX(f0), MAX(r1_1), MAX(f23_0), MAX(f20_3), MAX(d), MAX(d1_0), MAX(d10_10), MAX(d53), MAX(d53_10) FROM t1; -MAX(f) 9.999999680285692e37 -MAX(d) 1e81 -MAX(d10_10) 0.9999999999 -MAX(d1_0) 9 -MAX(d53) 100000000000000000000000000000000000000000000000000000 -MAX(d53_10) 10000000000000000000000000000000000000000000.0000000000 -MAX(f0) 9.999999680285692e37 -MAX(f20_3) 99999998430674940.000 -MAX(f23_0) 9.999999680285692e37 -MAX(r1_1) 0.9 +SELECT +CONCAT('', MAX(f)), +CONCAT('', MAX(f0)), +CONCAT('', MAX(r1_1)), +CONCAT('', MAX(f23_0)), +CONCAT('', MAX(f20_3)), +CONCAT('', MAX(d)), +CONCAT('', MAX(d1_0)), +CONCAT('', MAX(d10_10)), +CONCAT('', MAX(d53)), +CONCAT('', MAX(d53_10)) FROM t1; +CONCAT('', MAX(f)) 9.999999680285692e37 +CONCAT('', MAX(d)) 1e81 +CONCAT('', MAX(d10_10)) 0.9999999999 +CONCAT('', MAX(d1_0)) 9 +CONCAT('', MAX(d53)) 100000000000000000000000000000000000000000000000000000 +CONCAT('', MAX(d53_10)) 10000000000000000000000000000000000000000000.0000000000 +CONCAT('', MAX(f0)) 9.999999680285692e37 +CONCAT('', MAX(f20_3)) 99999998430674940.000 +CONCAT('', MAX(f23_0)) 9.999999680285692e37 +CONCAT('', MAX(r1_1)) 0.9 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10,pk) VALUES ( 9999999999999999999999999999999999999999999999999999999999999.9999, 9999999999999999999999999999999999999999999999999999999999999.9999, diff --git a/storage/rocksdb/mysql-test/rocksdb/r/covered_unpack_info_format.result b/storage/rocksdb/mysql-test/rocksdb/r/covered_unpack_info_format.result new file mode 100644 index 00000000000..195215331b8 --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/covered_unpack_info_format.result @@ -0,0 +1,73 @@ +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( +id INT, +fake_id INT, +bigfield VARCHAR(4096), +PRIMARY KEY (id), +KEY bf (bigfield(32)), +KEY fid (fake_id, bigfield(32)) +) ENGINE=rocksdb; +INSERT INTO t1 VALUES (1, 1001, REPEAT('a', 1)), +(8, 1008, REPEAT('b', 8)), +(24, 1024, REPEAT('c', 24)), +(31, 1031, REPEAT('d', 31)), +(32, 1032, REPEAT('x', 32)), +(33, 1033, REPEAT('y', 33)), +(128, 1128, REPEAT('z', 128)); +SELECT * FROM t1; +id fake_id bigfield +1 1001 a +8 1008 bbbbbbbb +24 1024 cccccccccccccccccccccccc +31 1031 ddddddddddddddddddddddddddddddd +32 1032 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +33 1033 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy +128 1128 zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz +# Eligible for optimization, access via fake_id only +SELECT id, bigfield FROM t1 FORCE INDEX(fid) WHERE fake_id = 1031; +id bigfield +31 ddddddddddddddddddddddddddddddd +include/assert.inc [Eligible for optimization, access via fake_id only: 2 rocksdb_covered_secondary_key_lookups] +# Not eligible for optimization, access via fake_id of big row. +SELECT id, bigfield FROM t1 FORCE INDEX(fid) WHERE fake_id = 1033; +id bigfield +33 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy +include/assert.inc [Not eligible for optimization, access via fake_id of big row.: 0 rocksdb_covered_secondary_key_lookups] +DROP TABLE t1; +set session debug= '+d,MYROCKS_NO_COVERED_BITMAP_FORMAT'; +CREATE TABLE t1 ( +id INT, +fake_id INT, +bigfield VARCHAR(4096), +PRIMARY KEY (id), +KEY bf (bigfield(32)), +KEY fid (fake_id, bigfield(32)) +) ENGINE=rocksdb; +set session debug= '-d,MYROCKS_NO_COVERED_BITMAP_FORMAT'; +INSERT INTO t1 VALUES (1, 1001, REPEAT('a', 1)), +(8, 1008, REPEAT('b', 8)), +(24, 1024, REPEAT('c', 24)), +(31, 1031, REPEAT('d', 31)), +(32, 1032, REPEAT('x', 32)), +(33, 1033, REPEAT('y', 33)), +(128, 1128, REPEAT('z', 128)); +SELECT * FROM t1; +id fake_id bigfield +1 1001 a +8 1008 bbbbbbbb +24 1024 cccccccccccccccccccccccc +31 1031 ddddddddddddddddddddddddddddddd +32 1032 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +33 1033 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy +128 1128 zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz +# No longer eligible for optimization since no covered bitmap was stored. +SELECT id, bigfield FROM t1 FORCE INDEX(fid) WHERE fake_id = 1031; +id bigfield +31 ddddddddddddddddddddddddddddddd +include/assert.inc [No longer eligible for optimization since no covered bitmap was stored.: 0 rocksdb_covered_secondary_key_lookups] +# Not eligible for optimization. +SELECT id, bigfield FROM t1 FORCE INDEX(fid) WHERE fake_id = 1033; +id bigfield +33 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy +include/assert.inc [Not eligible for optimization.: 0 rocksdb_covered_secondary_key_lookups] +DROP TABLE t1; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/ddl_high_priority.result b/storage/rocksdb/mysql-test/rocksdb/r/ddl_high_priority.result new file mode 100644 index 00000000000..39130475349 --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/ddl_high_priority.result @@ -0,0 +1,1009 @@ +## +## Using the system variable high_priority_ddl" +## +create user test_user1@localhost; +grant all on test to test_user1@localhost; +create user test_user2@localhost; +grant all on test to test_user2@localhost; + +## Test parameters: +## use_sys_var = 1; +## con_block = con1 +## con_kill = default +## cmd = alter table t1 modify i bigint; +## high_priority_cmd = alter high_priority table t1 modify i bigint; +## should_kill = 1 +## recreate_table = 1 +## throw_error = 1 + +drop table if exists t1; +create table t1 (i int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 +insert into t1 values (1), (2), (3); +connection: con1 +lock tables t1 read;; +connection: default +set lock_wait_timeout = 0.02; +set high_priority_lock_wait_timeout = 0.02; +describe t1; +Field Type Null Key Default Extra +i int(11) YES NULL +connection: default (for show processlist) +# both con1 and default exist +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user1 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +connection: default +alter table t1 modify i bigint;; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +set high_priority_ddl = 1; +select @@high_priority_ddl; +@@high_priority_ddl +1 +lock tables t1 write; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +alter table t1 modify i bigint;; +set high_priority_ddl = 0; +connection: default (for show processlist) +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 + +## Test parameters: +## use_sys_var = 1; +## con_block = con1 +## con_kill = default +## cmd = alter table t1 rename t1_new; +## high_priority_cmd = alter high_priority table t1 rename t1_new; +## should_kill = 1 +## recreate_table = 1 +## throw_error = 1 + +drop table if exists t1; +create table t1 (i int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 +insert into t1 values (1), (2), (3); +connection: con1 +begin; insert into t1 values (4); select i from t1;; +i +1 +2 +3 +4 +connection: default +set lock_wait_timeout = 0.02; +set high_priority_lock_wait_timeout = 0.02; +describe t1; +Field Type Null Key Default Extra +i int(11) YES NULL +connection: default (for show processlist) +# both con1 and default exist +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user1 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +connection: default +alter table t1 rename t1_new;; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +set high_priority_ddl = 1; +select @@high_priority_ddl; +@@high_priority_ddl +1 +lock tables t1 write; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +alter table t1 rename t1_new;; +set high_priority_ddl = 0; +connection: default (for show processlist) +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +select * from t1_new; +i +1 +2 +3 +drop table t1_new; + +## Test parameters: +## use_sys_var = 1; +## con_block = con1 +## con_kill = default +## cmd = drop table t1; +## high_priority_cmd = drop high_priority table t1; +## should_kill = 0 +## recreate_table = 1 +## throw_error = 1 + +drop table if exists t1; +create table t1 (i int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 +insert into t1 values (1), (2), (3); +connection: con1 +lock tables t1 write;; +connection: default +set lock_wait_timeout = 0.02; +set high_priority_lock_wait_timeout = 0.02; +describe t1; +Field Type Null Key Default Extra +i int(11) YES NULL +connection: default (for show processlist) +# both con1 and default exist +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user1 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +connection: default +drop table t1;; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +set high_priority_ddl = 1; +select @@high_priority_ddl; +@@high_priority_ddl +1 +lock tables t1 write; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +drop table t1;; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +set high_priority_ddl = 0; +connection: default (for show processlist) +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user1 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 + +## Test parameters: +## use_sys_var = 1; +## con_block = con1 +## con_kill = default +## cmd = drop table t1; +## high_priority_cmd = drop high_priority table t1; +## should_kill = 1 +## recreate_table = 1 +## throw_error = 1 + +drop table if exists t1; +create table t1 (i int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 +insert into t1 values (1), (2), (3); +connection: con1 +lock tables t1 read; begin; insert into t1 values (4);; +connection: default +set lock_wait_timeout = 0.02; +set high_priority_lock_wait_timeout = 0.02; +describe t1; +Field Type Null Key Default Extra +i int(11) YES NULL +connection: default (for show processlist) +# both con1 and default exist +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user1 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +connection: default +drop table t1;; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +set high_priority_ddl = 1; +select @@high_priority_ddl; +@@high_priority_ddl +1 +lock tables t1 write; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +drop table t1;; +set high_priority_ddl = 0; +connection: default (for show processlist) +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 + +## Test parameters: +## use_sys_var = 1; +## con_block = con1 +## con_kill = con2 +## cmd = alter table t1 modify i bigint; +## high_priority_cmd = alter high_priority table t1 modify i bigint; +## should_kill = 0 +## recreate_table = 1 +## throw_error = 1 + +drop table if exists t1; +create table t1 (i int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 +insert into t1 values (1), (2), (3); +connection: con1 +lock tables t1 read;; +connection: con2 +set lock_wait_timeout = 0.02; +set high_priority_lock_wait_timeout = 0.02; +describe t1; +Field Type Null Key Default Extra +i int(11) YES NULL +connection: default (for show processlist) +# both con1 and con2 exist +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user2 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user1 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +connection: con2 +alter table t1 modify i bigint;; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +set high_priority_ddl = 1; +select @@high_priority_ddl; +@@high_priority_ddl +1 +lock tables t1 write; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +alter table t1 modify i bigint;; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +set high_priority_ddl = 0; +connection: default (for show processlist) +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user2 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user1 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 + +## Test parameters: +## use_sys_var = 1; +## con_block = con1 +## con_kill = default +## cmd = create index idx1 on t1 (i); +## high_priority_cmd = create high_priority index idx1 on t1 (i); +## should_kill = 1 +## recreate_table = 1 +## throw_error = 1 + +drop table if exists t1; +create table t1 (i int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 +insert into t1 values (1), (2), (3); +connection: con1 +lock tables t1 read;; +connection: default +set lock_wait_timeout = 0.02; +set high_priority_lock_wait_timeout = 0.02; +describe t1; +Field Type Null Key Default Extra +i int(11) YES NULL +connection: default (for show processlist) +# both con1 and default exist +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user1 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +connection: default +create index idx1 on t1 (i);; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +set high_priority_ddl = 1; +select @@high_priority_ddl; +@@high_priority_ddl +1 +lock tables t1 write; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +create index idx1 on t1 (i);; +set high_priority_ddl = 0; +connection: default (for show processlist) +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 + +## Test parameters: +## use_sys_var = 1; +## con_block = con1 +## con_kill = default +## cmd = drop index idx1 on t1; +## high_priority_cmd = drop high_priority index idx1 on t1; +## should_kill = 1 +## recreate_table = 0 +## throw_error = 1 + +connection: con1 +lock tables t1 read;; +connection: default +set lock_wait_timeout = 0.02; +set high_priority_lock_wait_timeout = 0.02; +describe t1; +Field Type Null Key Default Extra +i int(11) YES MUL NULL +connection: default (for show processlist) +# both con1 and default exist +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user1 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +connection: default +drop index idx1 on t1;; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +set high_priority_ddl = 1; +select @@high_priority_ddl; +@@high_priority_ddl +1 +lock tables t1 write; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +drop index idx1 on t1;; +set high_priority_ddl = 0; +connection: default (for show processlist) +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 + +## Test parameters: +## use_sys_var = 1; +## con_block = con1 +## con_kill = default +## cmd = truncate t1; +## high_priority_cmd = truncate high_priority t1; +## should_kill = 1 +## recreate_table = 1 +## throw_error = 1 + +drop table if exists t1; +create table t1 (i int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 +insert into t1 values (1), (2), (3); +connection: con1 +lock tables t1 read;; +connection: default +set lock_wait_timeout = 0.02; +set high_priority_lock_wait_timeout = 0.02; +describe t1; +Field Type Null Key Default Extra +i int(11) YES NULL +connection: default (for show processlist) +# both con1 and default exist +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user1 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +connection: default +truncate t1;; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +set high_priority_ddl = 1; +select @@high_priority_ddl; +@@high_priority_ddl +1 +lock tables t1 write; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +truncate t1;; +set high_priority_ddl = 0; +connection: default (for show processlist) +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 + +## Test parameters: +## use_sys_var = 1; +## con_block = con1 +## con_kill = default +## cmd = create trigger ins_sum before insert on t1 for each row set @sum = @sum + new.i; +## high_priority_cmd = create high_priority trigger ins_sum before insert on t1 for each row set @sum = @sum + new.i; +## should_kill = 1 +## recreate_table = 1 +## throw_error = 1 + +drop table if exists t1; +create table t1 (i int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 +insert into t1 values (1), (2), (3); +connection: con1 +lock tables t1 read;; +connection: default +set lock_wait_timeout = 0.02; +set high_priority_lock_wait_timeout = 0.02; +describe t1; +Field Type Null Key Default Extra +i int(11) YES NULL +connection: default (for show processlist) +# both con1 and default exist +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user1 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +connection: default +create trigger ins_sum before insert on t1 for each row set @sum = @sum + new.i;; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +set high_priority_ddl = 1; +select @@high_priority_ddl; +@@high_priority_ddl +1 +lock tables t1 write; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +create trigger ins_sum before insert on t1 for each row set @sum = @sum + new.i;; +set high_priority_ddl = 0; +connection: default (for show processlist) +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 + +## Test parameters: +## use_sys_var = 1; +## con_block = con1 +## con_kill = default +## cmd = drop trigger ins_sum; +## high_priority_cmd = drop high_priority trigger ins_sum; +## should_kill = 1 +## recreate_table = 0 +## throw_error = 1 + +connection: con1 +lock tables t1 read;; +connection: default +set lock_wait_timeout = 0.02; +set high_priority_lock_wait_timeout = 0.02; +describe t1; +Field Type Null Key Default Extra +i int(11) YES NULL +connection: default (for show processlist) +# both con1 and default exist +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user1 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +connection: default +drop trigger ins_sum;; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +set high_priority_ddl = 1; +select @@high_priority_ddl; +@@high_priority_ddl +1 +lock tables t1 write; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +drop trigger ins_sum;; +set high_priority_ddl = 0; +connection: default (for show processlist) +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 + +## Test parameters: +## use_sys_var = 1; +## con_block = con1 +## con_kill = default +## cmd = optimize table t1; +## high_priority_cmd = optimize high_priority table t1; +## should_kill = 1 +## recreate_table = 1 +## throw_error = 0 + +drop table if exists t1; +create table t1 (i int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 +insert into t1 values (1), (2), (3); +connection: con1 +lock tables t1 read;; +connection: default +set lock_wait_timeout = 0.02; +set high_priority_lock_wait_timeout = 0.02; +describe t1; +Field Type Null Key Default Extra +i int(11) YES NULL +connection: default (for show processlist) +# both con1 and default exist +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user1 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +connection: default +optimize table t1;; +Table Op Msg_type Msg_text +test.t1 optimize Error Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +test.t1 optimize status Operation failed +set high_priority_ddl = 1; +select @@high_priority_ddl; +@@high_priority_ddl +1 +lock tables t1 write; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +optimize table t1;; +Table Op Msg_type Msg_text +test.t1 optimize status OK +set high_priority_ddl = 0; +connection: default (for show processlist) +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +drop user test_user1@localhost; +drop user test_user2@localhost; +drop table if exists t1; +## +## Using HIGH_PRIORITY syntax +## +create user test_user1@localhost; +grant all on test to test_user1@localhost; +create user test_user2@localhost; +grant all on test to test_user2@localhost; + +## Test parameters: +## use_sys_var = 0; +## con_block = con1 +## con_kill = default +## cmd = alter table t1 modify i bigint; +## high_priority_cmd = alter high_priority table t1 modify i bigint; +## should_kill = 1 +## recreate_table = 1 +## throw_error = 1 + +drop table if exists t1; +create table t1 (i int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 +insert into t1 values (1), (2), (3); +connection: con1 +lock tables t1 read;; +connection: default +set lock_wait_timeout = 0.02; +set high_priority_lock_wait_timeout = 0.02; +describe t1; +Field Type Null Key Default Extra +i int(11) YES NULL +connection: default (for show processlist) +# both con1 and default exist +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user1 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +connection: default +alter table t1 modify i bigint;; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +alter high_priority table t1 modify i bigint;; +connection: default (for show processlist) +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 + +## Test parameters: +## use_sys_var = 0; +## con_block = con1 +## con_kill = default +## cmd = alter table t1 rename t1_new; +## high_priority_cmd = alter high_priority table t1 rename t1_new; +## should_kill = 1 +## recreate_table = 1 +## throw_error = 1 + +drop table if exists t1; +create table t1 (i int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 +insert into t1 values (1), (2), (3); +connection: con1 +begin; insert into t1 values (4); select i from t1;; +i +1 +2 +3 +4 +connection: default +set lock_wait_timeout = 0.02; +set high_priority_lock_wait_timeout = 0.02; +describe t1; +Field Type Null Key Default Extra +i int(11) YES NULL +connection: default (for show processlist) +# both con1 and default exist +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user1 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +connection: default +alter table t1 rename t1_new;; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +alter high_priority table t1 rename t1_new;; +connection: default (for show processlist) +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +select * from t1_new; +i +1 +2 +3 +drop table t1_new; + +## Test parameters: +## use_sys_var = 0; +## con_block = con1 +## con_kill = default +## cmd = drop table t1; +## high_priority_cmd = drop high_priority table t1; +## should_kill = 0 +## recreate_table = 1 +## throw_error = 1 + +drop table if exists t1; +create table t1 (i int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 +insert into t1 values (1), (2), (3); +connection: con1 +lock tables t1 write;; +connection: default +set lock_wait_timeout = 0.02; +set high_priority_lock_wait_timeout = 0.02; +describe t1; +Field Type Null Key Default Extra +i int(11) YES NULL +connection: default (for show processlist) +# both con1 and default exist +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user1 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +connection: default +drop table t1;; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +drop high_priority table t1;; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +connection: default (for show processlist) +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user1 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 + +## Test parameters: +## use_sys_var = 0; +## con_block = con1 +## con_kill = default +## cmd = drop table t1; +## high_priority_cmd = drop high_priority table t1; +## should_kill = 1 +## recreate_table = 1 +## throw_error = 1 + +drop table if exists t1; +create table t1 (i int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 +insert into t1 values (1), (2), (3); +connection: con1 +lock tables t1 read; begin; insert into t1 values (4);; +connection: default +set lock_wait_timeout = 0.02; +set high_priority_lock_wait_timeout = 0.02; +describe t1; +Field Type Null Key Default Extra +i int(11) YES NULL +connection: default (for show processlist) +# both con1 and default exist +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user1 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +connection: default +drop table t1;; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +drop high_priority table t1;; +connection: default (for show processlist) +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 + +## Test parameters: +## use_sys_var = 0; +## con_block = con1 +## con_kill = con2 +## cmd = alter table t1 modify i bigint; +## high_priority_cmd = alter high_priority table t1 modify i bigint; +## should_kill = 0 +## recreate_table = 1 +## throw_error = 1 + +drop table if exists t1; +create table t1 (i int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 +insert into t1 values (1), (2), (3); +connection: con1 +lock tables t1 read;; +connection: con2 +set lock_wait_timeout = 0.02; +set high_priority_lock_wait_timeout = 0.02; +describe t1; +Field Type Null Key Default Extra +i int(11) YES NULL +connection: default (for show processlist) +# both con1 and con2 exist +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user2 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user1 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +connection: con2 +alter table t1 modify i bigint;; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +alter high_priority table t1 modify i bigint;; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +connection: default (for show processlist) +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user2 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user1 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 + +## Test parameters: +## use_sys_var = 0; +## con_block = con1 +## con_kill = default +## cmd = create index idx1 on t1 (i); +## high_priority_cmd = create high_priority index idx1 on t1 (i); +## should_kill = 1 +## recreate_table = 1 +## throw_error = 1 + +drop table if exists t1; +create table t1 (i int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 +insert into t1 values (1), (2), (3); +connection: con1 +lock tables t1 read;; +connection: default +set lock_wait_timeout = 0.02; +set high_priority_lock_wait_timeout = 0.02; +describe t1; +Field Type Null Key Default Extra +i int(11) YES NULL +connection: default (for show processlist) +# both con1 and default exist +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user1 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +connection: default +create index idx1 on t1 (i);; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +create high_priority index idx1 on t1 (i);; +connection: default (for show processlist) +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 + +## Test parameters: +## use_sys_var = 0; +## con_block = con1 +## con_kill = default +## cmd = drop index idx1 on t1; +## high_priority_cmd = drop high_priority index idx1 on t1; +## should_kill = 1 +## recreate_table = 0 +## throw_error = 1 + +connection: con1 +lock tables t1 read;; +connection: default +set lock_wait_timeout = 0.02; +set high_priority_lock_wait_timeout = 0.02; +describe t1; +Field Type Null Key Default Extra +i int(11) YES MUL NULL +connection: default (for show processlist) +# both con1 and default exist +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user1 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +connection: default +drop index idx1 on t1;; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +drop high_priority index idx1 on t1;; +connection: default (for show processlist) +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 + +## Test parameters: +## use_sys_var = 0; +## con_block = con1 +## con_kill = default +## cmd = truncate t1; +## high_priority_cmd = truncate high_priority t1; +## should_kill = 1 +## recreate_table = 1 +## throw_error = 1 + +drop table if exists t1; +create table t1 (i int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 +insert into t1 values (1), (2), (3); +connection: con1 +lock tables t1 read;; +connection: default +set lock_wait_timeout = 0.02; +set high_priority_lock_wait_timeout = 0.02; +describe t1; +Field Type Null Key Default Extra +i int(11) YES NULL +connection: default (for show processlist) +# both con1 and default exist +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user1 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +connection: default +truncate t1;; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +truncate high_priority t1;; +connection: default (for show processlist) +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 + +## Test parameters: +## use_sys_var = 0; +## con_block = con1 +## con_kill = default +## cmd = create trigger ins_sum before insert on t1 for each row set @sum = @sum + new.i; +## high_priority_cmd = create high_priority trigger ins_sum before insert on t1 for each row set @sum = @sum + new.i; +## should_kill = 1 +## recreate_table = 1 +## throw_error = 1 + +drop table if exists t1; +create table t1 (i int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 +insert into t1 values (1), (2), (3); +connection: con1 +lock tables t1 read;; +connection: default +set lock_wait_timeout = 0.02; +set high_priority_lock_wait_timeout = 0.02; +describe t1; +Field Type Null Key Default Extra +i int(11) YES NULL +connection: default (for show processlist) +# both con1 and default exist +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user1 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +connection: default +create trigger ins_sum before insert on t1 for each row set @sum = @sum + new.i;; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +create high_priority trigger ins_sum before insert on t1 for each row set @sum = @sum + new.i;; +connection: default (for show processlist) +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 + +## Test parameters: +## use_sys_var = 0; +## con_block = con1 +## con_kill = default +## cmd = drop trigger ins_sum; +## high_priority_cmd = drop high_priority trigger ins_sum; +## should_kill = 1 +## recreate_table = 0 +## throw_error = 1 + +connection: con1 +lock tables t1 read;; +connection: default +set lock_wait_timeout = 0.02; +set high_priority_lock_wait_timeout = 0.02; +describe t1; +Field Type Null Key Default Extra +i int(11) YES NULL +connection: default (for show processlist) +# both con1 and default exist +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user1 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +connection: default +drop trigger ins_sum;; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +drop high_priority trigger ins_sum;; +connection: default (for show processlist) +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 + +## Test parameters: +## use_sys_var = 0; +## con_block = con1 +## con_kill = default +## cmd = optimize table t1; +## high_priority_cmd = optimize high_priority table t1; +## should_kill = 1 +## recreate_table = 1 +## throw_error = 0 + +drop table if exists t1; +create table t1 (i int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 +insert into t1 values (1), (2), (3); +connection: con1 +lock tables t1 read;; +connection: default +set lock_wait_timeout = 0.02; +set high_priority_lock_wait_timeout = 0.02; +describe t1; +Field Type Null Key Default Extra +i int(11) YES NULL +connection: default (for show processlist) +# both con1 and default exist +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +<Id> test_user1 <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +connection: default +optimize table t1;; +Table Op Msg_type Msg_text +test.t1 optimize Error Lock wait timeout exceeded; try restarting transaction: Timeout on table metadata: test.t1 +test.t1 optimize status Operation failed +optimize high_priority table t1;; +Table Op Msg_type Msg_text +test.t1 optimize status OK +connection: default (for show processlist) +show processlist; +Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id +<Id> root <Host> test <Command> <Time> <State> <Info> <RExam> <RSent> <TID> 0 +drop user test_user1@localhost; +drop user test_user2@localhost; +drop table if exists t1; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/deadlock_tracking.result b/storage/rocksdb/mysql-test/rocksdb/r/deadlock_tracking.result new file mode 100644 index 00000000000..d7cb89becb7 --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/deadlock_tracking.result @@ -0,0 +1,490 @@ +set @prior_lock_wait_timeout = @@rocksdb_lock_wait_timeout; +set @prior_deadlock_detect = @@rocksdb_deadlock_detect; +set @prior_max_latest_deadlocks = @@rocksdb_max_latest_deadlocks; +set global rocksdb_deadlock_detect = on; +set global rocksdb_lock_wait_timeout = 10000; +# Clears deadlock buffer of any prior deadlocks. +set global rocksdb_max_latest_deadlocks = 0; +set global rocksdb_max_latest_deadlocks = @prior_max_latest_deadlocks; +create table t (i int primary key) engine=rocksdb; +insert into t values (1), (2), (3); +show engine rocksdb transaction status; +Type Name Status +rocksdb +============================================================ +TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT +============================================================ +--------- +SNAPSHOTS +--------- +LIST OF SNAPSHOTS FOR EACH SESSION: +----------LATEST DETECTED DEADLOCKS---------- +----------------------------------------- +END OF ROCKSDB TRANSACTION MONITOR OUTPUT +========================================= + +Deadlock #1 +begin; +select * from t where i=1 for update; +i +1 +begin; +select * from t where i=2 for update; +i +2 +select * from t where i=2 for update; +select * from t where i=1 for update; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +rollback; +i +2 +rollback; +show engine rocksdb transaction status; +Type Name Status +rocksdb +============================================================ +TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT +============================================================ +--------- +SNAPSHOTS +--------- +LIST OF SNAPSHOTS FOR EACH SESSION: +----------LATEST DETECTED DEADLOCKS---------- + +*** DEADLOCK PATH +========================================= +TXN_ID +COLUMN FAMILY NAME: default +KEY +LOCK TYPE: EXCLUSIVE +INDEX NAME: PRIMARY +TABLE NAME: test.t +---------------WAITING FOR--------------- +TXN_ID +COLUMN FAMILY NAME: default +KEY +LOCK TYPE: EXCLUSIVE +INDEX NAME: PRIMARY +TABLE NAME: test.t +---------------WAITING FOR--------------- +TXN_ID +COLUMN FAMILY NAME: default +KEY +LOCK TYPE: EXCLUSIVE +INDEX NAME: PRIMARY +TABLE NAME: test.t + +--------TXN_ID GOT DEADLOCK--------- +----------------------------------------- +END OF ROCKSDB TRANSACTION MONITOR OUTPUT +========================================= + +Deadlock #2 +begin; +select * from t where i=1 for update; +i +1 +begin; +select * from t where i=2 for update; +i +2 +select * from t where i=2 for update; +select * from t where i=1 for update; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +rollback; +i +2 +rollback; +show engine rocksdb transaction status; +Type Name Status +rocksdb +============================================================ +TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT +============================================================ +--------- +SNAPSHOTS +--------- +LIST OF SNAPSHOTS FOR EACH SESSION: +----------LATEST DETECTED DEADLOCKS---------- + +*** DEADLOCK PATH +========================================= +TXN_ID +COLUMN FAMILY NAME: default +KEY +LOCK TYPE: EXCLUSIVE +INDEX NAME: PRIMARY +TABLE NAME: test.t +---------------WAITING FOR--------------- +TXN_ID +COLUMN FAMILY NAME: default +KEY +LOCK TYPE: EXCLUSIVE +INDEX NAME: PRIMARY +TABLE NAME: test.t +---------------WAITING FOR--------------- +TXN_ID +COLUMN FAMILY NAME: default +KEY +LOCK TYPE: EXCLUSIVE +INDEX NAME: PRIMARY +TABLE NAME: test.t + +--------TXN_ID GOT DEADLOCK--------- + +*** DEADLOCK PATH +========================================= +TXN_ID +COLUMN FAMILY NAME: default +KEY +LOCK TYPE: EXCLUSIVE +INDEX NAME: PRIMARY +TABLE NAME: test.t +---------------WAITING FOR--------------- +TXN_ID +COLUMN FAMILY NAME: default +KEY +LOCK TYPE: EXCLUSIVE +INDEX NAME: PRIMARY +TABLE NAME: test.t +---------------WAITING FOR--------------- +TXN_ID +COLUMN FAMILY NAME: default +KEY +LOCK TYPE: EXCLUSIVE +INDEX NAME: PRIMARY +TABLE NAME: test.t + +--------TXN_ID GOT DEADLOCK--------- +----------------------------------------- +END OF ROCKSDB TRANSACTION MONITOR OUTPUT +========================================= + +set global rocksdb_max_latest_deadlocks = 10; +Deadlock #3 +begin; +select * from t where i=1 for update; +i +1 +begin; +select * from t where i=2 for update; +i +2 +select * from t where i=2 for update; +select * from t where i=1 for update; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +rollback; +i +2 +rollback; +show engine rocksdb transaction status; +Type Name Status +rocksdb +============================================================ +TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT +============================================================ +--------- +SNAPSHOTS +--------- +LIST OF SNAPSHOTS FOR EACH SESSION: +----------LATEST DETECTED DEADLOCKS---------- + +*** DEADLOCK PATH +========================================= +TXN_ID +COLUMN FAMILY NAME: default +KEY +LOCK TYPE: EXCLUSIVE +INDEX NAME: PRIMARY +TABLE NAME: test.t +---------------WAITING FOR--------------- +TXN_ID +COLUMN FAMILY NAME: default +KEY +LOCK TYPE: EXCLUSIVE +INDEX NAME: PRIMARY +TABLE NAME: test.t +---------------WAITING FOR--------------- +TXN_ID +COLUMN FAMILY NAME: default +KEY +LOCK TYPE: EXCLUSIVE +INDEX NAME: PRIMARY +TABLE NAME: test.t + +--------TXN_ID GOT DEADLOCK--------- + +*** DEADLOCK PATH +========================================= +TXN_ID +COLUMN FAMILY NAME: default +KEY +LOCK TYPE: EXCLUSIVE +INDEX NAME: PRIMARY +TABLE NAME: test.t +---------------WAITING FOR--------------- +TXN_ID +COLUMN FAMILY NAME: default +KEY +LOCK TYPE: EXCLUSIVE +INDEX NAME: PRIMARY +TABLE NAME: test.t +---------------WAITING FOR--------------- +TXN_ID +COLUMN FAMILY NAME: default +KEY +LOCK TYPE: EXCLUSIVE +INDEX NAME: PRIMARY +TABLE NAME: test.t + +--------TXN_ID GOT DEADLOCK--------- + +*** DEADLOCK PATH +========================================= +TXN_ID +COLUMN FAMILY NAME: default +KEY +LOCK TYPE: EXCLUSIVE +INDEX NAME: PRIMARY +TABLE NAME: test.t +---------------WAITING FOR--------------- +TXN_ID +COLUMN FAMILY NAME: default +KEY +LOCK TYPE: EXCLUSIVE +INDEX NAME: PRIMARY +TABLE NAME: test.t +---------------WAITING FOR--------------- +TXN_ID +COLUMN FAMILY NAME: default +KEY +LOCK TYPE: EXCLUSIVE +INDEX NAME: PRIMARY +TABLE NAME: test.t + +--------TXN_ID GOT DEADLOCK--------- +----------------------------------------- +END OF ROCKSDB TRANSACTION MONITOR OUTPUT +========================================= + +set global rocksdb_max_latest_deadlocks = 1; +show engine rocksdb transaction status; +Type Name Status +rocksdb +============================================================ +TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT +============================================================ +--------- +SNAPSHOTS +--------- +LIST OF SNAPSHOTS FOR EACH SESSION: +----------LATEST DETECTED DEADLOCKS---------- + +*** DEADLOCK PATH +========================================= +TXN_ID +COLUMN FAMILY NAME: default +KEY +LOCK TYPE: EXCLUSIVE +INDEX NAME: PRIMARY +TABLE NAME: test.t +---------------WAITING FOR--------------- +TXN_ID +COLUMN FAMILY NAME: default +KEY +LOCK TYPE: EXCLUSIVE +INDEX NAME: PRIMARY +TABLE NAME: test.t +---------------WAITING FOR--------------- +TXN_ID +COLUMN FAMILY NAME: default +KEY +LOCK TYPE: EXCLUSIVE +INDEX NAME: PRIMARY +TABLE NAME: test.t + +--------TXN_ID GOT DEADLOCK--------- +----------------------------------------- +END OF ROCKSDB TRANSACTION MONITOR OUTPUT +========================================= + +set rocksdb_deadlock_detect_depth = 2; +Deadlock #4 +begin; +select * from t where i=1 for update; +i +1 +begin; +select * from t where i=2 for update; +i +2 +begin; +select * from t where i=3 for update; +i +3 +select * from t where i=2 for update; +select * from t where i=3 for update; +select * from t where i=1 for update; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +rollback; +i +3 +rollback; +i +2 +rollback; +set global rocksdb_max_latest_deadlocks = 5; +show engine rocksdb transaction status; +Type Name Status +rocksdb +============================================================ +TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT +============================================================ +--------- +SNAPSHOTS +--------- +LIST OF SNAPSHOTS FOR EACH SESSION: +----------LATEST DETECTED DEADLOCKS---------- + +-------DEADLOCK EXCEEDED MAX DEPTH------- +----------------------------------------- +END OF ROCKSDB TRANSACTION MONITOR OUTPUT +========================================= + +Deadlock #5 +begin; +select * from t where i=1 for update; +i +1 +begin; +select * from t where i=2 for update; +i +2 +begin; +select * from t where i=3 lock in share mode; +i +3 +select * from t where i=100 for update; +i +select * from t where i=101 for update; +i +select * from t where i=2 for update; +select * from t where i=3 lock in share mode; +i +3 +select * from t where i=200 for update; +i +select * from t where i=201 for update; +i +select * from t where i=1 lock in share mode; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +rollback; +i +2 +rollback; +rollback; +show engine rocksdb transaction status; +Type Name Status +rocksdb +============================================================ +TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT +============================================================ +--------- +SNAPSHOTS +--------- +LIST OF SNAPSHOTS FOR EACH SESSION: +----------LATEST DETECTED DEADLOCKS---------- + +*** DEADLOCK PATH +========================================= +TXN_ID +COLUMN FAMILY NAME: default +KEY +LOCK TYPE: EXCLUSIVE +INDEX NAME: PRIMARY +TABLE NAME: test.t +---------------WAITING FOR--------------- +TXN_ID +COLUMN FAMILY NAME: default +KEY +LOCK TYPE: SHARED +INDEX NAME: PRIMARY +TABLE NAME: test.t +---------------WAITING FOR--------------- +TXN_ID +COLUMN FAMILY NAME: default +KEY +LOCK TYPE: EXCLUSIVE +INDEX NAME: PRIMARY +TABLE NAME: test.t + +--------TXN_ID GOT DEADLOCK--------- + +-------DEADLOCK EXCEEDED MAX DEPTH------- +----------------------------------------- +END OF ROCKSDB TRANSACTION MONITOR OUTPUT +========================================= + +set global rocksdb_lock_wait_timeout = @prior_lock_wait_timeout; +set global rocksdb_deadlock_detect = @prior_deadlock_detect; +drop table t; +show engine rocksdb transaction status; +Type Name Status +rocksdb +============================================================ +TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT +============================================================ +--------- +SNAPSHOTS +--------- +LIST OF SNAPSHOTS FOR EACH SESSION: +----------LATEST DETECTED DEADLOCKS---------- + +*** DEADLOCK PATH +========================================= +TXN_ID +COLUMN FAMILY NAME: default +KEY +LOCK TYPE: EXCLUSIVE +INDEX NAME: NOT FOUND; IDX_ID +TABLE NAME: NOT FOUND; IDX_ID +---------------WAITING FOR--------------- +TXN_ID +COLUMN FAMILY NAME: default +KEY +LOCK TYPE: SHARED +INDEX NAME: NOT FOUND; IDX_ID +TABLE NAME: NOT FOUND; IDX_ID +---------------WAITING FOR--------------- +TXN_ID +COLUMN FAMILY NAME: default +KEY +LOCK TYPE: EXCLUSIVE +INDEX NAME: NOT FOUND; IDX_ID +TABLE NAME: NOT FOUND; IDX_ID + +--------TXN_ID GOT DEADLOCK--------- + +-------DEADLOCK EXCEEDED MAX DEPTH------- +----------------------------------------- +END OF ROCKSDB TRANSACTION MONITOR OUTPUT +========================================= + +set global rocksdb_max_latest_deadlocks = 0; +# Clears deadlock buffer of any existent deadlocks. +set global rocksdb_max_latest_deadlocks = @prior_max_latest_deadlocks; +show engine rocksdb transaction status; +Type Name Status +rocksdb +============================================================ +TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT +============================================================ +--------- +SNAPSHOTS +--------- +LIST OF SNAPSHOTS FOR EACH SESSION: +----------LATEST DETECTED DEADLOCKS---------- +----------------------------------------- +END OF ROCKSDB TRANSACTION MONITOR OUTPUT +========================================= + diff --git a/storage/rocksdb/mysql-test/rocksdb/r/drop_table.result b/storage/rocksdb/mysql-test/rocksdb/r/drop_table.result index 49df0b28bdf..dba49d8ff41 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/drop_table.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/drop_table.result @@ -5,6 +5,9 @@ DROP TABLE IF EXISTS t2; DROP TABLE IF EXISTS t3; DROP TABLE IF EXISTS t4; DROP TABLE IF EXISTS t5; +call mtr.add_suppression("Column family 'cf1' not found"); +call mtr.add_suppression("Column family 'rev:cf2' not found"); +call mtr.add_suppression("LibRocksDB"); set global rocksdb_compact_cf = 'cf1'; set global rocksdb_compact_cf = 'rev:cf2'; set global rocksdb_signal_drop_index_thread = 1; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/drop_table2.result b/storage/rocksdb/mysql-test/rocksdb/r/drop_table2.result index 44668847649..fad2939d206 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/drop_table2.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/drop_table2.result @@ -5,6 +5,8 @@ DROP TABLE IF EXISTS t2; DROP TABLE IF EXISTS t3; DROP TABLE IF EXISTS t4; DROP TABLE IF EXISTS t5; +call mtr.add_suppression("Column family 'cf1' not found"); +call mtr.add_suppression("Column family 'rev:cf2' not found"); set global rocksdb_compact_cf = 'cf1'; set global rocksdb_compact_cf = 'rev:cf2'; set global rocksdb_signal_drop_index_thread = 1; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/drop_table3.result b/storage/rocksdb/mysql-test/rocksdb/r/drop_table3.result index e5237fe9b1e..7a33fa83cb4 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/drop_table3.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/drop_table3.result @@ -1,6 +1,8 @@ call mtr.add_suppression("Column family 'cf1' not found"); call mtr.add_suppression("Column family 'rev:cf2' not found"); DROP TABLE IF EXISTS t1; +call mtr.add_suppression("Column family 'cf1' not found"); +call mtr.add_suppression("Column family 'rev:cf2' not found"); set global rocksdb_compact_cf = 'cf1'; set global rocksdb_compact_cf = 'rev:cf2'; set global rocksdb_signal_drop_index_thread = 1; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/dup_key_update.result b/storage/rocksdb/mysql-test/rocksdb/r/dup_key_update.result index 954335debf2..b4cebb08bb1 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/dup_key_update.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/dup_key_update.result @@ -178,16 +178,20 @@ id1 id2 id3 9 17 9 DROP TABLE t1; DROP TABLE t2; +set global rocksdb_large_prefix=1; CREATE TABLE t1 (id1 varchar(128) CHARACTER SET latin1 COLLATE latin1_bin, id2 varchar(256) CHARACTER SET utf8 COLLATE utf8_bin, id3 varchar(200) CHARACTER SET latin1 COLLATE latin1_swedish_ci, PRIMARY KEY (id1, id2, id3), UNIQUE KEY (id3, id1)) ENGINE=ROCKSDB; +set global rocksdb_large_prefix=DEFAULT; +set global rocksdb_large_prefix=1; CREATE TABLE t2 (id1 varchar(128) CHARACTER SET latin1 COLLATE latin1_bin, id2 varchar(256) CHARACTER SET utf8 COLLATE utf8_bin, id3 varchar(200) CHARACTER SET latin1 COLLATE latin1_swedish_ci, PRIMARY KEY (id1, id2, id3), UNIQUE KEY (id3, id1) COMMENT 'rev:cf') ENGINE=ROCKSDB; +set global rocksdb_large_prefix=DEFAULT; INSERT INTO t1 VALUES (1, 1, 1) ON DUPLICATE KEY UPDATE id2 = 9; SELECT * FROM t1 WHERE id1 = 1; id1 id2 id3 diff --git a/storage/rocksdb/mysql-test/rocksdb/r/fast_prefix_index_fetch.result b/storage/rocksdb/mysql-test/rocksdb/r/fast_prefix_index_fetch.result new file mode 100644 index 00000000000..963f9706ee8 --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/fast_prefix_index_fetch.result @@ -0,0 +1,80 @@ +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( +id INT, +fake_id INT, +bigfield VARCHAR(4096), +PRIMARY KEY (id), +KEY bf (bigfield(32)), +KEY fid (fake_id, bigfield(32)) +) ENGINE=rocksdb; +INSERT INTO t1 VALUES (1, 1001, REPEAT('a', 1)), +(8, 1008, REPEAT('b', 8)), +(24, 1024, REPEAT('c', 24)), +(31, 1031, REPEAT('d', 31)), +(32, 1032, REPEAT('x', 32)), +(33, 1033, REPEAT('y', 33)), +(128, 1128, REPEAT('z', 128)); +SELECT * FROM t1; +id fake_id bigfield +1 1001 a +8 1008 bbbbbbbb +24 1024 cccccccccccccccccccccccc +31 1031 ddddddddddddddddddddddddddddddd +32 1032 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +33 1033 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy +128 1128 zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz +# Baseline sanity check +no-op query +no-op query +include/assert.inc [Baseline sanity check: 0 rocksdb_covered_secondary_key_lookups] +# Eligible for optimization. +id bigfield +31 ddddddddddddddddddddddddddddddd +include/assert.inc [Eligible for optimization.: 2 rocksdb_covered_secondary_key_lookups] +# Eligible for optimization, access via fake_id only +id bigfield +31 ddddddddddddddddddddddddddddddd +include/assert.inc [Eligible for optimization, access via fake_id only: 2 rocksdb_covered_secondary_key_lookups] +# Not eligible for optimization, access via fake_id of big row. +id bigfield +33 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy +include/assert.inc [Not eligible for optimization, access via fake_id of big row.: 0 rocksdb_covered_secondary_key_lookups] +# Eligible for optimization. +id bigfield +32 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +include/assert.inc [Eligible for optimization.: 1 rocksdb_covered_secondary_key_lookups] +# Not eligible for optimization. +id bigfield +33 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy +include/assert.inc [Not eligible for optimization.: 0 rocksdb_covered_secondary_key_lookups] +# Eligible for optimization. +id bigfield +8 bbbbbbbb +include/assert.inc [Eligible for optimization.: 2 rocksdb_covered_secondary_key_lookups] +# Eligible for optimization. +id bigfield +24 cccccccccccccccccccccccc +include/assert.inc [Eligible for optimization.: 2 rocksdb_covered_secondary_key_lookups] +# Not eligible for optimization. +id bigfield +128 zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz +include/assert.inc [Not eligible for optimization.: 0 rocksdb_covered_secondary_key_lookups] +# +# Test that multi-byte charsets are handled correctly +# +# Charset record obviously shorter than the prefix +a b +1 a +include/assert.inc [Charset record obviously shorter than the prefix: 2 rocksdb_covered_secondary_key_lookups] +# Charset record shorter than prefix +a b +2 cc +include/assert.inc [Charset record shorter than prefix: 2 rocksdb_covered_secondary_key_lookups] +# Charset record with glyphs shorter than prefix +a b +3 ŽŽ +include/assert.inc [Charset record with glyphs shorter than prefix: 1 rocksdb_covered_secondary_key_lookups] +# Charset record longer than prefix +a b +4 žžžž +include/assert.inc [Charset record longer than prefix: 0 rocksdb_covered_secondary_key_lookups] diff --git a/storage/rocksdb/mysql-test/rocksdb/r/i_s_ddl.result b/storage/rocksdb/mysql-test/rocksdb/r/i_s_ddl.result index 2a1fcd78126..a39f2d8c0d6 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/i_s_ddl.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/i_s_ddl.result @@ -9,9 +9,9 @@ KEY (x)) ENGINE = ROCKSDB; SELECT TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,INDEX_NAME,INDEX_TYPE,KV_FORMAT_VERSION,CF FROM INFORMATION_SCHEMA.ROCKSDB_DDL WHERE TABLE_NAME like 'is_ddl_t%'; TABLE_SCHEMA TABLE_NAME PARTITION_NAME INDEX_NAME INDEX_TYPE KV_FORMAT_VERSION CF test is_ddl_t1 NULL PRIMARY 1 13 default -test is_ddl_t1 NULL j 2 12 default -test is_ddl_t1 NULL k 2 12 kl_cf +test is_ddl_t1 NULL j 2 13 default +test is_ddl_t1 NULL k 2 13 kl_cf test is_ddl_t2 NULL PRIMARY 1 13 zy_cf -test is_ddl_t2 NULL x 2 12 default +test is_ddl_t2 NULL x 2 13 default DROP TABLE is_ddl_t1; DROP TABLE is_ddl_t2; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/index.result b/storage/rocksdb/mysql-test/rocksdb/r/index.result index 99390c8ceb2..0920d0e01b2 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/index.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/index.result @@ -40,6 +40,33 @@ t1 0 PRIMARY 1 pk A # NULL NULL LSMTREE t1 1 a 1 a A # NULL NULL YES LSMTREE simple index on a ALTER TABLE t1 DROP KEY a; DROP TABLE t1; +set global rocksdb_large_prefix=0; +CREATE TABLE t1 ( +a BLOB(1024), +KEY (a(767)) +) ENGINE=rocksdb; +DROP TABLE t1; +CREATE TABLE t1 ( +a BLOB(1024), +KEY (a(768)) +) ENGINE=rocksdb; +Warnings: +Note 1071 Specified key was too long; max key length is 767 bytes +DROP TABLE t1; +set global rocksdb_large_prefix=1; +CREATE TABLE t1 ( +a BLOB(4096), +KEY (a(3072)) +) ENGINE=rocksdb; +DROP TABLE t1; +CREATE TABLE t1 ( +a BLOB(4096), +KEY (a(3073)) +) ENGINE=rocksdb; +Warnings: +Note 1071 Specified key was too long; max key length is 3072 bytes +DROP TABLE t1; +set global rocksdb_large_prefix=DEFAULT; # # Issue #376: MyRocks: ORDER BY optimizer is unable to use the index extension # diff --git a/storage/rocksdb/mysql-test/rocksdb/r/index_merge_rocksdb2.result b/storage/rocksdb/mysql-test/rocksdb/r/index_merge_rocksdb2.result index ff33e6061b3..4370715925a 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/index_merge_rocksdb2.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/index_merge_rocksdb2.result @@ -603,7 +603,7 @@ count(*) 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 2 Using intersect(key2,key1); Using where; Using index +1 SIMPLE t1 index_merge key1,key2 key2,key1 5,5 NULL # Using intersect(key2,key1); Using where; Using index select key1,key2 from t1 where key1=100 and key2=100; key1 key2 100 100 diff --git a/storage/rocksdb/mysql-test/rocksdb/r/index_primary.result b/storage/rocksdb/mysql-test/rocksdb/r/index_primary.result index 66481f81c67..1f4c88e5a33 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/index_primary.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/index_primary.result @@ -46,3 +46,26 @@ SHOW KEYS IN t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment t1 0 PRIMARY 1 b A # NULL NULL LSMTREE DROP TABLE t1; +set global rocksdb_large_prefix=0; +CREATE TABLE t1 ( +a BLOB(1024), +PRIMARY KEY (a(767)) +) ENGINE=rocksdb; +DROP TABLE t1; +CREATE TABLE t1 ( +a BLOB(1024), +PRIMARY KEY (a(768)) +) ENGINE=rocksdb; +ERROR 42000: Specified key was too long; max key length is 767 bytes +set global rocksdb_large_prefix=1; +CREATE TABLE t1 ( +a BLOB(4096), +PRIMARY KEY (a(3072)) +) ENGINE=rocksdb; +DROP TABLE t1; +CREATE TABLE t1 ( +a BLOB(4096), +PRIMARY KEY (a(3073)) +) ENGINE=rocksdb; +ERROR 42000: Specified key was too long; max key length is 3072 bytes +set global rocksdb_large_prefix=DEFAULT; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/index_type_btree.result b/storage/rocksdb/mysql-test/rocksdb/r/index_type_btree.result index a604663954b..9eaab9f53aa 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/index_type_btree.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/index_type_btree.result @@ -40,3 +40,30 @@ t1 0 PRIMARY 1 pk A # NULL NULL LSMTREE t1 1 a 1 a A # NULL NULL YES LSMTREE simple index on a ALTER TABLE t1 DROP KEY a; DROP TABLE t1; +set global rocksdb_large_prefix=0; +CREATE TABLE t1 ( +a BLOB(1024), +KEY (a(767)) +) ENGINE=rocksdb; +DROP TABLE t1; +CREATE TABLE t1 ( +a BLOB(1024), +KEY (a(768)) +) ENGINE=rocksdb; +Warnings: +Note 1071 Specified key was too long; max key length is 767 bytes +DROP TABLE t1; +set global rocksdb_large_prefix=1; +CREATE TABLE t1 ( +a BLOB(4096), +KEY (a(3072)) +) ENGINE=rocksdb; +DROP TABLE t1; +CREATE TABLE t1 ( +a BLOB(4096), +KEY (a(3073)) +) ENGINE=rocksdb; +Warnings: +Note 1071 Specified key was too long; max key length is 3072 bytes +DROP TABLE t1; +set global rocksdb_large_prefix=DEFAULT; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/index_type_hash.result b/storage/rocksdb/mysql-test/rocksdb/r/index_type_hash.result index ae99badff14..bc1a96fa726 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/index_type_hash.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/index_type_hash.result @@ -40,3 +40,30 @@ t1 0 PRIMARY 1 pk A # NULL NULL LSMTREE t1 1 a 1 a A # NULL NULL YES LSMTREE simple index on a ALTER TABLE t1 DROP KEY a; DROP TABLE t1; +set global rocksdb_large_prefix=0; +CREATE TABLE t1 ( +a BLOB(1024), +KEY (a(767)) +) ENGINE=rocksdb; +DROP TABLE t1; +CREATE TABLE t1 ( +a BLOB(1024), +KEY (a(768)) +) ENGINE=rocksdb; +Warnings: +Note 1071 Specified key was too long; max key length is 767 bytes +DROP TABLE t1; +set global rocksdb_large_prefix=1; +CREATE TABLE t1 ( +a BLOB(4096), +KEY (a(3072)) +) ENGINE=rocksdb; +DROP TABLE t1; +CREATE TABLE t1 ( +a BLOB(4096), +KEY (a(3073)) +) ENGINE=rocksdb; +Warnings: +Note 1071 Specified key was too long; max key length is 3072 bytes +DROP TABLE t1; +set global rocksdb_large_prefix=DEFAULT; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/issue243_transactionStatus.result b/storage/rocksdb/mysql-test/rocksdb/r/issue243_transactionStatus.result index 83db308627d..d4d211b9288 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/issue243_transactionStatus.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/issue243_transactionStatus.result @@ -17,7 +17,7 @@ id val1 val2 2 2 2 SHOW ENGINE rocksdb TRANSACTION STATUS; Type Name Status -SNAPSHOTS rocksdb +rocksdb ============================================================ TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT ============================================================ @@ -25,6 +25,7 @@ TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT SNAPSHOTS --------- LIST OF SNAPSHOTS FOR EACH SESSION: +----------LATEST DETECTED DEADLOCKS---------- ----------------------------------------- END OF ROCKSDB TRANSACTION MONITOR OUTPUT ========================================= @@ -48,7 +49,7 @@ id val1 val2 DELETE FROM t1 WHERE id=30; SHOW ENGINE rocksdb TRANSACTION STATUS; Type Name Status -SNAPSHOTS rocksdb +rocksdb ============================================================ TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT ============================================================ @@ -61,6 +62,7 @@ MySQL thread id TID, OS thread handle PTR, query id QID localhost root ACTION SHOW ENGINE rocksdb TRANSACTION STATUS lock count 8, write count 4 insert count 2, update count 1, delete count 1 +----------LATEST DETECTED DEADLOCKS---------- ----------------------------------------- END OF ROCKSDB TRANSACTION MONITOR OUTPUT ========================================= @@ -68,7 +70,7 @@ END OF ROCKSDB TRANSACTION MONITOR OUTPUT ROLLBACK; SHOW ENGINE rocksdb TRANSACTION STATUS; Type Name Status -SNAPSHOTS rocksdb +rocksdb ============================================================ TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT ============================================================ @@ -76,6 +78,7 @@ TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT SNAPSHOTS --------- LIST OF SNAPSHOTS FOR EACH SESSION: +----------LATEST DETECTED DEADLOCKS---------- ----------------------------------------- END OF ROCKSDB TRANSACTION MONITOR OUTPUT ========================================= @@ -84,7 +87,7 @@ START TRANSACTION; INSERT INTO t1 VALUES(40,40,40); SHOW ENGINE rocksdb TRANSACTION STATUS; Type Name Status -SNAPSHOTS rocksdb +rocksdb ============================================================ TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT ============================================================ @@ -97,6 +100,7 @@ MySQL thread id TID, OS thread handle PTR, query id QID localhost root ACTION SHOW ENGINE rocksdb TRANSACTION STATUS lock count 2, write count 1 insert count 1, update count 0, delete count 0 +----------LATEST DETECTED DEADLOCKS---------- ----------------------------------------- END OF ROCKSDB TRANSACTION MONITOR OUTPUT ========================================= @@ -104,7 +108,7 @@ END OF ROCKSDB TRANSACTION MONITOR OUTPUT COMMIT; SHOW ENGINE rocksdb TRANSACTION STATUS; Type Name Status -SNAPSHOTS rocksdb +rocksdb ============================================================ TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT ============================================================ @@ -112,6 +116,7 @@ TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT SNAPSHOTS --------- LIST OF SNAPSHOTS FOR EACH SESSION: +----------LATEST DETECTED DEADLOCKS---------- ----------------------------------------- END OF ROCKSDB TRANSACTION MONITOR OUTPUT ========================================= @@ -133,7 +138,7 @@ UPDATE t2 SET value=3 WHERE id2=2; DELETE FROM t2 WHERE id1=10; SHOW ENGINE rocksdb TRANSACTION STATUS; Type Name Status -SNAPSHOTS rocksdb +rocksdb ============================================================ TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT ============================================================ @@ -146,6 +151,7 @@ MySQL thread id TID, OS thread handle PTR, query id QID localhost root ACTION SHOW ENGINE rocksdb TRANSACTION STATUS lock count 9, write count 7 insert count 2, update count 1, delete count 1 +----------LATEST DETECTED DEADLOCKS---------- ----------------------------------------- END OF ROCKSDB TRANSACTION MONITOR OUTPUT ========================================= diff --git a/storage/rocksdb/mysql-test/rocksdb/r/multi_varchar_sk_lookup.result b/storage/rocksdb/mysql-test/rocksdb/r/multi_varchar_sk_lookup.result new file mode 100644 index 00000000000..86ba6d923a8 --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/multi_varchar_sk_lookup.result @@ -0,0 +1,37 @@ +DROP TABLE IF EXISTS T1; +CREATE TABLE T1 ( +P1 VARCHAR(64), P2 VARCHAR(64), P3 VARCHAR(64), P4 VARCHAR(64), +S1 VARCHAR(64), S2 VARCHAR(64), S3 VARCHAR(64), S4 VARCHAR(64), +S5 VARCHAR(64), S6 VARCHAR(64), S7 VARCHAR(64), S8 VARCHAR(64), +S9 VARCHAR(64), S10 VARCHAR(64), S11 VARCHAR(64), S12 VARCHAR(64), +S13 VARCHAR(64), S14 VARCHAR(64), S15 VARCHAR(64), S16 VARCHAR(64), +PRIMARY KEY (P1(8), P2(8), P3(8), P4(8)), +KEY SK (S1(8), S2(8), S3(8), S4(8), +S5(8), S6(8), S7(8), S8(8), +S9(8), S10(8), S11(8), S12(8), +S13(8), S14(8), S15(8), S16(8)) +) ENGINE=rocksdb; +INSERT INTO T1 VALUES ('1', '2', '3', '4', +'5', '6', '7', '8', +'9', '10', '11', '12', +'13', '14', '15', '16', +'17', '18', '19', '20'); +SELECT * FROM T1; +P1 P2 P3 P4 S1 S2 S3 S4 S5 S6 S7 S8 S9 S10 S11 S12 S13 S14 S15 S16 +1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 +# Not eligible for optimization, shorter than prefix length. +SELECT P4, S2 FROM T1 FORCE INDEX(SK) WHERE S1='5'; +P4 S2 +4 6 +include/assert.inc [Not eligible for optimization, shorter than prefix length.: 0 rocksdb_covered_secondary_key_lookups] +DELETE FROM T1; +INSERT INTO T1 VALUES ('100000000', '200000000', '300000000', '400000000', +'500000000', '600000000', '700000000', '800000000', +'900000000', '100000000', '110000000', '120000000', +'130000000', '140000000', '150000000', '160000000', +'170000000', '180000000', '190000000', '200000000'); +# Not eligible for optimization, longer than prefix length. +SELECT P4, S2 FROM T1 FORCE INDEX(SK) WHERE S1='5'; +P4 S2 +include/assert.inc [Not eligible for optimization, longer than prefix length.: 0 rocksdb_covered_secondary_key_lookups] +DROP TABLE T1; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/perf_context.result b/storage/rocksdb/mysql-test/rocksdb/r/perf_context.result index 2e8610d43bd..6586b92d129 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/perf_context.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/perf_context.result @@ -127,6 +127,8 @@ AND STAT_TYPE in ('INTERNAL_KEY_SKIPPED_COUNT', 'INTERNAL_DELETE_SKIPPED_COUNT') TABLE_SCHEMA TABLE_NAME PARTITION_NAME STAT_TYPE VALUE test t1 NULL INTERNAL_KEY_SKIPPED_COUNT 10 test t1 NULL INTERNAL_DELETE_SKIPPED_COUNT 0 +set @tmp_flush_log= @@rocksdb_flush_log_at_trx_commit; +set global rocksdb_flush_log_at_trx_commit=1; BEGIN; INSERT INTO t2 VALUES (1), (2); INSERT INTO t2 VALUES (3), (4); @@ -158,3 +160,4 @@ true DROP TABLE t1; DROP TABLE t2; SET GLOBAL rocksdb_perf_context_level = @prior_rocksdb_perf_context_level; +set global rocksdb_flush_log_at_trx_commit= @tmp_flush_log; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb.result b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb.result index 46acd055421..ed0688c96ad 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb.result @@ -881,6 +881,7 @@ rocksdb_block_restart_interval 16 rocksdb_block_size 4096 rocksdb_block_size_deviation 10 rocksdb_bulk_load OFF +rocksdb_bulk_load_allow_unsorted OFF rocksdb_bulk_load_size 1000 rocksdb_bytes_per_sync 0 rocksdb_cache_index_and_filter_blocks ON @@ -893,13 +894,16 @@ rocksdb_compaction_sequential_deletes 0 rocksdb_compaction_sequential_deletes_count_sd OFF rocksdb_compaction_sequential_deletes_file_size 0 rocksdb_compaction_sequential_deletes_window 0 +rocksdb_concurrent_prepare ON rocksdb_create_checkpoint rocksdb_create_if_missing ON rocksdb_create_missing_column_families OFF rocksdb_datadir ./.rocksdb rocksdb_db_write_buffer_size 0 rocksdb_deadlock_detect OFF +rocksdb_deadlock_detect_depth 50 rocksdb_debug_optimizer_no_zero_cardinality ON +rocksdb_debug_ttl_ignore_pk OFF rocksdb_debug_ttl_read_filter_ts 0 rocksdb_debug_ttl_rec_ts 0 rocksdb_debug_ttl_snapshot_ts 0 @@ -916,6 +920,7 @@ rocksdb_error_if_exists OFF rocksdb_flush_log_at_trx_commit 0 rocksdb_flush_memtable_on_analyze ON rocksdb_force_compute_memtable_stats ON +rocksdb_force_compute_memtable_stats_cachetime 0 rocksdb_force_flush_memtable_and_lzero_now OFF rocksdb_force_flush_memtable_now OFF rocksdb_force_index_records_in_range 0 @@ -925,12 +930,15 @@ rocksdb_info_log_level error_level rocksdb_io_write_timeout 0 rocksdb_is_fd_close_on_exec ON rocksdb_keep_log_file_num 1000 +rocksdb_large_prefix OFF rocksdb_lock_scanned_rows OFF rocksdb_lock_wait_timeout 1 rocksdb_log_file_time_to_roll 0 rocksdb_manifest_preallocation_size 4194304 +rocksdb_manual_wal_flush ON rocksdb_master_skip_tx_api OFF rocksdb_max_background_jobs 2 +rocksdb_max_latest_deadlocks 5 rocksdb_max_log_file_size 0 rocksdb_max_manifest_file_size 18446744073709551615 rocksdb_max_open_files -1 @@ -939,6 +947,7 @@ rocksdb_max_subcompactions 1 rocksdb_max_total_wal_size 0 rocksdb_merge_buf_size 67108864 rocksdb_merge_combine_read_size 1073741824 +rocksdb_merge_tmp_file_removal_delay_ms 0 rocksdb_new_table_reader_for_compaction_inputs OFF rocksdb_no_block_cache OFF rocksdb_override_cf_options @@ -955,6 +964,7 @@ rocksdb_records_in_range 50 rocksdb_reset_stats OFF rocksdb_seconds_between_stat_computes 3600 rocksdb_signal_drop_index_thread OFF +rocksdb_sim_cache_size 0 rocksdb_skip_bloom_filter_on_read OFF rocksdb_skip_fill_cache OFF rocksdb_skip_unique_check_tables .* @@ -970,6 +980,7 @@ rocksdb_trace_sst_api OFF rocksdb_unsafe_for_binlog OFF rocksdb_update_cf_options rocksdb_use_adaptive_mutex OFF +rocksdb_use_clock_cache OFF rocksdb_use_direct_io_for_flush_and_compaction OFF rocksdb_use_direct_reads OFF rocksdb_use_fsync OFF @@ -1451,16 +1462,21 @@ Rocksdb_rows_inserted # Rocksdb_rows_read # Rocksdb_rows_updated # Rocksdb_rows_deleted_blind # -rocksdb_rows_expired # +Rocksdb_rows_expired # Rocksdb_system_rows_deleted # Rocksdb_system_rows_inserted # Rocksdb_system_rows_read # Rocksdb_system_rows_updated # +Rocksdb_memtable_total # +Rocksdb_memtable_unflushed # +Rocksdb_queries_point # +Rocksdb_queries_range # +Rocksdb_covered_secondary_key_lookups # Rocksdb_block_cache_add # -rocksdb_memtable_total # -rocksdb_memtable_unflushed # -rocksdb_queries_point # -rocksdb_queries_range # +Rocksdb_block_cache_data_hit # +Rocksdb_block_cache_data_miss # +Rocksdb_block_cache_filter_hit # +Rocksdb_block_cache_filter_miss # Rocksdb_block_cache_hit # Rocksdb_block_cache_index_hit # Rocksdb_block_cache_index_miss # @@ -1479,13 +1495,13 @@ Rocksdb_compaction_key_drop_obsolete # Rocksdb_compaction_key_drop_user # Rocksdb_flush_write_bytes # Rocksdb_getupdatessince_calls # -Rocksdb_l0_num_files_stall_micros # -Rocksdb_l0_slowdown_micros # -Rocksdb_memtable_compaction_micros # Rocksdb_memtable_hit # Rocksdb_memtable_miss # Rocksdb_no_file_closes # Rocksdb_no_file_errors # +Rocksdb_no_file_opens # +Rocksdb_num_iterators # +Rocksdb_number_block_not_compressed # Rocksdb_number_deletes_filtered # Rocksdb_number_keys_read # Rocksdb_number_keys_updated # @@ -1506,6 +1522,17 @@ Rocksdb_number_superversion_cleanups # Rocksdb_number_superversion_releases # Rocksdb_rate_limit_delay_millis # Rocksdb_snapshot_conflict_errors # +Rocksdb_stall_l0_file_count_limit_slowdowns # +Rocksdb_stall_locked_l0_file_count_limit_slowdowns # +Rocksdb_stall_l0_file_count_limit_stops # +Rocksdb_stall_locked_l0_file_count_limit_stops # +Rocksdb_stall_pending_compaction_limit_stops # +Rocksdb_stall_pending_compaction_limit_slowdowns # +Rocksdb_stall_memtable_limit_stops # +Rocksdb_stall_memtable_limit_slowdowns # +Rocksdb_stall_total_stops # +Rocksdb_stall_total_slowdowns # +Rocksdb_stall_micros # Rocksdb_wal_bytes # Rocksdb_wal_group_syncs # Rocksdb_wal_synced # @@ -1513,7 +1540,6 @@ Rocksdb_write_other # Rocksdb_write_self # Rocksdb_write_timedout # Rocksdb_write_wal # -rocksdb_stall_micros # select VARIABLE_NAME from INFORMATION_SCHEMA.global_status where VARIABLE_NAME LIKE 'rocksdb%'; VARIABLE_NAME ROCKSDB_ROWS_DELETED @@ -1530,6 +1556,7 @@ ROCKSDB_MEMTABLE_TOTAL ROCKSDB_MEMTABLE_UNFLUSHED ROCKSDB_QUERIES_POINT ROCKSDB_QUERIES_RANGE +ROCKSDB_COVERED_SECONDARY_KEY_LOOKUPS ROCKSDB_BLOCK_CACHE_ADD ROCKSDB_BLOCK_CACHE_DATA_HIT ROCKSDB_BLOCK_CACHE_DATA_MISS @@ -1580,6 +1607,16 @@ ROCKSDB_NUMBER_SUPERVERSION_CLEANUPS ROCKSDB_NUMBER_SUPERVERSION_RELEASES ROCKSDB_RATE_LIMIT_DELAY_MILLIS ROCKSDB_SNAPSHOT_CONFLICT_ERRORS +ROCKSDB_STALL_L0_FILE_COUNT_LIMIT_SLOWDOWNS +ROCKSDB_STALL_LOCKED_L0_FILE_COUNT_LIMIT_SLOWDOWNS +ROCKSDB_STALL_L0_FILE_COUNT_LIMIT_STOPS +ROCKSDB_STALL_LOCKED_L0_FILE_COUNT_LIMIT_STOPS +ROCKSDB_STALL_PENDING_COMPACTION_LIMIT_STOPS +ROCKSDB_STALL_PENDING_COMPACTION_LIMIT_SLOWDOWNS +ROCKSDB_STALL_MEMTABLE_LIMIT_STOPS +ROCKSDB_STALL_MEMTABLE_LIMIT_SLOWDOWNS +ROCKSDB_STALL_TOTAL_STOPS +ROCKSDB_STALL_TOTAL_SLOWDOWNS ROCKSDB_STALL_MICROS ROCKSDB_WAL_BYTES ROCKSDB_WAL_GROUP_SYNCS @@ -1606,6 +1643,7 @@ ROCKSDB_MEMTABLE_TOTAL ROCKSDB_MEMTABLE_UNFLUSHED ROCKSDB_QUERIES_POINT ROCKSDB_QUERIES_RANGE +ROCKSDB_COVERED_SECONDARY_KEY_LOOKUPS ROCKSDB_BLOCK_CACHE_ADD ROCKSDB_BLOCK_CACHE_DATA_HIT ROCKSDB_BLOCK_CACHE_DATA_MISS @@ -1656,6 +1694,16 @@ ROCKSDB_NUMBER_SUPERVERSION_CLEANUPS ROCKSDB_NUMBER_SUPERVERSION_RELEASES ROCKSDB_RATE_LIMIT_DELAY_MILLIS ROCKSDB_SNAPSHOT_CONFLICT_ERRORS +ROCKSDB_STALL_L0_FILE_COUNT_LIMIT_SLOWDOWNS +ROCKSDB_STALL_LOCKED_L0_FILE_COUNT_LIMIT_SLOWDOWNS +ROCKSDB_STALL_L0_FILE_COUNT_LIMIT_STOPS +ROCKSDB_STALL_LOCKED_L0_FILE_COUNT_LIMIT_STOPS +ROCKSDB_STALL_PENDING_COMPACTION_LIMIT_STOPS +ROCKSDB_STALL_PENDING_COMPACTION_LIMIT_SLOWDOWNS +ROCKSDB_STALL_MEMTABLE_LIMIT_STOPS +ROCKSDB_STALL_MEMTABLE_LIMIT_SLOWDOWNS +ROCKSDB_STALL_TOTAL_STOPS +ROCKSDB_STALL_TOTAL_SLOWDOWNS ROCKSDB_STALL_MICROS ROCKSDB_WAL_BYTES ROCKSDB_WAL_GROUP_SYNCS @@ -2155,7 +2203,9 @@ SET @old_mode = @@sql_mode; SET sql_mode = 'strict_all_tables'; create table t1 (a int, b text, c varchar(400), Primary Key(a), Key(c, b(255))) engine=rocksdb; drop table t1; +set global rocksdb_large_prefix=1; create table t1 (a int, b text, c varchar(400), Primary Key(a), Key(b(1255))) engine=rocksdb; +set global rocksdb_large_prefix=0; insert into t1 values (1, '1abcde', '1abcde'), (2, '2abcde', '2abcde'), (3, '3abcde', '3abcde'); select * from t1; a b c @@ -2177,7 +2227,7 @@ a b c drop table t1; create table t1 (a int, b text, c varchar(400), Primary Key(a), Key(b(2255))) engine=rocksdb; Warnings: -Warning 1071 Specified key was too long; max key length is 2048 bytes +Note 1071 Specified key was too long; max key length is 767 bytes drop table t1; SET sql_mode = @old_mode; drop table t0; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/show_engine.result b/storage/rocksdb/mysql-test/rocksdb/r/show_engine.result index d1072eee4ad..1bcd3692b4a 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/show_engine.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/show_engine.result @@ -7,17 +7,8 @@ CREATE TABLE t2 (j INT, PRIMARY KEY (j) COMMENT 'rev:cf_t2') ENGINE = ROCKSDB; CREATE TABLE t3 (k INT, PRIMARY KEY (k) COMMENT 'cf_t1') ENGINE = ROCKSDB; CREATE TABLE t4 (l INT, PRIMARY KEY (l) COMMENT 'cf_t4') ENGINE = ROCKSDB PARTITION BY KEY(l) PARTITIONS 4; -SET GLOBAL rocksdb_force_flush_memtable_now=1; -SHOW ENGINE rocksdb STATUS; -Type Name Status -STATISTICS # # -DBSTATS # # -CF_COMPACTION # # -CF_COMPACTION # # -CF_COMPACTION # # -CF_COMPACTION # # -MEMORY_STATS # # -BG_THREADS # # +SET @save.rocksdb_max_background_jobs= @@global.rocksdb_max_background_jobs; +SET GLOBAL rocksdb_max_background_jobs= 1; INSERT INTO t1 VALUES (1), (2), (3); SELECT COUNT(*) FROM t1; COUNT(*) @@ -30,6 +21,19 @@ INSERT INTO t4 VALUES (1), (2), (3), (4), (5); SELECT COUNT(*) FROM t4; COUNT(*) 5 +SET GLOBAL rocksdb_force_flush_memtable_now=1; +SET GLOBAL rocksdb_compact_cf="cf_t1"; +SHOW ENGINE rocksdb STATUS; +Type Name Status +STATISTICS # # +DBSTATS # # +CF_COMPACTION # # +CF_COMPACTION # # +CF_COMPACTION # # +CF_COMPACTION # # +MEMORY_STATS # # +BG_THREADS # # +BG_THREADS # # SELECT * FROM INFORMATION_SCHEMA.ROCKSDB_CFSTATS; CF_NAME STAT_TYPE VALUE __system__ NUM_IMMUTABLE_MEM_TABLE # @@ -356,7 +360,7 @@ Type Name Status SHOW ENGINE ALL MUTEX; SHOW ENGINE rocksdb TRANSACTION STATUS; Type Name Status -SNAPSHOTS rocksdb +rocksdb ============================================================ TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT ============================================================ @@ -364,6 +368,7 @@ TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT SNAPSHOTS --------- LIST OF SNAPSHOTS FOR EACH SESSION: +----------LATEST DETECTED DEADLOCKS---------- ----------------------------------------- END OF ROCKSDB TRANSACTION MONITOR OUTPUT ========================================= @@ -371,7 +376,7 @@ END OF ROCKSDB TRANSACTION MONITOR OUTPUT START TRANSACTION WITH CONSISTENT SNAPSHOT; SHOW ENGINE rocksdb TRANSACTION STATUS; Type Name Status -SNAPSHOTS rocksdb +rocksdb ============================================================ TIMESTAMP ROCKSDB TRANSACTION MONITOR OUTPUT ============================================================ @@ -384,8 +389,10 @@ MySQL thread id TID, OS thread handle PTR, query id QID localhost root ACTION SHOW ENGINE rocksdb TRANSACTION STATUS lock count 0, write count 0 insert count 0, update count 0, delete count 0 +----------LATEST DETECTED DEADLOCKS---------- ----------------------------------------- END OF ROCKSDB TRANSACTION MONITOR OUTPUT ========================================= ROLLBACK; +SET GLOBAL rocksdb_max_background_jobs= @save.rocksdb_max_background_jobs; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/skip_validate_tmp_table.result b/storage/rocksdb/mysql-test/rocksdb/r/skip_validate_tmp_table.result new file mode 100644 index 00000000000..7642dcda43f --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/skip_validate_tmp_table.result @@ -0,0 +1,4 @@ +CREATE TABLE t1 (pk int primary key) ENGINE=ROCKSDB; +set session debug_dbug="+d,gen_sql_table_name"; +rename table t1 to t2; +set session debug_dbug= "-d,gen_sql_table_name"; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/truncate_table3.result b/storage/rocksdb/mysql-test/rocksdb/r/truncate_table3.result index 813f651be62..eda560fefdb 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/truncate_table3.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/truncate_table3.result @@ -1,6 +1,8 @@ call mtr.add_suppression("Column family 'cf1' not found"); call mtr.add_suppression("Column family 'rev:cf2' not found"); DROP TABLE IF EXISTS t1; +call mtr.add_suppression("Column family 'cf1' not found"); +call mtr.add_suppression("Column family 'rev:cf2' not found"); set global rocksdb_compact_cf = 'cf1'; set global rocksdb_compact_cf = 'rev:cf2'; set global rocksdb_signal_drop_index_thread = 1; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/ttl_primary.result b/storage/rocksdb/mysql-test/rocksdb/r/ttl_primary.result index 79ed7ec0396..2903e9aa7eb 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/ttl_primary.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/ttl_primary.result @@ -337,9 +337,7 @@ b INT ) ENGINE=rocksdb COMMENT='ttl_duration=100;'; ALTER TABLE t1 DROP PRIMARY KEY; -ERROR HY000: TTL support is currently disabled when table has secondary indexes or hidden PK. -ALTER TABLE t1 ADD INDEX kb(b), ALGORITHM=INPLACE; -ERROR HY000: TTL support is currently disabled when table has secondary indexes or hidden PK. +ERROR HY000: TTL support is currently disabled when table has a hidden PK. DROP TABLE t1; CREATE TABLE t1 ( a INT PRIMARY KEY, diff --git a/storage/rocksdb/mysql-test/rocksdb/r/ttl_secondary.result b/storage/rocksdb/mysql-test/rocksdb/r/ttl_secondary.result new file mode 100644 index 00000000000..1f748a3841a --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/ttl_secondary.result @@ -0,0 +1,709 @@ +CREATE TABLE t1 ( +`a` binary(8) NOT NULL, +`b` varbinary(64) NOT NULL, +`c` varbinary(256) NOT NULL, +`ts` bigint(20) UNSIGNED NOT NULL, +`value` mediumblob NOT NULL, +PRIMARY KEY (`b`,`a`,`c`) +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 +COMMENT='ttl_duration=1;ttl_col=ts;'; +set global rocksdb_debug_ttl_rec_ts = -100; +INSERT INTO t1 values ('a', 'b', 'c', UNIX_TIMESTAMP(), 'd'); +INSERT INTO t1 values ('d', 'e', 'f', UNIX_TIMESTAMP(), 'g'); +set global rocksdb_debug_ttl_rec_ts = 0; +SELECT COUNT(*) FROM t1; +COUNT(*) +2 +set global rocksdb_debug_ttl_ignore_pk = 1; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_ignore_pk = 0; +SELECT COUNT(*) FROM t1; +COUNT(*) +2 +DROP TABLE t1; +CREATE TABLE t1 ( +`a` binary(8) NOT NULL, +`b` varbinary(64) NOT NULL, +`c` varbinary(256) NOT NULL, +`ts` bigint(20) UNSIGNED NOT NULL, +`value` mediumblob NOT NULL, +PRIMARY KEY (`b`,`a`,`c`), +KEY kb (`b`) +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 +COMMENT='ttl_duration=1;ttl_col=ts;'; +set global rocksdb_debug_ttl_rec_ts = -100; +INSERT INTO t1 values ('a', 'b', 'c', UNIX_TIMESTAMP(), 'd'); +INSERT INTO t1 values ('d', 'e', 'f', UNIX_TIMESTAMP(), 'g'); +set global rocksdb_debug_ttl_rec_ts = 0; +SELECT COUNT(*) FROM t1 FORCE INDEX (kb); +COUNT(*) +2 +set global rocksdb_debug_ttl_ignore_pk = 1; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_ignore_pk = 0; +SELECT COUNT(*) FROM t1 FORCE INDEX (kb); +COUNT(*) +0 +DROP TABLE t1; +CREATE TABLE t1 ( +a bigint(20) NOT NULL, +b int NOT NULL, +ts bigint(20) UNSIGNED NOT NULL, +c int NOT NULL, +PRIMARY KEY (a), +KEY kb (b) +) ENGINE=rocksdb +COMMENT='ttl_duration=1;ttl_col=ts;'; +set global rocksdb_debug_ttl_rec_ts = -100; +INSERT INTO t1 values (1, 3, UNIX_TIMESTAMP(), 5); +INSERT INTO t1 values (2, 4, UNIX_TIMESTAMP(), 6); +set global rocksdb_debug_ttl_rec_ts = 0; +SELECT COUNT(*) FROM t1 FORCE INDEX (kb); +COUNT(*) +2 +set global rocksdb_debug_ttl_ignore_pk = 1; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_ignore_pk = 0; +SELECT COUNT(*) FROM t1 FORCE INDEX (kb); +COUNT(*) +0 +DROP TABLE t1; +CREATE TABLE t1 ( +a bigint(20) NOT NULL, +b int NOT NULL, +c int NOT NULL, +ts bigint(20) UNSIGNED NOT NULL, +PRIMARY KEY (a,c), +KEY kb (b) +) ENGINE=rocksdb +COMMENT='ttl_duration=1;ttl_col=ts;'; +set global rocksdb_debug_ttl_rec_ts = -100; +INSERT INTO t1 values (1, 3, 5, UNIX_TIMESTAMP()); +INSERT INTO t1 values (2, 4, 6, UNIX_TIMESTAMP()); +set global rocksdb_debug_ttl_rec_ts = 0; +SELECT COUNT(*) FROM t1 FORCE INDEX (kb); +COUNT(*) +2 +set global rocksdb_debug_ttl_ignore_pk=1; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_ignore_pk=0; +SELECT COUNT(*) FROM t1 FORCE INDEX (kb); +COUNT(*) +0 +DROP TABLE t1; +CREATE TABLE t1 ( +a bigint(20) NOT NULL, +b int, +c int, +ts bigint(20) UNSIGNED NOT NULL, +PRIMARY KEY (a), +KEY kbc (b, c) +) ENGINE=rocksdb +COMMENT='ttl_duration=1;ttl_col=ts;'; +set global rocksdb_debug_ttl_rec_ts = -100; +INSERT INTO t1 values (1, NULL, NULL, UNIX_TIMESTAMP()); +INSERT INTO t1 values (2, NULL, NULL, UNIX_TIMESTAMP()); +set global rocksdb_debug_ttl_rec_ts = 0; +SELECT COUNT(*) FROM t1 FORCE INDEX (kb); +COUNT(*) +2 +set global rocksdb_debug_ttl_ignore_pk=1; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_ignore_pk=0; +SELECT COUNT(*) FROM t1 FORCE INDEX (kb); +COUNT(*) +0 +DROP TABLE t1; +CREATE TABLE t1 ( +`a` binary(8) NOT NULL, +`b` varbinary(64), +`c` varbinary(256), +`ts` bigint(20) UNSIGNED NOT NULL, +`value` mediumblob NOT NULL, +PRIMARY KEY (`a`), +KEY kbc (`b`, `c`) +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 +COMMENT='ttl_duration=1;ttl_col=ts;'; +set global rocksdb_debug_ttl_rec_ts = -100; +INSERT INTO t1 values ('a', NULL, 'bc', UNIX_TIMESTAMP(), 'd'); +INSERT INTO t1 values ('d', 'efghijk', NULL, UNIX_TIMESTAMP(), 'l'); +set global rocksdb_debug_ttl_rec_ts = 0; +SELECT COUNT(*) FROM t1 FORCE INDEX (kb); +COUNT(*) +2 +set global rocksdb_debug_ttl_ignore_pk=1; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_ignore_pk=0; +SELECT COUNT(*) FROM t1 FORCE INDEX (kb); +COUNT(*) +0 +DROP TABLE t1; +CREATE TABLE t1 ( +a bigint(20) NOT NULL, +b int NOT NULL, +c int NOT NULL, +PRIMARY KEY (a), +KEY kb (b) +) ENGINE=rocksdb +COMMENT='ttl_duration=1;'; +set global rocksdb_debug_ttl_rec_ts = -100; +INSERT INTO t1 values (1, 3, 5); +INSERT INTO t1 values (2, 4, 6); +set global rocksdb_debug_ttl_rec_ts = 0; +SELECT COUNT(*) FROM t1 FORCE INDEX (kb); +COUNT(*) +2 +set global rocksdb_debug_ttl_ignore_pk=1; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_ignore_pk=0; +SELECT COUNT(*) FROM t1 FORCE INDEX (kb); +COUNT(*) +0 +DROP TABLE t1; +CREATE TABLE t1 ( +a int, +ts bigint(20) UNSIGNED NOT NULL, +PRIMARY KEY (a, ts), +KEY kt (ts) +) ENGINE=rocksdb +COMMENT='ttl_duration=5;ttl_col=ts;'; +INSERT INTO t1 values (1, UNIX_TIMESTAMP()); +INSERT INTO t1 values (2, UNIX_TIMESTAMP()); +SELECT COUNT(*) FROM t1 FORCE INDEX(kt); +COUNT(*) +2 +set global rocksdb_debug_ttl_snapshot_ts = -10; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_snapshot_ts = 0; +SELECT COUNT(*) FROM t1 FORCE INDEX(kt); +COUNT(*) +2 +set global rocksdb_debug_ttl_ignore_pk=1; +set global rocksdb_debug_ttl_snapshot_ts = 10; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_snapshot_ts = 0; +set global rocksdb_debug_ttl_ignore_pk=0; +SELECT COUNT(*) FROM t1 FORCE INDEX(kt); +COUNT(*) +0 +DROP TABLE t1; +CREATE TABLE t1 ( +a bigint(20) NOT NULL, +b int NOT NULL, +ts bigint(20) UNSIGNED NOT NULL, +c int NOT NULL, +PRIMARY KEY (a, ts), +KEY kb (b) +) ENGINE=rocksdb +COMMENT='ttl_duration=1;ttl_col=ts;'; +set global rocksdb_debug_ttl_rec_ts = -100; +INSERT INTO t1 values (1, 3, UNIX_TIMESTAMP(), 5); +INSERT INTO t1 values (2, 4, UNIX_TIMESTAMP(), 6); +set global rocksdb_debug_ttl_rec_ts = 0; +SELECT COUNT(*) FROM t1 FORCE INDEX (kb); +COUNT(*) +2 +set global rocksdb_debug_ttl_ignore_pk=1; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_ignore_pk=0; +SELECT COUNT(*) FROM t1; +COUNT(*) +0 +DROP TABLE t1; +CREATE TABLE t1 ( +`a` binary(8) NOT NULL, +`b` varbinary(64), +`c` varbinary(256), +`ts` bigint(20) UNSIGNED NOT NULL, +`value` mediumblob NOT NULL, +PRIMARY KEY (`a`, `ts`), +KEY kb (`b`) +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 +COMMENT='ttl_duration=1;ttl_col=ts;'; +set global rocksdb_debug_ttl_rec_ts = -100; +INSERT INTO t1 values ('a', NULL, 'bc', UNIX_TIMESTAMP(), 'd'); +INSERT INTO t1 values ('de', 'fghijk', NULL, UNIX_TIMESTAMP(), 'l'); +set global rocksdb_debug_ttl_rec_ts = 0; +SELECT COUNT(*) FROM t1; +COUNT(*) +2 +set global rocksdb_debug_ttl_ignore_pk=1; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_ignore_pk=0; +SELECT COUNT(*) FROM t1; +COUNT(*) +0 +DROP TABLE t1; +CREATE TABLE t1 ( +a INT NOT NULL, +b varbinary(64) NOT NULL, +c varbinary(256) NOT NULL, +ts bigint(20) UNSIGNED NOT NULL, +value mediumblob NOT NULL, +PRIMARY KEY (b,a,c), +KEY kb (b) +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 +COMMENT='ttl_duration=10;ttl_col=ts;'; +set global rocksdb_debug_ttl_rec_ts = -300; +INSERT INTO t1 values (1, 'b', 'c', UNIX_TIMESTAMP(), 'd'); +INSERT INTO t1 values (2, 'e', 'f', UNIX_TIMESTAMP(), 'g'); +set global rocksdb_debug_ttl_rec_ts = 300; +INSERT INTO t1 values (3, 'i', 'j', UNIX_TIMESTAMP(), 'k'); +INSERT INTO t1 values (4, 'm', 'n', UNIX_TIMESTAMP(), 'o'); +set global rocksdb_debug_ttl_rec_ts = 0; +set global rocksdb_debug_ttl_snapshot_ts = -3600; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_snapshot_ts = 0; +SELECT a FROM t1 FORCE INDEX (kb); +a +1 +2 +3 +4 +set global rocksdb_debug_ttl_ignore_pk=1; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_ignore_pk=0; +SELECT a FROM t1 FORCE INDEX (kb); +a +3 +4 +set global rocksdb_debug_ttl_ignore_pk=1; +set global rocksdb_debug_ttl_snapshot_ts = 3600; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_snapshot_ts = 0; +set global rocksdb_debug_ttl_ignore_pk=0; +SELECT a FROM t1 FORCE INDEX (kb); +a +DROP TABLE t1; +CREATE TABLE t1 ( +a bigint(20) UNSIGNED NOT NULL, +b int NOT NULL, +c int NOT NULL, +ts bigint(20), +PRIMARY KEY (a,c), +KEY (b) +) ENGINE=rocksdb +COMMENT='ttl_duration=1;ttl_col=ts;'; +ERROR HY000: TTL column (ts) in MyRocks must be an unsigned non-null 64-bit integer, exist inside the table, and have an accompanying ttl duration. +CREATE TABLE t1 ( +a bigint(20) UNSIGNED NOT NULL, +b int NOT NULL, +c int NOT NULL, +ts int, +PRIMARY KEY (a,c), +KEY (b) +) ENGINE=rocksdb +COMMENT='ttl_duration=1;ttl_col=ts;'; +ERROR HY000: TTL column (ts) in MyRocks must be an unsigned non-null 64-bit integer, exist inside the table, and have an accompanying ttl duration. +CREATE TABLE t1 ( +a bigint(20) UNSIGNED NOT NULL, +b int NOT NULL, +c int NOT NULL, +PRIMARY KEY (a,c), +KEY (b) +) ENGINE=rocksdb +COMMENT='ttl_duration=abc;'; +ERROR HY000: TTL duration (abc) in MyRocks must be an unsigned non-null 64-bit integer. +CREATE TABLE t1 ( +a bigint(20) UNSIGNED NOT NULL, +b int NOT NULL, +c int NOT NULL, +PRIMARY KEY (a,c), +KEY (b) +) ENGINE=rocksdb +COMMENT='ttl_duration=1;ttl_col=abc;'; +ERROR HY000: TTL column (abc) in MyRocks must be an unsigned non-null 64-bit integer, exist inside the table, and have an accompanying ttl duration. +CREATE TABLE t1 ( +a bigint(20) UNSIGNED NOT NULL, +b int NOT NULL, +c int NOT NULL, +PRIMARY KEY (a,c), +KEY (b) +) ENGINE=rocksdb +COMMENT='ttl_col=abc;'; +ERROR HY000: TTL column (abc) in MyRocks must be an unsigned non-null 64-bit integer, exist inside the table, and have an accompanying ttl duration. +CREATE TABLE t1 ( +a bigint(20) NOT NULL, +b int NOT NULL, +PRIMARY KEY (a), +KEY kb (b) +) ENGINE=rocksdb +COMMENT='ttl_duration=500;'; +INSERT INTO t1 values (1, 1); +SELECT COUNT(*) FROM t1 FORCE INDEX (kb); +COUNT(*) +1 +set global rocksdb_debug_ttl_ignore_pk=1; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_ignore_pk=0; +SELECT COUNT(*) FROM t1 FORCE INDEX (kb); +COUNT(*) +1 +DROP TABLE t1; +CREATE TABLE t1 ( +a INT PRIMARY KEY, +b INT NOT NULL, +KEY kb (b) +) ENGINE=rocksdb +COMMENT='ttl_duration=100;'; +INSERT INTO t1 values (1, 1); +SELECT * FROM t1 FORCE INDEX (kb); +a b +1 1 +set global rocksdb_debug_ttl_rec_ts = -300; +ALTER TABLE t1 COMMENT = 'ttl_duration=1'; +set global rocksdb_debug_ttl_rec_ts = 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + `b` int(11) NOT NULL, + PRIMARY KEY (`a`), + KEY `kb` (`b`) +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 COMMENT='ttl_duration=1' +set global rocksdb_debug_ttl_ignore_pk=1; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_ignore_pk=0; +SELECT COUNT(*) FROM t1 FORCE INDEX (kb); +COUNT(*) +0 +DROP TABLE t1; +CREATE TABLE t1 ( +a INT PRIMARY KEY, +b INT, +KEY (b) +) ENGINE=rocksdb +COMMENT='ttl_duration=100;'; +ALTER TABLE t1 DROP PRIMARY KEY; +ERROR HY000: TTL support is currently disabled when table has a hidden PK. +DROP TABLE t1; +CREATE TABLE t1 ( +a INT PRIMARY KEY, +b INT, +KEY kb (b) +) ENGINE=rocksdb +COMMENT='ttl_duration=5;'; +INSERT INTO t1 VALUES (1,1); +INSERT INTO t1 VALUES (2,2); +ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(b); +set global rocksdb_debug_ttl_snapshot_ts = -3600; +set global rocksdb_force_flush_memtable_now=1; +set @@global.rocksdb_compact_cf = 'default'; +set global rocksdb_debug_ttl_snapshot_ts = 0; +SELECT COUNT(*) FROM t1 FORCE INDEX (kb); +COUNT(*) +2 +set global rocksdb_debug_ttl_ignore_pk=1; +set global rocksdb_debug_ttl_snapshot_ts = 3600; +set @@global.rocksdb_compact_cf = 'default'; +set global rocksdb_debug_ttl_snapshot_ts = 0; +set global rocksdb_debug_ttl_ignore_pk=0; +SELECT COUNT(*) FROM t1 FORCE INDEX (kb); +COUNT(*) +0 +DROP TABLE t1; +CREATE TABLE t1 ( +a bigint(20) UNSIGNED NOT NULL, +b int, +PRIMARY KEY (a,b), +KEY kb (b) +) ENGINE=rocksdb +COMMENT='asdadfasdfsadfadf ;ttl_duration=1; asfasdfasdfadfa'; +INSERT INTO t1 values (UNIX_TIMESTAMP(), 1); +SELECT COUNT(*) FROM t1 FORCE INDEX (kb); +COUNT(*) +1 +set global rocksdb_debug_ttl_snapshot_ts = 3600; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_snapshot_ts = 0; +SELECT COUNT(*) FROM t1 FORCE INDEX (kb); +COUNT(*) +0 +ALTER TABLE t1 COMMENT = 'adsf;;ttl_duration=5;asfasdfa;ttl_col=a;asdfasdf;'; +set global rocksdb_debug_ttl_rec_ts = 300; +INSERT INTO t1 values (UNIX_TIMESTAMP(), 2); +set global rocksdb_debug_ttl_rec_ts = 0; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +SELECT COUNT(*) FROM t1 FORCE INDEX (kb); +COUNT(*) +1 +set global rocksdb_debug_ttl_ignore_pk=1; +set global rocksdb_debug_ttl_snapshot_ts = 3600; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_snapshot_ts = 0; +set global rocksdb_debug_ttl_ignore_pk=0; +SELECT COUNT(*) FROM t1 FORCE INDEX (kb); +COUNT(*) +0 +DROP TABLE t1; +CREATE TABLE t1 ( +a bigint(20) NOT NULL, +b int NOT NULL, +PRIMARY KEY (a), +KEY kb (b) +) ENGINE=rocksdb +COMMENT='ttl_duration=5;'; +set global rocksdb_debug_ttl_rec_ts = -300; +INSERT INTO t1 values (1, 0); +INSERT INTO t1 values (3, 0); +INSERT INTO t1 values (5, 0); +set global rocksdb_debug_ttl_rec_ts = 300; +INSERT INTO t1 values (7, 0); +INSERT INTO t1 values (9, 0); +set global rocksdb_debug_ttl_rec_ts = 0; +UPDATE t1 SET a=a+1; +SELECT * FROM t1 FORCE INDEX (kb); +a b +10 0 +2 0 +4 0 +6 0 +8 0 +set global rocksdb_debug_ttl_ignore_pk=1; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_ignore_pk=0; +SELECT * FROM t1; +a b +10 0 +8 0 +DROP TABLE t1; +CREATE TABLE t1 ( +a INT, +b bigint(20) UNSIGNED NOT NULL, +PRIMARY KEY (a), +KEY kb (b) +) ENGINE=rocksdb +COMMENT='ttl_duration=5;ttl_col=b;'; +set global rocksdb_debug_ttl_rec_ts = -300; +INSERT INTO t1 values (1, UNIX_TIMESTAMP()); +INSERT INTO t1 values (3, UNIX_TIMESTAMP()); +INSERT INTO t1 values (5, UNIX_TIMESTAMP()); +INSERT INTO t1 values (7, UNIX_TIMESTAMP()); +set global rocksdb_debug_ttl_rec_ts = 300; +UPDATE t1 SET b=(UNIX_TIMESTAMP()+1) WHERE a < 4; +set global rocksdb_debug_ttl_rec_ts = 0; +SELECT a FROM t1 FORCE INDEX (kb); +a +1 +3 +5 +7 +set global rocksdb_debug_ttl_ignore_pk=1; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_ignore_pk=0; +SELECT a FROM t1 FORCE INDEX (kb); +a +1 +3 +DROP TABLE t1; +CREATE TABLE t1 ( +a bigint(20) NOT NULL, +b int NOT NULL, +PRIMARY KEY (a), +KEY kb (b) +) ENGINE=rocksdb +COMMENT='ttl_duration=1;'; +set global rocksdb_debug_ttl_rec_ts = -100; +INSERT INTO t1 values (1, 1); +INSERT INTO t1 values (2, 1); +INSERT INTO t1 values (3, 1); +set global rocksdb_debug_ttl_rec_ts = 0; +set global rocksdb_enable_ttl=0; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +select variable_value into @c from information_schema.global_status where variable_name='rocksdb_rows_expired'; +set global rocksdb_enable_ttl=1; +set global rocksdb_compact_cf='default'; +select variable_value-@c from information_schema.global_status where variable_name='rocksdb_rows_expired'; +variable_value-@c +6 +SELECT COUNT(*) FROM t1 FORCE INDEX (kb); +COUNT(*) +0 +DROP TABLE t1; +CREATE TABLE t1 ( +a bigint(20) NOT NULL, +b int NOT NULL, +PRIMARY KEY (a), +KEY kb (b) +) ENGINE=rocksdb +COMMENT='ttl_duration=100;'; +INSERT INTO t1 values (1, 1); +INSERT INTO t1 values (2, 2); +INSERT INTO t1 values (3, 3); +select variable_value into @c from information_schema.global_status where variable_name='rocksdb_rows_expired'; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +select variable_value-@c from information_schema.global_status where variable_name='rocksdb_rows_expired'; +variable_value-@c +0 +DROP TABLE t1; +CREATE TABLE t1 ( +a INT, +b bigint(20) UNSIGNED NOT NULL, +PRIMARY KEY (a, b), +KEY kb (b) +) ENGINE=rocksdb +COMMENT='ttl_duration=5;ttl_col=b;'; +set global rocksdb_debug_ttl_rec_ts = -300; +INSERT INTO t1 values (1, UNIX_TIMESTAMP()); +INSERT INTO t1 values (3, UNIX_TIMESTAMP()); +INSERT INTO t1 values (5, UNIX_TIMESTAMP()); +INSERT INTO t1 values (7, UNIX_TIMESTAMP()); +set global rocksdb_debug_ttl_rec_ts = 300; +UPDATE t1 SET b=(UNIX_TIMESTAMP()+1) WHERE a < 4; +set global rocksdb_debug_ttl_rec_ts = 0; +SELECT a FROM t1 FORCE INDEX (kb); +a +1 +3 +5 +7 +set global rocksdb_debug_ttl_ignore_pk=1; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_ignore_pk=0; +SELECT a FROM t1 FORCE INDEX (kb); +a +1 +3 +DROP TABLE t1; +CREATE TABLE t1 ( +a INT, +b bigint(20) UNSIGNED NOT NULL, +PRIMARY KEY (a, b) +) ENGINE=rocksdb +COMMENT='ttl_duration=5;ttl_col=b;'; +set global rocksdb_debug_ttl_rec_ts = -300; +INSERT INTO t1 values (1, UNIX_TIMESTAMP()); +INSERT INTO t1 values (3, UNIX_TIMESTAMP()); +INSERT INTO t1 values (5, UNIX_TIMESTAMP()); +INSERT INTO t1 values (7, UNIX_TIMESTAMP()); +set global rocksdb_debug_ttl_rec_ts = 300; +UPDATE t1 SET b=(UNIX_TIMESTAMP()+1) WHERE a < 4; +set global rocksdb_debug_ttl_rec_ts = 0; +SELECT a FROM t1; +a +1 +3 +5 +7 +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +SELECT a FROM t1; +a +1 +3 +DROP TABLE t1; +CREATE TABLE t1 ( +`a` binary(8) NOT NULL, +`b` varbinary(64) NOT NULL, +`c` varbinary(256) NOT NULL, +`ts` bigint(20) UNSIGNED NOT NULL, +`value` mediumblob NOT NULL, +PRIMARY KEY (`b`,`a`,`c`) +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 +COMMENT='ttl_duration=1;ttl_col=ts;'; +set global rocksdb_debug_ttl_rec_ts = -100; +INSERT INTO t1 values ('a', 'b', 'c', UNIX_TIMESTAMP(), 'd'); +INSERT INTO t1 values ('d', 'e', 'f', UNIX_TIMESTAMP(), 'g'); +set global rocksdb_debug_ttl_rec_ts = 0; +SELECT COUNT(*); +COUNT(*) +1 +set global rocksdb_debug_ttl_ignore_pk = 1; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_ignore_pk = 0; +SELECT COUNT(*); +COUNT(*) +1 +CREATE INDEX kb on t1 (b); +set global rocksdb_debug_ttl_ignore_pk = 1; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_ignore_pk = 0; +SELECT COUNT(*) FROM t1 FORCE INDEX (kb); +COUNT(*) +0 +DROP TABLE t1; +CREATE TABLE t1 ( +`a` binary(8) NOT NULL, +`b` varbinary(64) NOT NULL, +`c` varbinary(256) NOT NULL, +`value` mediumblob NOT NULL, +PRIMARY KEY (`b`,`a`,`c`) +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 +COMMENT='ttl_duration=1'; +set global rocksdb_debug_ttl_rec_ts = -100; +INSERT INTO t1 values ('a', 'b', 'c', 'd'); +INSERT INTO t1 values ('d', 'e', 'f', 'g'); +set global rocksdb_debug_ttl_rec_ts = 0; +SELECT COUNT(*); +COUNT(*) +1 +set global rocksdb_debug_ttl_ignore_pk = 1; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_ignore_pk = 0; +SELECT COUNT(*); +COUNT(*) +1 +CREATE INDEX kb on t1 (b); +set global rocksdb_debug_ttl_ignore_pk = 1; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_ignore_pk = 0; +SELECT COUNT(*) FROM t1 FORCE INDEX (kb); +COUNT(*) +0 +DROP TABLE t1; +CREATE TABLE t1 ( +`a` binary(8) NOT NULL, +`b` varbinary(64) NOT NULL, +`c` varbinary(256) NOT NULL, +`ts` bigint(20) UNSIGNED NOT NULL, +`value` mediumblob NOT NULL, +PRIMARY KEY (`b`,`a`,`c`, `ts`) +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 +COMMENT='ttl_duration=1;ttl_col=ts;'; +set global rocksdb_debug_ttl_rec_ts = -100; +INSERT INTO t1 values ('a', 'b', 'c', UNIX_TIMESTAMP(), 'd'); +INSERT INTO t1 values ('d', 'e', 'f', UNIX_TIMESTAMP(), 'g'); +set global rocksdb_debug_ttl_rec_ts = 0; +SELECT COUNT(*); +COUNT(*) +1 +set global rocksdb_debug_ttl_ignore_pk = 1; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_ignore_pk = 0; +SELECT COUNT(*); +COUNT(*) +1 +CREATE INDEX kb on t1 (b); +set global rocksdb_debug_ttl_ignore_pk = 1; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_ignore_pk = 0; +SELECT COUNT(*) FROM t1 FORCE INDEX (kb); +COUNT(*) +0 +DROP TABLE t1; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/ttl_secondary_read_filtering.result b/storage/rocksdb/mysql-test/rocksdb/r/ttl_secondary_read_filtering.result new file mode 100644 index 00000000000..90de5447891 --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/ttl_secondary_read_filtering.result @@ -0,0 +1,510 @@ +CREATE TABLE t1 ( +a int PRIMARY KEY, +b int NOT NULL, +KEY kb (b) +) ENGINE=rocksdb +COMMENT='ttl_duration=1;'; +set global rocksdb_debug_ttl_rec_ts = -100; +INSERT INTO t1 values (1, 1); +INSERT INTO t1 values (2, 2); +set global rocksdb_debug_ttl_rec_ts = 0; +set global rocksdb_force_flush_memtable_now=1; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +a b +SELECT * FROM t1 FORCE INDEX (kb); +a b +select variable_value into @c from information_schema.global_status where variable_name='rocksdb_rows_expired'; +set global rocksdb_debug_ttl_ignore_pk = 1; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_ignore_pk = 0; +select variable_value-@c from information_schema.global_status where variable_name='rocksdb_rows_expired'; +variable_value-@c +2 +DROP TABLE t1; +CREATE TABLE t1 ( +a int PRIMARY KEY, +b BIGINT UNSIGNED NOT NULL, +KEY kb (b) +) ENGINE=rocksdb +COMMENT='ttl_duration=10;'; +set global rocksdb_debug_ttl_rec_ts = -300; +INSERT INTO t1 values (1, UNIX_TIMESTAMP()); +set global rocksdb_debug_ttl_rec_ts = 300; +INSERT INTO t1 values (2, UNIX_TIMESTAMP()); +INSERT INTO t1 values (3, UNIX_TIMESTAMP()); +set global rocksdb_debug_ttl_rec_ts = 0; +set global rocksdb_force_flush_memtable_now=1; +# 1 should be hidden +SELECT a FROM t1 FORCE INDEX (PRIMARY); +a +2 +3 +SELECT a FROM t1 FORCE INDEX (kb); +a +2 +3 +set global rocksdb_debug_ttl_ignore_pk = 1; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_ignore_pk = 0; +# none should be hidden yet, compaction runs but records aren't expired +SELECT a FROM t1 FORCE INDEX (PRIMARY); +a +2 +3 +SELECT a FROM t1 FORCE INDEX (kb); +a +2 +3 +# all should be hidden now, even though compaction hasn't run again +set global rocksdb_debug_ttl_read_filter_ts = -310; +SELECT a FROM t1 FORCE INDEX (PRIMARY); +a +SELECT a FROM t1 FORCE INDEX (kb); +a +set global rocksdb_debug_ttl_read_filter_ts = 0; +DROP TABLE t1; +CREATE TABLE t1 ( +a int PRIMARY KEY, +b int NOT NULL, +KEY kb (b) +) ENGINE=rocksdb +COMMENT='ttl_duration=1;'; +set global rocksdb_debug_ttl_rec_ts = -100; +INSERT INTO t1 values (1, 1); +INSERT INTO t1 values (3, 3); +INSERT INTO t1 values (5, 5); +INSERT INTO t1 values (7, 7); +set global rocksdb_debug_ttl_rec_ts = 0; +# should return nothing. +SELECT * FROM t1 FORCE INDEX (PRIMARY); +a b +SELECT * FROM t1 FORCE INDEX (kb); +a b +set global rocksdb_enable_ttl_read_filtering=0; +# should return everything +SELECT * FROM t1 FORCE INDEX (PRIMARY); +a b +1 1 +3 3 +5 5 +7 7 +SELECT * FROM t1 FORCE INDEX (kb); +a b +1 1 +3 3 +5 5 +7 7 +set global rocksdb_enable_ttl_read_filtering=1; +# should return nothing. +SELECT * FROM t1 FORCE INDEX (PRIMARY); +a b +SELECT * FROM t1 FORCE INDEX (kb); +a b +DROP TABLE t1; +# Read filtering index scan tests (None of these queries should return any results) +CREATE TABLE t1 ( +a int, +b int, +c int, +PRIMARY KEY (a,b,c), +KEY kb (b) +) ENGINE=rocksdb +COMMENT='ttl_duration=1;'; +set global rocksdb_debug_ttl_rec_ts = -100; +INSERT INTO t1 values (0,0,0); +INSERT INTO t1 values (0,0,1); +INSERT INTO t1 values (0,1,0); +INSERT INTO t1 values (0,1,1); +INSERT INTO t1 values (1,1,2); +INSERT INTO t1 values (1,2,1); +INSERT INTO t1 values (1,2,2); +INSERT INTO t1 values (1,2,3); +set global rocksdb_debug_ttl_rec_ts = 0; +select variable_value into @c from information_schema.global_status where variable_name='rocksdb_rows_expired'; +set global rocksdb_force_flush_memtable_now=1; +SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE a=1 AND b=2 AND c=2; +a b c +SELECT * FROM t1 FORCE INDEX (kb) WHERE a=1 AND b=2 AND c=2; +a b c +SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE a = 1; +a b c +SELECT * FROM t1 FORCE INDEX (kb) WHERE a = 1; +a b c +SELECT max(a) FROM t1 FORCE INDEX (PRIMARY) WHERE a < 3; +max(a) +NULL +SELECT max(a) FROM t1 FORCE INDEX (kb) WHERE a < 3; +max(a) +NULL +SELECT max(a) FROM t1 FORCE INDEX (PRIMARY) WHERE a < 2 AND b = 1 AND c < 3; +max(a) +NULL +SELECT max(a) FROM t1 FORCE INDEX (kb) WHERE a < 2 AND b = 1 AND c < 3; +max(a) +NULL +SELECT min(a) FROM t1 FORCE INDEX (PRIMARY) WHERE a >= 1; +min(a) +NULL +SELECT min(a) FROM t1 FORCE INDEX (kb) WHERE a >= 1; +min(a) +NULL +SELECT min(a) FROM t1 FORCE INDEX (PRIMARY) WHERE a > 1; +min(a) +NULL +SELECT min(a) FROM t1 FORCE INDEX (kb) WHERE a > 1; +min(a) +NULL +SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE a=1 and b in (1) order by c desc; +a b c +SELECT * FROM t1 FORCE INDEX (kb) WHERE a=1 and b in (1) order by c desc; +a b c +SELECT max(a) FROM t1 FORCE INDEX (PRIMARY) WHERE a <=10; +max(a) +NULL +SELECT max(a) FROM t1 FORCE INDEX (kb) WHERE a <=10; +max(a) +NULL +SELECT a FROM t1 FORCE INDEX (PRIMARY) WHERE a > 0 and a <= 2; +a +SELECT a FROM t1 FORCE INDEX (kb) WHERE a > 0 and a <= 2; +a +select variable_value-@c from information_schema.global_status where variable_name='rocksdb_rows_expired'; +variable_value-@c +0 +set global rocksdb_debug_ttl_ignore_pk = 1; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_ignore_pk = 0; +select variable_value-@c from information_schema.global_status where variable_name='rocksdb_rows_expired'; +variable_value-@c +8 +DROP TABLE t1; +# Attempt to update expired value, should filter out +set global rocksdb_force_flush_memtable_now=1; +CREATE TABLE t1 ( +a int PRIMARY KEY +) ENGINE=rocksdb +COMMENT='ttl_duration=1;'; +set global rocksdb_debug_ttl_rec_ts = -100; +INSERT INTO t1 values (1); +set global rocksdb_debug_ttl_rec_ts = 0; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +a +SELECT * FROM t1; +a +# No error is thrown here, under the hood index_next_with_direction is +# filtering out the record from being seen in the first place. +UPDATE t1 set a = 1; +DROP TABLE t1; +# Ensure no rows can disappear in the middle of long-running transactions +# Also ensure repeatable-read works as expected +connect con1,localhost,root,,; +connect con2,localhost,root,,; +CREATE TABLE t1 ( +a int PRIMARY KEY, +b int NOT NULL, +KEY kb (b) +) ENGINE=rocksdb +COMMENT='ttl_duration=5;'; +INSERT INTO t1 values (1, 1); +connection con1; +# Creating Snapshot (start transaction) +BEGIN; +# Nothing filtered out here +SELECT * FROM t1 FORCE INDEX (PRIMARY); +a b +1 1 +SELECT * FROM t1 FORCE INDEX (kb); +a b +1 1 +SELECT * FROM t1 FORCE INDEX (PRIMARY); +a b +1 1 +SELECT * FROM t1 FORCE INDEX (kb); +a b +1 1 +# Switching to connection 2 +connection con2; +# compaction doesn't do anything since con1 snapshot is still open +set global rocksdb_debug_ttl_ignore_pk = 1; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_ignore_pk = 0; +# read filtered out, because on a different connection, on +# this connection the records have 'expired' already so they are filtered out +# even though they have not yet been removed by compaction +SELECT * FROM t1 FORCE INDEX (PRIMARY); +a b +SELECT * FROM t1 FORCE INDEX (kb); +a b +# Switching to connection 1 +connection con1; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +a b +1 1 +SELECT * FROM t1 FORCE INDEX (kb); +a b +1 1 +UPDATE t1 set a = a + 1; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +a b +2 1 +SELECT * FROM t1 FORCE INDEX (kb); +a b +2 1 +COMMIT; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +a b +SELECT * FROM t1 FORCE INDEX (kb); +a b +DROP TABLE t1; +disconnect con1; +disconnect con2; +connect con1,localhost,root,,; +connect con2,localhost,root,,; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +CREATE TABLE t1 ( +a int PRIMARY KEY, +b int NOT NULL, +KEY kb (b) +) ENGINE=rocksdb +COMMENT='ttl_duration=1;'; +# On Connection 1 +connection con1; +# Creating Snapshot (start transaction) +BEGIN; +SELECT * FROM t1 FORCE INDEX (kb); +a b +# On Connection 2 +connection con2; +set global rocksdb_debug_ttl_rec_ts = -2; +INSERT INTO t1 values (1, 1); +INSERT INTO t1 values (3, 3); +INSERT INTO t1 values (5, 5); +INSERT INTO t1 values (7, 7); +set global rocksdb_debug_ttl_rec_ts = 0; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +# On Connection 1 +connection con1; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +a b +SELECT * FROM t1 FORCE INDEX (kb); +a b +# On Connection 2 +connection con2; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +a b +SELECT * FROM t1 FORCE INDEX (kb); +a b +set global rocksdb_enable_ttl_read_filtering=0; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +a b +1 1 +3 3 +5 5 +7 7 +SELECT * FROM t1 FORCE INDEX (kb); +a b +1 1 +3 3 +5 5 +7 7 +set global rocksdb_enable_ttl_read_filtering=1; +disconnect con2; +disconnect con1; +connection default; +DROP TABLE t1; +CREATE TABLE t1 ( +a int, +b int, +ts bigint(20) UNSIGNED NOT NULL, +PRIMARY KEY (a), +KEY kb (b) +) ENGINE=rocksdb +COMMENT='ttl_duration=1;ttl_col=ts;'; +set global rocksdb_debug_ttl_rec_ts = 100; +INSERT INTO t1 VALUES (1, 1, UNIX_TIMESTAMP()); +INSERT INTO t1 VALUES (2, 2, UNIX_TIMESTAMP()); +INSERT INTO t1 VALUES (3, 3, UNIX_TIMESTAMP()); +INSERT INTO t1 VALUES (4, 4, UNIX_TIMESTAMP()); +INSERT INTO t1 VALUES (5, 5, UNIX_TIMESTAMP()); +INSERT INTO t1 VALUES (6, 6, UNIX_TIMESTAMP()); +INSERT INTO t1 VALUES (7, 7, UNIX_TIMESTAMP()); +INSERT INTO t1 VALUES (8, 8, UNIX_TIMESTAMP()); +INSERT INTO t1 VALUES (9, 9, UNIX_TIMESTAMP()); +INSERT INTO t1 VALUES (10, 10, UNIX_TIMESTAMP()); +set global rocksdb_debug_ttl_rec_ts = 0; +set global rocksdb_force_flush_memtable_now=1; +# None are expired +SELECT a, b FROM t1 FORCE INDEX (kb); +a b +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +10 10 +set global rocksdb_debug_ttl_rec_ts = -100; +UPDATE t1 SET ts=(UNIX_TIMESTAMP()+1) WHERE a IN (4, 7); +set global rocksdb_debug_ttl_rec_ts = 0; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +# 4 and 7 should be gone +SELECT a, b FROM t1 FORCE INDEX (kb); +a b +1 1 +2 2 +3 3 +5 5 +6 6 +8 8 +9 9 +10 10 +DROP TABLE t1; +CREATE TABLE t1 ( +c1 INT, +c2 INT, +name VARCHAR(25) NOT NULL, +PRIMARY KEY (c1, c2), +KEY kc2 (c2) +) ENGINE=ROCKSDB +COMMENT='ttl_duration=1;'; +set global rocksdb_debug_ttl_rec_ts = -1200; +INSERT INTO t1 values (1,1,'a'); +INSERT INTO t1 values (2,2,'b'); +set global rocksdb_debug_ttl_rec_ts = 1200; +INSERT INTO t1 values (3,3,'c'); +INSERT INTO t1 values (4,4,'d'); +set global rocksdb_debug_ttl_rec_ts = -1200; +INSERT INTO t1 values (5,5,'e'); +INSERT INTO t1 values (6,6,'f'); +set global rocksdb_debug_ttl_rec_ts = 1200; +INSERT INTO t1 values (7,7,'g'); +INSERT INTO t1 values (8,8,'h'); +set global rocksdb_debug_ttl_rec_ts = 0; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +c1 c2 name +3 3 c +4 4 d +7 7 g +8 8 h +SELECT * FROM t1 FORCE INDEX (kc2); +c1 c2 name +3 3 c +4 4 d +7 7 g +8 8 h +SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE c1 > 5; +c1 c2 name +7 7 g +8 8 h +SELECT * FROM t1 FORCE INDEX (kc2) WHERE c2 > 5; +c1 c2 name +7 7 g +8 8 h +SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE 3 < c1 AND c1 < 6; +c1 c2 name +4 4 d +SELECT * FROM t1 FORCE INDEX (kc2) WHERE 3 < c2 AND c2 < 6; +c1 c2 name +4 4 d +DROP TABLE t1; +CREATE TABLE t1 ( +a int, +b int, +PRIMARY KEY (a), +KEY kb (b) +) ENGINE=rocksdb +COMMENT='ttl_duration=1800;'; +set global rocksdb_debug_ttl_rec_ts = 0; +INSERT INTO t1 values (1,1); +INSERT INTO t1 values (2,2); +INSERT INTO t1 values (7,7); +INSERT INTO t1 values (10,10); +INSERT INTO t1 values (11,11); +INSERT INTO t1 values (12,12); +set global rocksdb_debug_ttl_rec_ts = 450; +INSERT INTO t1 values (3,3); +INSERT INTO t1 values (4,4); +INSERT INTO t1 values (8,8); +INSERT INTO t1 values (16,16); +INSERT INTO t1 values (17,17); +INSERT INTO t1 values (18,18); +set global rocksdb_debug_ttl_rec_ts = 900; +INSERT INTO t1 values (5,5); +INSERT INTO t1 values (6,6); +INSERT INTO t1 values (9,9); +INSERT INTO t1 values (13,13); +INSERT INTO t1 values (14,14); +INSERT INTO t1 values (15,15); +set global rocksdb_debug_ttl_rec_ts = 0; +# Should see everything +SELECT * FROM t1; +a b +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +10 10 +11 11 +12 12 +13 13 +14 14 +15 15 +16 16 +17 17 +18 18 +# Should have no records from the first group +set global rocksdb_debug_ttl_read_filter_ts = -1800; +SELECT * FROM t1; +a b +3 3 +4 4 +5 5 +6 6 +8 8 +9 9 +13 13 +14 14 +15 15 +16 16 +17 17 +18 18 +SELECT * FROM t1 FORCE INDEX (kb) WHERE a > 5 AND a < 15; +a b +6 6 +8 8 +9 9 +13 13 +14 14 +# Should only have records from the last group +set global rocksdb_debug_ttl_read_filter_ts = -1800 - 450; +SELECT * FROM t1; +a b +5 5 +6 6 +9 9 +13 13 +14 14 +15 15 +SELECT * FROM t1 FORCE INDEX (kb) WHERE a < 10; +a b +5 5 +6 6 +9 9 +# Should be empty +set global rocksdb_debug_ttl_read_filter_ts = -1800 - 900; +SELECT * FROM t1; +a b +set global rocksdb_debug_ttl_read_filter_ts = 0; +DROP TABLE t1; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/ttl_secondary_read_filtering_multiple_index.result b/storage/rocksdb/mysql-test/rocksdb/r/ttl_secondary_read_filtering_multiple_index.result new file mode 100644 index 00000000000..e4c361576f5 --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/ttl_secondary_read_filtering_multiple_index.result @@ -0,0 +1,82 @@ +CREATE TABLE t1 ( +a int NOT NULL, +b int NOT NULL, +c int NOT NULL, +PRIMARY KEY (a), +KEY kb (b) COMMENT 'kb', +KEY kc (c) COMMENT 'kc' +) ENGINE=ROCKSDB +COMMENT='ttl_duration=1'; +set global rocksdb_debug_ttl_rec_ts = -100; +INSERT INTO t1 values (1, 1, 1); +INSERT INTO t1 values (2, 2, 2); +set global rocksdb_debug_ttl_rec_ts = 100; +INSERT INTO t1 values (3, 3, 3); +set global rocksdb_debug_ttl_rec_ts = 0; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='kb'; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +a b c +3 3 3 +SELECT * FROM t1 FORCE INDEX (kb); +a b c +3 3 3 +SELECT * FROM t1 FORCE INDEX (kc); +a b c +3 3 3 +DROP TABLE t1; +CREATE TABLE t1 ( +a int NOT NULL, +b int NOT NULL, +c int NOT NULL, +PRIMARY KEY (a), +KEY kb (b) COMMENT 'kb', +KEY kc (c) COMMENT 'kc' +) ENGINE=ROCKSDB +COMMENT='ttl_duration=1'; +set global rocksdb_debug_ttl_rec_ts = -100; +INSERT INTO t1 values (1, 1, 1); +INSERT INTO t1 values (2, 2, 2); +set global rocksdb_debug_ttl_rec_ts = 100; +INSERT INTO t1 values (3, 3, 3); +set global rocksdb_debug_ttl_rec_ts = 0; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +a b c +3 3 3 +SELECT * FROM t1 FORCE INDEX (kb); +a b c +3 3 3 +SELECT * FROM t1 FORCE INDEX (kc); +a b c +3 3 3 +DROP TABLE t1; +CREATE TABLE t1 ( +a int NOT NULL, +b int NOT NULL, +c int NOT NULL, +PRIMARY KEY (a), +KEY kb (b) COMMENT 'kb', +KEY kc (c) COMMENT 'kc' +) ENGINE=ROCKSDB +COMMENT='ttl_duration=1'; +set global rocksdb_debug_ttl_rec_ts = -100; +INSERT INTO t1 values (1, 1, 1); +INSERT INTO t1 values (2, 2, 2); +set global rocksdb_debug_ttl_rec_ts = 100; +INSERT INTO t1 values (3, 3, 3); +set global rocksdb_debug_ttl_rec_ts = 0; +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +set global rocksdb_compact_cf='kb'; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +a b c +3 3 3 +SELECT * FROM t1 FORCE INDEX (kb); +a b c +3 3 3 +SELECT * FROM t1 FORCE INDEX (kc); +a b c +3 3 3 +DROP TABLE t1; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/ttl_secondary_with_partitions.result b/storage/rocksdb/mysql-test/rocksdb/r/ttl_secondary_with_partitions.result new file mode 100644 index 00000000000..713c7e92fa8 --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/ttl_secondary_with_partitions.result @@ -0,0 +1,389 @@ +CREATE TABLE t1 ( +c1 INT, +c2 INT, +PRIMARY KEY (`c1`), +KEY kc2 (`c2`) +) ENGINE=ROCKSDB +COMMENT="custom_p0_ttl_duration=1;" +PARTITION BY LIST(c1) ( +PARTITION custom_p0 VALUES IN (1, 4, 7), +PARTITION custom_p1 VALUES IN (2, 5, 8), +PARTITION custom_p2 VALUES IN (3, 6, 9) +); +set global rocksdb_debug_ttl_rec_ts = -3600; +INSERT INTO t1 values (1, 1); +INSERT INTO t1 values (2, 2); +INSERT INTO t1 values (3, 3); +INSERT INTO t1 values (4, 4); +INSERT INTO t1 values (5, 5); +INSERT INTO t1 values (6, 6); +INSERT INTO t1 values (7, 7); +INSERT INTO t1 values (8, 8); +INSERT INTO t1 values (9, 9); +set global rocksdb_debug_ttl_rec_ts = 0; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +c1 c2 +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +SELECT * FROM t1 FORCE INDEX (kc2); +c1 c2 +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='default'; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +c1 c2 +2 2 +3 3 +5 5 +6 6 +8 8 +9 9 +SELECT * FROM t1 FORCE INDEX (kc2); +c1 c2 +2 2 +3 3 +5 5 +6 6 +8 8 +9 9 +DROP TABLE t1; +CREATE TABLE t1 ( +c1 INT, +c2 INT, +name VARCHAR(25) NOT NULL, +PRIMARY KEY (`c1`, `c2`) COMMENT 'custom_p0_cfname=foo;custom_p1_cfname=my_custom_cf;custom_p2_cfname=baz', +KEY kc2 (`c2`) +) ENGINE=ROCKSDB +COMMENT="custom_p0_ttl_duration=1;custom_p1_ttl_duration=7;" +PARTITION BY LIST(c1) ( +PARTITION custom_p0 VALUES IN (1, 4, 7), +PARTITION custom_p1 VALUES IN (2, 5, 8), +PARTITION custom_p2 VALUES IN (3, 6, 9) +); +set global rocksdb_debug_ttl_rec_ts = -1200; +INSERT INTO t1 values (1,1,'a'); +INSERT INTO t1 values (4,4,'aaaa'); +INSERT INTO t1 values (7,7,'aaaaaaa'); +set global rocksdb_debug_ttl_rec_ts = 1200; +INSERT INTO t1 values (2,2,'aa'); +INSERT INTO t1 values (3,3,'aaa'); +INSERT INTO t1 values (5,5,'aaaaa'); +INSERT INTO t1 values (6,6,'aaaaaa'); +INSERT INTO t1 values (8,8,'aaaaaaaa'); +INSERT INTO t1 values (9,9,'aaaaaaaaa'); +set global rocksdb_debug_ttl_rec_ts = 0; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +c1 c2 name +1 1 a +2 2 aa +3 3 aaa +4 4 aaaa +5 5 aaaaa +6 6 aaaaaa +7 7 aaaaaaa +8 8 aaaaaaaa +9 9 aaaaaaaaa +SELECT * FROM t1 FORCE INDEX (kc2); +c1 c2 name +1 1 a +2 2 aa +3 3 aaa +4 4 aaaa +5 5 aaaaa +6 6 aaaaaa +7 7 aaaaaaa +8 8 aaaaaaaa +9 9 aaaaaaaaa +set global rocksdb_force_flush_memtable_now=1; +set @@global.rocksdb_compact_cf = 'foo'; +set @@global.rocksdb_compact_cf = 'my_custom_cf'; +set @@global.rocksdb_compact_cf = 'default'; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +c1 c2 name +2 2 aa +3 3 aaa +5 5 aaaaa +6 6 aaaaaa +8 8 aaaaaaaa +9 9 aaaaaaaaa +SELECT * FROM t1 FORCE INDEX (kc2); +c1 c2 name +2 2 aa +3 3 aaa +5 5 aaaaa +6 6 aaaaaa +8 8 aaaaaaaa +9 9 aaaaaaaaa +set global rocksdb_debug_ttl_snapshot_ts = 3600; +set @@global.rocksdb_compact_cf = 'foo'; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +c1 c2 name +2 2 aa +3 3 aaa +5 5 aaaaa +6 6 aaaaaa +8 8 aaaaaaaa +9 9 aaaaaaaaa +SELECT * FROM t1 FORCE INDEX (kc2); +c1 c2 name +2 2 aa +3 3 aaa +5 5 aaaaa +6 6 aaaaaa +8 8 aaaaaaaa +9 9 aaaaaaaaa +set @@global.rocksdb_compact_cf = 'my_custom_cf'; +set @@global.rocksdb_compact_cf = 'default'; +set global rocksdb_debug_ttl_snapshot_ts = 0; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +c1 c2 name +3 3 aaa +6 6 aaaaaa +9 9 aaaaaaaaa +SELECT * FROM t1 FORCE INDEX (kc2); +c1 c2 name +3 3 aaa +6 6 aaaaaa +9 9 aaaaaaaaa +DROP TABLE t1; +CREATE TABLE t1 ( +c1 INT, +c2 INT, +name VARCHAR(25) NOT NULL, +event DATE, +PRIMARY KEY (`c1`, `c2`) COMMENT 'custom_p0_cfname=foo;custom_p1_cfname=bar;custom_p2_cfname=baz;', +KEY kc2 (c2) +) ENGINE=ROCKSDB +COMMENT="custom_p0_ttl_duration=9999;custom_p2_ttl_duration=5;" +PARTITION BY LIST(c1) ( +PARTITION custom_p0 VALUES IN (1, 2, 3), +PARTITION custom_p1 VALUES IN (4, 5, 6), +PARTITION custom_p2 VALUES IN (7, 8, 9) +); +INSERT INTO t1 VALUES (1, 1, "one", null); +INSERT INTO t1 VALUES (2, 2, "two", null); +INSERT INTO t1 VALUES (3, 3, "three", null); +INSERT INTO t1 VALUES (4, 4, "four", null); +INSERT INTO t1 VALUES (5, 5, "five", null); +INSERT INTO t1 VALUES (6, 6, "six", null); +INSERT INTO t1 VALUES (7, 7, "seven", null); +INSERT INTO t1 VALUES (8, 8, "eight", null); +INSERT INTO t1 VALUES (9, 9, "nine", null); +SELECT * FROM t1 FORCE INDEX (PRIMARY); +c1 c2 name event +1 1 one NULL +2 2 two NULL +3 3 three NULL +4 4 four NULL +5 5 five NULL +6 6 six NULL +7 7 seven NULL +8 8 eight NULL +9 9 nine NULL +SELECT * FROM t1 FORCE INDEX (kc2); +c1 c2 name event +1 1 one NULL +2 2 two NULL +3 3 three NULL +4 4 four NULL +5 5 five NULL +6 6 six NULL +7 7 seven NULL +8 8 eight NULL +9 9 nine NULL +set global rocksdb_debug_ttl_rec_ts = 600; +ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(`c2`,`c1`) COMMENT 'custom_p0_cfname=foo;custom_p1_cfname=bar;custom_p2_cfname=baz;'; +set global rocksdb_debug_ttl_rec_ts = 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL, + `c2` int(11) NOT NULL, + `name` varchar(25) NOT NULL, + `event` date DEFAULT NULL, + PRIMARY KEY (`c2`,`c1`) COMMENT 'custom_p0_cfname=foo;custom_p1_cfname=bar;custom_p2_cfname=baz;', + KEY `kc2` (`c2`) +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 COMMENT='custom_p0_ttl_duration=9999;custom_p2_ttl_duration=5;' + PARTITION BY LIST (`c1`) +(PARTITION `custom_p0` VALUES IN (1,2,3) ENGINE = ROCKSDB, + PARTITION `custom_p1` VALUES IN (4,5,6) ENGINE = ROCKSDB, + PARTITION `custom_p2` VALUES IN (7,8,9) ENGINE = ROCKSDB) +set global rocksdb_debug_ttl_snapshot_ts = 100; +set global rocksdb_force_flush_memtable_now=1; +set @@global.rocksdb_compact_cf = 'baz'; +set @@global.rocksdb_compact_cf = 'default'; +set global rocksdb_debug_ttl_snapshot_ts = 0; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +c1 c2 name event +1 1 one NULL +2 2 two NULL +3 3 three NULL +4 4 four NULL +5 5 five NULL +6 6 six NULL +7 7 seven NULL +8 8 eight NULL +9 9 nine NULL +SELECT * FROM t1 FORCE INDEX (kc2); +c1 c2 name event +1 1 one NULL +2 2 two NULL +3 3 three NULL +4 4 four NULL +5 5 five NULL +6 6 six NULL +7 7 seven NULL +8 8 eight NULL +9 9 nine NULL +set global rocksdb_debug_ttl_snapshot_ts = 1200; +set @@global.rocksdb_compact_cf = 'foo'; +set @@global.rocksdb_compact_cf = 'baz'; +set @@global.rocksdb_compact_cf = 'default'; +set global rocksdb_debug_ttl_snapshot_ts = 0; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +c1 c2 name event +1 1 one NULL +2 2 two NULL +3 3 three NULL +4 4 four NULL +5 5 five NULL +6 6 six NULL +SELECT * FROM t1 FORCE INDEX (kc2); +c1 c2 name event +1 1 one NULL +2 2 two NULL +3 3 three NULL +4 4 four NULL +5 5 five NULL +6 6 six NULL +DROP TABLE t1; +CREATE TABLE t1 ( +c1 BIGINT, +c2 BIGINT UNSIGNED NOT NULL, +name VARCHAR(25) NOT NULL, +event DATE, +PRIMARY KEY (`c1`) COMMENT 'custom_p0_cfname=foo;custom_p1_cfname=bar;custom_p2_cfname=baz;', +KEY kc2 (`c2`) +) ENGINE=ROCKSDB +COMMENT="ttl_duration=1;custom_p1_ttl_duration=100;custom_p1_ttl_col=c2;custom_p2_ttl_duration=5000;" +PARTITION BY LIST(c1) ( +PARTITION custom_p0 VALUES IN (1, 2, 3), +PARTITION custom_p1 VALUES IN (4, 5, 6), +PARTITION custom_p2 VALUES IN (7, 8, 9) +); +set global rocksdb_debug_ttl_rec_ts = -300; +INSERT INTO t1 VALUES (1, UNIX_TIMESTAMP(), "one", null); +INSERT INTO t1 VALUES (2, UNIX_TIMESTAMP(), "two", null); +INSERT INTO t1 VALUES (3, UNIX_TIMESTAMP(), "three", null); +set global rocksdb_debug_ttl_rec_ts = 0; +INSERT INTO t1 VALUES (4, UNIX_TIMESTAMP(), "four", null); +INSERT INTO t1 VALUES (5, UNIX_TIMESTAMP(), "five", null); +INSERT INTO t1 VALUES (6, UNIX_TIMESTAMP(), "six", null); +INSERT INTO t1 VALUES (7, UNIX_TIMESTAMP(), "seven", null); +INSERT INTO t1 VALUES (8, UNIX_TIMESTAMP(), "eight", null); +INSERT INTO t1 VALUES (9, UNIX_TIMESTAMP(), "nine", null); +set global rocksdb_force_flush_memtable_now=1; +set @@global.rocksdb_compact_cf = 'foo'; +set @@global.rocksdb_compact_cf = 'baz'; +set @@global.rocksdb_compact_cf = 'bar'; +set @@global.rocksdb_compact_cf = 'default'; +SELECT c1 FROM t1 FORCE INDEX (PRIMARY); +c1 +4 +5 +6 +7 +8 +9 +SELECT c1 FROM t1 FORCE INDEX (kc2); +c1 +4 +5 +6 +7 +8 +9 +set global rocksdb_debug_ttl_snapshot_ts = 600; +set @@global.rocksdb_compact_cf = 'bar'; +set @@global.rocksdb_compact_cf = 'default'; +set global rocksdb_debug_ttl_snapshot_ts = 0; +SELECT c1 FROM t1 FORCE INDEX (PRIMARY); +c1 +7 +8 +9 +SELECT c1 FROM t1 FORCE INDEX (kc2); +c1 +7 +8 +9 +DROP TABLE t1; +CREATE TABLE t1 ( +c1 INT, +c2 INT, +PRIMARY KEY (`c1`) COMMENT 'custom_p0_cfname=foo;' +) ENGINE=ROCKSDB +COMMENT="custom_p0_ttl_duration=1;" +PARTITION BY LIST(c1) ( +PARTITION custom_p0 VALUES IN (1, 4, 7), +PARTITION custom_p1 VALUES IN (2, 5, 8), +PARTITION custom_p2 VALUES IN (3, 6, 9) +); +set global rocksdb_debug_ttl_rec_ts = -3600; +INSERT INTO t1 values (1, 1); +INSERT INTO t1 values (4, 4); +INSERT INTO t1 values (7, 7); +set global rocksdb_debug_ttl_rec_ts = 0; +INSERT INTO t1 values (2, 2); +INSERT INTO t1 values (3, 3); +INSERT INTO t1 values (5, 5); +INSERT INTO t1 values (6, 6); +INSERT INTO t1 values (8, 8); +INSERT INTO t1 values (9, 9); +SELECT * FROM t1; +c1 c2 +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +CREATE INDEX kc2 on t1 (c2); +set global rocksdb_force_flush_memtable_now=1; +set global rocksdb_compact_cf='foo'; +set global rocksdb_compact_cf='default'; +SELECT * FROM t1 FORCE INDEX (PRIMARY); +c1 c2 +2 2 +3 3 +5 5 +6 6 +8 8 +9 9 +SELECT * FROM t1 FORCE INDEX (kc2); +c1 c2 +2 2 +3 3 +5 5 +6 6 +8 8 +9 9 +DROP TABLE t1; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/type_float.result b/storage/rocksdb/mysql-test/rocksdb/r/type_float.result index fbb44d1552c..778ed95d338 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/type_float.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/type_float.result @@ -127,17 +127,27 @@ r1_1 -0.9 r1_1 0.0 r1_1 0.9 r1_1 0.9 -SELECT MAX(f), MAX(f0), MAX(r1_1), MAX(f23_0), MAX(f20_3), MAX(d), MAX(d1_0), MAX(d10_10), MAX(d53), MAX(d53_10) FROM t1; -MAX(f) 9.999999680285692e37 -MAX(d) 1e81 -MAX(d10_10) 0.9999999999 -MAX(d1_0) 9 -MAX(d53) 100000000000000000000000000000000000000000000000000000 -MAX(d53_10) 10000000000000000000000000000000000000000000.0000000000 -MAX(f0) 9.999999680285692e37 -MAX(f20_3) 99999998430674940.000 -MAX(f23_0) 9.999999680285692e37 -MAX(r1_1) 0.9 +SELECT +CONCAT('', MAX(f)), +CONCAT('', MAX(f0)), +CONCAT('', MAX(r1_1)), +CONCAT('', MAX(f23_0)), +CONCAT('', MAX(f20_3)), +CONCAT('', MAX(d)), +CONCAT('', MAX(d1_0)), +CONCAT('', MAX(d10_10)), +CONCAT('', MAX(d53)), +CONCAT('', MAX(d53_10)) FROM t1; +CONCAT('', MAX(f)) 9.999999680285692e37 +CONCAT('', MAX(d)) 1e81 +CONCAT('', MAX(d10_10)) 0.9999999999 +CONCAT('', MAX(d1_0)) 9 +CONCAT('', MAX(d53)) 100000000000000000000000000000000000000000000000000000 +CONCAT('', MAX(d53_10)) 10000000000000000000000000000000000000000000.0000000000 +CONCAT('', MAX(f0)) 9.999999680285692e37 +CONCAT('', MAX(f20_3)) 99999998430674940.000 +CONCAT('', MAX(f23_0)) 9.999999680285692e37 +CONCAT('', MAX(r1_1)) 0.9 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10,pk) VALUES ( 9999999999999999999999999999999999999999999999999999999999999.9999, 9999999999999999999999999999999999999999999999999999999999999.9999, diff --git a/storage/rocksdb/mysql-test/rocksdb/r/write_sync.result b/storage/rocksdb/mysql-test/rocksdb/r/write_sync.result index 9eed611a970..ee23446eec0 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/write_sync.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/write_sync.result @@ -2,35 +2,26 @@ SET GLOBAL rocksdb_write_disable_wal=false; SET GLOBAL rocksdb_write_ignore_missing_column_families=true; create table aaa (id int primary key, i int) engine rocksdb; set @save_rocksdb_flush_log_at_trx_commit=@@global.rocksdb_flush_log_at_trx_commit; -SET GLOBAL rocksdb_flush_log_at_trx_commit=0; +SET GLOBAL rocksdb_flush_log_at_trx_commit=1; select variable_value into @a from information_schema.global_status where variable_name='rocksdb_wal_synced'; insert aaa(id, i) values(1,1); select variable_value-@a from information_schema.global_status where variable_name='rocksdb_wal_synced'; variable_value-@a -0 -insert aaa(id, i) values(2,1); -select variable_value-@a from information_schema.global_status where variable_name='rocksdb_wal_synced'; -variable_value-@a -0 -insert aaa(id, i) values(3,1); -select variable_value-@a from information_schema.global_status where variable_name='rocksdb_wal_synced'; -variable_value-@a -0 -SET GLOBAL rocksdb_flush_log_at_trx_commit=1; -insert aaa(id, i) values(4,1); -select variable_value-@a from information_schema.global_status where variable_name='rocksdb_wal_synced'; -variable_value-@a 1 -insert aaa(id, i) values(5,1); +insert aaa(id, i) values(2,1); select variable_value-@a from information_schema.global_status where variable_name='rocksdb_wal_synced'; variable_value-@a 2 -insert aaa(id, i) values(6,1); +insert aaa(id, i) values(3,1); select variable_value-@a from information_schema.global_status where variable_name='rocksdb_wal_synced'; variable_value-@a 3 +SET GLOBAL rocksdb_flush_log_at_trx_commit=0; +select variable_value into @a from information_schema.global_status where variable_name='rocksdb_wal_synced'; +insert aaa(id, i) values(4,1); SET GLOBAL rocksdb_flush_log_at_trx_commit=2; -insert aaa(id, i) values(7,1); +select variable_value into @a from information_schema.global_status where variable_name='rocksdb_wal_synced'; +insert aaa(id, i) values(5,1); truncate table aaa; drop table aaa; set @@global.rocksdb_flush_log_at_trx_commit=@save_rocksdb_flush_log_at_trx_commit; |