xref: /dports/databases/mariadb103-server/mariadb-10.3.34/storage/rocksdb/mysql-test/rocksdb/t/ttl_primary.test

--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;