1--source include/have_debug.inc 2--source include/have_rocksdb.inc 3 4# Basic TTL test, pk ignored, no sk 5CREATE TABLE t1 ( 6`a` binary(8) NOT NULL, 7`b` varbinary(64) NOT NULL, 8`c` varbinary(256) NOT NULL, 9`ts` bigint(20) UNSIGNED NOT NULL, 10`value` mediumblob NOT NULL, 11PRIMARY KEY (`b`,`a`,`c`) 12) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 13COMMENT='ttl_duration=1;ttl_col=ts;'; 14 15set global rocksdb_debug_ttl_rec_ts = -100; 16INSERT INTO t1 values ('a', 'b', 'c', UNIX_TIMESTAMP(), 'd'); 17INSERT INTO t1 values ('d', 'e', 'f', UNIX_TIMESTAMP(), 'g'); 18set global rocksdb_debug_ttl_rec_ts = 0; 19SELECT COUNT(*) FROM t1; 20 21set global rocksdb_debug_ttl_ignore_pk = 1; 22set global rocksdb_force_flush_memtable_now=1; 23set global rocksdb_compact_cf='default'; 24set global rocksdb_debug_ttl_ignore_pk = 0; 25 26# no rows should be filtered 27SELECT COUNT(*) FROM t1; 28DROP TABLE t1; 29 30# Basic TTL test 31CREATE TABLE t1 ( 32`a` binary(8) NOT NULL, 33`b` varbinary(64) NOT NULL, 34`c` varbinary(256) NOT NULL, 35`ts` bigint(20) UNSIGNED NOT NULL, 36`value` mediumblob NOT NULL, 37PRIMARY KEY (`b`,`a`,`c`), 38KEY kb (`b`) 39) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 40COMMENT='ttl_duration=1;ttl_col=ts;'; 41 42set global rocksdb_debug_ttl_rec_ts = -100; 43INSERT INTO t1 values ('a', 'b', 'c', UNIX_TIMESTAMP(), 'd'); 44INSERT INTO t1 values ('d', 'e', 'f', UNIX_TIMESTAMP(), 'g'); 45set global rocksdb_debug_ttl_rec_ts = 0; 46SELECT COUNT(*) FROM t1 FORCE INDEX (kb); 47 48set global rocksdb_debug_ttl_ignore_pk = 1; 49set global rocksdb_force_flush_memtable_now=1; 50set global rocksdb_compact_cf='default'; 51set global rocksdb_debug_ttl_ignore_pk = 0; 52 53# should have filtered the rows out since ttl is passed in compaction filter 54SELECT COUNT(*) FROM t1 FORCE INDEX (kb); 55DROP TABLE t1; 56 57# column before TTL in value 58CREATE TABLE t1 ( 59 a bigint(20) NOT NULL, 60 b int NOT NULL, 61 ts bigint(20) UNSIGNED NOT NULL, 62 c int NOT NULL, 63 PRIMARY KEY (a), 64 KEY kb (b) 65) ENGINE=rocksdb 66COMMENT='ttl_duration=1;ttl_col=ts;'; 67 68set global rocksdb_debug_ttl_rec_ts = -100; 69INSERT INTO t1 values (1, 3, UNIX_TIMESTAMP(), 5); 70INSERT INTO t1 values (2, 4, UNIX_TIMESTAMP(), 6); 71set global rocksdb_debug_ttl_rec_ts = 0; 72SELECT COUNT(*) FROM t1 FORCE INDEX (kb); 73 74set global rocksdb_debug_ttl_ignore_pk = 1; 75set global rocksdb_force_flush_memtable_now=1; 76set global rocksdb_compact_cf='default'; 77set global rocksdb_debug_ttl_ignore_pk = 0; 78 79# should have filtered the rows out since ttl is passed in compaction filter 80SELECT COUNT(*) FROM t1 FORCE INDEX (kb); 81DROP TABLE t1; 82 83# multi-part PK w/ TTL 84CREATE TABLE t1 ( 85 a bigint(20) NOT NULL, 86 b int NOT NULL, 87 c int NOT NULL, 88 ts bigint(20) UNSIGNED NOT NULL, 89 PRIMARY KEY (a,c), 90 KEY kb (b) 91) ENGINE=rocksdb 92COMMENT='ttl_duration=1;ttl_col=ts;'; 93 94set global rocksdb_debug_ttl_rec_ts = -100; 95INSERT INTO t1 values (1, 3, 5, UNIX_TIMESTAMP()); 96INSERT INTO t1 values (2, 4, 6, UNIX_TIMESTAMP()); 97set global rocksdb_debug_ttl_rec_ts = 0; 98SELECT COUNT(*) FROM t1 FORCE INDEX (kb); 99 100set global rocksdb_debug_ttl_ignore_pk=1; 101set global rocksdb_force_flush_memtable_now=1; 102set global rocksdb_compact_cf='default'; 103set global rocksdb_debug_ttl_ignore_pk=0; 104 105# should have filtered the rows out since ttl is passed in compaction filter 106SELECT COUNT(*) FROM t1 FORCE INDEX (kb); 107DROP TABLE t1; 108 109# nullable column(s) before TTL 110CREATE TABLE t1 ( 111 a bigint(20) NOT NULL, 112 b int, 113 c int, 114 ts bigint(20) UNSIGNED NOT NULL, 115 PRIMARY KEY (a), 116 KEY kbc (b, c) 117) ENGINE=rocksdb 118COMMENT='ttl_duration=1;ttl_col=ts;'; 119 120set global rocksdb_debug_ttl_rec_ts = -100; 121INSERT INTO t1 values (1, NULL, NULL, UNIX_TIMESTAMP()); 122INSERT INTO t1 values (2, NULL, NULL, UNIX_TIMESTAMP()); 123set global rocksdb_debug_ttl_rec_ts = 0; 124SELECT COUNT(*) FROM t1 FORCE INDEX (kb); 125 126set global rocksdb_debug_ttl_ignore_pk=1; 127set global rocksdb_force_flush_memtable_now=1; 128set global rocksdb_compact_cf='default'; 129set global rocksdb_debug_ttl_ignore_pk=0; 130 131# should have filtered the rows out since ttl is passed in compaction filter 132SELECT COUNT(*) FROM t1 FORCE INDEX (kb); 133DROP TABLE t1; 134 135# variable len columns + null column(s) before TTL 136CREATE TABLE t1 ( 137`a` binary(8) NOT NULL, 138`b` varbinary(64), 139`c` varbinary(256), 140`ts` bigint(20) UNSIGNED NOT NULL, 141`value` mediumblob NOT NULL, 142PRIMARY KEY (`a`), 143KEY kbc (`b`, `c`) 144) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 145COMMENT='ttl_duration=1;ttl_col=ts;'; 146 147set global rocksdb_debug_ttl_rec_ts = -100; 148INSERT INTO t1 values ('a', NULL, 'bc', UNIX_TIMESTAMP(), 'd'); 149INSERT INTO t1 values ('d', 'efghijk', NULL, UNIX_TIMESTAMP(), 'l'); 150set global rocksdb_debug_ttl_rec_ts = 0; 151SELECT COUNT(*) FROM t1 FORCE INDEX (kb); 152 153set global rocksdb_debug_ttl_ignore_pk=1; 154set global rocksdb_force_flush_memtable_now=1; 155set global rocksdb_compact_cf='default'; 156set global rocksdb_debug_ttl_ignore_pk=0; 157 158# should have filtered the rows out since ttl is passed in compaction filter 159SELECT COUNT(*) FROM t1 FORCE INDEX (kb); 160DROP TABLE t1; 161 162# TTL implicitly generated (no ttl column) 163CREATE TABLE t1 ( 164 a bigint(20) NOT NULL, 165 b int NOT NULL, 166 c int NOT NULL, 167 PRIMARY KEY (a), 168 KEY kb (b) 169) ENGINE=rocksdb 170COMMENT='ttl_duration=1;'; 171 172set global rocksdb_debug_ttl_rec_ts = -100; 173INSERT INTO t1 values (1, 3, 5); 174INSERT INTO t1 values (2, 4, 6); 175set global rocksdb_debug_ttl_rec_ts = 0; 176SELECT COUNT(*) FROM t1 FORCE INDEX (kb); 177 178set global rocksdb_debug_ttl_ignore_pk=1; 179set global rocksdb_force_flush_memtable_now=1; 180set global rocksdb_compact_cf='default'; 181set global rocksdb_debug_ttl_ignore_pk=0; 182 183# should have filtered the rows out since ttl is passed in compaction filter 184SELECT COUNT(*) FROM t1 FORCE INDEX (kb); 185DROP TABLE t1; 186 187# TTL field as the PK 188CREATE TABLE t1 ( 189 a int, 190 ts bigint(20) UNSIGNED NOT NULL, 191 PRIMARY KEY (a, ts), 192 KEY kt (ts) 193) ENGINE=rocksdb 194COMMENT='ttl_duration=5;ttl_col=ts;'; 195 196INSERT INTO t1 values (1, UNIX_TIMESTAMP()); 197INSERT INTO t1 values (2, UNIX_TIMESTAMP()); 198SELECT COUNT(*) FROM t1 FORCE INDEX(kt); 199 200set global rocksdb_debug_ttl_snapshot_ts = -10; 201set global rocksdb_force_flush_memtable_now=1; 202set global rocksdb_compact_cf='default'; 203set global rocksdb_debug_ttl_snapshot_ts = 0; 204# should all still be there.. 205SELECT COUNT(*) FROM t1 FORCE INDEX(kt); 206 207set global rocksdb_debug_ttl_ignore_pk=1; 208set global rocksdb_debug_ttl_snapshot_ts = 10; 209set global rocksdb_compact_cf='default'; 210set global rocksdb_debug_ttl_snapshot_ts = 0; 211set global rocksdb_debug_ttl_ignore_pk=0; 212 213# should have filtered the rows out since ttl is passed in compaction filter 214SELECT COUNT(*) FROM t1 FORCE INDEX(kt); 215DROP TABLE t1; 216 217# TTL field inside multi-part pk 218CREATE TABLE t1 ( 219 a bigint(20) NOT NULL, 220 b int NOT NULL, 221 ts bigint(20) UNSIGNED NOT NULL, 222 c int NOT NULL, 223 PRIMARY KEY (a, ts), 224 KEY kb (b) 225) ENGINE=rocksdb 226COMMENT='ttl_duration=1;ttl_col=ts;'; 227 228set global rocksdb_debug_ttl_rec_ts = -100; 229INSERT INTO t1 values (1, 3, UNIX_TIMESTAMP(), 5); 230INSERT INTO t1 values (2, 4, UNIX_TIMESTAMP(), 6); 231set global rocksdb_debug_ttl_rec_ts = 0; 232SELECT COUNT(*) FROM t1 FORCE INDEX (kb); 233 234set global rocksdb_debug_ttl_ignore_pk=1; 235set global rocksdb_force_flush_memtable_now=1; 236set global rocksdb_compact_cf='default'; 237set global rocksdb_debug_ttl_ignore_pk=0; 238 239# should have filtered the rows out since ttl is passed in compaction filter 240SELECT COUNT(*) FROM t1; 241DROP TABLE t1; 242 243# TTL field inside key with variable length things.. 244CREATE TABLE t1 ( 245`a` binary(8) NOT NULL, 246`b` varbinary(64), 247`c` varbinary(256), 248`ts` bigint(20) UNSIGNED NOT NULL, 249`value` mediumblob NOT NULL, 250PRIMARY KEY (`a`, `ts`), 251KEY kb (`b`) 252) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 253COMMENT='ttl_duration=1;ttl_col=ts;'; 254 255set global rocksdb_debug_ttl_rec_ts = -100; 256INSERT INTO t1 values ('a', NULL, 'bc', UNIX_TIMESTAMP(), 'd'); 257INSERT INTO t1 values ('de', 'fghijk', NULL, UNIX_TIMESTAMP(), 'l'); 258set global rocksdb_debug_ttl_rec_ts = 0; 259SELECT COUNT(*) FROM t1; 260 261set global rocksdb_debug_ttl_ignore_pk=1; 262set global rocksdb_force_flush_memtable_now=1; 263set global rocksdb_compact_cf='default'; 264set global rocksdb_debug_ttl_ignore_pk=0; 265 266# should have filtered the rows out since ttl is passed in compaction filter 267SELECT COUNT(*) FROM t1; 268DROP TABLE t1; 269 270# TTL test where you compact (values still exist), real_sleep, then compact again, 271# values should now be gone. 272CREATE TABLE t1 ( 273a INT NOT NULL, 274b varbinary(64) NOT NULL, 275c varbinary(256) NOT NULL, 276ts bigint(20) UNSIGNED NOT NULL, 277value mediumblob NOT NULL, 278PRIMARY KEY (b,a,c), 279KEY kb (b) 280) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 281COMMENT='ttl_duration=10;ttl_col=ts;'; 282 283set global rocksdb_debug_ttl_rec_ts = -300; 284INSERT INTO t1 values (1, 'b', 'c', UNIX_TIMESTAMP(), 'd'); 285INSERT INTO t1 values (2, 'e', 'f', UNIX_TIMESTAMP(), 'g'); 286set global rocksdb_debug_ttl_rec_ts = 300; 287INSERT INTO t1 values (3, 'i', 'j', UNIX_TIMESTAMP(), 'k'); 288INSERT INTO t1 values (4, 'm', 'n', UNIX_TIMESTAMP(), 'o'); 289set global rocksdb_debug_ttl_rec_ts = 0; 290 291# Nothing should get removed here. 292set global rocksdb_debug_ttl_snapshot_ts = -3600; 293set global rocksdb_force_flush_memtable_now=1; 294set global rocksdb_compact_cf='default'; 295set global rocksdb_debug_ttl_snapshot_ts = 0; 296--sorted_result 297SELECT a FROM t1 FORCE INDEX (kb); 298 299# 1 and 2 should get removed here. 300set global rocksdb_debug_ttl_ignore_pk=1; 301set global rocksdb_compact_cf='default'; 302set global rocksdb_debug_ttl_ignore_pk=0; 303--sorted_result 304SELECT a FROM t1 FORCE INDEX (kb); 305 306# 3 and 4 should get removed here. 307set global rocksdb_debug_ttl_ignore_pk=1; 308set global rocksdb_debug_ttl_snapshot_ts = 3600; 309set global rocksdb_compact_cf='default'; 310set global rocksdb_debug_ttl_snapshot_ts = 0; 311set global rocksdb_debug_ttl_ignore_pk=0; 312--sorted_result 313SELECT a FROM t1 FORCE INDEX (kb); 314 315DROP TABLE t1; 316 317# TTL field with nullable ttl column (should fail) 318--error ER_RDB_TTL_COL_FORMAT 319CREATE TABLE t1 ( 320 a bigint(20) UNSIGNED NOT NULL, 321 b int NOT NULL, 322 c int NOT NULL, 323 ts bigint(20), 324 PRIMARY KEY (a,c), 325 KEY (b) 326) ENGINE=rocksdb 327COMMENT='ttl_duration=1;ttl_col=ts;'; 328 329# TTL field with non 8-bit integer column (should fail) 330--error ER_RDB_TTL_COL_FORMAT 331CREATE TABLE t1 ( 332 a bigint(20) UNSIGNED NOT NULL, 333 b int NOT NULL, 334 c int NOT NULL, 335 ts int, 336 PRIMARY KEY (a,c), 337 KEY (b) 338) ENGINE=rocksdb 339COMMENT='ttl_duration=1;ttl_col=ts;'; 340 341# TTL duration as some random garbage value 342--error ER_RDB_TTL_DURATION_FORMAT 343CREATE TABLE t1 ( 344 a bigint(20) UNSIGNED NOT NULL, 345 b int NOT NULL, 346 c int NOT NULL, 347 PRIMARY KEY (a,c), 348 KEY (b) 349) ENGINE=rocksdb 350COMMENT='ttl_duration=abc;'; 351 352# TTL col is some column outside of the table 353--error ER_RDB_TTL_COL_FORMAT 354CREATE TABLE t1 ( 355 a bigint(20) UNSIGNED NOT NULL, 356 b int NOT NULL, 357 c int NOT NULL, 358 PRIMARY KEY (a,c), 359 KEY (b) 360) ENGINE=rocksdb 361COMMENT='ttl_duration=1;ttl_col=abc;'; 362 363# TTL col must have accompanying duration 364--error ER_RDB_TTL_COL_FORMAT 365CREATE TABLE t1 ( 366 a bigint(20) UNSIGNED NOT NULL, 367 b int NOT NULL, 368 c int NOT NULL, 369 PRIMARY KEY (a,c), 370 KEY (b) 371) ENGINE=rocksdb 372COMMENT='ttl_col=abc;'; 373 374# Make sure it doesn't filter out things early 375CREATE TABLE t1 ( 376 a bigint(20) NOT NULL, 377 b int NOT NULL, 378 PRIMARY KEY (a), 379 KEY kb (b) 380) ENGINE=rocksdb 381COMMENT='ttl_duration=500;'; 382 383INSERT INTO t1 values (1, 1); 384SELECT COUNT(*) FROM t1 FORCE INDEX (kb); 385 386set global rocksdb_debug_ttl_ignore_pk=1; 387set global rocksdb_force_flush_memtable_now=1; 388set global rocksdb_compact_cf='default'; 389set global rocksdb_debug_ttl_ignore_pk=0; 390 391SELECT COUNT(*) FROM t1 FORCE INDEX (kb); 392DROP TABLE t1; 393 394# Testing altering table comment with updated TTL duration 395# This should trigger a rebuild of the table 396CREATE TABLE t1 ( 397 a INT PRIMARY KEY, 398 b INT NOT NULL, 399 KEY kb (b) 400) ENGINE=rocksdb 401COMMENT='ttl_duration=100;'; 402 403INSERT INTO t1 values (1, 1); 404SELECT * FROM t1 FORCE INDEX (kb); 405 406set global rocksdb_debug_ttl_rec_ts = -300; 407ALTER TABLE t1 COMMENT = 'ttl_duration=1'; 408set global rocksdb_debug_ttl_rec_ts = 0; 409SHOW CREATE TABLE t1; 410 411set global rocksdb_debug_ttl_ignore_pk=1; 412set global rocksdb_force_flush_memtable_now=1; 413set global rocksdb_compact_cf='default'; 414set global rocksdb_debug_ttl_ignore_pk=0; 415 416SELECT COUNT(*) FROM t1 FORCE INDEX (kb); 417DROP TABLE t1; 418 419# Tables with hidden PK disabled 420CREATE TABLE t1 ( 421 a INT PRIMARY KEY, 422 b INT, 423 KEY (b) 424) ENGINE=rocksdb 425COMMENT='ttl_duration=100;'; 426 427--error ER_RDB_TTL_UNSUPPORTED 428ALTER TABLE t1 DROP PRIMARY KEY; 429 430DROP TABLE t1; 431 432# Test replacing PK, ttl should still work after 433CREATE TABLE t1 ( 434 a INT PRIMARY KEY, 435 b INT, 436 KEY kb (b) 437) ENGINE=rocksdb 438COMMENT='ttl_duration=5;'; 439 440INSERT INTO t1 VALUES (1,1); 441INSERT INTO t1 VALUES (2,2); 442 443ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(b); 444set global rocksdb_debug_ttl_snapshot_ts = -3600; 445set global rocksdb_force_flush_memtable_now=1; 446set @@global.rocksdb_compact_cf = 'default'; 447set global rocksdb_debug_ttl_snapshot_ts = 0; 448 449--sorted_result 450SELECT COUNT(*) FROM t1 FORCE INDEX (kb); 451 452set global rocksdb_debug_ttl_ignore_pk=1; 453set global rocksdb_debug_ttl_snapshot_ts = 3600; 454set @@global.rocksdb_compact_cf = 'default'; 455set global rocksdb_debug_ttl_snapshot_ts = 0; 456set global rocksdb_debug_ttl_ignore_pk=0; 457 458--sorted_result 459SELECT COUNT(*) FROM t1 FORCE INDEX (kb); 460 461DROP TABLE t1; 462 463# Make sure table comment filled with other text before/after will work 464# (basically, it needs semicolon before and after) 465CREATE TABLE t1 ( 466 a bigint(20) UNSIGNED NOT NULL, 467 b int, 468 PRIMARY KEY (a,b), 469 KEY kb (b) 470) ENGINE=rocksdb 471COMMENT='asdadfasdfsadfadf ;ttl_duration=1; asfasdfasdfadfa'; 472INSERT INTO t1 values (UNIX_TIMESTAMP(), 1); 473SELECT COUNT(*) FROM t1 FORCE INDEX (kb); 474 475set global rocksdb_debug_ttl_snapshot_ts = 3600; 476set global rocksdb_force_flush_memtable_now=1; 477set global rocksdb_compact_cf='default'; 478set global rocksdb_debug_ttl_snapshot_ts = 0; 479 480SELECT COUNT(*) FROM t1 FORCE INDEX (kb); 481 482ALTER TABLE t1 COMMENT = 'adsf;;ttl_duration=5;asfasdfa;ttl_col=a;asdfasdf;'; 483set global rocksdb_debug_ttl_rec_ts = 300; 484INSERT INTO t1 values (UNIX_TIMESTAMP(), 2); 485set global rocksdb_debug_ttl_rec_ts = 0; 486set global rocksdb_force_flush_memtable_now=1; 487 488# nothing removed here 489set global rocksdb_compact_cf='default'; 490SELECT COUNT(*) FROM t1 FORCE INDEX (kb); 491 492# all removed here 493set global rocksdb_debug_ttl_ignore_pk=1; 494set global rocksdb_debug_ttl_snapshot_ts = 3600; 495set global rocksdb_compact_cf='default'; 496set global rocksdb_debug_ttl_snapshot_ts = 0; 497set global rocksdb_debug_ttl_ignore_pk=0; 498SELECT COUNT(*) FROM t1 FORCE INDEX (kb); 499 500DROP TABLE t1; 501 502# Test to make sure that TTL retains original timestamp during update 503CREATE TABLE t1 ( 504 a bigint(20) NOT NULL, 505 b int NOT NULL, 506 PRIMARY KEY (a), 507 KEY kb (b) 508) ENGINE=rocksdb 509COMMENT='ttl_duration=5;'; 510 511set global rocksdb_debug_ttl_rec_ts = -300; 512INSERT INTO t1 values (1, 0); 513INSERT INTO t1 values (3, 0); 514INSERT INTO t1 values (5, 0); 515set global rocksdb_debug_ttl_rec_ts = 300; 516INSERT INTO t1 values (7, 0); 517INSERT INTO t1 values (9, 0); 518set global rocksdb_debug_ttl_rec_ts = 0; 519 520UPDATE t1 SET a=a+1; 521--sorted_result 522SELECT * FROM t1 FORCE INDEX (kb); 523 524set global rocksdb_debug_ttl_ignore_pk=1; 525set global rocksdb_force_flush_memtable_now=1; 526set global rocksdb_compact_cf='default'; 527set global rocksdb_debug_ttl_ignore_pk=0; 528 529# 1,3,5 should be dropped 530--sorted_result 531SELECT * FROM t1; 532DROP TABLE t1; 533 534# test behaviour on update with TTL column, TTL time can be updated here. 535CREATE TABLE t1 ( 536 a INT, 537 b bigint(20) UNSIGNED NOT NULL, 538 PRIMARY KEY (a), 539 KEY kb (b) 540) ENGINE=rocksdb 541COMMENT='ttl_duration=5;ttl_col=b;'; 542 543set global rocksdb_debug_ttl_rec_ts = -300; 544INSERT INTO t1 values (1, UNIX_TIMESTAMP()); 545INSERT INTO t1 values (3, UNIX_TIMESTAMP()); 546INSERT INTO t1 values (5, UNIX_TIMESTAMP()); 547INSERT INTO t1 values (7, UNIX_TIMESTAMP()); 548 549set global rocksdb_debug_ttl_rec_ts = 300; 550UPDATE t1 SET b=(UNIX_TIMESTAMP()+1) WHERE a < 4; 551set global rocksdb_debug_ttl_rec_ts = 0; 552 553--sorted_result 554SELECT a FROM t1 FORCE INDEX (kb); 555 556set global rocksdb_debug_ttl_ignore_pk=1; 557set global rocksdb_force_flush_memtable_now=1; 558set global rocksdb_compact_cf='default'; 559set global rocksdb_debug_ttl_ignore_pk=0; 560 561# 5 and 7 should be gone here 562--sorted_result 563SELECT a FROM t1 FORCE INDEX (kb); 564DROP TABLE t1; 565 566# Test rows expired stat variable and disable ttl variable 567CREATE TABLE t1 ( 568 a bigint(20) NOT NULL, 569 b int NOT NULL, 570 PRIMARY KEY (a), 571 KEY kb (b) 572) ENGINE=rocksdb 573COMMENT='ttl_duration=1;'; 574 575set global rocksdb_debug_ttl_rec_ts = -100; 576INSERT INTO t1 values (1, 1); 577INSERT INTO t1 values (2, 1); 578INSERT INTO t1 values (3, 1); 579set global rocksdb_debug_ttl_rec_ts = 0; 580 581set global rocksdb_enable_ttl=0; 582set global rocksdb_force_flush_memtable_now=1; 583set global rocksdb_compact_cf='default'; 584 585select variable_value into @c from information_schema.global_status where variable_name='rocksdb_rows_expired'; 586set global rocksdb_enable_ttl=1; 587set global rocksdb_compact_cf='default'; 588 589select variable_value-@c from information_schema.global_status where variable_name='rocksdb_rows_expired'; 590SELECT COUNT(*) FROM t1 FORCE INDEX (kb); 591DROP TABLE t1; 592 593# Table with TTL won't increment rows expired when no records have been 594# compacted 595CREATE TABLE t1 ( 596 a bigint(20) NOT NULL, 597 b int NOT NULL, 598 PRIMARY KEY (a), 599 KEY kb (b) 600) ENGINE=rocksdb 601COMMENT='ttl_duration=100;'; 602 603INSERT INTO t1 values (1, 1); 604INSERT INTO t1 values (2, 2); 605INSERT INTO t1 values (3, 3); 606 607select variable_value into @c from information_schema.global_status where variable_name='rocksdb_rows_expired'; 608set global rocksdb_force_flush_memtable_now=1; 609set global rocksdb_compact_cf='default'; 610select variable_value-@c from information_schema.global_status where variable_name='rocksdb_rows_expired'; 611 612DROP TABLE t1; 613 614# Test update on TTL column in pk 615CREATE TABLE t1 ( 616 a INT, 617 b bigint(20) UNSIGNED NOT NULL, 618 PRIMARY KEY (a, b), 619 KEY kb (b) 620) ENGINE=rocksdb 621COMMENT='ttl_duration=5;ttl_col=b;'; 622 623set global rocksdb_debug_ttl_rec_ts = -300; 624INSERT INTO t1 values (1, UNIX_TIMESTAMP()); 625INSERT INTO t1 values (3, UNIX_TIMESTAMP()); 626INSERT INTO t1 values (5, UNIX_TIMESTAMP()); 627INSERT INTO t1 values (7, UNIX_TIMESTAMP()); 628 629set global rocksdb_debug_ttl_rec_ts = 300; 630UPDATE t1 SET b=(UNIX_TIMESTAMP()+1) WHERE a < 4; 631set global rocksdb_debug_ttl_rec_ts = 0; 632 633--sorted_result 634SELECT a FROM t1 FORCE INDEX (kb); 635 636set global rocksdb_debug_ttl_ignore_pk=1; 637set global rocksdb_force_flush_memtable_now=1; 638set global rocksdb_compact_cf='default'; 639set global rocksdb_debug_ttl_ignore_pk=0; 640 641# 5 and 7 should be gone here 642--sorted_result 643SELECT a FROM t1 FORCE INDEX (kb); 644DROP TABLE t1; 645 646# test behaviour on update with TTL column, TTL time can be updated here. 647CREATE TABLE t1 ( 648 a INT, 649 b bigint(20) UNSIGNED NOT NULL, 650 PRIMARY KEY (a, b) 651) ENGINE=rocksdb 652COMMENT='ttl_duration=5;ttl_col=b;'; 653 654set global rocksdb_debug_ttl_rec_ts = -300; 655INSERT INTO t1 values (1, UNIX_TIMESTAMP()); 656INSERT INTO t1 values (3, UNIX_TIMESTAMP()); 657INSERT INTO t1 values (5, UNIX_TIMESTAMP()); 658INSERT INTO t1 values (7, UNIX_TIMESTAMP()); 659 660set global rocksdb_debug_ttl_rec_ts = 300; 661UPDATE t1 SET b=(UNIX_TIMESTAMP()+1) WHERE a < 4; 662set global rocksdb_debug_ttl_rec_ts = 0; 663 664--sorted_result 665SELECT a FROM t1; 666 667set global rocksdb_force_flush_memtable_now=1; 668set global rocksdb_compact_cf='default'; 669 670# 7 should be gone here 671--sorted_result 672SELECT a FROM t1; 673DROP TABLE t1; 674 675# Add index inplace 676CREATE TABLE t1 ( 677`a` binary(8) NOT NULL, 678`b` varbinary(64) NOT NULL, 679`c` varbinary(256) NOT NULL, 680`ts` bigint(20) UNSIGNED NOT NULL, 681`value` mediumblob NOT NULL, 682PRIMARY KEY (`b`,`a`,`c`) 683) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 684COMMENT='ttl_duration=1;ttl_col=ts;'; 685 686set global rocksdb_debug_ttl_rec_ts = -100; 687INSERT INTO t1 values ('a', 'b', 'c', UNIX_TIMESTAMP(), 'd'); 688INSERT INTO t1 values ('d', 'e', 'f', UNIX_TIMESTAMP(), 'g'); 689set global rocksdb_debug_ttl_rec_ts = 0; 690SELECT COUNT(*); 691 692set global rocksdb_debug_ttl_ignore_pk = 1; 693set global rocksdb_force_flush_memtable_now=1; 694set global rocksdb_compact_cf='default'; 695set global rocksdb_debug_ttl_ignore_pk = 0; 696 697# nothing filtered out 698SELECT COUNT(*); 699 700CREATE INDEX kb on t1 (b); 701 702set global rocksdb_debug_ttl_ignore_pk = 1; 703set global rocksdb_force_flush_memtable_now=1; 704set global rocksdb_compact_cf='default'; 705set global rocksdb_debug_ttl_ignore_pk = 0; 706 707# should have filtered the rows out since ttl is passed in compaction filter 708SELECT COUNT(*) FROM t1 FORCE INDEX (kb); 709DROP TABLE t1; 710 711# Add index inplace, implicit TTL 712CREATE TABLE t1 ( 713`a` binary(8) NOT NULL, 714`b` varbinary(64) NOT NULL, 715`c` varbinary(256) NOT NULL, 716`value` mediumblob NOT NULL, 717PRIMARY KEY (`b`,`a`,`c`) 718) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 719COMMENT='ttl_duration=1'; 720 721set global rocksdb_debug_ttl_rec_ts = -100; 722INSERT INTO t1 values ('a', 'b', 'c', 'd'); 723INSERT INTO t1 values ('d', 'e', 'f', 'g'); 724set global rocksdb_debug_ttl_rec_ts = 0; 725SELECT COUNT(*); 726 727set global rocksdb_debug_ttl_ignore_pk = 1; 728set global rocksdb_force_flush_memtable_now=1; 729set global rocksdb_compact_cf='default'; 730set global rocksdb_debug_ttl_ignore_pk = 0; 731 732# nothing filtered out 733SELECT COUNT(*); 734 735CREATE INDEX kb on t1 (b); 736 737set global rocksdb_debug_ttl_ignore_pk = 1; 738set global rocksdb_force_flush_memtable_now=1; 739set global rocksdb_compact_cf='default'; 740set global rocksdb_debug_ttl_ignore_pk = 0; 741 742# should have filtered the rows out since ttl is passed in compaction filter 743SELECT COUNT(*) FROM t1 FORCE INDEX (kb); 744DROP TABLE t1; 745 746# Add index inplace, TTL column in PK 747CREATE TABLE t1 ( 748`a` binary(8) NOT NULL, 749`b` varbinary(64) NOT NULL, 750`c` varbinary(256) NOT NULL, 751`ts` bigint(20) UNSIGNED NOT NULL, 752`value` mediumblob NOT NULL, 753PRIMARY KEY (`b`,`a`,`c`, `ts`) 754) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 755COMMENT='ttl_duration=1;ttl_col=ts;'; 756 757set global rocksdb_debug_ttl_rec_ts = -100; 758INSERT INTO t1 values ('a', 'b', 'c', UNIX_TIMESTAMP(), 'd'); 759INSERT INTO t1 values ('d', 'e', 'f', UNIX_TIMESTAMP(), 'g'); 760set global rocksdb_debug_ttl_rec_ts = 0; 761SELECT COUNT(*); 762 763set global rocksdb_debug_ttl_ignore_pk = 1; 764set global rocksdb_force_flush_memtable_now=1; 765set global rocksdb_compact_cf='default'; 766set global rocksdb_debug_ttl_ignore_pk = 0; 767 768# nothing filtered out 769SELECT COUNT(*); 770 771CREATE INDEX kb on t1 (b); 772 773set global rocksdb_debug_ttl_ignore_pk = 1; 774set global rocksdb_force_flush_memtable_now=1; 775set global rocksdb_compact_cf='default'; 776set global rocksdb_debug_ttl_ignore_pk = 0; 777 778# should have filtered the rows out since ttl is passed in compaction filter 779SELECT COUNT(*) FROM t1 FORCE INDEX (kb); 780DROP TABLE t1; 781