1--source include/have_rocksdb.inc 2--source ../include/have_write_committed.inc 3--source include/count_sessions.inc 4 5# 6# RocksDB Storage Engine tests 7# 8select ENGINE,COMMENT,TRANSACTIONS,XA,SAVEPOINTS from information_schema.engines where engine = 'rocksdb'; 9 10# Disable background compaction to prevent stats from affect explain output 11SET @ORIG_PAUSE_BACKGROUND_WORK = @@ROCKSDB_PAUSE_BACKGROUND_WORK; 12SET GLOBAL ROCKSDB_PAUSE_BACKGROUND_WORK = 1; 13 14--echo # 15--echo # Issue #1: Don't update indexes if index values have not changed 16--echo # 17# [Jay Edgar] I moved this test first because it uses the 18# rocksdb_number_keys_written value, but this value is affected out of band 19# by drop tables. There is a background thread that periodically processes 20# through the list of dropped keys and if any are gone from the database it 21# deletes information related to the key - and this delete causes this count 22# to be incorrect. I moved this test first and made the whole test require 23# a fresh server to hopefully avoid tihs. 24create table t1 ( 25 pk int primary key, 26 a int, 27 b int, 28 key(a) 29) engine=rocksdb; 30 31insert into t1 values 32(1,1,1), (2,2,2), (3,3,3), (4,4,4); 33 34set @var1=(select variable_value 35 from performance_schema.global_status 36 where variable_name='rocksdb_number_keys_written'); 37 38--echo # Do an update that doesn't change the key 'a'. 39update t1 set b=3334341 where a=2; 40 41set @var2=(select variable_value 42 from performance_schema.global_status 43 where variable_name='rocksdb_number_keys_written'); 44--echo # The following should produce 1 45select @var2 - @var1; 46 47--echo # Do an update that sets the key to the same value 48update t1 set a=pk where a=3; 49set @var3=(select variable_value 50 from performance_schema.global_status 51 where variable_name='rocksdb_number_keys_written'); 52--echo # We have 'updated' column to the same value, so the following must return 0: 53select @var3 - @var2; 54drop table t1; 55 56create table t0 (a int primary key) engine=rocksdb; 57show create table t0; 58drop table t0; 59 60create table t1 (a int primary key, b int) engine=rocksdb; 61insert into t1 values (1,1); 62insert into t1 values (2,2); 63 64select * from t1; 65 66--echo # Check that we can create another table and insert there 67create table t2 (a varchar(10) primary key, b varchar(10)) engine=rocksdb; 68insert into t2 value ('abc','def'); 69insert into t2 value ('hijkl','mnopq'); 70select * from t2; 71 72--echo # Select again from t1 to see that records from different tables dont mix 73select * from t1; 74 75explain select * from t2 where a='no-such-key'; 76--replace_column 10 # 77explain select * from t2 where a='abc'; 78select * from t2 where a='abc'; 79 80--echo # Try a composite PK 81create table t3 ( 82 pk1 int, 83 pk2 varchar(10), 84 col1 varchar(10), 85 primary key(pk1, pk2) 86) engine=rocksdb; 87 88insert into t3 values (2,'two', 'row#2'); 89insert into t3 values (3,'three', 'row#3'); 90insert into t3 values (1,'one', 'row#1'); 91 92select * from t3; 93select * from t3 where pk1=3 and pk2='three'; 94 95drop table t1, t2, t3; 96 97--echo # 98--echo # Test blob values 99--echo # 100 101create table t4 (a int primary key, b blob) engine=rocksdb; 102insert into t4 values (1, repeat('quux-quux', 60)); 103insert into t4 values (10, repeat('foo-bar', 43)); 104insert into t4 values (5, repeat('foo-bar', 200)); 105 106insert into t4 values (2, NULL); 107 108 109select 110 a, 111 (case a 112 when 1 then b=repeat('quux-quux', 60) 113 when 10 then b=repeat('foo-bar', 43) 114 when 5 then b=repeat('foo-bar', 200) 115 when 2 then b is null 116 else 'IMPOSSIBLE!' end) as CMP 117from t4; 118 119drop table t4; 120 121--echo # 122--echo # Test blobs of various sizes 123--echo # 124 125--echo # TINYBLOB 126create table t5 (a int primary key, b tinyblob) engine=rocksdb; 127insert into t5 values (1, repeat('quux-quux', 6)); 128insert into t5 values (10, repeat('foo-bar', 4)); 129insert into t5 values (5, repeat('foo-bar', 2)); 130select 131 a, 132 (case a 133 when 1 then b=repeat('quux-quux', 6) 134 when 10 then b=repeat('foo-bar', 4) 135 when 5 then b=repeat('foo-bar', 2) 136 else 'IMPOSSIBLE!' end) as CMP 137from t5; 138drop table t5; 139 140--echo # MEDIUMBLOB 141create table t6 (a int primary key, b mediumblob) engine=rocksdb; 142insert into t6 values (1, repeat('AB', 65000)); 143insert into t6 values (10, repeat('bbb', 40000)); 144insert into t6 values (5, repeat('foo-bar', 2)); 145select 146 a, 147 (case a 148 when 1 then b=repeat('AB', 65000) 149 when 10 then b=repeat('bbb', 40000) 150 when 5 then b=repeat('foo-bar', 2) 151 else 'IMPOSSIBLE!' end) as CMP 152from t6; 153drop table t6; 154 155--echo # LONGBLOB 156create table t7 (a int primary key, b longblob) engine=rocksdb; 157insert into t7 values (1, repeat('AB', 65000)); 158insert into t7 values (10, repeat('bbb', 40000)); 159insert into t7 values (5, repeat('foo-bar', 2)); 160select 161 a, 162 (case a 163 when 1 then b=repeat('AB', 65000) 164 when 10 then b=repeat('bbb', 40000) 165 when 5 then b=repeat('foo-bar', 2) 166 else 'IMPOSSIBLE!' end) as CMP 167from t7; 168drop table t7; 169 170 171--echo # 172--echo # Check if DELETEs work 173--echo # 174create table t8 (a varchar(10) primary key, col1 varchar(12)) engine=rocksdb; 175 176insert into t8 values 177 ('one', 'eins'), 178 ('two', 'zwei'), 179 ('three', 'drei'), 180 ('four', 'vier'), 181 ('five', 'funf'); 182 183--echo # Delete by PK 184--replace_column 10 # 185explain delete from t8 where a='three'; 186delete from t8 where a='three'; 187 188select * from t8; 189 190--echo # Delete while doing a full table scan 191delete from t8 where col1='eins' or col1='vier'; 192select * from t8; 193 194--echo # delete w/o WHERE: 195delete from t8; 196select * from t8; 197 198--echo # 199--echo # Test UPDATEs 200--echo # 201insert into t8 values 202 ('one', 'eins'), 203 ('two', 'zwei'), 204 ('three', 'drei'), 205 ('four', 'vier'), 206 ('five', 'funf'); 207 208update t8 set col1='dva' where a='two'; 209 210update t8 set a='fourAAA' where col1='vier'; 211 212select * from t8; 213delete from t8; 214 215--echo # 216--echo # Basic transactions tests 217--echo # 218begin; 219insert into t8 values ('trx1-val1', 'data'); 220insert into t8 values ('trx1-val2', 'data'); 221rollback; 222select * from t8; 223 224begin; 225insert into t8 values ('trx1-val1', 'data'); 226insert into t8 values ('trx1-val2', 'data'); 227commit; 228select * from t8; 229 230drop table t8; 231 232--echo # 233--echo # Check if DROP TABLE works 234--echo # 235create table t8 (a varchar(10) primary key, col1 varchar(12)) engine=rocksdb; 236select * from t8; 237insert into t8 values ('foo','foo'); 238drop table t8; 239create table t8 (a varchar(10) primary key, col1 varchar(12)) engine=rocksdb; 240select * from t8; 241drop table t8; 242 243--echo # 244--echo # MDEV-3961: Assertion ... on creating a TEMPORARY RocksDB table 245--echo # 246--error ER_ILLEGAL_HA_CREATE_OPTION 247CREATE TEMPORARY TABLE t10 (pk INT PRIMARY KEY) ENGINE=RocksDB; 248 249--echo # 250--echo # MDEV-3963: JOIN or WHERE conditions involving keys on RocksDB tables don't work 251--echo # 252CREATE TABLE t10 (i INT PRIMARY KEY) ENGINE=RocksDB; 253INSERT INTO t10 VALUES (1),(3); 254CREATE TABLE t11 (j INT PRIMARY KEY) ENGINE=RocksDB; 255INSERT INTO t11 VALUES (1),(4); 256 257select * from t10; 258select * from t11; 259--replace_column 10 # 260EXPLAIN 261SELECT * FROM t10, t11 WHERE i=j; 262SELECT * FROM t10, t11 WHERE i=j; 263 264DROP TABLE t10,t11; 265 266--echo # 267--echo # MDEV-3962: SELECT with ORDER BY causes "ERROR 1030 (HY000): Got error 122 268--echo # 269CREATE TABLE t12 (pk INT PRIMARY KEY) ENGINE=RocksDB; 270INSERT INTO t12 VALUES (2),(1); 271SELECT * FROM t12 ORDER BY pk; 272DROP TABLE t12; 273 274--echo # 275--echo # MDEV-3964: Assertion `!pk_descr' fails in ha_rocksdb::open on adding partitions ... 276--echo # 277create table t14 (pk int primary key) engine=RocksDB partition by hash(pk) partitions 2; 278#--error ER_GET_ERRNO 279#alter table t14 add partition partitions 2; 280# ^^ works, but causes weird warnings in error log. 281drop table t14; 282 283--echo # 284--echo # MDEV-3960: Server crashes on running DISCARD TABLESPACE on a RocksDB table 285--echo # 286create table t9 (i int primary key) engine=rocksdb; 287--error ER_ILLEGAL_HA 288alter table t9 discard tablespace; 289drop table t9; 290 291--echo # 292--echo # MDEV-3959: Assertion `slice->size() == table->s->reclength' fails ... 293--echo # on accessing a table after ALTER 294--echo # 295CREATE TABLE t15 (a INT, rocksdb_pk INT PRIMARY KEY) ENGINE=RocksDB; 296INSERT INTO t15 VALUES (1,1),(5,2); 297#--error ER_ILLEGAL_HA 298ALTER TABLE t15 DROP COLUMN a; 299DROP TABLE t15; 300 301--echo # 302--echo # MDEV-3968: UPDATE produces a wrong result while modifying a PK on a RocksDB table 303--echo # 304create table t16 (pk int primary key, a char(8)) engine=RocksDB; 305insert into t16 values (1,'a'),(2,'b'),(3,'c'),(4,'d'); 306 307# 308# Not anymore: The following query will still eat a record because of CANT-SEE-OWN-CHANGES 309# property. 310# 311--error ER_DUP_ENTRY 312update t16 set pk=100, a = 'updated' where a in ('b','c'); 313select * from t16; 314drop table t16; 315 316--echo # 317--echo # MDEV-3970: A set of assorted crashes on inserting a row into a RocksDB table 318--echo # 319--disable_warnings 320drop table if exists t_very_long_table_name; 321--enable_warnings 322 323CREATE TABLE `t_very_long_table_name` ( 324 `c` char(1) NOT NULL, 325 `c0` char(0) NOT NULL, 326 `c1` char(1) NOT NULL, 327 `c20` char(20) NOT NULL, 328 `c255` char(255) NOT NULL, 329 PRIMARY KEY (`c255`) 330 ) ENGINE=RocksDB DEFAULT CHARSET=latin1; 331INSERT INTO t_very_long_table_name VALUES ('a', '', 'c', REPEAT('a',20), REPEAT('x',255)); 332drop table t_very_long_table_name; 333 334 335--echo # 336--echo # Test table locking and read-before-write checks. 337--echo # 338create table t17 (pk varchar(12) primary key, col1 varchar(12)) engine=rocksdb; 339insert into t17 values ('row1', 'val1'); 340 341--error ER_DUP_ENTRY 342insert into t17 values ('row1', 'val1-try2'); 343--error ER_DUP_ENTRY 344insert into t17 values ('ROW1', 'val1-try2'); 345 346insert into t17 values ('row2', 'val2'); 347insert into t17 values ('row3', 'val3'); 348 349--echo # This is ok 350update t17 set pk='row4' where pk='row1'; 351 352--echo # This will try to overwrite another row: 353--error ER_DUP_ENTRY 354update t17 set pk='row3' where pk='row2'; 355 356select * from t17; 357 358--echo # 359--echo # Locking tests 360--echo # 361 362connect (con1,localhost,root,,); 363 364--echo # First, make sure there's no locking when transactions update different rows 365connection con1; 366set autocommit=0; 367update t17 set col1='UPD1' where pk='row2'; 368 369connection default; 370update t17 set col1='UPD2' where pk='row3'; 371 372connection con1; 373commit; 374 375connection default; 376select * from t17; 377 378--echo # Check the variable 379show variables like 'rocksdb_lock_wait_timeout'; 380set rocksdb_lock_wait_timeout=2; # seconds 381show variables like 'rocksdb_lock_wait_timeout'; 382 383--echo # Try updating the same row from two transactions 384connection con1; 385begin; 386update t17 set col1='UPD2-AA' where pk='row2'; 387 388connection default; 389--error ER_LOCK_WAIT_TIMEOUT 390update t17 set col1='UPD2-BB' where pk='row2'; 391 392set rocksdb_lock_wait_timeout=1000; # seconds 393--send 394 update t17 set col1='UPD2-CC' where pk='row2'; 395 396connection con1; 397rollback; 398 399connection default; 400reap; 401select * from t17 where pk='row2'; 402 403drop table t17; 404 405disconnect con1; 406--echo # 407--echo # MDEV-4035: RocksDB: SELECT produces different results inside a transaction (read is not repeatable) 408--echo # 409--enable_connect_log 410 411create table t18 (pk int primary key, i int) engine=RocksDB; 412begin; 413select * from t18; 414select * from t18 where pk = 1; 415 416--connect (con1,localhost,root,,) 417insert into t18 values (1,100); 418 419--connection default 420select * from t18; 421select * from t18 where pk = 1; 422commit; 423 424drop table t18; 425 426--echo # 427--echo # MDEV-4036: RocksDB: INSERT .. ON DUPLICATE KEY UPDATE does not work, produces ER_DUP_KEY 428--echo # 429create table t19 (pk int primary key, i int) engine=RocksDB; 430insert into t19 values (1,1); 431insert into t19 values (1,100) on duplicate key update i = 102; 432select * from t19; 433drop table t19; 434 435--echo # MDEV-4037: RocksDB: REPLACE doesn't work, produces ER_DUP_KEY 436create table t20 (pk int primary key, i int) engine=RocksDB; 437insert into t20 values (1,1); 438replace into t20 values (1,100); 439select * from t20; 440drop table t20; 441 442--echo # 443--echo # MDEV-4041: Server crashes in Primary_key_comparator::get_hashnr on INSERT 444--echo # 445create table t21 (v varbinary(16) primary key, i int) engine=RocksDB; 446insert into t21 values ('a',1); 447select * from t21; 448drop table t21; 449 450--echo # 451--echo # MDEV-4047: RocksDB: Assertion `0' fails in Protocol::end_statement() on multi-table INSERT IGNORE 452--echo # 453 454CREATE TABLE t22 (a int primary key) ENGINE=RocksDB; 455INSERT INTO t22 VALUES (1),(2); 456CREATE TABLE t23 (b int primary key) ENGINE=RocksDB; 457# MyRocks does not support gap locks in REPEATABLE-READ mode, this part of the 458# test does not require RR ISO to complete, so lets temporarily alter the ISO 459# to RC 460set @orig_tx_iso=@@session.transaction_isolation; 461set session transaction_isolation='READ-COMMITTED'; 462INSERT INTO t23 SELECT * FROM t22; 463DELETE IGNORE t22.*, t23.* FROM t22, t23 WHERE b < a; 464set session transaction_isolation=@orig_tx_iso; 465DROP TABLE t22,t23; 466 467--echo # 468--echo # MDEV-4046: RocksDB: Multi-table DELETE locks itself and ends with ER_LOCK_WAIT_TIMEOUT 469--echo # 470CREATE TABLE t24 (pk int primary key) ENGINE=RocksDB; 471INSERT INTO t24 VALUES (1),(2); 472 473CREATE TABLE t25 LIKE t24; 474# MyRocks does not support gap locks in REPEATABLE-READ mode, this part of the 475# test does not require RR ISO to complete, so lets temporarily alter the ISO 476# to RC 477set @orig_tx_iso=@@session.transaction_isolation; 478set session transaction_isolation='READ-COMMITTED'; 479INSERT INTO t25 SELECT * FROM t24; 480 481DELETE t25.* FROM t24, t25; 482set session transaction_isolation=@orig_tx_iso; 483DROP TABLE t24,t25; 484 485--echo # 486--echo # MDEV-4044: RocksDB: UPDATE or DELETE with ORDER BY locks itself 487--echo # 488create table t26 (pk int primary key, c char(1)) engine=RocksDB; 489insert into t26 values (1,'a'),(2,'b'); 490update t26 set c = 'x' order by pk limit 1; 491delete from t26 order by pk limit 1; 492select * from t26; 493drop table t26; 494 495 496--echo # 497--echo # Test whether SELECT ... FOR UPDATE puts locks 498--echo # 499create table t27(pk varchar(10) primary key, col1 varchar(20)) engine=RocksDB; 500insert into t27 values 501 ('row1', 'row1data'), 502 ('row2', 'row2data'), 503 ('row3', 'row3data'); 504 505connection con1; 506begin; 507select * from t27 where pk='row3' for update; 508 509connection default; 510set rocksdb_lock_wait_timeout=1; 511--error ER_LOCK_WAIT_TIMEOUT 512update t27 set col1='row2-modified' where pk='row3'; 513 514connection con1; 515rollback; 516connection default; 517disconnect con1; 518 519drop table t27; 520 521--echo # 522--echo # MDEV-4060: RocksDB: Assertion `! trx->batch' fails in 523--echo # 524create table t28 (pk int primary key, a int) engine=RocksDB; 525insert into t28 values (1,10),(2,20); 526begin; 527update t28 set a = 100 where pk = 3; 528rollback; 529select * from t28; 530drop table t28; 531 532 533--echo # 534--echo # Secondary indexes 535--echo # 536create table t30 ( 537 pk varchar(16) not null primary key, 538 key1 varchar(16) not null, 539 col1 varchar(16) not null, 540 key(key1) 541) engine=rocksdb; 542 543insert into t30 values ('row1', 'row1-key', 'row1-data'); 544insert into t30 values ('row2', 'row2-key', 'row2-data'); 545insert into t30 values ('row3', 'row3-key', 'row3-data'); 546 547--replace_column 10 # 548explain 549select * from t30 where key1='row2-key'; 550select * from t30 where key1='row2-key'; 551 552--replace_column 10 # 553explain 554select * from t30 where key1='row1'; 555--echo # This will produce nothing: 556select * from t30 where key1='row1'; 557 558--replace_column 10 # 559explain 560select key1 from t30; 561select key1 from t30; 562 563--echo # Create a duplicate record 564insert into t30 values ('row2a', 'row2-key', 'row2a-data'); 565 566--echo # Can we see it? 567select * from t30 where key1='row2-key'; 568 569delete from t30 where pk='row2'; 570select * from t30 where key1='row2-key'; 571 572--echo # 573--echo # Range scans on secondary index 574--echo # 575delete from t30; 576insert into t30 values 577 ('row1', 'row1-key', 'row1-data'), 578 ('row2', 'row2-key', 'row2-data'), 579 ('row3', 'row3-key', 'row3-data'), 580 ('row4', 'row4-key', 'row4-data'), 581 ('row5', 'row5-key', 'row5-data'); 582 583--replace_column 10 # 584explain 585select * from t30 where key1 <='row3-key'; 586select * from t30 where key1 <='row3-key'; 587 588--replace_column 10 # 589explain 590select * from t30 where key1 between 'row2-key' and 'row4-key'; 591select * from t30 where key1 between 'row2-key' and 'row4-key'; 592 593--replace_column 10 # 594explain 595select * from t30 where key1 in ('row2-key','row4-key'); 596select * from t30 where key1 in ('row2-key','row4-key'); 597 598--replace_column 10 # 599explain 600select key1 from t30 where key1 in ('row2-key','row4-key'); 601select key1 from t30 where key1 in ('row2-key','row4-key'); 602 603--replace_column 10 # 604explain 605select * from t30 where key1 > 'row1-key' and key1 < 'row4-key'; 606select * from t30 where key1 > 'row1-key' and key1 < 'row4-key'; 607 608--replace_column 10 # 609explain 610select * from t30 order by key1 limit 3; 611select * from t30 order by key1 limit 3; 612 613--replace_column 10 # 614explain 615select * from t30 order by key1 desc limit 3; 616select * from t30 order by key1 desc limit 3; 617 618--echo # 619--echo # Range scans on primary key 620--echo # 621--replace_column 10 # 622explain 623select * from t30 where pk <='row3'; 624select * from t30 where pk <='row3'; 625 626--replace_column 10 # 627explain 628select * from t30 where pk between 'row2' and 'row4'; 629select * from t30 where pk between 'row2' and 'row4'; 630 631--replace_column 10 # 632explain 633select * from t30 where pk in ('row2','row4'); 634select * from t30 where pk in ('row2','row4'); 635 636--replace_column 10 # 637explain 638select * from t30 order by pk limit 3; 639select * from t30 order by pk limit 3; 640 641drop table t30; 642 643 644--echo # 645--echo # MDEV-3841: RocksDB: Reading by PK prefix does not work 646--echo # 647create table t31 (i int, j int, k int, primary key(i,j,k)) engine=RocksDB; 648insert into t31 values (1,10,100),(2,20,200); 649select * from t31 where i = 1; 650select * from t31 where j = 10; 651select * from t31 where k = 100; 652select * from t31 where i = 1 and j = 10; 653select * from t31 where i = 1 and k = 100; 654select * from t31 where j = 10 and k = 100; 655select * from t31 where i = 1 and j = 10 and k = 100; 656drop table t31; 657 658--echo # 659--echo # MDEV-4055: RocksDB: UPDATE/DELETE by a multi-part PK does not work 660--echo # 661create table t32 (i int, j int, k int, primary key(i,j,k), a varchar(8)) engine=RocksDB; 662insert into t32 values 663 (1,10,100,''), 664 (2,20,200,''); 665select * from t32 where i = 1 and j = 10 and k = 100; 666update t32 set a = 'updated' where i = 1 and j = 10 and k = 100; 667select * from t32; 668drop table t32; 669 670--echo # 671--echo # MDEV-3841: RocksDB: Assertion `0' fails in ha_rocksdb::index_read_map on range select with ORDER BY .. DESC 672--echo # 673CREATE TABLE t33 (pk INT PRIMARY KEY, a CHAR(1)) ENGINE=RocksDB; 674INSERT INTO t33 VALUES (1,'a'),(2,'b'); 675SELECT * FROM t33 WHERE pk <= 10 ORDER BY pk DESC; 676DROP TABLE t33; 677 678--echo # 679--echo # MDEV-4081: RocksDB throws error 122 on an attempt to create a table with unique index 680--echo # 681#--error ER_GET_ERRMSG 682--echo # Unique indexes can be created, but uniqueness won't be enforced 683create table t33 (pk int primary key, u int, unique index(u)) engine=RocksDB; 684drop table t33; 685 686--echo # 687--echo # MDEV-4077: RocksDB: Wrong result (duplicate row) on select with range 688--echo # 689CREATE TABLE t34 (pk INT PRIMARY KEY) ENGINE=RocksDB; 690INSERT INTO t34 VALUES (10),(11); 691SELECT pk FROM t34 WHERE pk > 5 AND pk < 15; 692SELECT pk FROM t34 WHERE pk BETWEEN 5 AND 15; 693SELECT pk FROM t34 WHERE pk > 5; 694SELECT pk FROM t34 WHERE pk < 15; 695drop table t34; 696 697--echo # 698--echo # MDEV-4086: RocksDB does not allow a query with multi-part pk and index and ORDER BY .. DEC 699--echo # 700create table t35 (a int, b int, c int, d int, e int, primary key (a,b,c), key (a,c,d,e)) engine=RocksDB; 701insert into t35 values (1,1,1,1,1),(2,2,2,2,2); 702select * from t35 where a = 1 and c = 1 and d = 1 order by e desc; 703drop table t35; 704 705--echo # 706--echo # MDEV-4084: RocksDB: Wrong result on IN subquery with index 707--echo # 708CREATE TABLE t36 (pk INT PRIMARY KEY, a INT, KEY(a)) ENGINE=RocksDB; 709INSERT INTO t36 VALUES (1,10),(2,20); 710SELECT 3 IN ( SELECT a FROM t36 ); 711drop table t36; 712 713--echo # 714--echo # MDEV-4084: RocksDB: Wrong result on IN subquery with index 715--echo # 716CREATE TABLE t37 (pk INT PRIMARY KEY, a INT, b CHAR(1), KEY(a), KEY(a,b)) 717 ENGINE=RocksDB; 718INSERT INTO t37 VALUES (1,10,'x'), (2,20,'y'); 719SELECT MAX(a) FROM t37 WHERE a < 100; 720DROP TABLE t37; 721 722--echo # 723--echo # MDEV-4090: RocksDB: Wrong result (duplicate rows) on range access with secondary key and ORDER BY DESC 724--echo # 725CREATE TABLE t38 (pk INT PRIMARY KEY, i INT, KEY(i)) ENGINE=RocksDB; 726INSERT INTO t38 VALUES (1,10), (2,20); 727SELECT i FROM t38 WHERE i NOT IN (8) ORDER BY i DESC; 728drop table t38; 729 730--echo # 731--echo # MDEV-4092: RocksDB: Assertion `in_table(pa, a_len)' fails in Rdb_key_def::cmp_full_keys 732--echo # with a multi-part key and ORDER BY .. DESC 733--echo # 734CREATE TABLE t40 (pk1 INT PRIMARY KEY, a INT, b VARCHAR(1), KEY(b,a)) ENGINE=RocksDB; 735INSERT INTO t40 VALUES (1, 7,'x'),(2,8,'y'); 736 737CREATE TABLE t41 (pk2 INT PRIMARY KEY) ENGINE=RocksDB; 738INSERT INTO t41 VALUES (1),(2); 739 740SELECT * FROM t40, t41 WHERE pk1 = pk2 AND b = 'o' ORDER BY a DESC; 741DROP TABLE t40,t41; 742 743--echo # 744--echo # MDEV-4093: RocksDB: IN subquery by secondary key with NULL among values returns true instead of NULL 745--echo # 746CREATE TABLE t42 (pk INT PRIMARY KEY, a INT, KEY(a)) ENGINE=RocksDB; 747INSERT INTO t42 VALUES (1, NULL),(2, 8); 748SELECT ( 3 ) NOT IN ( SELECT a FROM t42 ); 749DROP TABLE t42; 750 751--echo # 752--echo # MDEV-4094: RocksDB: Wrong result on SELECT and ER_KEY_NOT_FOUND on 753--echo # DELETE with search by NULL-able secondary key ... 754--echo # 755CREATE TABLE t43 (pk INT PRIMARY KEY, a INT, b CHAR(1), KEY(a)) ENGINE=RocksDB; 756INSERT INTO t43 VALUES (1,8,'g'),(2,9,'x'); 757UPDATE t43 SET pk = 10 WHERE a = 8; 758REPLACE INTO t43 ( pk, a ) VALUES ( 1, 8 ); 759REPLACE INTO t43 ( pk, b ) VALUES ( 3, 'y' ); 760SELECT * FROM t43 WHERE a = 8; 761DELETE FROM t43 WHERE a = 8; 762DROP TABLE t43; 763 764--echo # 765--echo # Basic AUTO_INCREMENT tests 766--echo # 767create table t44(pk int primary key auto_increment, col1 varchar(12)) engine=rocksdb; 768insert into t44 (col1) values ('row1'); 769insert into t44 (col1) values ('row2'); 770insert into t44 (col1) values ('row3'); 771select * from t44; 772drop table t44; 773 774--echo # 775--echo # ALTER TABLE tests 776--echo # 777create table t45 (pk int primary key, col1 varchar(12)) engine=rocksdb; 778insert into t45 values (1, 'row1'); 779insert into t45 values (2, 'row2'); 780alter table t45 rename t46; 781select * from t46; 782drop table t46; 783--error ER_BAD_TABLE_ERROR 784drop table t45; 785 786 787--echo # 788--echo # Check Bulk loading 789--echo # Bulk loading used to overwrite existing data 790--echo # Now it fails if there is data overlap with what 791--echo # already exists 792--echo # 793# We exclude rocksdb_max_open_files here because it value is dependent on 794# the value of the servers open_file_limit and is expected to be different 795# across distros and installs 796show variables where variable_name like 'rocksdb%' and variable_name not like 'rocksdb_max_open_files'; 797create table t47 (pk int primary key, col1 varchar(12)) engine=rocksdb; 798insert into t47 values (1, 'row1'); 799insert into t47 values (2, 'row2'); 800set rocksdb_bulk_load=1; 801insert into t47 values (3, 'row3'),(4, 'row4'); 802set rocksdb_bulk_load=0; 803# Check concurrent bulk loading 804--connect (con1,localhost,root,,) 805set rocksdb_bulk_load=1; 806insert into t47 values (10, 'row10'),(11, 'row11'); 807--connection default 808set rocksdb_bulk_load=1; 809insert into t47 values (100, 'row100'),(101, 'row101'); 810--disconnect con1 811--connection default 812set rocksdb_bulk_load=0; 813--disable_query_log 814let $wait_condition = select count(*) = 8 as c from t47; 815--source include/wait_condition.inc 816--enable_query_log 817select * from t47; 818drop table t47; 819 820--echo # 821--echo # Fix TRUNCATE over empty table (transaction is committed when it wasn't 822--echo # started) 823--echo # 824create table t48(pk int primary key auto_increment, col1 varchar(12)) engine=rocksdb; 825set autocommit=0; 826#--error ER_ILLEGAL_HA 827truncate table t48; 828set autocommit=1; 829drop table t48; 830 831--echo # 832--echo # MDEV-4059: RocksDB: query waiting for a lock cannot be killed until query timeout exceeded 833--echo # 834--enable_connect_log 835 836create table t49 (pk int primary key, a int) engine=RocksDB; 837insert into t49 values (1,10),(2,20); 838begin; 839update t49 set a = 100 where pk = 1; 840 841--connect (con1,localhost,root,,) 842--let $con1_id = `SELECT CONNECTION_ID()` 843set rocksdb_lock_wait_timeout=60; 844set @var1= to_seconds(now()); 845send update t49 set a = 1000 where pk = 1; 846 847--connect (con2,localhost,root,,) 848--echo kill query \$con1_id; 849--disable_query_log 850# TODO : this is bad and will result in unstable tests 851# See MYR-122 : split up rocksdb.rocksdb test into individual tests that 852# actually test something specific 853# If we immeditely kill the query - internally the condition broadcast can 854# occur before the lock is waiting on the condition, thus the broadcast call 855# is lost. Sleep 1 second to avoid this condition. 856--sleep 1 857eval kill query $con1_id; 858--enable_query_log 859--connection con1 860--error ER_QUERY_INTERRUPTED 861--reap 862set @var2= to_seconds(now()); 863 864# We expect the time to kill query in con1 should be below 865# rocksdb_lock_wait_timeout (60). 866select if ((@var2 - @var1) < 60, "passed", (@var2 - @var1)) as 'result'; 867 868--connection default 869--disconnect con1 870--disconnect con2 871 872commit; 873drop table t49; 874 875--echo # 876--echo # Index-only tests for INT-based columns 877--echo # 878create table t1 (pk int primary key, key1 int, col1 int, key(key1)) engine=rocksdb; 879insert into t1 values (1,1,1); 880insert into t1 values (2,2,2); 881insert into t1 values (-5,-5,-5); 882--echo # INT column uses index-only: 883--replace_column 10 # 884explain 885select key1 from t1 where key1=2; 886select key1 from t1 where key1=2; 887select key1 from t1 where key1=-5; 888drop table t1; 889 890 891create table t2 (pk int primary key, key1 int unsigned, col1 int, key(key1)) engine=rocksdb; 892insert into t2 values (1,1,1), (2,2,2); 893--echo # INT UNSIGNED column uses index-only: 894--replace_column 10 # 895explain 896select key1 from t2 where key1=2; 897select key1 from t2 where key1=2; 898drop table t2; 899 900 901create table t3 (pk bigint primary key, key1 bigint, col1 int, key(key1)) engine=rocksdb; 902insert into t3 values (1,1,1), (2,2,2); 903--echo # BIGINT uses index-only: 904--replace_column 10 # 905explain 906select key1 from t3 where key1=2; 907select key1 from t3 where key1=2; 908drop table t3; 909 910--echo # 911--echo # Index-only reads for string columns 912--echo # 913create table t1 ( 914 pk int primary key, 915 key1 char(10) character set binary, 916 col1 int, 917 key (key1) 918) engine=rocksdb; 919insert into t1 values(1, 'one',11), (2,'two',22); 920--replace_column 10 # 921explain 922select key1 from t1 where key1='one'; 923--echo # The following will produce no rows. This looks like a bug, 924--echo # but it is actually correct behavior. Binary strings are end-padded 925--echo # with \0 character (and not space). Comparison does not ignore 926--echo # the tail of \0. 927select key1 from t1 where key1='one'; 928--replace_column 10 # 929explain 930select hex(key1) from t1 where key1='one\0\0\0\0\0\0\0'; 931select hex(key1) from t1 where key1='one\0\0\0\0\0\0\0'; 932drop table t1; 933 934 935create table t2 ( 936 pk int primary key, 937 key1 char(10) collate latin1_bin, 938 col1 int, 939 key (key1) 940) engine=rocksdb; 941insert into t2 values(1, 'one',11), (2,'two',22); 942--replace_column 10 # 943explain 944select key1 from t2 where key1='one'; 945select key1 from t2 where key1='one'; 946drop table t2; 947 948 949create table t3 ( 950 pk int primary key, 951 key1 char(10) collate utf8_bin, 952 col1 int, 953 key (key1) 954) engine=rocksdb; 955insert into t3 values(1, 'one',11), (2,'two',22); 956--replace_column 10 # 957explain 958select key1 from t3 where key1='one'; 959select key1 from t3 where key1='one'; 960drop table t3; 961 962 963--echo # a VARCHAR column 964create table t4 ( 965 pk int primary key, 966 key1 varchar(10) collate latin1_bin, 967 key(key1) 968) engine=rocksdb; 969insert into t4 values(1, 'one'), (2,'two'),(3,'threee'),(55,'fifty-five'); 970 971--replace_column 10 # 972explain 973select key1 from t4 where key1='two'; 974select key1 from t4 where key1='two'; 975 976select key1 from t4 where key1='fifty-five'; 977 978--replace_column 10 # 979explain 980select key1 from t4 where key1 between 's' and 'u'; 981select key1 from t4 where key1 between 's' and 'u'; 982 983drop table t4; 984 985--echo # 986--echo # MDEV-4305: RocksDB: Assertion `((keypart_map + 1) & keypart_map) == 0' fails in calculate_key_len 987--echo # 988CREATE TABLE t1 (pk1 INT, pk2 CHAR(32), i INT, PRIMARY KEY(pk1,pk2), KEY(i)) ENGINE=RocksDB; 989INSERT INTO t1 VALUES (1,'test1',6),(2,'test2',8); 990SELECT * FROM t1 WHERE i != 3 OR pk1 > 9; 991DROP TABLE t1; 992 993--echo # 994--echo # MDEV-4298: RocksDB: Assertion `thd->is_error() || kill_errno' fails in ha_rows filesort 995--echo # 996CREATE TABLE t1 (pk INT PRIMARY KEY, i INT, KEY(i)) ENGINE=RocksDB; 997INSERT INTO t1 VALUES (1,1),(2,2); 998# MyRocks does not support gap locks in REPEATABLE-READ mode, this part of the 999# test does not require RR ISO to complete, so lets temporarily alter the ISO 1000# to RC 1001set @orig_tx_iso=@@session.transaction_isolation; 1002set session transaction_isolation='READ-COMMITTED'; 1003BEGIN; 1004UPDATE t1 SET i = 100; 1005 1006--connect (con1,localhost,root,,test) 1007--error ER_LOCK_WAIT_TIMEOUT 1008DELETE IGNORE FROM t1 ORDER BY i; 1009--disconnect con1 1010 1011--connection default 1012COMMIT; 1013set session transaction_isolation=@orig_tx_iso; 1014DROP TABLE t1; 1015 1016--echo # 1017--echo # MDEV-4324: RocksDB: Valgrind "Use of uninitialised value" warnings on inserting value into varchar field 1018--echo # (testcase only) 1019--echo # 1020CREATE TABLE t1 (pk INT PRIMARY KEY, c VARCHAR(4)) ENGINE=RocksDB; 1021INSERT INTO t1 VALUES (1,'foo'), (2,'bar'); 1022DROP TABLE t1; 1023 1024--echo # 1025--echo # MDEV-4304: RocksDB: Index-only scan by a field with utf8_bin collation returns garbage symbols 1026--echo # 1027CREATE TABLE t1 (pk INT PRIMARY KEY, c1 CHAR(1), c2 CHAR(1), KEY(c1)) ENGINE=RocksDB CHARSET utf8 COLLATE utf8_bin; 1028INSERT INTO t1 VALUES (1,'h','h'); 1029SELECT * FROM t1; 1030SELECT c1 FROM t1; 1031DROP TABLE t1; 1032 1033--echo # 1034--echo # MDEV-4300: RocksDB: Server crashes in inline_mysql_mutex_lock on SELECT .. FOR UPDATE 1035--echo # 1036CREATE TABLE t2 (pk INT PRIMARY KEY, i INT, KEY (i)) ENGINE=RocksDB; 1037INSERT INTO t2 VALUES (1,4),(2,5); 1038SELECT 1 FROM t2 WHERE i < 0 FOR UPDATE; 1039DROP TABLE t2; 1040 1041--echo # 1042--echo # MDEV-4301: RocksDB: Assertion `pack_info != __null' fails in Rdb_key_def::unpack_record 1043--echo # 1044CREATE TABLE t1 (pk INT PRIMARY KEY, i INT, c CHAR(1), KEY(c,i)) ENGINE=RocksDB; 1045INSERT INTO t1 VALUES (1,4,'d'),(2,8,'e'); 1046SELECT MAX( pk ) FROM t1 WHERE i = 105 AND c = 'h'; 1047DROP TABLE t1; 1048 1049--echo # 1050--echo # MDEV-4337: RocksDB: Inconsistent results comparing a char field with an int field 1051--echo # 1052create table t1 (c char(1), i int, primary key(c), key(i)) engine=RocksDB; 1053insert into t1 values ('2',2),('6',6); 1054select * from t1 where c = i; 1055select * from t1 ignore index (i) where c = i; 1056drop table t1; 1057 1058 1059--echo # 1060--echo # Test statement rollback inside a transaction 1061--echo # 1062create table t1 (pk varchar(12) primary key) engine=rocksdb; 1063insert into t1 values ('old-val1'),('old-val2'); 1064 1065create table t2 (pk varchar(12) primary key) engine=rocksdb; 1066insert into t2 values ('new-val2'),('old-val1'); 1067 1068# MyRocks does not support gap locks in REPEATABLE-READ mode, this part of the 1069# test does not require RR ISO to complete, so lets temporarily alter the ISO 1070# to RC 1071set @orig_tx_iso=@@session.transaction_isolation; 1072set session transaction_isolation='READ-COMMITTED'; 1073begin; 1074insert into t1 values ('new-val1'); 1075--error ER_DUP_ENTRY 1076insert into t1 select * from t2; 1077commit; 1078set session transaction_isolation=@orig_tx_iso; 1079 1080select * from t1; 1081drop table t1, t2; 1082 1083--echo # 1084--echo # MDEV-4383: RocksDB: Wrong result of DELETE .. ORDER BY .. LIMIT: 1085--echo # rows that should be deleted remain in the table 1086--echo # 1087CREATE TABLE t2 (pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=RocksDB; 1088CREATE TABLE t1 (pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=RocksDB; 1089 1090INSERT INTO t1 (pk) VALUES (NULL),(NULL); 1091# MyRocks does not support gap locks in REPEATABLE-READ mode, this part of the 1092# test does not require RR ISO to complete, so lets temporarily alter the ISO 1093# to RC 1094set @orig_tx_iso=@@session.transaction_isolation; 1095set session transaction_isolation='READ-COMMITTED'; 1096BEGIN; 1097INSERT INTO t2 (pk) VALUES (NULL),(NULL); 1098INSERT INTO t1 (pk) VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL); 1099 1100--enable_info 1101SELECT * FROM t1 ORDER BY pk LIMIT 9; 1102DELETE FROM t1 ORDER BY pk LIMIT 9; 1103SELECT * FROM t1 ORDER BY pk LIMIT 9; 1104--disable_info 1105 1106COMMIT; 1107DROP TABLE t1,t2; 1108set session transaction_isolation=@orig_tx_iso; 1109 1110--echo # 1111--echo # MDEV-4374: RocksDB: Valgrind warnings 'Use of uninitialised value' on 1112--echo # inserting into a varchar column 1113--echo # 1114CREATE TABLE t1 (pk INT PRIMARY KEY, a VARCHAR(32)) ENGINE=ROCKSDB; 1115INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); 1116DROP TABLE t1; 1117 1118 1119# See MYR-122 : split up rocksdb.rocksdb test into individual tests that 1120# actually test something specific 1121--echo # 1122--echo # MDEV-4061: RocksDB: Changes from an interrupted query are still applied 1123--echo # 1124 1125--enable_connect_log 1126 1127create table t1 (pk int primary key, a int) engine=rocksdb; 1128insert into t1 values (1,10),(2,20); 1129 1130--let $con_id = `select connection_id()` 1131 1132set autocommit = 1; 1133--send update t1 set a = sleep(300) where pk = 1; 1134 1135--connect (con1,localhost,root,,) 1136 1137 1138let $wait_condition= select State='User sleep' from information_schema.processlist where id=$con_id; 1139--source include/wait_condition.inc 1140 1141--echo kill query \$con_id; 1142--disable_query_log 1143eval kill query $con_id; 1144--enable_query_log 1145 1146--connection default 1147--error ER_QUERY_INTERRUPTED 1148--reap 1149 1150select * from t1; 1151--disconnect con1 1152--disable_connect_log 1153drop table t1; 1154 1155 1156--echo # 1157--echo # MDEV-4099: RocksDB: Wrong results with index and range access after INSERT IGNORE or REPLACE 1158--echo # 1159CREATE TABLE t1 (pk INT PRIMARY KEY, a SMALLINT, b INT, KEY (a)) ENGINE=RocksDB; 1160INSERT IGNORE INTO t1 VALUES (1, 157, 0), (2, 1898, -504403), (1, -14659, 0); 1161SELECT * FROM t1; 1162SELECT pk FROM t1; 1163SELECT * FROM t1 WHERE a != 97; 1164DROP TABLE t1; 1165 1166 1167--echo # 1168--echo # Test @@rocksdb_max_row_locks 1169--echo # 1170CREATE TABLE t1 (pk INT PRIMARY KEY, a int) ENGINE=RocksDB; 1171set @a=-1; 1172insert into t1 select (@a:=@a+1), 1234 from performance_schema.session_variables limit 100; 1173set @tmp1= @@rocksdb_max_row_locks; 1174set GLOBAL rocksdb_max_row_locks= 20; 1175--error ER_GET_ERRMSG 1176update t1 set a=a+10; 1177set @@global.rocksdb_max_row_locks = @tmp1; 1178DROP TABLE t1; 1179 1180 1181--echo # 1182--echo # Test AUTO_INCREMENT behavior problem, 1183--echo # "explicit insert into an auto-inc column is not noticed by RocksDB" 1184--echo # 1185create table t1 (i int primary key auto_increment) engine=RocksDB; 1186 1187insert into t1 values (null); 1188insert into t1 values (null); 1189select * from t1; 1190drop table t1; 1191 1192create table t2 (i int primary key auto_increment) engine=RocksDB; 1193 1194insert into t2 values (1); 1195select * from t2; 1196 1197--echo # this fails (ie. used to fail), RocksDB engine did not notice use of '1' above 1198insert into t2 values (null); 1199select * from t2; 1200 1201--echo # but then this succeeds, so previous statement must have incremented next number counter 1202insert into t2 values (null); 1203select * from t2; 1204drop table t2; 1205 1206--echo # 1207--echo # Fix Issue#2: AUTO_INCREMENT value doesn't survive server shutdown 1208--echo # 1209create table t1 (i int primary key auto_increment) engine=RocksDB; 1210 1211insert into t1 values (null); 1212insert into t1 values (null); 1213 1214SET GLOBAL ROCKSDB_PAUSE_BACKGROUND_WORK = @ORIG_PAUSE_BACKGROUND_WORK; 1215 1216--source include/restart_mysqld.inc 1217 1218SET @ORIG_PAUSE_BACKGROUND_WORK = @@ROCKSDB_PAUSE_BACKGROUND_WORK; 1219SET GLOBAL ROCKSDB_PAUSE_BACKGROUND_WORK = 1; 1220 1221insert into t1 values (null); 1222select * from t1; 1223 1224drop table t1; 1225 1226--echo # 1227--echo # Fix Issue #3: SHOW TABLE STATUS shows Auto_increment=0 1228--echo # 1229create table t1 (i int primary key auto_increment) engine=RocksDB; 1230 1231insert into t1 values (null),(null); 1232--replace_column 7 # 12 # 13 # 1233show table status like 't1'; 1234drop table t1; 1235 1236--echo # 1237--echo # Fix Issue #4: Crash when using pseudo-unique keys 1238--echo # 1239CREATE TABLE t1 ( 1240 i INT, 1241 t TINYINT, 1242 s SMALLINT, 1243 m MEDIUMINT, 1244 b BIGINT, 1245 pk MEDIUMINT AUTO_INCREMENT PRIMARY KEY, 1246 UNIQUE KEY b_t (b,t) 1247) ENGINE=rocksdb; 1248 1249INSERT INTO t1 (i,t,s,m,b) VALUES (1,2,3,4,5),(1000,100,10000,1000000,1000000000000000000),(5,100,10000,1000000,100000000000000000),(2,3,4,5,6),(3,4,5,6,7),(101,102,103,104,105),(10001,103,10002,10003,10004),(10,11,12,13,14),(11,12,13,14,15),(12,13,14,15,16); 1250 1251SELECT b+t FROM t1 WHERE (b,t) IN ( SELECT b, t FROM t1 WHERE i>1 ) ORDER BY b+t; 1252DROP TABLE t1; 1253 1254--echo # 1255--echo # Fix issue #5: Transaction rollback doesn't undo all changes. 1256--echo # 1257create table t0 (a int) engine=myisam; 1258insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1259 1260create table t1 (id int auto_increment primary key, value int) engine=rocksdb; 1261 1262set autocommit=0; 1263begin; 1264set @a:=0; 1265insert into t1 select @a:=@a+1, @a from t0 A, t0 B, t0 C, t0 D where D.a<4; 1266insert into t1 select @a:=@a+1, @a from t0 A, t0 B, t0 C, t0 D where D.a<4; 1267insert into t1 select @a:=@a+1, @a from t0 A, t0 B, t0 C, t0 D where D.a<4; 1268rollback; 1269select count(*) from t1; 1270 1271set autocommit=1; 1272drop table t0, t1; 1273 1274--echo # 1275--echo # Check status variables 1276--echo # NOTE: We exclude rocksdb_num_get_for_update_calls because it's a debug only status var 1277--echo # 1278--replace_column 2 # 1279show status where variable_name like 'rocksdb%' and variable_name not like '%num_get_for_update%'; 1280 1281select VARIABLE_NAME from performance_schema.global_status where VARIABLE_NAME LIKE 'rocksdb%' and VARIABLE_NAME NOT LIKE '%num_get_for_update%'; 1282--echo # RocksDB-SE's status variables are global internally 1283--echo # but they are shown as both session and global, like InnoDB's status vars. 1284select VARIABLE_NAME from performance_schema.session_status where VARIABLE_NAME LIKE 'rocksdb%' and VARIABLE_NAME NOT LIKE '%num_get_for_update%'; 1285 1286 1287--echo # 1288--echo # Fix issue #9: HA_ERR_INTERNAL_ERROR when running linkbench 1289--echo # 1290create table t0 (a int) engine=myisam; 1291insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1292 1293create table t1 ( 1294 pk int primary key, 1295 col1 varchar(255), 1296 key(col1) 1297) engine=rocksdb; 1298insert into t1 select a, repeat('123456789ABCDEF-', 15) from t0; 1299select * from t1 where pk=3; 1300drop table t0, t1; 1301 1302--echo # 1303--echo # Fix issue #10: Segfault in Rdb_key_def::get_primary_key_tuple 1304--echo # 1305create table t0 (a int) engine=myisam; 1306insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1307 1308CREATE TABLE t1 ( 1309 id1 bigint(20) unsigned NOT NULL DEFAULT '0', 1310 id2 bigint(20) unsigned NOT NULL DEFAULT '0', 1311 link_type bigint(20) unsigned NOT NULL DEFAULT '0', 1312 visibility tinyint(3) NOT NULL DEFAULT '0', 1313 data varchar(255) NOT NULL DEFAULT '', 1314 time bigint(20) unsigned NOT NULL DEFAULT '0', 1315 version int(11) unsigned NOT NULL DEFAULT '0', 1316 PRIMARY KEY (link_type,id1,id2) 1317) engine=rocksdb; 1318 1319insert into t1 select a,a,a,1,a,a,a from t0; 1320 1321alter table t1 add index id1_type (id1,link_type,visibility,time,version,data); 1322select * from t1 where id1 = 3; 1323 1324drop table t0,t1; 1325 1326--echo # 1327--echo # Test column families 1328--echo # 1329 1330create table t1 ( 1331 pk int primary key, 1332 col1 int, 1333 col2 int, 1334 key(col1) comment 'cf3', 1335 key(col2) comment 'cf4' 1336) engine=rocksdb; 1337 1338insert into t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5); 1339 1340--replace_column 10 # 1341explain 1342select * from t1 where col1=2; 1343select * from t1 where col1=2; 1344 1345--replace_column 10 # 1346explain 1347select * from t1 where col2=3; 1348select * from t1 where col2=3; 1349 1350select * from t1 where pk=4; 1351 1352drop table t1; 1353 1354--echo # 1355--echo # Try primary key in a non-default CF: 1356--echo # 1357create table t1 ( 1358 pk int, 1359 col1 int, 1360 col2 int, 1361 key(col1) comment 'cf3', 1362 key(col2) comment 'cf4', 1363 primary key (pk) comment 'cf5' 1364) engine=rocksdb; 1365insert into t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5); 1366 1367--replace_column 10 # 1368explain 1369select * from t1 where col1=2; 1370select * from t1 where col1=2; 1371 1372select * from t1 where pk=4; 1373 1374drop table t1; 1375 1376--echo # 1377--echo # Issue #15: SIGSEGV from reading in blob data 1378--echo # 1379CREATE TABLE t1 ( 1380 id int not null, 1381 blob_col text, 1382 PRIMARY KEY (id) 1383) ENGINE=ROCKSDB CHARSET=latin1; 1384 1385INSERT INTO t1 SET id=123, blob_col=repeat('z',64000) ON DUPLICATE KEY UPDATE blob_col=VALUES(blob_col); 1386INSERT INTO t1 SET id=123, blob_col='' ON DUPLICATE KEY UPDATE blob_col=VALUES(blob_col); 1387DROP TABLE t1; 1388 1389 1390--echo # 1391--echo # Issue #17: Automatic per-index column families 1392--echo # (Now deprecated) 1393--echo # 1394--error ER_PER_INDEX_CF_DEPRECATED 1395create table t1 ( 1396 id int not null, 1397 key1 int, 1398 PRIMARY KEY (id), 1399 index (key1) comment '$per_index_cf' 1400) engine=rocksdb; 1401 1402 1403--echo # 1404--echo # Issue #22: SELECT ... FOR UPDATE takes a long time 1405--echo # 1406create table t0 (a int) engine=myisam; 1407insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1408 1409create table t1 ( 1410 id1 int, 1411 id2 int, 1412 value1 int, 1413 value2 int, 1414 primary key(id1, id2) COMMENT 'new_column_family', 1415 key(id2) 1416) engine=rocksdb default charset=latin1 collate=latin1_bin; 1417 1418insert into t1 select A.a, B.a, 31, 1234 from t0 A, t0 B; 1419 1420--replace_column 10 # 1421explain 1422select * from t1 where id1=30 and value1=30 for update; 1423 1424set @var1=(select variable_value 1425 from performance_schema.global_status 1426 where variable_name='rocksdb_number_keys_read'); 1427 1428select * from t1 where id1=3 and value1=3 for update; 1429 1430set @var2=(select variable_value 1431 from performance_schema.global_status 1432 where variable_name='rocksdb_number_keys_read'); 1433--echo # The following must return true (before the fix, the difference was 70): 1434select if((@var2 - @var1) < 30, 1, @var2-@var1); 1435 1436drop table t0,t1; 1437 1438--echo # 1439--echo # Issue #33: SELECT ... FROM rocksdb_table ORDER BY primary_key uses sorting 1440--echo # 1441create table t1 (id int primary key, value int) engine=rocksdb; 1442insert into t1 values (1,1),(2,2),(3,3); 1443--echo # The following must not use 'Using filesort': 1444--replace_column 10 # 1445explain select * from t1 ORDER BY id; 1446drop table t1; 1447 1448--echo # 1449--echo # Issue #26: Index-only scans for DATETIME and TIMESTAMP 1450--echo # 1451create table t0 (a int) engine=myisam; 1452insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1453 1454--echo # Try a DATETIME column: 1455create table t1 ( 1456 pk int auto_increment primary key, 1457 kp1 datetime, 1458 kp2 int, 1459 col1 int, 1460 key(kp1, kp2) 1461) engine=rocksdb; 1462insert into t1 (kp1,kp2) 1463select date_add('2015-01-01 12:34:56', interval a day), a from t0; 1464 1465select * from t1; 1466 1467--echo # This must show 'Using index' 1468--replace_column 10 # 1469explain 1470select kp1,kp2 from t1 force index (kp1) 1471where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; 1472 1473select kp1,kp2 from t1 force index (kp1) 1474where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; 1475 1476--echo # Now, the same with NOT NULL column 1477create table t2 ( 1478 pk int auto_increment primary key, 1479 kp1 datetime not null, 1480 kp2 int, 1481 col1 int, 1482 key(kp1, kp2) 1483) engine=rocksdb; 1484# MyRocks does not support gap locks in REPEATABLE-READ mode, this part of the 1485# test does not require RR ISO to complete, so lets temporarily alter the ISO 1486# to RC 1487set @orig_tx_iso=@@session.transaction_isolation; 1488set session transaction_isolation='READ-COMMITTED'; 1489insert into t2 select * from t1; 1490set session transaction_isolation=@orig_tx_iso; 1491--echo # This must show 'Using index' 1492--replace_column 10 # 1493explain 1494select kp1,kp2 from t2 force index (kp1) 1495where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; 1496 1497select kp1,kp2 from t2 force index (kp1) 1498where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; 1499drop table t1,t2; 1500 1501--echo # Try a DATE column: 1502create table t1 ( 1503 pk int auto_increment primary key, 1504 kp1 date, 1505 kp2 int, 1506 col1 int, 1507 key(kp1, kp2) 1508) engine=rocksdb; 1509insert into t1 (kp1,kp2) 1510select date_add('2015-01-01', interval a day), a from t0; 1511 1512select * from t1; 1513 1514--echo # This must show 'Using index' 1515--replace_column 10 # 1516explain 1517select kp1,kp2 from t1 force index (kp1) 1518where kp1 between '2015-01-01' and '2015-01-05'; 1519 1520select kp1,kp2 from t1 force index (kp1) 1521where kp1 between '2015-01-01' and '2015-01-05'; 1522 1523--echo # Now, the same with NOT NULL column 1524create table t2 ( 1525 pk int auto_increment primary key, 1526 kp1 date not null, 1527 kp2 int, 1528 col1 int, 1529 key(kp1, kp2) 1530) engine=rocksdb; 1531# MyRocks does not support gap locks in REPEATABLE-READ mode, this part of the 1532# test does not require RR ISO to complete, so lets temporarily alter the ISO 1533# to RC 1534set @orig_tx_iso=@@session.transaction_isolation; 1535set session transaction_isolation='READ-COMMITTED'; 1536insert into t2 select * from t1; 1537set session transaction_isolation=@orig_tx_iso; 1538--echo # This must show 'Using index' 1539--replace_column 10 # 1540explain 1541select kp1,kp2 from t2 force index (kp1) 1542where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; 1543 1544select kp1,kp2 from t2 force index (kp1) 1545where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; 1546drop table t1,t2; 1547 1548--echo # 1549--echo # Try a TIMESTAMP column: 1550--echo # 1551create table t1 ( 1552 pk int auto_increment primary key, 1553 kp1 timestamp, 1554 kp2 int, 1555 col1 int, 1556 key(kp1, kp2) 1557) engine=rocksdb; 1558insert into t1 (kp1,kp2) 1559select date_add('2015-01-01 12:34:56', interval a day), a from t0; 1560 1561select * from t1; 1562 1563--echo # This must show 'Using index' 1564--replace_column 10 # 1565explain 1566select kp1,kp2 from t1 force index (kp1) 1567where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; 1568 1569select kp1,kp2 from t1 force index (kp1) 1570where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; 1571 1572--echo # Now, the same with NOT NULL column 1573create table t2 ( 1574 pk int auto_increment primary key, 1575 kp1 timestamp not null, 1576 kp2 int, 1577 col1 int, 1578 key(kp1, kp2) 1579) engine=rocksdb; 1580# MyRocks does not support gap locks in REPEATABLE-READ mode, this part of the 1581# test does not require RR ISO to complete, so lets temporarily alter the ISO 1582# to RC 1583set @orig_tx_iso=@@session.transaction_isolation; 1584set session transaction_isolation='READ-COMMITTED'; 1585insert into t2 select * from t1; 1586set session transaction_isolation=@orig_tx_iso; 1587--echo # This must show 'Using index' 1588--replace_column 10 # 1589explain 1590select kp1,kp2 from t2 force index (kp1) 1591where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; 1592 1593select kp1,kp2 from t2 force index (kp1) 1594where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; 1595drop table t1,t2; 1596 1597--echo # 1598--echo # Try a TIME column: 1599--echo # 1600create table t1 ( 1601 pk int auto_increment primary key, 1602 kp1 time, 1603 kp2 int, 1604 col1 int, 1605 key(kp1, kp2) 1606) engine=rocksdb; 1607--disable_warnings 1608insert into t1 (kp1,kp2) 1609select date_add('2015-01-01 09:00:00', interval a minute), a from t0; 1610--enable_warnings 1611 1612select * from t1; 1613 1614--echo # This must show 'Using index' 1615--replace_column 10 # 1616explain 1617select kp1,kp2 from t1 force index (kp1) 1618where kp1 between '09:01:00' and '09:05:00'; 1619 1620select kp1,kp2 from t1 force index (kp1) 1621where kp1 between '09:01:00' and '09:05:00'; 1622 1623--echo # Now, the same with NOT NULL column 1624create table t2 ( 1625 pk int auto_increment primary key, 1626 kp1 time not null, 1627 kp2 int, 1628 col1 int, 1629 key(kp1, kp2) 1630) engine=rocksdb; 1631# MyRocks does not support gap locks in REPEATABLE-READ mode, this part of the 1632# test does not require RR ISO to complete, so lets temporarily alter the ISO 1633# to RC 1634set @orig_tx_iso=@@session.transaction_isolation; 1635set session transaction_isolation='READ-COMMITTED'; 1636insert into t2 select * from t1; 1637set session transaction_isolation=@orig_tx_iso; 1638--echo # This must show 'Using index' 1639--replace_column 10 # 1640explain 1641select kp1,kp2 from t2 force index (kp1) 1642where kp1 between '09:01:00' and '09:05:00'; 1643 1644select kp1,kp2 from t2 force index (kp1) 1645where kp1 between '09:01:00' and '09:05:00'; 1646drop table t1,t2; 1647 1648--echo # 1649--echo # Try a YEAR column: 1650--echo # 1651create table t1 ( 1652 pk int auto_increment primary key, 1653 kp1 year, 1654 kp2 int, 1655 col1 int, 1656 key(kp1, kp2) 1657) engine=rocksdb; 1658--disable_warnings 1659insert into t1 (kp1,kp2) select 2015+a, a from t0; 1660--enable_warnings 1661 1662select * from t1; 1663 1664--echo # This must show 'Using index' 1665--replace_column 10 # 1666explain 1667select kp1,kp2 from t1 force index (kp1) 1668where kp1 between '2016' and '2020'; 1669 1670select kp1,kp2 from t1 force index (kp1) 1671where kp1 between '2016' and '2020'; 1672 1673--echo # Now, the same with NOT NULL column 1674create table t2 ( 1675 pk int auto_increment primary key, 1676 kp1 year not null, 1677 kp2 int, 1678 col1 int, 1679 key(kp1, kp2) 1680) engine=rocksdb; 1681# MyRocks does not support gap locks in REPEATABLE-READ mode, this part of the 1682# test does not require RR ISO to complete, so lets temporarily alter the ISO 1683# to RC 1684set @orig_tx_iso=@@session.transaction_isolation; 1685set session transaction_isolation='READ-COMMITTED'; 1686insert into t2 select * from t1; 1687set session transaction_isolation=@orig_tx_iso; 1688--echo # This must show 'Using index' 1689--replace_column 10 # 1690explain 1691select kp1,kp2 from t2 force index (kp1) 1692where kp1 between '2016' and '2020'; 1693 1694select kp1,kp2 from t2 force index (kp1) 1695where kp1 between '2016' and '2020'; 1696 1697drop table t1,t2; 1698 1699--echo # 1700--echo # Issue #57: Release row locks on statement errors 1701--echo # 1702create table t1 (id int primary key) engine=rocksdb; 1703insert into t1 values (1), (2), (3); 1704begin; 1705insert into t1 values (4), (5), (6); 1706--error ER_DUP_ENTRY 1707insert into t1 values (7), (8), (2), (9); 1708select * from t1; 1709 1710-- connect(con1,localhost,root,,) 1711--connection con1 1712begin; 1713--error ER_LOCK_WAIT_TIMEOUT 1714select * from t1 where id=4 for update; 1715 1716select * from t1 where id=7 for update; 1717 1718select * from t1 where id=9 for update; 1719 1720--connection default 1721-- disconnect con1 1722drop table t1; 1723 1724--echo #Index on blob column 1725SET @old_mode = @@sql_mode; 1726SET sql_mode = 'strict_all_tables'; 1727create table t1 (a int, b text, c varchar(400), Primary Key(a), Key(c, b(255))) engine=rocksdb; 1728drop table t1; 1729set @orig_rocksdb_large_prefix=@@global.rocksdb_large_prefix; 1730set @@global.rocksdb_large_prefix=1; 1731create table t1 (a int, b text, c varchar(400), Primary Key(a), Key(b(1255))) engine=rocksdb; 1732set @@global.rocksdb_large_prefix=@orig_rocksdb_large_prefix; 1733insert into t1 values (1, '1abcde', '1abcde'), (2, '2abcde', '2abcde'), (3, '3abcde', '3abcde'); 1734select * from t1; 1735--replace_column 10 # 1736explain select * from t1 where b like '1%'; 1737--replace_column 10 # 1738explain select b, a from t1 where b like '1%'; 1739update t1 set b= '12345' where b = '2abcde'; 1740select * from t1; 1741drop table t1; 1742--error ER_TOO_LONG_KEY 1743create table t1 (a int, b text, c varchar(400), Primary Key(a), Key(b(2255))) engine=rocksdb; 1744SET sql_mode = @old_mode; 1745 1746drop table t0; 1747 1748--echo # 1749--echo # Fix assertion failure (attempt to overrun the key buffer) for prefix indexes 1750--echo # 1751 1752create table t1 ( 1753 pk int primary key, 1754 col1 varchar(100), 1755 key (col1(10)) 1756) engine=rocksdb; 1757 1758insert into t1 values (1, repeat('0123456789', 9)); 1759 1760drop table t1; 1761 1762--echo # 1763--echo # Issue #76: Assertion `buf == table->record[0]' fails in virtual int ha_rocksdb::delete_row(const uchar*) 1764--echo # 1765 1766CREATE TABLE t1 (pk INT PRIMARY KEY, f1 INT) ENGINE=RocksDB; 1767CREATE TABLE t2 (pk INT PRIMARY KEY, f1 INT) ENGINE=RocksDB; 1768 1769CREATE TRIGGER tr AFTER DELETE ON t1 FOR EACH ROW DELETE FROM t2 WHERE pk = old.pk; 1770 1771INSERT INTO t1 VALUES (1,1); 1772REPLACE INTO t1 VALUES (1,2); 1773 1774SELECT * FROM t1; 1775DROP TABLE t1, t2; 1776 1777--echo # 1778--echo # Issue #99: UPDATE for table with VARCHAR pk gives "Can't find record" error 1779--echo # 1780create table t1(a int primary key) engine=rocksdb; 1781insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1782 1783create table t2 ( 1784 a varchar(32) primary key, 1785 col1 int 1786) engine=rocksdb; 1787 1788# MyRocks does not support gap locks in REPEATABLE-READ mode, this part of the 1789# test does not require RR ISO to complete, so lets temporarily alter the ISO 1790# to RC 1791set @orig_tx_iso=@@session.transaction_isolation; 1792set session transaction_isolation='READ-COMMITTED'; 1793insert into t2 1794select concat('v-', 100 + A.a*100 + B.a), 12345 from t1 A, t1 B; 1795update t2 set a=concat('x-', a) where a between 'v-1002' and 'v-1004'; 1796set session transaction_isolation=@orig_tx_iso; 1797 1798drop table t1,t2; 1799 1800--echo # 1801--echo # Issue #131: Assertion `v->cfd_->internal_comparator().Compare(start, end) <= 0' failed 1802--echo # 1803CREATE TABLE t2(c1 INTEGER UNSIGNED NOT NULL, c2 INTEGER NULL, c3 TINYINT, c4 SMALLINT , c5 MEDIUMINT, c6 INT, c7 BIGINT, PRIMARY KEY(c1,c6)) ENGINE=RocksDB; 1804INSERT INTO t2 VALUES (1,1,1,1,1,1,1); 1805SELECT * FROM t2 WHERE c1 > 4294967295 ORDER BY c1,c6; 1806EXPLAIN SELECT * FROM t2 WHERE c1 > 4294967295 ORDER BY c1,c6; 1807drop table t2; 1808 1809--echo # 1810--echo # Issue #135: register transaction was not being called for statement 1811--echo # 1812--disable_warnings 1813--enable_warnings 1814CREATE DATABASE test_db; 1815CREATE TABLE test_db.t1(c1 INT PRIMARY KEY) ENGINE=ROCKSDB; 1816INSERT INTO test_db.t1(c1) VALUES(0), (1), (2), (3); 1817LOCK TABLES test_db.t1 READ; 1818SET AUTOCOMMIT=0; 1819SELECT c1 FROM test_db.t1 WHERE c1=2; 1820START TRANSACTION WITH CONSISTENT SNAPSHOT, READ ONLY; 1821DROP DATABASE test_db; 1822COMMIT; 1823SET AUTOCOMMIT=1; 1824 1825--echo # 1826--echo # Issue #143: Split rocksdb_bulk_load option into two 1827--echo # 1828CREATE TABLE t1 (id int primary key, value int) engine=RocksDB; 1829SET unique_checks=0; 1830INSERT INTO t1 VALUES(1, 1); 1831INSERT INTO t1 VALUES(1, 2); 1832INSERT INTO t1 VALUES(1, 3); 1833SELECT * FROM t1; 1834--error ER_ON_DUPLICATE_DISABLED 1835REPLACE INTO t1 VALUES(4, 4); 1836--error ER_ON_DUPLICATE_DISABLED 1837INSERT INTO t1 VALUES(5, 5) ON DUPLICATE KEY UPDATE value=value+1; 1838TRUNCATE TABLE t1; 1839SET @save_rocksdb_bulk_load_size= @@rocksdb_bulk_load_size; 1840SET unique_checks=1; 1841SET rocksdb_commit_in_the_middle=1; 1842SET rocksdb_bulk_load_size=10; 1843BEGIN; 1844INSERT INTO t1 (id) VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10), 1845 (11),(12),(13),(14),(15),(16),(17),(18),(19); 1846ROLLBACK; 1847SELECT * FROM t1; 1848INSERT INTO t1 (id) VALUES (11),(12),(13),(14),(15); 1849# MyRocks does not support gap locks in REPEATABLE-READ mode, this part of the 1850# test does not require RR ISO to complete, so lets temporarily alter the ISO 1851# to RC 1852set @orig_tx_iso=@@session.transaction_isolation; 1853set session transaction_isolation='READ-COMMITTED'; 1854BEGIN; 1855 1856UPDATE t1 SET value=100; 1857ROLLBACK; 1858SELECT * FROM t1; 1859BEGIN; 1860DELETE FROM t1; 1861ROLLBACK; 1862SELECT * FROM t1; 1863set session transaction_isolation=@orig_tx_iso; 1864SET rocksdb_commit_in_the_middle=0; 1865SET rocksdb_bulk_load_size= @save_rocksdb_bulk_load_size; 1866DROP TABLE t1; 1867 1868--echo # 1869--echo # Issue #185 Assertion `BaseValid()' failed in void rocksdb::BaseDeltaIterator::Advance() 1870--echo # 1871CREATE TABLE t2(id INT NOT NULL PRIMARY KEY, data INT) Engine=MEMORY; 1872INSERT INTO t2 VALUES (100,NULL),(150,"long varchar"),(200,"varchar"),(250,"long long long varchar"); 1873CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a,b)) ENGINE=ROCKSDB; 1874INSERT INTO t1 VALUES (1,1); 1875SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1 WHERE a > 4)); 1876DROP TABLE t1, t2; 1877 1878--echo # 1879--echo # Issue #189 ha_rocksdb::load_auto_incr_value() creates implicit snapshot and doesn't release 1880--echo # 1881--connect (con1,localhost,root,,) 1882create table r1 (id int auto_increment primary key, value int) engine=rocksdb; 1883insert into r1 (id) values (null), (null), (null), (null), (null); 1884connection con1; 1885create table r2 like r1; 1886show create table r2; 1887connection default; 1888begin; 1889insert into r1 values (10, 1); 1890commit; 1891connection con1; 1892begin; 1893select * from r1; 1894commit; 1895connection default; 1896drop table r1, r2; 1897 1898# hidden primary key 1899create table r1 (id int auto_increment, value int, index i(id)) engine=rocksdb; 1900insert into r1 (id) values (null), (null), (null), (null), (null); 1901connection con1; 1902create table r2 like r1; 1903show create table r2; 1904connection default; 1905begin; 1906insert into r1 values (10, 1); 1907commit; 1908connection con1; 1909begin; 1910select * from r1; 1911commit; 1912connection default; 1913drop table r1, r2; 1914 1915disconnect con1; 1916 1917--echo # 1918--echo # Issue#211 Crash on LOCK TABLES + START TRANSACTION WITH CONSISTENT SNAPSHOT 1919--echo # 1920CREATE TABLE t1(c1 INT) ENGINE=ROCKSDB; 1921lock TABLE t1 read local; 1922SELECT 1 FROM t1 GROUP BY TRIM(LEADING RAND()FROM''); 1923set AUTOCOMMIT=0; 1924start transaction with consistent snapshot; 1925SELECT * FROM t1; 1926COMMIT; 1927UNLOCK TABLES; 1928DROP TABLE t1; 1929set AUTOCOMMIT=1; 1930 1931--echo # 1932--echo # Issue#213 Crash on LOCK TABLES + partitions 1933--echo # 1934CREATE TABLE t1(a INT,b INT,KEY (b)) engine=rocksdb PARTITION BY HASH(a) PARTITIONS 2; 1935# 5.7 uses strict mode by default, so this will now return an error 1936--error ER_WARN_DATA_OUT_OF_RANGE 1937INSERT INTO t1(a)VALUES (20010101101010.999949); 1938# now test with the 5.6 behavior just to be sure 1939set @orig_sql_mode=@@session.sql_mode; 1940set session sql_mode=""; 1941INSERT INTO t1(a)VALUES (20010101101010.999949); 1942set session sql_mode=@orig_sql_mode; 1943lock tables t1 write,t1 as t0 write,t1 as t2 write; 1944# MyRocks does not support gap locks in REPEATABLE-READ mode, this part of the 1945# test does not require RR ISO to complete, so lets temporarily alter the ISO 1946# to RC 1947set @orig_tx_iso=@@session.transaction_isolation; 1948set session transaction_isolation='READ-COMMITTED'; 1949SELECT a FROM t1 ORDER BY a; 1950set session transaction_isolation=@orig_tx_iso; 1951truncate t1; 1952# 5.7 uses strict mode by default, so this will now return an error 1953--error ER_WARN_DATA_OUT_OF_RANGE 1954INSERT INTO t1 VALUES(X'042000200020',X'042000200020'),(X'200400200020',X'200400200020'); 1955# now test with the 5.6 behavior just to be sure 1956set @orig_sql_mode=@@session.sql_mode; 1957set session sql_mode=""; 1958INSERT INTO t1 VALUES(X'042000200020',X'042000200020'),(X'200400200020',X'200400200020'); 1959set session sql_mode=@orig_sql_mode; 1960UNLOCK TABLES; 1961DROP TABLE t1; 1962 1963--echo # 1964--echo # Issue#250: MyRocks/Innodb different output from query with order by on table with index and decimal type 1965--echo # (the test was changed to use VARCHAR, because DECIMAL now supports index-only, and this issue 1966--echo # needs a datype that doesn't support index-inly) 1967--echo # 1968 1969CREATE TABLE t1( 1970 c1 varchar(10) character set utf8 collate utf8_general_ci NOT NULL, 1971 c2 varchar(10) character set utf8 collate utf8_general_ci, 1972 c3 INT, 1973 INDEX idx(c1,c2) 1974) ENGINE=ROCKSDB; 1975INSERT INTO t1 VALUES ('c1-val1','c2-val1',5); 1976INSERT INTO t1 VALUES ('c1-val2','c2-val3',6); 1977INSERT INTO t1 VALUES ('c1-val3','c2-val3',7); 1978SELECT * FROM t1 force index(idx) WHERE c1 <> 'c1-val2' ORDER BY c1 DESC; 1979--replace_column 10 # 1980explain SELECT * FROM t1 force index(idx) WHERE c1 <> '1' ORDER BY c1 DESC; 1981drop table t1; 1982 1983--echo # 1984--echo # Issue#267: MyRocks issue with no matching min/max row and count(*) 1985--echo # 1986CREATE TABLE t1(c1 INT UNSIGNED, c2 INT SIGNED, INDEX idx2(c2)) ENGINE=ROCKSDB; 1987INSERT INTO t1 VALUES(1,null); 1988INSERT INTO t1 VALUES(2,null); 1989SELECT count(*) as total_rows, min(c2) as min_value FROM t1; 1990DROP TABLE t1; 1991 1992--echo # 1993--echo # Issue#263: MyRocks auto_increment skips values if you insert a negative value 1994--echo # 1995# We have slightly different behavior regarding auto-increment values than 1996# InnoDB, so the results of the SHOW TABLE STATUS command will be slightly 1997# different. InnoDB will reserve 3 values but only use 2 of them (because 1998# the user hard-coded a -1 as the second value). MyRocks will only reserve 1999# the values as needed, so only 2 values will be used. This means that the 2000# SHOW TABLE STATUS in InnoDB will indicate that the next auto-increment 2001# value is 4 while MyRocks will show it as 3. 2002CREATE TABLE t1(a INT AUTO_INCREMENT KEY) ENGINE=ROCKSDB; 2003INSERT INTO t1 VALUES(0),(-1),(0); 2004--replace_column 6 # 7 # 8 # 9 # 10 # 12 # 13 # 2005SHOW TABLE STATUS LIKE 't1'; 2006SELECT * FROM t1; 2007DROP TABLE t1; 2008CREATE TABLE t1(a INT AUTO_INCREMENT KEY) ENGINE=ROCKSDB; 2009INSERT INTO t1 VALUES(0),(10),(0); 2010--replace_column 6 # 7 # 8 # 9 # 10 # 12 # 13 # 2011SHOW TABLE STATUS LIKE 't1'; 2012SELECT * FROM t1; 2013DROP TABLE t1; 2014 2015--echo # 2016--echo # Issue #411: Setting rocksdb_commit_in_the_middle commits transaction 2017--echo # without releasing iterator 2018--echo # 2019 2020CREATE TABLE t1 (id1 bigint(20), 2021 id2 bigint(20), 2022 id3 bigint(20), 2023 PRIMARY KEY (id1, id2, id3)) 2024 ENGINE=ROCKSDB 2025 DEFAULT CHARSET=latin1; 2026 2027CREATE TABLE t2 (id1 bigint(20), 2028 id2 bigint(20), 2029 PRIMARY KEY (id1, id2)) 2030 ENGINE=ROCKSDB 2031 DEFAULT CHARSET=latin1; 2032 2033 2034set rocksdb_commit_in_the_middle=1; 2035SET @save_rocksdb_bulk_load_size= @@rocksdb_bulk_load_size; 2036set rocksdb_bulk_load_size = 100; 2037 2038--disable_query_log 2039let $j = 10000; 2040while ($j) 2041{ 2042 --eval insert into t1 (id1, id2, id3) values (0, $j, 0); 2043 --eval insert into t2 (id1, id2) values (0, $j); 2044 dec $j; 2045} 2046--enable_query_log 2047 2048# MyRocks does not support gap locks in REPEATABLE-READ mode, this part of the 2049# test does not require RR ISO to complete, so lets temporarily alter the ISO 2050# to RC 2051set @orig_tx_iso=@@session.transaction_isolation; 2052set session transaction_isolation='READ-COMMITTED'; 2053DELETE t2, t1 FROM t2 LEFT JOIN t1 ON t2.id2 = t1.id2 AND t2.id1 = t1.id1 WHERE t2.id1 = 0; 2054set session transaction_isolation=@orig_tx_iso; 2055 2056SET rocksdb_bulk_load_size= @save_rocksdb_bulk_load_size; 2057SET rocksdb_commit_in_the_middle=0; 2058DROP TABLE t1, t2; 2059 2060 2061SET GLOBAL ROCKSDB_PAUSE_BACKGROUND_WORK = @ORIG_PAUSE_BACKGROUND_WORK; 2062 2063--echo # 2064--echo # Issue #728: Assertion `covers_key(b)' failed in int 2065--echo # myrocks::Rdb_key_def::cmp_full_keys(const rocks db::Slice&, 2066--echo # const rocksdb::Slice&) 2067--echo # 2068 2069CREATE TABLE t2(c1 TINYINT SIGNED KEY,c2 TINYINT UNSIGNED,c3 INT) ENGINE=ROCKSDB; 2070INSERT INTO t2(c1)VALUES(0); 2071SELECT * FROM t2 WHERE c1<=127 ORDER BY c1 DESC; 2072DROP TABLE t2; 2073 2074--source include/wait_until_count_sessions.inc 2075