diff options
Diffstat (limited to 'storage/rocksdb/mysql-test/rocksdb/r/ttl_secondary.result')
| -rw-r--r-- | storage/rocksdb/mysql-test/rocksdb/r/ttl_secondary.result | 709 |
1 files changed, 709 insertions, 0 deletions
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 00000000..1f748a38 --- /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; |