diff options
Diffstat (limited to '')
| -rw-r--r-- | storage/rocksdb/mysql-test/rocksdb/t/ttl_secondary_read_filtering.test | 503 |
1 files changed, 503 insertions, 0 deletions
diff --git a/storage/rocksdb/mysql-test/rocksdb/t/ttl_secondary_read_filtering.test b/storage/rocksdb/mysql-test/rocksdb/t/ttl_secondary_read_filtering.test new file mode 100644 index 00000000..f6042cc5 --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/t/ttl_secondary_read_filtering.test @@ -0,0 +1,503 @@ +--source include/have_debug.inc +--source include/have_rocksdb.inc + +# The purpose of read filtering for tables with TTL is to ensure that during a +# transaction a key which has expired already but not removed by compaction +# yet, is not returned to the user. +# +# Without this the user might be hit with problems such as disappearing rows +# within a transaction, etc, because the compaction filter ignores snapshots +# when filtering keys. + +# Basic read filtering test +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; + +--sorted_result +SELECT * FROM t1 FORCE INDEX (PRIMARY); +--sorted_result +SELECT * FROM t1 FORCE INDEX (kb); + +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'; + +DROP TABLE t1; + +# Test that some rows are hidden but others aren't... +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; + +--echo # 1 should be hidden +--sorted_result +SELECT a FROM t1 FORCE INDEX (PRIMARY); +--sorted_result +SELECT a FROM t1 FORCE INDEX (kb); + +set global rocksdb_debug_ttl_ignore_pk = 1; +set global rocksdb_compact_cf='default'; +set global rocksdb_debug_ttl_ignore_pk = 0; + +--echo # none should be hidden yet, compaction runs but records aren't expired +--sorted_result +SELECT a FROM t1 FORCE INDEX (PRIMARY); +--sorted_result +SELECT a FROM t1 FORCE INDEX (kb); + +--echo # all should be hidden now, even though compaction hasn't run again +set global rocksdb_debug_ttl_read_filter_ts = -310; +--sorted_result +SELECT a FROM t1 FORCE INDEX (PRIMARY); +--sorted_result +SELECT a FROM t1 FORCE INDEX (kb); +set global rocksdb_debug_ttl_read_filter_ts = 0; + +DROP TABLE t1; + +# Test the filtering code explicitly. +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; + +--echo # should return nothing. +--sorted_result +SELECT * FROM t1 FORCE INDEX (PRIMARY); +--sorted_result +SELECT * FROM t1 FORCE INDEX (kb); + +# disable filtering +set global rocksdb_enable_ttl_read_filtering=0; + +--echo # should return everything +--sorted_result +SELECT * FROM t1 FORCE INDEX (PRIMARY); +--sorted_result +SELECT * FROM t1 FORCE INDEX (kb); + +# enable filtering +set global rocksdb_enable_ttl_read_filtering=1; + +--echo # should return nothing. +--sorted_result +SELECT * FROM t1 FORCE INDEX (PRIMARY); +--sorted_result +SELECT * FROM t1 FORCE INDEX (kb); + +DROP TABLE t1; + +# Compact away the dropped data +set global rocksdb_compact_cf= 'default'; + +--echo # 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; + +# HA_READ_KEY_EXACT, using full key +SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE a=1 AND b=2 AND c=2; +SELECT * FROM t1 FORCE INDEX (kb) WHERE a=1 AND b=2 AND c=2; + +# HA_READ_KEY_EXACT, not using full key +SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE a = 1; +SELECT * FROM t1 FORCE INDEX (kb) WHERE a = 1; + +# HA_READ_BEFORE_KEY, not using full key +SELECT max(a) FROM t1 FORCE INDEX (PRIMARY) WHERE a < 3; +SELECT max(a) FROM t1 FORCE INDEX (kb) WHERE a < 3; + +# HA_READ_BEFORE_KEY, using full key +SELECT max(a) FROM t1 FORCE INDEX (PRIMARY) WHERE a < 2 AND b = 1 AND c < 3; +SELECT max(a) FROM t1 FORCE INDEX (kb) WHERE a < 2 AND b = 1 AND c < 3; + +# HA_READ_KEY_OR_NEXT +SELECT min(a) FROM t1 FORCE INDEX (PRIMARY) WHERE a >= 1; +SELECT min(a) FROM t1 FORCE INDEX (kb) WHERE a >= 1; + +# HA_READ_AFTER_KEY, /* Find next rec. after key-record */ +SELECT min(a) FROM t1 FORCE INDEX (PRIMARY) WHERE a > 1; +SELECT min(a) FROM t1 FORCE INDEX (kb) WHERE a > 1; + +# HA_READ_PREFIX_LAST, /* Last key with the same prefix */ +SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE a=1 and b in (1) order by c desc; +SELECT * FROM t1 FORCE INDEX (kb) WHERE a=1 and b in (1) order by c desc; + +# HA_READ_PREFIX_LAST_OR_PREV, /* Last or prev key with the same prefix */ +SELECT max(a) FROM t1 FORCE INDEX (PRIMARY) WHERE a <=10; +SELECT max(a) FROM t1 FORCE INDEX (kb) WHERE a <=10; + +# need to test read_range_first() +# calls into read_range_next() and uses compare_keys() to see if its out of +# range +SELECT a FROM t1 FORCE INDEX (PRIMARY) WHERE a > 0 and a <= 2; +SELECT a FROM t1 FORCE INDEX (kb) WHERE a > 0 and a <= 2; + +select variable_value-@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'; +DROP TABLE t1; + +--echo # 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; + +--sorted_result +SELECT * FROM t1 FORCE INDEX (PRIMARY); +--sorted_result +SELECT * FROM t1; + +--echo # No error is thrown here, under the hood index_next_with_direction is +--echo # filtering out the record from being seen in the first place. +UPDATE t1 set a = 1; +DROP TABLE t1; + +--echo # Ensure no rows can disappear in the middle of long-running transactions +--echo # Also ensure repeatable-read works as expected +--source include/count_sessions.inc +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; +--echo # Creating Snapshot (start transaction) +BEGIN; + +# We need the below snippet in case establishing con1 took an arbitrary +# amount of time. See https://github.com/facebook/mysql-5.6/pull/617#discussion_r120525391. +--disable_query_log +--let $snapshot_size= `SELECT COUNT(*) FROM t1` +--let $i= 0 +while ($snapshot_size != 1) +{ + if ($i == 1000) + { + --die Your testing host is too slow for reasonable TTL testing + } + + $i++; + ROLLBACK; + INSERT INTO t1 values (1,1); + BEGIN; + --let $snapshot_size= `SELECT COUNT(*) FROM t1` +} +--enable_query_log + +--echo # Nothing filtered out here +--sorted_result +SELECT * FROM t1 FORCE INDEX (PRIMARY); +--sorted_result +SELECT * FROM t1 FORCE INDEX (kb); + +--sleep 5 + +--sorted_result +SELECT * FROM t1 FORCE INDEX (PRIMARY); +--sorted_result +SELECT * FROM t1 FORCE INDEX (kb); # <= shouldn't be filtered out here + +--echo # Switching to connection 2 +connection con2; +--echo # 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; +--echo # read filtered out, because on a different connection, on +--echo # this connection the records have 'expired' already so they are filtered out +--echo # even though they have not yet been removed by compaction +--sorted_result +SELECT * FROM t1 FORCE INDEX (PRIMARY); +--sorted_result +SELECT * FROM t1 FORCE INDEX (kb); + +--echo # Switching to connection 1 +connection con1; +--sorted_result +SELECT * FROM t1 FORCE INDEX (PRIMARY); +--sorted_result +SELECT * FROM t1 FORCE INDEX (kb); # <= shouldn't be filtered out here + +UPDATE t1 set a = a + 1; +--sorted_result +SELECT * FROM t1 FORCE INDEX (PRIMARY); +--sorted_result +SELECT * FROM t1 FORCE INDEX (kb); # <= shouldn't be filtered out here + +COMMIT; + +--sorted_result # <= filtered out here because time has passed. +SELECT * FROM t1 FORCE INDEX (PRIMARY); +--sorted_result +SELECT * FROM t1 FORCE INDEX (kb); + +DROP TABLE t1; +disconnect con1; +disconnect con2; + +#transaction 1, create a snapshot and select * => returns nothing. +#transaction 2, insert into table, flush +#transaction 1, select * => returns nothing, but the snapshot should prevent the compaction code from removing the rows, no matter what the ttl duration is. +#transaction 2, select * -> sees nothing, disable filter, select * -> sees everything, enable filter, select * -> sees nothing. +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;'; + +--echo # On Connection 1 +connection con1; +--echo # Creating Snapshot (start transaction) +BEGIN; +--sorted_result +SELECT * FROM t1 FORCE INDEX (kb); +# Sleep 5 secs after creating snapshot, this ensures any records created after +# this can't be removed by compaction until this snapshot is released. +--sleep 5 + +--echo # 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'; + +--echo # On Connection 1 +connection con1; +--sorted_result +SELECT * FROM t1 FORCE INDEX (PRIMARY); +--sorted_result +SELECT * FROM t1 FORCE INDEX (kb); + +--echo # On Connection 2 +connection con2; +--sorted_result +SELECT * FROM t1 FORCE INDEX (PRIMARY); +--sorted_result +SELECT * FROM t1 FORCE INDEX (kb); +set global rocksdb_enable_ttl_read_filtering=0; +--sorted_result +SELECT * FROM t1 FORCE INDEX (PRIMARY); +--sorted_result +SELECT * FROM t1 FORCE INDEX (kb); +set global rocksdb_enable_ttl_read_filtering=1; + +disconnect con2; +disconnect con1; +connection default; + +DROP TABLE t1; +# Wait till we reached the initial number of concurrent sessions +--source include/wait_until_count_sessions.inc + +# Test that index_next_with_direction skips records properly +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; +--echo # None are expired +SELECT a, b FROM t1 FORCE INDEX (kb); + +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'; + +--echo # 4 and 7 should be gone +SELECT a, b FROM t1 FORCE INDEX (kb); + +DROP TABLE t1; + +# Test range scans with various conditionals +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; + +--sorted_result +SELECT * FROM t1 FORCE INDEX (PRIMARY); +--sorted_result +SELECT * FROM t1 FORCE INDEX (kc2); + +--sorted_result +SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE c1 > 5; +--sorted_result +SELECT * FROM t1 FORCE INDEX (kc2) WHERE c2 > 5; + +--sorted_result +SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE 3 < c1 AND c1 < 6; +--sorted_result +SELECT * FROM t1 FORCE INDEX (kc2) WHERE 3 < c2 AND c2 < 6; + +DROP TABLE t1; + +# Test range scans with varying expirations +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; + +--echo # Should see everything +SELECT * FROM t1; + +--echo # Should have no records from the first group +set global rocksdb_debug_ttl_read_filter_ts = -1800; +SELECT * FROM t1; +SELECT * FROM t1 FORCE INDEX (kb) WHERE a > 5 AND a < 15; + +--echo # Should only have records from the last group +set global rocksdb_debug_ttl_read_filter_ts = -1800 - 450; +SELECT * FROM t1; +SELECT * FROM t1 FORCE INDEX (kb) WHERE a < 10; + +--echo # Should be empty +set global rocksdb_debug_ttl_read_filter_ts = -1800 - 900; +SELECT * FROM t1; + +set global rocksdb_debug_ttl_read_filter_ts = 0; +DROP TABLE t1; |