--source include/have_debug.inc --source include/have_rocksdb.inc # Basic TTL test 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; set global rocksdb_force_flush_memtable_now=1; set global rocksdb_compact_cf='default'; # should have filtered the rows out since ttl is passed in compaction filter SELECT COUNT(*) FROM t1; DROP TABLE t1; # column before TTL in value 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) ) 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; set global rocksdb_force_flush_memtable_now=1; set global rocksdb_compact_cf='default'; # should have filtered the rows out since ttl is passed in compaction filter SELECT COUNT(*) FROM t1; DROP TABLE t1; # multi-part PK w/ TTL 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) ) 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; set global rocksdb_force_flush_memtable_now=1; set global rocksdb_compact_cf='default'; # should have filtered the rows out since ttl is passed in compaction filter SELECT COUNT(*) FROM t1; DROP TABLE t1; # multi-part PK w/ TTL 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) ) 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; set global rocksdb_force_flush_memtable_now=1; set global rocksdb_compact_cf='default'; # should have filtered the rows out since ttl is passed in compaction filter SELECT COUNT(*) FROM t1; DROP TABLE t1; # nullable column(s) before TTL CREATE TABLE t1 ( a bigint(20) NOT NULL, b int, c int, ts bigint(20) UNSIGNED NOT NULL, PRIMARY KEY (a) ) 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; set global rocksdb_force_flush_memtable_now=1; set global rocksdb_compact_cf='default'; # should have filtered the rows out since ttl is passed in compaction filter SELECT COUNT(*) FROM t1; DROP TABLE t1; # variable len columns + null column(s) before TTL 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`) ) 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; set global rocksdb_force_flush_memtable_now=1; set global rocksdb_compact_cf='default'; # should have filtered the rows out since ttl is passed in compaction filter SELECT COUNT(*) FROM t1; DROP TABLE t1; # TTL implicitly generated (no ttl column) CREATE TABLE t1 ( a bigint(20) NOT NULL, b int NOT NULL, c int NOT NULL, PRIMARY KEY (a) ) 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; set global rocksdb_force_flush_memtable_now=1; set global rocksdb_compact_cf='default'; # should have filtered the rows out since ttl is passed in compaction filter SELECT COUNT(*) FROM t1; DROP TABLE t1; # TTL field as the PK CREATE TABLE t1 ( a int, ts bigint(20) UNSIGNED NOT NULL, PRIMARY KEY (a, 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; 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; # should all still be there.. SELECT COUNT(*) FROM t1; set global rocksdb_debug_ttl_snapshot_ts = 10; set global rocksdb_compact_cf='default'; set global rocksdb_debug_ttl_snapshot_ts = 0; # should have filtered the rows out since ttl is passed in compaction filter SELECT COUNT(*) FROM t1; DROP TABLE t1; # TTL field inside multi-part pk 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) ) 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; set global rocksdb_force_flush_memtable_now=1; set global rocksdb_compact_cf='default'; # should have filtered the rows out since ttl is passed in compaction filter SELECT COUNT(*) FROM t1; DROP TABLE t1; # TTL field inside key with variable length things.. 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`) ) 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; set global rocksdb_force_flush_memtable_now=1; set global rocksdb_compact_cf='default'; # should have filtered the rows out since ttl is passed in compaction filter SELECT COUNT(*) FROM t1; DROP TABLE t1; # TTL test where you compact (values still exist), real_sleep, then compact again, # values should now be gone. 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) ) 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; # Nothing should get removed here. 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; --sorted_result SELECT a FROM t1; # 1 and 2 should get removed here. set global rocksdb_compact_cf='default'; --sorted_result SELECT a FROM t1; # 3 and 4 should get removed here. set global rocksdb_debug_ttl_snapshot_ts = 3600; set global rocksdb_compact_cf='default'; set global rocksdb_debug_ttl_snapshot_ts = 0; --sorted_result SELECT a FROM t1; DROP TABLE t1; # TTL field with nullable ttl column (should fail) --error ER_RDB_TTL_COL_FORMAT CREATE TABLE t1 ( a bigint(20) UNSIGNED NOT NULL, b int NOT NULL, c int NOT NULL, ts bigint(20), PRIMARY KEY (a,c) ) ENGINE=rocksdb COMMENT='ttl_duration=1;ttl_col=ts;'; # TTL field with non 8-bit integer column (should fail) --error ER_RDB_TTL_COL_FORMAT CREATE TABLE t1 ( a bigint(20) UNSIGNED NOT NULL, b int NOT NULL, c int NOT NULL, ts int, PRIMARY KEY (a,c) ) ENGINE=rocksdb COMMENT='ttl_duration=1;ttl_col=ts;'; # TTL duration as some random garbage value --error ER_RDB_TTL_DURATION_FORMAT CREATE TABLE t1 ( a bigint(20) UNSIGNED NOT NULL, b int NOT NULL, c int NOT NULL, PRIMARY KEY (a,c) ) ENGINE=rocksdb COMMENT='ttl_duration=abc;'; # TTL col is some column outside of the table --error ER_RDB_TTL_COL_FORMAT CREATE TABLE t1 ( a bigint(20) UNSIGNED NOT NULL, b int NOT NULL, c int NOT NULL, PRIMARY KEY (a,c) ) ENGINE=rocksdb COMMENT='ttl_duration=1;ttl_col=abc;'; # TTL col must have accompanying duration --error ER_RDB_TTL_COL_FORMAT CREATE TABLE t1 ( a bigint(20) UNSIGNED NOT NULL, b int NOT NULL, c int NOT NULL, PRIMARY KEY (a,c) ) ENGINE=rocksdb COMMENT='ttl_col=abc;'; # Make sure it doesn't filter out things early CREATE TABLE t1 ( a bigint(20) NOT NULL, PRIMARY KEY (a) ) ENGINE=rocksdb COMMENT='ttl_duration=500;'; INSERT INTO t1 values (1); SELECT COUNT(*) FROM t1; set global rocksdb_force_flush_memtable_now=1; set global rocksdb_compact_cf='default'; SELECT COUNT(*) FROM t1; DROP TABLE t1; # Testing altering table comment with updated TTL duration # This should trigger a rebuild of the table CREATE TABLE t1 ( a INT PRIMARY KEY ) ENGINE=rocksdb COMMENT='ttl_duration=100;'; INSERT INTO t1 values (1); SELECT * FROM t1; 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; set global rocksdb_force_flush_memtable_now=1; set global rocksdb_compact_cf='default'; SELECT COUNT(*) FROM t1; DROP TABLE t1; # Tables with hidden PK and SK disabled CREATE TABLE t1 ( a INT PRIMARY KEY, b INT ) ENGINE=rocksdb COMMENT='ttl_duration=100;'; --error ER_RDB_TTL_UNSUPPORTED ALTER TABLE t1 DROP PRIMARY KEY; DROP TABLE t1; # Test replacing PK, ttl should still work after CREATE TABLE t1 ( a INT PRIMARY KEY, b INT ) 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; --sorted_result SELECT COUNT(*) FROM t1; set global rocksdb_debug_ttl_snapshot_ts = 3600; set @@global.rocksdb_compact_cf = 'default'; set global rocksdb_debug_ttl_snapshot_ts = 0; --sorted_result SELECT COUNT(*) FROM t1; DROP TABLE t1; # Make sure table comment filled with other text before/after will work # (basically, it needs semicolon before and after) CREATE TABLE t1 ( a bigint(20) UNSIGNED NOT NULL, b int, PRIMARY KEY (a,b) ) ENGINE=rocksdb COMMENT='asdadfasdfsadfadf ;ttl_duration=1; asfasdfasdfadfa'; INSERT INTO t1 values (UNIX_TIMESTAMP(), 1); SELECT COUNT(*) FROM t1; 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; 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; # nothing removed here set global rocksdb_compact_cf='default'; SELECT COUNT(*) FROM t1; # all removed here set global rocksdb_debug_ttl_snapshot_ts = 3600; set global rocksdb_compact_cf='default'; set global rocksdb_debug_ttl_snapshot_ts = 0; SELECT COUNT(*) FROM t1; DROP TABLE t1; # Test to make sure that TTL retains original timestamp during update CREATE TABLE t1 ( a bigint(20) NOT NULL, PRIMARY KEY (a) ) ENGINE=rocksdb COMMENT='ttl_duration=5;'; set global rocksdb_debug_ttl_rec_ts = -300; INSERT INTO t1 values (1); INSERT INTO t1 values (3); INSERT INTO t1 values (5); set global rocksdb_debug_ttl_rec_ts = 300; INSERT INTO t1 values (7); INSERT INTO t1 values (9); set global rocksdb_debug_ttl_rec_ts = 0; UPDATE t1 SET a=a+1; --sorted_result SELECT * FROM t1; set global rocksdb_force_flush_memtable_now=1; set global rocksdb_compact_cf='default'; # 1,3,5 should be dropped --sorted_result SELECT * FROM t1; DROP TABLE t1; # test behaviour on update with TTL column, TTL time can be updated here. CREATE TABLE t1 ( a INT, b bigint(20) UNSIGNED NOT NULL, PRIMARY KEY (a) ) 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() WHERE a < 4; set global rocksdb_debug_ttl_rec_ts = 0; --sorted_result SELECT a FROM t1; set global rocksdb_force_flush_memtable_now=1; set global rocksdb_compact_cf='default'; # 5 and 7 should be gone here --sorted_result SELECT a FROM t1; DROP TABLE t1; # Test rows expired stat variable and disable ttl variable CREATE TABLE t1 ( a bigint(20) NOT NULL, PRIMARY KEY (a) ) ENGINE=rocksdb COMMENT='ttl_duration=1;'; set global rocksdb_debug_ttl_rec_ts = -100; INSERT INTO t1 values (1); INSERT INTO t1 values (2); INSERT INTO t1 values (3); 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'; SELECT COUNT(*) FROM t1; DROP TABLE t1; # Table with TTL won't increment rows expired when no records have been # compacted CREATE TABLE t1 ( a bigint(20) NOT NULL, PRIMARY KEY (a) ) ENGINE=rocksdb COMMENT='ttl_duration=100;'; INSERT INTO t1 values (1); INSERT INTO t1 values (2); INSERT INTO t1 values (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'; DROP TABLE t1;