1--source include/have_rocksdb.inc
2
3# only SingleDelete increases
4CREATE TABLE t1 (id INT, value int, PRIMARY KEY (id), INDEX (value)) ENGINE=ROCKSDB;
5INSERT INTO t1 VALUES (1,1);
6select variable_value into @s from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_singledelete';
7select variable_value into @d from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_delete';
8--disable_query_log
9let $i = 1;
10while ($i <= 10000) {
11  let $update = UPDATE t1 SET value=value+1 WHERE value=$i;
12  inc $i;
13  eval $update;
14}
15--enable_query_log
16optimize table t1;
17select case when variable_value-@s > 5 and variable_value-@s < 100 then 'true' else 'false' end from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_singledelete';
18select case when variable_value-@d < 10 then 'true' else 'false' end from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_delete';
19
20
21# both SingleDelete and Delete increases
22CREATE TABLE t2 (id INT, value int, PRIMARY KEY (id), INDEX (value)) ENGINE=ROCKSDB;
23INSERT INTO t2 VALUES (1,1);
24select variable_value into @s from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_singledelete';
25select variable_value into @d from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_delete';
26--disable_query_log
27let $i = 1;
28while ($i <= 10000) {
29  let $update = UPDATE t2 SET id=id+1 WHERE id=$i;
30  inc $i;
31  eval $update;
32}
33--enable_query_log
34optimize table t2;
35select case when variable_value-@s > 5 and variable_value-@s < 100 then 'true' else 'false' end from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_singledelete';
36select case when variable_value-@d > 9000 then 'true' else 'false' end from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_delete';
37
38# only Delete increases
39CREATE TABLE t3 (id INT, value int, PRIMARY KEY (id)) ENGINE=ROCKSDB;
40INSERT INTO t3 VALUES (1,1);
41select variable_value into @s from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_singledelete';
42select variable_value into @d from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_delete';
43--disable_query_log
44let $i = 1;
45while ($i <= 10000) {
46  let $update = UPDATE t3 SET id=id+1 WHERE id=$i;
47  inc $i;
48  eval $update;
49}
50--enable_query_log
51optimize table t3;
52select case when variable_value-@s = 0 then 'true' else 'false' end from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_singledelete';
53select case when variable_value-@d > 9000 then 'true' else 'false' end from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_delete';
54
55# only SingleDelete increases
56CREATE TABLE t4 (id INT, PRIMARY KEY (id)) ENGINE=ROCKSDB;
57INSERT INTO t4 VALUES (1);
58select variable_value into @s from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_singledelete';
59select variable_value into @d from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_delete';
60--disable_query_log
61let $i = 1;
62while ($i <= 10000) {
63  let $update = UPDATE t4 SET id=id+1 WHERE id=$i;
64  inc $i;
65  eval $update;
66}
67--enable_query_log
68optimize table t4;
69select case when variable_value-@s > 5 and variable_value-@s < 100 then 'true' else 'false' end from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_singledelete';
70select case when variable_value-@d < 10 then 'true' else 'false' end from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_delete';
71
72# only SingleDelete increases
73CREATE TABLE t5 (id1 INT, id2 INT, PRIMARY KEY (id1, id2), INDEX(id2)) ENGINE=ROCKSDB;
74INSERT INTO t5 VALUES (1, 1);
75select variable_value into @s from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_singledelete';
76select variable_value into @d from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_delete';
77--disable_query_log
78let $i = 1;
79while ($i <= 10000) {
80  let $update = UPDATE t5 SET id1=id1+1 WHERE id1=$i;
81  inc $i;
82  eval $update;
83}
84--enable_query_log
85optimize table t5;
86select case when variable_value-@s > 5 and variable_value-@s < 100 then 'true' else 'false' end from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_singledelete';
87select case when variable_value-@d < 10 then 'true' else 'false' end from performance_schema.global_status where variable_name='rocksdb_number_sst_entry_delete';
88
89# SingleDelete used for PK. Verify old PK is always deleted.
90CREATE TABLE t6 (
91  pk VARCHAR(64) COLLATE latin1_swedish_ci PRIMARY KEY
92) ENGINE=RocksDB;
93INSERT INTO t6 VALUES ('a');
94SET GLOBAL rocksdb_force_flush_memtable_now=1;
95SELECT * FROM t6;
96UPDATE t6 SET pk='A' WHERE pk='a';
97SELECT * FROM t6;
98DELETE FROM t6 where pk='A';
99--echo SELECT should return nothing;
100SELECT * FROM t6;
101SET GLOBAL rocksdb_force_flush_memtable_now=1;
102--echo SELECT should return nothing;
103SELECT * FROM t6;
104
105DROP TABLE t1, t2, t3, t4, t5, t6;
106