path: root/storage/rocksdb/mysql-test/rocksdb/t/ttl_secondary_read_filtering.test

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

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