1--source include/have_innodb.inc 2--source include/have_partition.inc 3 4--disable_warnings 5DROP TABLE IF EXISTS t1, t2, t3, t, tp, tsp, tmp; 6--enable_warnings 7 8--echo # 9--echo # Bug#11894100: EXCHANGE PARTITION CAN'T BE EXECUTED IF 10--echo # ROW_FORMAT WAS SET EXPLICITLY 11--echo # 12 13--echo # Same definition (both have ROW_FORMAT set) 14CREATE TABLE t1 ( 15 id int(11) NOT NULL AUTO_INCREMENT, 16 year year(2) DEFAULT NULL, 17 modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 18 PRIMARY KEY (id) 19) ENGINE=InnoDB ROW_FORMAT=COMPACT 20PARTITION BY HASH (id) 21PARTITIONS 2; 22 23CREATE TABLE t2 LIKE t1; 24ALTER TABLE t2 REMOVE PARTITIONING; 25 26--vertical_results 27SHOW CREATE TABLE t1; 28SHOW CREATE TABLE t2; 29 30SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS 31FROM INFORMATION_SCHEMA.TABLES 32WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2') 33ORDER BY TABLE_NAME; 34 35ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t2; 36SHOW CREATE TABLE t1; 37SHOW CREATE TABLE t2; 38SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS 39FROM INFORMATION_SCHEMA.TABLES 40WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2') 41ORDER BY TABLE_NAME; 42 43DROP TABLE t2; 44 45--echo # Only the partitioned table have ROW_FORMAT set. 46CREATE TABLE t2 ( 47 id int(11) NOT NULL AUTO_INCREMENT, 48 year year(2) DEFAULT NULL, 49 modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 50 PRIMARY KEY (id) 51) ENGINE=InnoDB; 52 53ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t2; 54SHOW CREATE TABLE t1; 55SHOW CREATE TABLE t2; 56SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS 57FROM INFORMATION_SCHEMA.TABLES 58WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2') 59ORDER BY TABLE_NAME; 60 61--echo # Only the non partitioned table have ROW_FORMAT set. 62DROP TABLE t1, t2; 63CREATE TABLE t1 ( 64 id int(11) NOT NULL AUTO_INCREMENT, 65 year year(2) DEFAULT NULL, 66 modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 67 PRIMARY KEY (id) 68) ENGINE=InnoDB 69PARTITION BY HASH (id) 70PARTITIONS 2; 71CREATE TABLE t2 ( 72 id int(11) NOT NULL AUTO_INCREMENT, 73 year year(2) DEFAULT NULL, 74 modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 75 PRIMARY KEY (id) 76) ENGINE=InnoDB ROW_FORMAT = COMPACT; 77 78ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t2; 79SHOW CREATE TABLE t1; 80SHOW CREATE TABLE t2; 81SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS 82FROM INFORMATION_SCHEMA.TABLES 83WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2') 84ORDER BY TABLE_NAME; 85 86--echo # No table have ROW_FORMAT set. 87DROP TABLE t1, t2; 88CREATE TABLE t1 ( 89 id int(11) NOT NULL AUTO_INCREMENT, 90 year year(2) DEFAULT NULL, 91 modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 92 PRIMARY KEY (id) 93) ENGINE=InnoDB 94PARTITION BY HASH (id) 95PARTITIONS 2; 96CREATE TABLE t2 ( 97 id int(11) NOT NULL AUTO_INCREMENT, 98 year year(2) DEFAULT NULL, 99 modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 100 PRIMARY KEY (id) 101) ENGINE=InnoDB; 102 103ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t2; 104SHOW CREATE TABLE t1; 105SHOW CREATE TABLE t2; 106SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS 107FROM INFORMATION_SCHEMA.TABLES 108WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2') 109ORDER BY TABLE_NAME; 110 111--echo # Not same ROW_FORMAT as default (but same). 112DROP TABLE t1, t2; 113CREATE TABLE t1 ( 114 id int(11) NOT NULL AUTO_INCREMENT, 115 year year(2) DEFAULT NULL, 116 modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 117 PRIMARY KEY (id) 118) ENGINE=InnoDB ROW_FORMAT = REDUNDANT 119PARTITION BY HASH (id) 120PARTITIONS 2; 121CREATE TABLE t2 ( 122 id int(11) NOT NULL AUTO_INCREMENT, 123 year year(2) DEFAULT NULL, 124 modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 125 PRIMARY KEY (id) 126) ENGINE=InnoDB ROW_FORMAT = REDUNDANT; 127 128ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t2; 129SHOW CREATE TABLE t1; 130SHOW CREATE TABLE t2; 131SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS 132FROM INFORMATION_SCHEMA.TABLES 133WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2') 134ORDER BY TABLE_NAME; 135 136--echo # Not same ROW_FORMAT as default (tables differs). 137DROP TABLE t1, t2; 138CREATE TABLE t1 ( 139 id int(11) NOT NULL AUTO_INCREMENT, 140 year year(2) DEFAULT NULL, 141 modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 142 PRIMARY KEY (id) 143) ENGINE=InnoDB 144PARTITION BY HASH (id) 145PARTITIONS 2; 146CREATE TABLE t2 ( 147 id int(11) NOT NULL AUTO_INCREMENT, 148 year year(2) DEFAULT NULL, 149 modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 150 PRIMARY KEY (id) 151) ENGINE=InnoDB ROW_FORMAT = REDUNDANT; 152 153--error ER_PARTITION_EXCHANGE_DIFFERENT_OPTION 154ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t2; 155SHOW CREATE TABLE t1; 156SHOW CREATE TABLE t2; 157SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS 158FROM INFORMATION_SCHEMA.TABLES 159WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2') 160ORDER BY TABLE_NAME; 161 162--echo # Different than default (forced ROW_TYPE) 163DROP TABLE t1, t2; 164CREATE TABLE t1 ( 165 id int(11) NOT NULL AUTO_INCREMENT, 166 year year(2) DEFAULT NULL, 167 modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 168 PRIMARY KEY (id) 169) ENGINE=InnoDB ROW_FORMAT = COMPACT 170PARTITION BY HASH (id) 171PARTITIONS 2; 172CREATE TABLE t2 ( 173 id int(11) NOT NULL AUTO_INCREMENT, 174 year year(2) DEFAULT NULL, 175 modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 176 PRIMARY KEY (id) 177) ENGINE=InnoDB ROW_FORMAT = REDUNDANT; 178 179--error ER_PARTITION_EXCHANGE_DIFFERENT_OPTION 180ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t2; 181SHOW CREATE TABLE t1; 182SHOW CREATE TABLE t2; 183SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS 184FROM INFORMATION_SCHEMA.TABLES 185WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2') 186ORDER BY TABLE_NAME; 187--horizontal_results 188DROP TABLE t1, t2; 189 190--echo # 191--echo # Bug#56484: !table || (!table->read_set || 192--echo # bitmap_is_set(table->read_set, field_index)) 193--echo # 194CREATE TABLE t1 (a INT NOT NULL,b TIME NOT NULL DEFAULT '00:00:00') 195ENGINE=MyISAM 196PARTITION BY HASH (a) PARTITIONS 2; 197 198CREATE TABLE t2 (a INT) ENGINE=MYISAM; 199--error ER_TABLES_DIFFERENT_METADATA 200ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t2; 201DROP TABLE t1, t2; 202 203--echo # 204--echo # Bug#55784: Foreign key integrity broken by alter table 205--echo # 206CREATE TABLE t1 (s1 INT PRIMARY KEY) ENGINE=InnoDB; 207 208CREATE TABLE t2 (s1 INT, FOREIGN KEY (s1) REFERENCES t1 (s1)) ENGINE=InnoDB; 209 210INSERT INTO t1 VALUES (1),(2),(3); 211 212INSERT INTO t2 VALUES (1),(2),(3); 213 214CREATE TABLE t3 (s1 INT PRIMARY KEY) 215 ENGINE=InnoDB 216 PARTITION BY LIST (s1) 217 (PARTITION p1 VALUES IN (1,2,3)); 218 219--error ER_PARTITION_EXCHANGE_FOREIGN_KEY 220ALTER TABLE t3 EXCHANGE PARTITION p1 WITH TABLE t1; 221 222DROP TABLE t2, t1, t3; 223 224--echo # Tests for WL#4445 225CREATE TABLE t (a INT, 226 b VARCHAR(55), 227 PRIMARY KEY (a)) 228ENGINE = MyISAM; 229 230CREATE TABLE tp (a INT, 231 b VARCHAR(55), 232 PRIMARY KEY (a)) 233ENGINE = MyISAM 234PARTITION BY RANGE (a) 235(PARTITION p0 VALUES LESS THAN (100), 236 PARTITION p1 VALUES LESS THAN MAXVALUE); 237 238CREATE TABLE tsp (a INT, 239 b VARCHAR(55), 240 PRIMARY KEY (a)) 241ENGINE = MyISAM 242PARTITION BY RANGE (a) 243SUBPARTITION BY HASH(a) 244(PARTITION p0 VALUES LESS THAN (100) 245 (SUBPARTITION sp0, 246 SUBPARTITION sp1), 247 PARTITION p1 VALUES LESS THAN MAXVALUE 248 (SUBPARTITION sp2, 249 SUBPARTITION sp3)); 250 251INSERT INTO t VALUES (1, "First value"), (3, "Three"), (5, "Five"), (99, "End of values"); 252INSERT 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"); 253INSERT 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"); 254SHOW CREATE TABLE t; 255SHOW CREATE TABLE tp; 256--sorted_result 257SELECT * FROM t; 258--sorted_result 259SELECT * FROM tp; 260ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t; 261SHOW CREATE TABLE t; 262SHOW CREATE TABLE tp; 263--sorted_result 264SELECT * FROM t; 265--sorted_result 266SELECT * FROM tp; 267ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t; 268--sorted_result 269SELECT * FROM t; 270--sorted_result 271SELECT * FROM tp; 272--error ER_ROW_DOES_NOT_MATCH_PARTITION 273ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t; 274SHOW CREATE TABLE t; 275SHOW CREATE TABLE tp; 276--sorted_result 277SELECT * FROM t; 278--sorted_result 279SELECT * FROM tp; 280--echo # Test list of partitions 281--error ER_PARSE_ERROR 282ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t IGNORE; 283--error ER_PARSE_ERROR 284ALTER TABLE tp EXCHANGE PARTITION p0,p1 WITH TABLE t IGNORE; 285--error ER_PARSE_ERROR 286ALTER TABLE tp EXCHANGE PARTITION p0,p1 WITH TABLE t; 287--error ER_PARSE_ERROR 288ALTER TABLE tp EXCHANGE PARTITION (p0,p1) WITH TABLE t; 289--error ER_PARSE_ERROR 290ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE (t,t2); 291--error ER_PARSE_ERROR 292ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t,t2; 293--error ER_UNKNOWN_PARTITION 294ALTER TABLE tp EXCHANGE PARTITION non_existent WITH TABLE t; 295--error ER_PARTITION_INSTEAD_OF_SUBPARTITION 296ALTER TABLE tsp EXCHANGE PARTITION p0 WITH TABLE t; 297--error ER_PARTITION_EXCHANGE_PART_TABLE 298ALTER TABLE tsp EXCHANGE PARTITION sp0 WITH TABLE tp; 299SHOW CREATE TABLE t; 300SHOW CREATE TABLE tp; 301SHOW CREATE TABLE tsp; 302--sorted_result 303SELECT * FROM t; 304--sorted_result 305SELECT * FROM tp; 306--echo # Test exchange partition 307ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t; 308SHOW CREATE TABLE t; 309SHOW CREATE TABLE tp; 310--sorted_result 311SELECT * FROM t; 312--sorted_result 313SELECT * FROM tp; 314ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t; 315SHOW CREATE TABLE t; 316SHOW CREATE TABLE tp; 317--sorted_result 318SELECT * FROM t; 319--sorted_result 320SELECT * FROM tp; 321--echo # Test exchange subpartition 322ALTER TABLE tsp EXCHANGE PARTITION sp1 WITH TABLE t; 323SHOW CREATE TABLE t; 324SHOW CREATE TABLE tsp; 325--sorted_result 326SELECT * FROM t; 327--sorted_result 328SELECT * FROM tsp; 329ALTER TABLE tsp EXCHANGE PARTITION sp1 WITH TABLE t; 330ALTER TABLE t ENGINE = InnoDB; 331ALTER TABLE tp ENGINE = InnoDB; 332SHOW CREATE TABLE t; 333SHOW CREATE TABLE tp; 334--sorted_result 335SELECT * FROM t; 336--sorted_result 337SELECT * FROM tp; 338ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t; 339SHOW CREATE TABLE t; 340SHOW CREATE TABLE tp; 341--sorted_result 342SELECT * FROM t; 343--sorted_result 344SELECT * FROM tp; 345ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t; 346--echo # test different engines 347ALTER TABLE t ENGINE = MyISAM; 348ALTER TABLE tp ENGINE = InnoDB; 349SHOW CREATE TABLE t; 350SHOW CREATE TABLE tp; 351--error ER_MIX_HANDLER_ERROR 352ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t; 353SHOW CREATE TABLE t; 354SHOW CREATE TABLE tp; 355--echo # Test different charsets 356ALTER TABLE t ENGINE = MyISAM; 357CREATE TABLE tmp LIKE t; 358INSERT INTO tmp SELECT * FROM t; 359RENAME TABLE t TO tmp2, tmp TO t; 360ALTER TABLE tp ENGINE = MyISAM; 361ALTER TABLE t CHARACTER SET = koi8r COLLATE koi8r_general_ci; 362--error ER_PARTITION_EXCHANGE_DIFFERENT_OPTION 363ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t; 364DROP TABLE t; 365--echo # Test multiple different table options 366CREATE TABLE t (a INT, 367 b VARCHAR(55), 368 PRIMARY KEY (a)) 369ENGINE = MyISAM MAX_ROWS = 100000 MIN_ROWS = 1000; 370INSERT INTO t SELECT * FROM tmp2; 371SHOW CREATE TABLE t; 372SHOW CREATE TABLE tp; 373--error ER_PARTITION_EXCHANGE_DIFFERENT_OPTION 374ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t; 375SHOW WARNINGS; 376DROP TABLE t; 377RENAME TABLE tmp2 TO t; 378# test different keys 379ALTER TABLE t ADD KEY ba_key (b, a); 380ALTER TABLE tp ADD KEY ba_key (b, a); 381ALTER TABLE tsp ADD KEY ba_key (b, a); 382ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t; 383SHOW CREATE TABLE t; 384SHOW CREATE TABLE tp; 385--sorted_result 386SELECT * FROM t; 387--sorted_result 388SELECT * FROM tp; 389ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t; 390ALTER TABLE t DROP KEY ba_key; 391--error ER_TABLES_DIFFERENT_METADATA 392ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t; 393ALTER TABLE t ADD KEY b_key (b); 394--error ER_TABLES_DIFFERENT_METADATA 395ALTER TABLE tsp EXCHANGE PARTITION sp1 WITH TABLE t; 396ALTER TABLE t ADD KEY ba_key (b, a); 397ALTER TABLE t DROP KEY b_key; 398# test different index types 399# test different columns 400ALTER TABLE t CHANGE a c INT; 401--error ER_TABLES_DIFFERENT_METADATA 402ALTER TABLE tsp EXCHANGE PARTITION sp1 WITH TABLE t; 403ALTER TABLE t CHANGE c a INT; 404# test different data/index dir 405# test different options (row_format, max/min_rows, comments, tablespace, 406# pack_keys, delay_key_write, checksum etc. 407# test foreign keys 408--echo # test temporary table 409ALTER TABLE t ENGINE = MyISAM; 410ALTER TABLE tp ENGINE = MyISAM; 411CREATE TEMPORARY TABLE tmp LIKE t; 412INSERT INTO tmp SELECT * FROM t; 413ALTER TABLE t RENAME TO tmp2; 414ALTER TABLE tmp RENAME TO t; 415SHOW CREATE TABLE t; 416SHOW CREATE TABLE tp; 417--error ER_PARTITION_EXCHANGE_TEMP_TABLE 418ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t; 419SHOW CREATE TABLE t; 420SHOW CREATE TABLE tp; 421DROP TEMPORARY TABLE t; 422ALTER TABLE tmp2 RENAME TO t; 423--echo # Test non partitioned table 424ALTER TABLE tp REMOVE PARTITIONING; 425--error ER_PARTITION_MGMT_ON_NONPARTITIONED 426ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t; 427 428# TODO: 429# Verify that it is possible to read a table that is going to be exchanged 430# (but not write) 431# Verify that it is possible to both read and write to the partition that is 432# going to be exchanged 433# test mdl locking (i.e. together with transactions and other ddl) 434# Add test in lc_0-2 to make sure renameing operators is ok with different lc 435# Add test to exchange between databases, also check grants. 436# Add test for privileges 437# check if correct error whithout partitioning support 438DROP TABLE t, tp, tsp; 439 440 441--echo # Test with general_log 442use mysql; 443SET @old_general_log_state = @@global.general_log; 444SET GLOBAL general_log = 0; 445ALTER TABLE general_log ENGINE = MyISAM; 446CREATE TABLE t LIKE general_log; 447ALTER TABLE t PARTITION BY RANGE (UNIX_TIMESTAMP(event_time) DIV 1) 448(PARTITION p0 VALUES LESS THAN (123456789), 449 PARTITION pMAX VALUES LESS THAN MAXVALUE); 450--error ER_BAD_LOG_STATEMENT 451ALTER TABLE t EXCHANGE PARTITION p0 WITH TABLE general_log; 452ALTER TABLE general_log ENGINE = CSV; 453SET @@global.general_log = @old_general_log_state; 454DROP TABLE t; 455use test; 456 457--echo # Test with LOCK TABLE 458# Test with only one table locked at a time, both table locked + read, write 459 460CREATE TABLE tp 461(a VARCHAR(24), 462 b DATETIME, 463 PRIMARY KEY (a,b)) 464PARTITION BY RANGE COLUMNS (a, b) 465(PARTITION p0 VALUES LESS THAN ("Middle", '0000-00-00'), 466 PARTITION p1 VALUES LESS THAN (MAXVALUE, '9999-12-31 23:59:59')); 467CREATE TABLE t LIKE tp; 468ALTER TABLE t REMOVE PARTITIONING; 469CREATE TABLE t2 LIKE t; 470INSERT INTO tp VALUES ("First in tp", '2000-01-02 03:04:25'), ("Zebra in tp", '0000-00-00 00:00:00'), ("Second in tp", '2010-01-01 05:12:24'); 471INSERT INTO t VALUES ("First in t", '2000-01-02 03:04:25'), ("a test in t", '0000-00-00 00:00:00'), ("Echo in t", '2010-01-01 05:12:24'); 472# 3 different kind of locks (none, READ, WRITE) for three different tables. 473# 3^3 = 18. The variant with no locks does not need testing -> 17 474let $count = 17; 475while ($count) 476{ 477let $t_lock = `SELECT (CASE ($count % 3) WHEN 0 THEN 0 WHEN 1 THEN 'WRITE' WHEN 2 THEN 'READ' END)`; 478let $tp_lock = `SELECT (CASE ((($count + 2) DIV 3) % 3) WHEN 0 THEN 0 WHEN 1 THEN 'WRITE' WHEN 2 THEN 'READ' END)`; 479let $t2_lock = `SELECT (CASE ((($count + 8) DIV 9) % 3) WHEN 0 THEN 0 WHEN 1 THEN 'WRITE' WHEN 2 THEN 'READ' END)`; 480--echo # tp_lock '$tp_lock' t_lock '$t_lock' t2_lock '$t2_lock' count '$count' 481let $lock_cmd = LOCK TABLE; 482let $take_lock = 0; 483if ($t_lock) 484{ 485 let $lock_cmd = $lock_cmd t $t_lock; 486 let $take_lock = 1; 487} 488if ($tp_lock) 489{ 490 if ($take_lock) 491 { 492 let $lock_cmd = $lock_cmd, tp $tp_lock; 493 } 494 if (!$take_lock) 495 { 496 let $lock_cmd = $lock_cmd tp $tp_lock; 497 } 498 let $take_lock = 1; 499} 500if ($t2_lock) 501{ 502 if ($take_lock) 503 { 504 let $lock_cmd = $lock_cmd, t2 $t2_lock; 505 } 506 if (!$take_lock) 507 { 508 let $lock_cmd = $lock_cmd t2 $t2_lock; 509 } 510 let $take_lock = 1; 511} 512if ($take_lock) 513{ 514 eval $lock_cmd; 515} 516--error 0, ER_TABLE_NOT_LOCKED, ER_TABLE_NOT_LOCKED_FOR_WRITE 517ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t; 518SHOW WARNINGS; 519--error 0, ER_TABLE_NOT_LOCKED, ER_TABLE_NOT_LOCKED_FOR_WRITE 520ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t2; 521SHOW WARNINGS; 522UNLOCK TABLES; 523dec $count; 524} 525DROP TABLE t, t2, tp; 526 527--echo # 528--echo # Assertion `!part_elem->tablespace_name && !table_create_info->tablespace' 529--echo # failed during EXCHANGE PARTITION with different TABLESPACE. 530--echo # 531CREATE TABLE t1 (a VARCHAR(200)) PARTITION BY KEY(a) partitions 10; 532ALTER TABLE t1 ADD PARTITION (PARTITION pm TABLESPACE = `innodb_file_per_table`); 533CREATE TABLE t2 like t1; 534ALTER TABLE t2 REMOVE PARTITIONING; 535--error ER_PARTITION_EXCHANGE_DIFFERENT_OPTION 536ALTER TABLE t1 EXCHANGE PARTITION pm WITH TABLE t2; 537DROP TABLE t1, t2; 538 539