1# Test with AUTO_INCREMENT 2CREATE TABLE tp 3(a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 4b varchar(24)) 5ENGINE = 'Memory' 6PARTITION BY HASH (a) PARTITIONS 4; 7Warnings: 8Warning 1287 The partition engine, used by table 'test.tp', is deprecated and will be removed in a future release. Please use native partitioning instead. 9CREATE TABLE t LIKE tp; 10Warnings: 11Warning 1287 The partition engine, used by table 'test.tp', is deprecated and will be removed in a future release. Please use native partitioning instead. 12Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 13ALTER TABLE t REMOVE PARTITIONING; 14SHOW CREATE TABLE tp; 15Table Create Table 16tp CREATE TABLE `tp` ( 17 `a` int(11) NOT NULL AUTO_INCREMENT, 18 `b` varchar(24) DEFAULT NULL, 19 PRIMARY KEY (`a`) 20) ENGINE=MEMORY DEFAULT CHARSET=latin1 21/*!50100 PARTITION BY HASH (a) 22PARTITIONS 4 */ 23Warnings: 24Warning 1287 The partition engine, used by table 'test.tp', is deprecated and will be removed in a future release. Please use native partitioning instead. 25SHOW CREATE TABLE t; 26Table Create Table 27t CREATE TABLE `t` ( 28 `a` int(11) NOT NULL AUTO_INCREMENT, 29 `b` varchar(24) DEFAULT NULL, 30 PRIMARY KEY (`a`) 31) ENGINE=MEMORY DEFAULT CHARSET=latin1 32INSERT INTO tp (b) VALUES ("One"), ("Two"), ("Three"), ("Four"), ("Five"), 33("Six"), ("Seven"), ("Eight"), ("Nine"), ("Ten"), ("Eleven"), ("Twelwe"); 34INSERT INTO tp VALUES (97, "Ninety seven"); 35INSERT INTO tp VALUES (111, "One hundred eleven"); 36INSERT INTO tp VALUES (101, "One hundred one"); 37SET INSERT_ID = 13; 38INSERT INTO t (b) VALUES ("Thirteen"); 39SET INSERT_ID = 21; 40INSERT INTO t (b) VALUES ("Twenty one"); 41SET INSERT_ID = 25; 42INSERT INTO t (b) VALUES ("Twenty five"); 43SET INSERT_ID = 55; 44INSERT INTO t (b) VALUES ("Fifty five"); 45DELETE FROM tp WHERE a = 111; 46DELETE FROM t WHERE a = 55; 47UPDATE tp SET a = 41 WHERE a = 101; 48UPDATE t SET a = 17 WHERE a = 25; 49SELECT PARTITION_NAME, IF(TABLE_ROWS, 'YES', 'NO') AS HAVE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA ='test' AND TABLE_NAME = 'tp'; 50PARTITION_NAME HAVE_ROWS 51p0 YES 52p1 YES 53p2 YES 54p3 YES 55SELECT IF(TABLE_ROWS, 'YES', 'NO') AS HAVE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='test' AND TABLE_NAME = 't'; 56HAVE_ROWS 57YES 58ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t; 59SELECT PARTITION_NAME, IF(TABLE_ROWS, 'YES', 'NO') AS HAVE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA ='test' AND TABLE_NAME = 'tp'; 60PARTITION_NAME HAVE_ROWS 61p0 YES 62p1 YES 63p2 YES 64p3 YES 65SELECT IF(TABLE_ROWS, 'YES', 'NO') AS HAVE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='test' AND TABLE_NAME = 't'; 66HAVE_ROWS 67YES 68SELECT * FROM tp ORDER BY a; 69a b 702 Two 713 Three 724 Four 736 Six 747 Seven 758 Eight 7610 Ten 7711 Eleven 7812 Twelwe 7913 Thirteen 8017 Twenty five 8121 Twenty one 82SELECT * FROM t ORDER BY a; 83a b 841 One 855 Five 869 Nine 8741 One hundred one 8897 Ninety seven 89SHOW CREATE TABLE tp; 90Table Create Table 91tp CREATE TABLE `tp` ( 92 `a` int(11) NOT NULL AUTO_INCREMENT, 93 `b` varchar(24) DEFAULT NULL, 94 PRIMARY KEY (`a`) 95) ENGINE=MEMORY AUTO_INCREMENT=112 DEFAULT CHARSET=latin1 96/*!50100 PARTITION BY HASH (a) 97PARTITIONS 4 */ 98Warnings: 99Warning 1287 The partition engine, used by table 'test.tp', is deprecated and will be removed in a future release. Please use native partitioning instead. 100SHOW CREATE TABLE t; 101Table Create Table 102t CREATE TABLE `t` ( 103 `a` int(11) NOT NULL AUTO_INCREMENT, 104 `b` varchar(24) DEFAULT NULL, 105 PRIMARY KEY (`a`) 106) ENGINE=MEMORY AUTO_INCREMENT=102 DEFAULT CHARSET=latin1 107DROP TABLE tp, t; 108CREATE TABLE t 109(a INT, 110b VARCHAR(55), 111PRIMARY KEY (a)) 112ENGINE = 'Memory'; 113CREATE TABLE tp 114(a INT, 115b VARCHAR(55), 116PRIMARY KEY (a)) 117ENGINE = 'Memory' 118PARTITION BY RANGE (a) 119(PARTITION p0 VALUES LESS THAN (100), 120PARTITION p1 VALUES LESS THAN MAXVALUE); 121Warnings: 122Warning 1287 The partition engine, used by table 'test.tp', is deprecated and will be removed in a future release. Please use native partitioning instead. 123CREATE TABLE tsp 124(a INT, 125b VARCHAR(55), 126PRIMARY KEY (a)) 127ENGINE = 'Memory' 128PARTITION BY RANGE (a) 129SUBPARTITION BY HASH(a) 130(PARTITION p0 VALUES LESS THAN (100) 131(SUBPARTITION sp0, 132SUBPARTITION sp1), 133PARTITION p1 VALUES LESS THAN MAXVALUE 134(SUBPARTITION sp2, 135SUBPARTITION sp3)); 136Warnings: 137Warning 1287 The partition engine, used by table 'test.tsp', is deprecated and will be removed in a future release. Please use native partitioning instead. 138INSERT INTO t VALUES (1, "First value"), (3, "Three"), (5, "Five"), (99, "End of values"); 139INSERT INTO tp VALUES (2, "First value"), (10, "Ten"), (50, "Fifty"), (200, "Two hundred, end of values"), (61, "Sixty one"), (62, "Sixty two"), (63, "Sixty three"), (64, "Sixty four"), (161, "161"), (162, "162"), (163, "163"), (164, "164"); 140Warnings: 141Warning 1287 The partition engine, used by table 'test.tp', is deprecated and will be removed in a future release. Please use native partitioning instead. 142INSERT INTO tsp VALUES (2, "First value"), (10, "Ten"), (50, "Fifty"), (200, "Two hundred, end of values"), (61, "Sixty one"), (62, "Sixty two"), (63, "Sixty three"), (64, "Sixty four"), (161, "161"), (162, "162"), (163, "163"), (164, "164"); 143Warnings: 144Warning 1287 The partition engine, used by table 'test.tsp', is deprecated and will be removed in a future release. Please use native partitioning instead. 145SELECT * FROM t; 146a b 1471 First value 1483 Three 1495 Five 15099 End of values 151SELECT * FROM tp; 152a b 15310 Ten 154161 161 155162 162 156163 163 157164 164 1582 First value 159200 Two hundred, end of values 16050 Fifty 16161 Sixty one 16262 Sixty two 16363 Sixty three 16464 Sixty four 165# Start by testing read/write locking 166SET AUTOCOMMIT = 1; 167# con1 168SET DEBUG_SYNC= 'swap_partition_after_compare_tables SIGNAL swap_in_progress WAIT_FOR goto_verification'; 169SET DEBUG_SYNC= 'swap_partition_first_row_read SIGNAL swap_in_progress WAIT_FOR goto_wait'; 170SET DEBUG_SYNC= 'swap_partition_after_wait SIGNAL swap_in_progress WAIT_FOR goto_rename'; 171SET DEBUG_SYNC= 'swap_partition_before_rename SIGNAL swap_in_progress WAIT_FOR test_done'; 172ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t; 173# con default 174SET DEBUG_SYNC= 'now WAIT_FOR swap_in_progress'; 175# select from t and select/update/delete/insert from tp should work 176SELECT * FROM t WHERE a = 99; 177a b 17899 End of values 179SELECT * FROM tp WHERE a = 61; 180a b 18161 Sixty one 182# any write (update/delete/insert) into t or tp should fail 183SET SESSION lock_wait_timeout=1; 184UPDATE tp SET a = 53, b = concat("Fifty three, was ", b) WHERE a = 63; 185ERROR HY000: Lock wait timeout exceeded; try restarting transaction 186INSERT INTO tp VALUES (63, "Sixty three, new"), (59, "To be deleted"); 187ERROR HY000: Lock wait timeout exceeded; try restarting transaction 188DELETE FROM tp WHERE a = 59; 189ERROR HY000: Lock wait timeout exceeded; try restarting transaction 190UPDATE t SET a = 53, b = "Fifty three, was three" WHERE a = 3; 191ERROR HY000: Lock wait timeout exceeded; try restarting transaction 192INSERT INTO t VALUES (63, "Sixty three, new"), (59, "To be deleted"); 193ERROR HY000: Lock wait timeout exceeded; try restarting transaction 194DELETE FROM t WHERE a = 3; 195ERROR HY000: Lock wait timeout exceeded; try restarting transaction 196ALTER TABLE t ENGINE = 'Memory'; 197ERROR HY000: Lock wait timeout exceeded; try restarting transaction 198ALTER TABLE tp ENGINE = 'Memory'; 199ERROR HY000: Lock wait timeout exceeded; try restarting transaction 200SHOW CREATE TABLE t; 201Table Create Table 202t CREATE TABLE `t` ( 203 `a` int(11) NOT NULL, 204 `b` varchar(55) DEFAULT NULL, 205 PRIMARY KEY (`a`) 206) ENGINE=MEMORY DEFAULT CHARSET=latin1 207SHOW CREATE TABLE tp; 208Table Create Table 209tp CREATE TABLE `tp` ( 210 `a` int(11) NOT NULL, 211 `b` varchar(55) DEFAULT NULL, 212 PRIMARY KEY (`a`) 213) ENGINE=MEMORY DEFAULT CHARSET=latin1 214/*!50100 PARTITION BY RANGE (a) 215(PARTITION p0 VALUES LESS THAN (100) ENGINE = MEMORY, 216 PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = MEMORY) */ 217Warnings: 218Warning 1287 The partition engine, used by table 'test.tp', is deprecated and will be removed in a future release. Please use native partitioning instead. 219SET DEBUG_SYNC= 'now SIGNAL goto_verification'; 220SET DEBUG_SYNC= 'now WAIT_FOR swap_in_progress'; 221# select from t and select/update/delete/insert from tp should work 222SELECT * FROM t WHERE a = 99; 223a b 22499 End of values 225SELECT * FROM tp WHERE a = 61; 226a b 22761 Sixty one 228UPDATE tp SET a = 43, b = concat("Fifty three, was ", b) WHERE a = 63; 229ERROR HY000: Lock wait timeout exceeded; try restarting transaction 230INSERT INTO tp VALUES (63, "Sixty three, new 2"), (59, "To be deleted"); 231ERROR HY000: Lock wait timeout exceeded; try restarting transaction 232DELETE FROM tp WHERE a = 59; 233ERROR HY000: Lock wait timeout exceeded; try restarting transaction 234# any write (update/delete/insert) into t should fail 235UPDATE t SET a = 53, b = "Fifty three, was three" WHERE a = 3; 236ERROR HY000: Lock wait timeout exceeded; try restarting transaction 237INSERT INTO t VALUES (63, "Sixty three, new"), (59, "To be deleted"); 238ERROR HY000: Lock wait timeout exceeded; try restarting transaction 239DELETE FROM t WHERE a = 3; 240ERROR HY000: Lock wait timeout exceeded; try restarting transaction 241ALTER TABLE t ENGINE = 'Memory'; 242ERROR HY000: Lock wait timeout exceeded; try restarting transaction 243ALTER TABLE tp ENGINE = 'Memory'; 244ERROR HY000: Lock wait timeout exceeded; try restarting transaction 245SHOW CREATE TABLE t; 246Table Create Table 247t CREATE TABLE `t` ( 248 `a` int(11) NOT NULL, 249 `b` varchar(55) DEFAULT NULL, 250 PRIMARY KEY (`a`) 251) ENGINE=MEMORY DEFAULT CHARSET=latin1 252SHOW CREATE TABLE tp; 253Table Create Table 254tp CREATE TABLE `tp` ( 255 `a` int(11) NOT NULL, 256 `b` varchar(55) DEFAULT NULL, 257 PRIMARY KEY (`a`) 258) ENGINE=MEMORY DEFAULT CHARSET=latin1 259/*!50100 PARTITION BY RANGE (a) 260(PARTITION p0 VALUES LESS THAN (100) ENGINE = MEMORY, 261 PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = MEMORY) */ 262Warnings: 263Warning 1287 The partition engine, used by table 'test.tp', is deprecated and will be removed in a future release. Please use native partitioning instead. 264SET DEBUG_SYNC= 'now SIGNAL goto_wait'; 265SET DEBUG_SYNC= 'now WAIT_FOR swap_in_progress'; 266# Both tables should now be under exclusive lock, even SHOW should fail 267SELECT * FROM t WHERE a = 99; 268ERROR HY000: Lock wait timeout exceeded; try restarting transaction 269SELECT * FROM tp WHERE a = 61; 270ERROR HY000: Lock wait timeout exceeded; try restarting transaction 271UPDATE tp SET a = 53, b = concat("Fifty three, was ", b) WHERE a = 63; 272ERROR HY000: Lock wait timeout exceeded; try restarting transaction 273INSERT INTO tp VALUES (63, "Sixty three, new 2"), (59, "To be deleted"); 274ERROR HY000: Lock wait timeout exceeded; try restarting transaction 275DELETE FROM tp WHERE a = 59; 276ERROR HY000: Lock wait timeout exceeded; try restarting transaction 277UPDATE t SET a = 53, b = "Fifty three, was three" WHERE a = 3; 278ERROR HY000: Lock wait timeout exceeded; try restarting transaction 279INSERT INTO t VALUES (63, "Sixty three, new"), (59, "To be deleted"); 280ERROR HY000: Lock wait timeout exceeded; try restarting transaction 281DELETE FROM t WHERE a = 3; 282ERROR HY000: Lock wait timeout exceeded; try restarting transaction 283SHOW CREATE TABLE t; 284ERROR HY000: Lock wait timeout exceeded; try restarting transaction 285SHOW CREATE TABLE tp; 286ERROR HY000: Lock wait timeout exceeded; try restarting transaction 287ALTER TABLE t ENGINE = 'Memory'; 288ERROR HY000: Lock wait timeout exceeded; try restarting transaction 289ALTER TABLE tp ENGINE = 'Memory'; 290ERROR HY000: Lock wait timeout exceeded; try restarting transaction 291SET DEBUG_SYNC= 'now SIGNAL goto_rename'; 292SET DEBUG_SYNC= 'now WAIT_FOR swap_in_progress'; 293# Both tables should now be under exclusive lock 294SELECT * FROM t WHERE a = 99; 295ERROR HY000: Lock wait timeout exceeded; try restarting transaction 296SELECT * FROM tp WHERE a = 61; 297ERROR HY000: Lock wait timeout exceeded; try restarting transaction 298UPDATE tp SET a = 53, b = concat("Fifty three, was ", b) WHERE a = 63; 299ERROR HY000: Lock wait timeout exceeded; try restarting transaction 300INSERT INTO tp VALUES (63, "Sixty three, new 2"), (59, "To be deleted"); 301ERROR HY000: Lock wait timeout exceeded; try restarting transaction 302DELETE FROM tp WHERE a = 59; 303ERROR HY000: Lock wait timeout exceeded; try restarting transaction 304UPDATE t SET a = 53, b = "Fifty three, was three" WHERE a = 3; 305ERROR HY000: Lock wait timeout exceeded; try restarting transaction 306INSERT INTO t VALUES (63, "Sixty three, new"), (59, "To be deleted"); 307ERROR HY000: Lock wait timeout exceeded; try restarting transaction 308DELETE FROM t WHERE a = 3; 309ERROR HY000: Lock wait timeout exceeded; try restarting transaction 310ALTER TABLE t ENGINE = 'Memory'; 311ERROR HY000: Lock wait timeout exceeded; try restarting transaction 312ALTER TABLE tp ENGINE = 'Memory'; 313ERROR HY000: Lock wait timeout exceeded; try restarting transaction 314SHOW CREATE TABLE t; 315ERROR HY000: Lock wait timeout exceeded; try restarting transaction 316SHOW CREATE TABLE tp; 317ERROR HY000: Lock wait timeout exceeded; try restarting transaction 318SET DEBUG_SYNC= 'now SIGNAL test_done'; 319# con1 320# con default 321# Tables should now be as normal 322SHOW CREATE TABLE t; 323Table Create Table 324t CREATE TABLE `t` ( 325 `a` int(11) NOT NULL, 326 `b` varchar(55) DEFAULT NULL, 327 PRIMARY KEY (`a`) 328) ENGINE=MEMORY DEFAULT CHARSET=latin1 329SHOW CREATE TABLE tp; 330Table Create Table 331tp CREATE TABLE `tp` ( 332 `a` int(11) NOT NULL, 333 `b` varchar(55) DEFAULT NULL, 334 PRIMARY KEY (`a`) 335) ENGINE=MEMORY DEFAULT CHARSET=latin1 336/*!50100 PARTITION BY RANGE (a) 337(PARTITION p0 VALUES LESS THAN (100) ENGINE = MEMORY, 338 PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = MEMORY) */ 339Warnings: 340Warning 1287 The partition engine, used by table 'test.tp', is deprecated and will be removed in a future release. Please use native partitioning instead. 341SELECT * FROM tp WHERE a = 99; 342a b 34399 End of values 344SELECT * FROM t WHERE a = 61; 345a b 34661 Sixty one 347UPDATE t SET a = 53, b = "Fifty three, was sixty three" WHERE a = 63; 348INSERT INTO t VALUES (63, "Sixty three, new"), (59, "To be deleted"); 349DELETE FROM t WHERE a = 59; 350UPDATE tp SET a = 53, b = "Fifty three, was three" WHERE a = 3; 351INSERT INTO tp VALUES (63, "Sixty three, new"), (59, "To be deleted"); 352DELETE FROM tp WHERE a = 3; 353ALTER TABLE t ENGINE = 'Memory'; 354ALTER TABLE tp ENGINE = 'Memory'; 355Warnings: 356Warning 1287 The partition engine, used by table 'test.tp', is deprecated and will be removed in a future release. Please use native partitioning instead. 357# con default 358SET DEBUG_SYNC= 'RESET'; 359SHOW CREATE TABLE t; 360Table Create Table 361t CREATE TABLE `t` ( 362 `a` int(11) NOT NULL, 363 `b` varchar(55) DEFAULT NULL, 364 PRIMARY KEY (`a`) 365) ENGINE=MEMORY DEFAULT CHARSET=latin1 366SHOW CREATE TABLE tp; 367Table Create Table 368tp CREATE TABLE `tp` ( 369 `a` int(11) NOT NULL, 370 `b` varchar(55) DEFAULT NULL, 371 PRIMARY KEY (`a`) 372) ENGINE=MEMORY DEFAULT CHARSET=latin1 373/*!50100 PARTITION BY RANGE (a) 374(PARTITION p0 VALUES LESS THAN (100) ENGINE = MEMORY, 375 PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = MEMORY) */ 376Warnings: 377Warning 1287 The partition engine, used by table 'test.tp', is deprecated and will be removed in a future release. Please use native partitioning instead. 378SELECT * FROM t; 379a b 38010 Ten 3812 First value 38250 Fifty 38353 Fifty three, was sixty three 38461 Sixty one 38562 Sixty two 38663 Sixty three, new 38764 Sixty four 388SELECT * FROM tp; 389a b 3901 First value 391161 161 392162 162 393163 163 394164 164 395200 Two hundred, end of values 3965 Five 39753 Fifty three, was three 39859 To be deleted 39963 Sixty three, new 40099 End of values 401DROP TABLE t, tp, tsp; 402