1SET @max_row = 20; 2SET @@session.default_storage_engine = 'InnoDB'; 3 4#------------------------------------------------------------------------ 5# 0. Setting of auxiliary variables + Creation of an auxiliary tables 6# needed in many testcases 7#------------------------------------------------------------------------ 8SELECT @max_row DIV 2 INTO @max_row_div2; 9SELECT @max_row DIV 3 INTO @max_row_div3; 10SELECT @max_row DIV 4 INTO @max_row_div4; 11SET @max_int_4 = 2147483647; 12DROP TABLE IF EXISTS t0_template; 13CREATE TABLE t0_template ( 14f_int1 INTEGER, 15f_int2 INTEGER, 16f_char1 CHAR(20), 17f_char2 CHAR(20), 18f_charbig VARCHAR(1000) , 19PRIMARY KEY(f_int1)) 20ENGINE = MEMORY; 21# Logging of <max_row> INSERTs into t0_template suppressed 22DROP TABLE IF EXISTS t0_definition; 23CREATE TABLE t0_definition ( 24state CHAR(3), 25create_command VARBINARY(5000), 26file_list VARBINARY(10000), 27PRIMARY KEY (state) 28) ENGINE = MEMORY; 29DROP TABLE IF EXISTS t0_aux; 30CREATE TABLE t0_aux ( f_int1 INTEGER, 31f_int2 INTEGER, 32f_char1 CHAR(20), 33f_char2 CHAR(20), 34f_charbig VARCHAR(1000) ) 35ENGINE = MEMORY; 36SET AUTOCOMMIT= 1; 37SET @@session.sql_mode= ''; 38Warnings: 39Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 40# End of basic preparations needed for all tests 41#----------------------------------------------- 42 43#======================================================================== 44# 1. Partition management commands on HASH partitioned table 45# column in partitioning function is of type DATE 46#======================================================================== 47DROP TABLE IF EXISTS t1; 48CREATE TABLE t1 (f_date DATE, f_varchar VARCHAR(30)); 49INSERT INTO t1 (f_date, f_varchar) 50SELECT CONCAT(CAST((f_int1 + 999) AS CHAR),'-02-10'), CAST(f_char1 AS CHAR) 51FROM t0_template 52WHERE f_int1 + 999 BETWEEN 1000 AND 9999; 53SELECT IF(9999 - 1000 + 1 > @max_row, @max_row , 9999 - 1000 + 1) 54INTO @exp_row_count; 55SHOW CREATE TABLE t1; 56Table Create Table 57t1 CREATE TABLE `t1` ( 58 `f_date` date DEFAULT NULL, 59 `f_varchar` varchar(30) DEFAULT NULL 60) ENGINE=InnoDB DEFAULT CHARSET=latin1 61t1.frm 62t1.ibd 63EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; 64id select_type table partitions type possible_keys key key_len ref rows filtered Extra 651 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 10.00 Using where 66Warnings: 67Warning 1681 'PARTITIONS' is deprecated and will be removed in a future release. 68Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`f_date` = '1000-02-10') 69# check read single success: 1 70# check read all success: 1 71# check read row by row success: 1 72#------------------------------------------------------------------------ 73# 1.1 Increase number of PARTITIONS 74#------------------------------------------------------------------------ 75# 1.1.1 ADD PARTITION to not partitioned table --> must fail 76ALTER TABLE t1 ADD PARTITION (PARTITION part2); 77ERROR HY000: Partition management on a not partitioned table is not possible 78# 1.1.2 Assign HASH partitioning 79ALTER TABLE t1 PARTITION BY HASH(YEAR(f_date)); 80SHOW CREATE TABLE t1; 81Table Create Table 82t1 CREATE TABLE `t1` ( 83 `f_date` date DEFAULT NULL, 84 `f_varchar` varchar(30) DEFAULT NULL 85) ENGINE=InnoDB DEFAULT CHARSET=latin1 86/*!50100 PARTITION BY HASH (YEAR(f_date)) */ 87t1#P#p0.ibd 88t1.frm 89EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; 90id select_type table partitions type possible_keys key key_len ref rows filtered Extra 911 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 10.00 Using where 92Warnings: 93Warning 1681 'PARTITIONS' is deprecated and will be removed in a future release. 94Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`f_date` = '1000-02-10') 95# check read single success: 1 96# check read all success: 1 97# check read row by row success: 1 98# 1.1.3 Assign other HASH partitioning to already partitioned table 99# + test and switch back + test 100ALTER TABLE t1 PARTITION BY HASH(DAYOFYEAR(f_date)); 101SHOW CREATE TABLE t1; 102Table Create Table 103t1 CREATE TABLE `t1` ( 104 `f_date` date DEFAULT NULL, 105 `f_varchar` varchar(30) DEFAULT NULL 106) ENGINE=InnoDB DEFAULT CHARSET=latin1 107/*!50100 PARTITION BY HASH (DAYOFYEAR(f_date)) */ 108t1#P#p0.ibd 109t1.frm 110EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; 111id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1121 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 10.00 Using where 113Warnings: 114Warning 1681 'PARTITIONS' is deprecated and will be removed in a future release. 115Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`f_date` = '1000-02-10') 116# check read single success: 1 117# check read all success: 1 118# check read row by row success: 1 119ALTER TABLE t1 PARTITION BY HASH(YEAR(f_date)); 120SHOW CREATE TABLE t1; 121Table Create Table 122t1 CREATE TABLE `t1` ( 123 `f_date` date DEFAULT NULL, 124 `f_varchar` varchar(30) DEFAULT NULL 125) ENGINE=InnoDB DEFAULT CHARSET=latin1 126/*!50100 PARTITION BY HASH (YEAR(f_date)) */ 127t1#P#p0.ibd 128t1.frm 129EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; 130id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1311 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 10.00 Using where 132Warnings: 133Warning 1681 'PARTITIONS' is deprecated and will be removed in a future release. 134Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`f_date` = '1000-02-10') 135# check read single success: 1 136# check read all success: 1 137# check read row by row success: 1 138# 1.1.4 Add PARTITIONS not fitting to HASH --> must fail 139ALTER TABLE t1 ADD PARTITION (PARTITION part1 VALUES IN (0)); 140ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition 141ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN (0)); 142ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition 143# 1.1.5 Add two named partitions + test 144ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7); 145SHOW CREATE TABLE t1; 146Table Create Table 147t1 CREATE TABLE `t1` ( 148 `f_date` date DEFAULT NULL, 149 `f_varchar` varchar(30) DEFAULT NULL 150) ENGINE=InnoDB DEFAULT CHARSET=latin1 151/*!50100 PARTITION BY HASH (YEAR(f_date)) 152(PARTITION p0 ENGINE = InnoDB, 153 PARTITION part1 ENGINE = InnoDB, 154 PARTITION part7 ENGINE = InnoDB) */ 155t1#P#p0.ibd 156t1#P#part1.ibd 157t1#P#part7.ibd 158t1.frm 159EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; 160id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1611 SIMPLE t1 part1 ALL NULL NULL NULL NULL 7 14.29 Using where 162Warnings: 163Warning 1681 'PARTITIONS' is deprecated and will be removed in a future release. 164Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`f_date` = '1000-02-10') 165# check read single success: 1 166# check read all success: 1 167# check read row by row success: 1 168# 1.1.6 Add two named partitions, name clash --> must fail 169ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7); 170ERROR HY000: Duplicate partition name part1 171# 1.1.7 Add one named partition + test 172ALTER TABLE t1 ADD PARTITION (PARTITION part2); 173SHOW CREATE TABLE t1; 174Table Create Table 175t1 CREATE TABLE `t1` ( 176 `f_date` date DEFAULT NULL, 177 `f_varchar` varchar(30) DEFAULT NULL 178) ENGINE=InnoDB DEFAULT CHARSET=latin1 179/*!50100 PARTITION BY HASH (YEAR(f_date)) 180(PARTITION p0 ENGINE = InnoDB, 181 PARTITION part1 ENGINE = InnoDB, 182 PARTITION part7 ENGINE = InnoDB, 183 PARTITION part2 ENGINE = InnoDB) */ 184t1#P#p0.ibd 185t1#P#part1.ibd 186t1#P#part2.ibd 187t1#P#part7.ibd 188t1.frm 189EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; 190id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1911 SIMPLE t1 p0 ALL NULL NULL NULL NULL 5 20.00 Using where 192Warnings: 193Warning 1681 'PARTITIONS' is deprecated and will be removed in a future release. 194Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`f_date` = '1000-02-10') 195# check read single success: 1 196# check read all success: 1 197# check read row by row success: 1 198# 1.1.8 Add four not named partitions + test 199ALTER TABLE t1 ADD PARTITION PARTITIONS 4; 200SHOW CREATE TABLE t1; 201Table Create Table 202t1 CREATE TABLE `t1` ( 203 `f_date` date DEFAULT NULL, 204 `f_varchar` varchar(30) DEFAULT NULL 205) ENGINE=InnoDB DEFAULT CHARSET=latin1 206/*!50100 PARTITION BY HASH (YEAR(f_date)) 207(PARTITION p0 ENGINE = InnoDB, 208 PARTITION part1 ENGINE = InnoDB, 209 PARTITION part7 ENGINE = InnoDB, 210 PARTITION part2 ENGINE = InnoDB, 211 PARTITION p4 ENGINE = InnoDB, 212 PARTITION p5 ENGINE = InnoDB, 213 PARTITION p6 ENGINE = InnoDB, 214 PARTITION p7 ENGINE = InnoDB) */ 215t1#P#p0.ibd 216t1#P#p4.ibd 217t1#P#p5.ibd 218t1#P#p6.ibd 219t1#P#p7.ibd 220t1#P#part1.ibd 221t1#P#part2.ibd 222t1#P#part7.ibd 223t1.frm 224EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; 225id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2261 SIMPLE t1 p0 ALL NULL NULL NULL NULL 3 33.33 Using where 227Warnings: 228Warning 1681 'PARTITIONS' is deprecated and will be removed in a future release. 229Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`f_date` = '1000-02-10') 230# check read single success: 1 231# check read all success: 1 232# check read row by row success: 1 233#------------------------------------------------------------------------ 234# 1.2 Decrease number of PARTITIONS 235#------------------------------------------------------------------------ 236# 1.2.1 DROP PARTITION is not supported for HASH --> must fail 237ALTER TABLE t1 DROP PARTITION part1; 238ERROR HY000: DROP PARTITION can only be used on RANGE/LIST partitions 239# 1.2.2 COALESCE PARTITION partitionname is not supported 240ALTER TABLE t1 COALESCE PARTITION part1; 241ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'part1' at line 1 242# 1.2.3 Decrease by 0 is non sense --> must fail 243ALTER TABLE t1 COALESCE PARTITION 0; 244ERROR HY000: At least one partition must be coalesced 245# 1.2.4 COALESCE one partition + test loop 246ALTER TABLE t1 COALESCE PARTITION 1; 247SHOW CREATE TABLE t1; 248Table Create Table 249t1 CREATE TABLE `t1` ( 250 `f_date` date DEFAULT NULL, 251 `f_varchar` varchar(30) DEFAULT NULL 252) ENGINE=InnoDB DEFAULT CHARSET=latin1 253/*!50100 PARTITION BY HASH (YEAR(f_date)) 254(PARTITION p0 ENGINE = InnoDB, 255 PARTITION part1 ENGINE = InnoDB, 256 PARTITION part7 ENGINE = InnoDB, 257 PARTITION part2 ENGINE = InnoDB, 258 PARTITION p4 ENGINE = InnoDB, 259 PARTITION p5 ENGINE = InnoDB, 260 PARTITION p6 ENGINE = InnoDB) */ 261t1#P#p0.ibd 262t1#P#p4.ibd 263t1#P#p5.ibd 264t1#P#p6.ibd 265t1#P#part1.ibd 266t1#P#part2.ibd 267t1#P#part7.ibd 268t1.frm 269EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; 270id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2711 SIMPLE t1 p6 ALL NULL NULL NULL NULL 3 33.33 Using where 272Warnings: 273Warning 1681 'PARTITIONS' is deprecated and will be removed in a future release. 274Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`f_date` = '1000-02-10') 275# check read single success: 1 276# check read all success: 1 277# check read row by row success: 1 278ALTER TABLE t1 COALESCE PARTITION 1; 279SHOW CREATE TABLE t1; 280Table Create Table 281t1 CREATE TABLE `t1` ( 282 `f_date` date DEFAULT NULL, 283 `f_varchar` varchar(30) DEFAULT NULL 284) ENGINE=InnoDB DEFAULT CHARSET=latin1 285/*!50100 PARTITION BY HASH (YEAR(f_date)) 286(PARTITION p0 ENGINE = InnoDB, 287 PARTITION part1 ENGINE = InnoDB, 288 PARTITION part7 ENGINE = InnoDB, 289 PARTITION part2 ENGINE = InnoDB, 290 PARTITION p4 ENGINE = InnoDB, 291 PARTITION p5 ENGINE = InnoDB) */ 292t1#P#p0.ibd 293t1#P#p4.ibd 294t1#P#p5.ibd 295t1#P#part1.ibd 296t1#P#part2.ibd 297t1#P#part7.ibd 298t1.frm 299EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; 300id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3011 SIMPLE t1 p4 ALL NULL NULL NULL NULL 4 25.00 Using where 302Warnings: 303Warning 1681 'PARTITIONS' is deprecated and will be removed in a future release. 304Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`f_date` = '1000-02-10') 305# check read single success: 1 306# check read all success: 1 307# check read row by row success: 1 308ALTER TABLE t1 COALESCE PARTITION 1; 309SHOW CREATE TABLE t1; 310Table Create Table 311t1 CREATE TABLE `t1` ( 312 `f_date` date DEFAULT NULL, 313 `f_varchar` varchar(30) DEFAULT NULL 314) ENGINE=InnoDB DEFAULT CHARSET=latin1 315/*!50100 PARTITION BY HASH (YEAR(f_date)) 316(PARTITION p0 ENGINE = InnoDB, 317 PARTITION part1 ENGINE = InnoDB, 318 PARTITION part7 ENGINE = InnoDB, 319 PARTITION part2 ENGINE = InnoDB, 320 PARTITION p4 ENGINE = InnoDB) */ 321t1#P#p0.ibd 322t1#P#p4.ibd 323t1#P#part1.ibd 324t1#P#part2.ibd 325t1#P#part7.ibd 326t1.frm 327EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; 328id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3291 SIMPLE t1 p0 ALL NULL NULL NULL NULL 4 25.00 Using where 330Warnings: 331Warning 1681 'PARTITIONS' is deprecated and will be removed in a future release. 332Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`f_date` = '1000-02-10') 333# check read single success: 1 334# check read all success: 1 335# check read row by row success: 1 336ALTER TABLE t1 COALESCE PARTITION 1; 337SHOW CREATE TABLE t1; 338Table Create Table 339t1 CREATE TABLE `t1` ( 340 `f_date` date DEFAULT NULL, 341 `f_varchar` varchar(30) DEFAULT NULL 342) ENGINE=InnoDB DEFAULT CHARSET=latin1 343/*!50100 PARTITION BY HASH (YEAR(f_date)) 344(PARTITION p0 ENGINE = InnoDB, 345 PARTITION part1 ENGINE = InnoDB, 346 PARTITION part7 ENGINE = InnoDB, 347 PARTITION part2 ENGINE = InnoDB) */ 348t1#P#p0.ibd 349t1#P#part1.ibd 350t1#P#part2.ibd 351t1#P#part7.ibd 352t1.frm 353EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; 354id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3551 SIMPLE t1 p0 ALL NULL NULL NULL NULL 5 20.00 Using where 356Warnings: 357Warning 1681 'PARTITIONS' is deprecated and will be removed in a future release. 358Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`f_date` = '1000-02-10') 359# check read single success: 1 360# check read all success: 1 361# check read row by row success: 1 362ALTER TABLE t1 COALESCE PARTITION 1; 363SHOW CREATE TABLE t1; 364Table Create Table 365t1 CREATE TABLE `t1` ( 366 `f_date` date DEFAULT NULL, 367 `f_varchar` varchar(30) DEFAULT NULL 368) ENGINE=InnoDB DEFAULT CHARSET=latin1 369/*!50100 PARTITION BY HASH (YEAR(f_date)) 370(PARTITION p0 ENGINE = InnoDB, 371 PARTITION part1 ENGINE = InnoDB, 372 PARTITION part7 ENGINE = InnoDB) */ 373t1#P#p0.ibd 374t1#P#part1.ibd 375t1#P#part7.ibd 376t1.frm 377EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; 378id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3791 SIMPLE t1 part1 ALL NULL NULL NULL NULL 7 14.29 Using where 380Warnings: 381Warning 1681 'PARTITIONS' is deprecated and will be removed in a future release. 382Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`f_date` = '1000-02-10') 383# check read single success: 1 384# check read all success: 1 385# check read row by row success: 1 386ALTER TABLE t1 COALESCE PARTITION 1; 387SHOW CREATE TABLE t1; 388Table Create Table 389t1 CREATE TABLE `t1` ( 390 `f_date` date DEFAULT NULL, 391 `f_varchar` varchar(30) DEFAULT NULL 392) ENGINE=InnoDB DEFAULT CHARSET=latin1 393/*!50100 PARTITION BY HASH (YEAR(f_date)) 394(PARTITION p0 ENGINE = InnoDB, 395 PARTITION part1 ENGINE = InnoDB) */ 396t1#P#p0.ibd 397t1#P#part1.ibd 398t1.frm 399EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; 400id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4011 SIMPLE t1 p0 ALL NULL NULL NULL NULL 10 10.00 Using where 402Warnings: 403Warning 1681 'PARTITIONS' is deprecated and will be removed in a future release. 404Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`f_date` = '1000-02-10') 405# check read single success: 1 406# check read all success: 1 407# check read row by row success: 1 408ALTER TABLE t1 COALESCE PARTITION 1; 409SHOW CREATE TABLE t1; 410Table Create Table 411t1 CREATE TABLE `t1` ( 412 `f_date` date DEFAULT NULL, 413 `f_varchar` varchar(30) DEFAULT NULL 414) ENGINE=InnoDB DEFAULT CHARSET=latin1 415/*!50100 PARTITION BY HASH (YEAR(f_date)) 416(PARTITION p0 ENGINE = InnoDB) */ 417t1#P#p0.ibd 418t1.frm 419EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; 420id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4211 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 10.00 Using where 422Warnings: 423Warning 1681 'PARTITIONS' is deprecated and will be removed in a future release. 424Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`f_date` = '1000-02-10') 425# check read single success: 1 426# check read all success: 1 427# check read row by row success: 1 428# 1.2.5 COALESCE of last partition --> must fail 429ALTER TABLE t1 COALESCE PARTITION 1; 430ERROR HY000: Cannot remove all partitions, use DROP TABLE instead 431# 1.2.6 Remove partitioning 432ALTER TABLE t1 REMOVE PARTITIONING; 433SHOW CREATE TABLE t1; 434Table Create Table 435t1 CREATE TABLE `t1` ( 436 `f_date` date DEFAULT NULL, 437 `f_varchar` varchar(30) DEFAULT NULL 438) ENGINE=InnoDB DEFAULT CHARSET=latin1 439t1.frm 440t1.ibd 441EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; 442id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4431 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 10.00 Using where 444Warnings: 445Warning 1681 'PARTITIONS' is deprecated and will be removed in a future release. 446Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`f_date` = '1000-02-10') 447# check read single success: 1 448# check read all success: 1 449# check read row by row success: 1 450# 1.2.7 Remove partitioning from not partitioned table --> ???? 451ALTER TABLE t1 REMOVE PARTITIONING; 452ERROR HY000: Partition management on a not partitioned table is not possible 453DROP TABLE t1; 454# Attention: There are unused files. 455# Either the DROP TABLE or a preceding ALTER TABLE 456# <alter partitioning> worked incomplete. 457# We found: 458unified filelist 459--- not determined --- 460 461#======================================================================== 462# 2. Partition management commands on KEY partitioned table 463#======================================================================== 464DROP TABLE IF EXISTS t1; 465CREATE TABLE t1 ( 466f_int1 INTEGER, 467f_int2 INTEGER, 468f_char1 CHAR(20), 469f_char2 CHAR(20), 470f_charbig VARCHAR(1000) 471); 472INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) 473SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; 474SHOW CREATE TABLE t1; 475Table Create Table 476t1 CREATE TABLE `t1` ( 477 `f_int1` int(11) DEFAULT NULL, 478 `f_int2` int(11) DEFAULT NULL, 479 `f_char1` char(20) DEFAULT NULL, 480 `f_char2` char(20) DEFAULT NULL, 481 `f_charbig` varchar(1000) DEFAULT NULL 482) ENGINE=InnoDB DEFAULT CHARSET=latin1 483t1.frm 484t1.ibd 485EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3; 486id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4871 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 10.00 Using where 488Warnings: 489Warning 1681 'PARTITIONS' is deprecated and will be removed in a future release. 490Note 1003 /* select#1 */ select (count(0) <> 1) AS `COUNT(*) <> 1` from `test`.`t1` where (`test`.`t1`.`f_int1` = 3) 491# check read single success: 1 492# check read all success: 1 493# check read row by row success: 1 494#------------------------------------------------------------------------ 495# 2.1 Increase number of PARTITIONS 496# Some negative testcases are omitted (already checked with HASH). 497#------------------------------------------------------------------------ 498# 2.1.1 Assign KEY partitioning 499ALTER TABLE t1 PARTITION BY KEY(f_int1); 500SHOW CREATE TABLE t1; 501Table Create Table 502t1 CREATE TABLE `t1` ( 503 `f_int1` int(11) DEFAULT NULL, 504 `f_int2` int(11) DEFAULT NULL, 505 `f_char1` char(20) DEFAULT NULL, 506 `f_char2` char(20) DEFAULT NULL, 507 `f_charbig` varchar(1000) DEFAULT NULL 508) ENGINE=InnoDB DEFAULT CHARSET=latin1 509/*!50100 PARTITION BY KEY (f_int1) */ 510t1#P#p0.ibd 511t1.frm 512EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3; 513id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5141 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 10.00 Using where 515Warnings: 516Warning 1681 'PARTITIONS' is deprecated and will be removed in a future release. 517Note 1003 /* select#1 */ select (count(0) <> 1) AS `COUNT(*) <> 1` from `test`.`t1` where (`test`.`t1`.`f_int1` = 3) 518# check read single success: 1 519# check read all success: 1 520# check read row by row success: 1 521# 2.1.2 Add PARTITIONS not fitting to KEY --> must fail 522ALTER TABLE t1 ADD PARTITION (PARTITION part1 VALUES IN (0)); 523ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition 524ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN (0)); 525ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition 526# 2.1.3 Add two named partitions + test 527ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7); 528SHOW CREATE TABLE t1; 529Table Create Table 530t1 CREATE TABLE `t1` ( 531 `f_int1` int(11) DEFAULT NULL, 532 `f_int2` int(11) DEFAULT NULL, 533 `f_char1` char(20) DEFAULT NULL, 534 `f_char2` char(20) DEFAULT NULL, 535 `f_charbig` varchar(1000) DEFAULT NULL 536) ENGINE=InnoDB DEFAULT CHARSET=latin1 537/*!50100 PARTITION BY KEY (f_int1) 538(PARTITION p0 ENGINE = InnoDB, 539 PARTITION part1 ENGINE = InnoDB, 540 PARTITION part7 ENGINE = InnoDB) */ 541t1#P#p0.ibd 542t1#P#part1.ibd 543t1#P#part7.ibd 544t1.frm 545EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3; 546id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5471 SIMPLE t1 part7 ALL NULL NULL NULL NULL 7 14.29 Using where 548Warnings: 549Warning 1681 'PARTITIONS' is deprecated and will be removed in a future release. 550Note 1003 /* select#1 */ select (count(0) <> 1) AS `COUNT(*) <> 1` from `test`.`t1` where (`test`.`t1`.`f_int1` = 3) 551# check read single success: 1 552# check read all success: 1 553# check read row by row success: 1 554# 2.1.4 Add one named partition + test 555ALTER TABLE t1 ADD PARTITION (PARTITION part2); 556SHOW CREATE TABLE t1; 557Table Create Table 558t1 CREATE TABLE `t1` ( 559 `f_int1` int(11) DEFAULT NULL, 560 `f_int2` int(11) DEFAULT NULL, 561 `f_char1` char(20) DEFAULT NULL, 562 `f_char2` char(20) DEFAULT NULL, 563 `f_charbig` varchar(1000) DEFAULT NULL 564) ENGINE=InnoDB DEFAULT CHARSET=latin1 565/*!50100 PARTITION BY KEY (f_int1) 566(PARTITION p0 ENGINE = InnoDB, 567 PARTITION part1 ENGINE = InnoDB, 568 PARTITION part7 ENGINE = InnoDB, 569 PARTITION part2 ENGINE = InnoDB) */ 570t1#P#p0.ibd 571t1#P#part1.ibd 572t1#P#part2.ibd 573t1#P#part7.ibd 574t1.frm 575EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3; 576id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5771 SIMPLE t1 part7 ALL NULL NULL NULL NULL 5 20.00 Using where 578Warnings: 579Warning 1681 'PARTITIONS' is deprecated and will be removed in a future release. 580Note 1003 /* select#1 */ select (count(0) <> 1) AS `COUNT(*) <> 1` from `test`.`t1` where (`test`.`t1`.`f_int1` = 3) 581# check read single success: 1 582# check read all success: 1 583# check read row by row success: 1 584# 2.1.5 Add four not named partitions + test 585ALTER TABLE t1 ADD PARTITION PARTITIONS 4; 586SHOW CREATE TABLE t1; 587Table Create Table 588t1 CREATE TABLE `t1` ( 589 `f_int1` int(11) DEFAULT NULL, 590 `f_int2` int(11) DEFAULT NULL, 591 `f_char1` char(20) DEFAULT NULL, 592 `f_char2` char(20) DEFAULT NULL, 593 `f_charbig` varchar(1000) DEFAULT NULL 594) ENGINE=InnoDB DEFAULT CHARSET=latin1 595/*!50100 PARTITION BY KEY (f_int1) 596(PARTITION p0 ENGINE = InnoDB, 597 PARTITION part1 ENGINE = InnoDB, 598 PARTITION part7 ENGINE = InnoDB, 599 PARTITION part2 ENGINE = InnoDB, 600 PARTITION p4 ENGINE = InnoDB, 601 PARTITION p5 ENGINE = InnoDB, 602 PARTITION p6 ENGINE = InnoDB, 603 PARTITION p7 ENGINE = InnoDB) */ 604t1#P#p0.ibd 605t1#P#p4.ibd 606t1#P#p5.ibd 607t1#P#p6.ibd 608t1#P#p7.ibd 609t1#P#part1.ibd 610t1#P#part2.ibd 611t1#P#part7.ibd 612t1.frm 613EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3; 614id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6151 SIMPLE t1 p6 ALL NULL NULL NULL NULL 3 33.33 Using where 616Warnings: 617Warning 1681 'PARTITIONS' is deprecated and will be removed in a future release. 618Note 1003 /* select#1 */ select (count(0) <> 1) AS `COUNT(*) <> 1` from `test`.`t1` where (`test`.`t1`.`f_int1` = 3) 619# check read single success: 1 620# check read all success: 1 621# check read row by row success: 1 622#------------------------------------------------------------------------ 623# 2.2 Decrease number of PARTITIONS 624# Some negative testcases are omitted (already checked with HASH). 625#------------------------------------------------------------------------ 626# 2.2.1 DROP PARTITION is not supported for KEY --> must fail 627ALTER TABLE t1 DROP PARTITION part1; 628ERROR HY000: DROP PARTITION can only be used on RANGE/LIST partitions 629# 2.2.4 COALESCE one partition + test loop 630ALTER TABLE t1 COALESCE PARTITION 1; 631SHOW CREATE TABLE t1; 632Table Create Table 633t1 CREATE TABLE `t1` ( 634 `f_int1` int(11) DEFAULT NULL, 635 `f_int2` int(11) DEFAULT NULL, 636 `f_char1` char(20) DEFAULT NULL, 637 `f_char2` char(20) DEFAULT NULL, 638 `f_charbig` varchar(1000) DEFAULT NULL 639) ENGINE=InnoDB DEFAULT CHARSET=latin1 640/*!50100 PARTITION BY KEY (f_int1) 641(PARTITION p0 ENGINE = InnoDB, 642 PARTITION part1 ENGINE = InnoDB, 643 PARTITION part7 ENGINE = InnoDB, 644 PARTITION part2 ENGINE = InnoDB, 645 PARTITION p4 ENGINE = InnoDB, 646 PARTITION p5 ENGINE = InnoDB, 647 PARTITION p6 ENGINE = InnoDB) */ 648t1#P#p0.ibd 649t1#P#p4.ibd 650t1#P#p5.ibd 651t1#P#p6.ibd 652t1#P#part1.ibd 653t1#P#part2.ibd 654t1#P#part7.ibd 655t1.frm 656EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3; 657id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6581 SIMPLE t1 p0 ALL NULL NULL NULL NULL 4 25.00 Using where 659Warnings: 660Warning 1681 'PARTITIONS' is deprecated and will be removed in a future release. 661Note 1003 /* select#1 */ select (count(0) <> 1) AS `COUNT(*) <> 1` from `test`.`t1` where (`test`.`t1`.`f_int1` = 3) 662# check read single success: 1 663# check read all success: 1 664# check read row by row success: 1 665ALTER TABLE t1 COALESCE PARTITION 1; 666SHOW CREATE TABLE t1; 667Table Create Table 668t1 CREATE TABLE `t1` ( 669 `f_int1` int(11) DEFAULT NULL, 670 `f_int2` int(11) DEFAULT NULL, 671 `f_char1` char(20) DEFAULT NULL, 672 `f_char2` char(20) DEFAULT NULL, 673 `f_charbig` varchar(1000) DEFAULT NULL 674) ENGINE=InnoDB DEFAULT CHARSET=latin1 675/*!50100 PARTITION BY KEY (f_int1) 676(PARTITION p0 ENGINE = InnoDB, 677 PARTITION part1 ENGINE = InnoDB, 678 PARTITION part7 ENGINE = InnoDB, 679 PARTITION part2 ENGINE = InnoDB, 680 PARTITION p4 ENGINE = InnoDB, 681 PARTITION p5 ENGINE = InnoDB) */ 682t1#P#p0.ibd 683t1#P#p4.ibd 684t1#P#p5.ibd 685t1#P#part1.ibd 686t1#P#part2.ibd 687t1#P#part7.ibd 688t1.frm 689EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3; 690id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6911 SIMPLE t1 part7 ALL NULL NULL NULL NULL 3 33.33 Using where 692Warnings: 693Warning 1681 'PARTITIONS' is deprecated and will be removed in a future release. 694Note 1003 /* select#1 */ select (count(0) <> 1) AS `COUNT(*) <> 1` from `test`.`t1` where (`test`.`t1`.`f_int1` = 3) 695# check read single success: 1 696# check read all success: 1 697# check read row by row success: 1 698ALTER TABLE t1 COALESCE PARTITION 1; 699SHOW CREATE TABLE t1; 700Table Create Table 701t1 CREATE TABLE `t1` ( 702 `f_int1` int(11) DEFAULT NULL, 703 `f_int2` int(11) DEFAULT NULL, 704 `f_char1` char(20) DEFAULT NULL, 705 `f_char2` char(20) DEFAULT NULL, 706 `f_charbig` varchar(1000) DEFAULT NULL 707) ENGINE=InnoDB DEFAULT CHARSET=latin1 708/*!50100 PARTITION BY KEY (f_int1) 709(PARTITION p0 ENGINE = InnoDB, 710 PARTITION part1 ENGINE = InnoDB, 711 PARTITION part7 ENGINE = InnoDB, 712 PARTITION part2 ENGINE = InnoDB, 713 PARTITION p4 ENGINE = InnoDB) */ 714t1#P#p0.ibd 715t1#P#p4.ibd 716t1#P#part1.ibd 717t1#P#part2.ibd 718t1#P#part7.ibd 719t1.frm 720EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3; 721id select_type table partitions type possible_keys key key_len ref rows filtered Extra 7221 SIMPLE t1 p4 ALL NULL NULL NULL NULL 10 10.00 Using where 723Warnings: 724Warning 1681 'PARTITIONS' is deprecated and will be removed in a future release. 725Note 1003 /* select#1 */ select (count(0) <> 1) AS `COUNT(*) <> 1` from `test`.`t1` where (`test`.`t1`.`f_int1` = 3) 726# check read single success: 1 727# check read all success: 1 728# check read row by row success: 1 729ALTER TABLE t1 COALESCE PARTITION 1; 730SHOW CREATE TABLE t1; 731Table Create Table 732t1 CREATE TABLE `t1` ( 733 `f_int1` int(11) DEFAULT NULL, 734 `f_int2` int(11) DEFAULT NULL, 735 `f_char1` char(20) DEFAULT NULL, 736 `f_char2` char(20) DEFAULT NULL, 737 `f_charbig` varchar(1000) DEFAULT NULL 738) ENGINE=InnoDB DEFAULT CHARSET=latin1 739/*!50100 PARTITION BY KEY (f_int1) 740(PARTITION p0 ENGINE = InnoDB, 741 PARTITION part1 ENGINE = InnoDB, 742 PARTITION part7 ENGINE = InnoDB, 743 PARTITION part2 ENGINE = InnoDB) */ 744t1#P#p0.ibd 745t1#P#part1.ibd 746t1#P#part2.ibd 747t1#P#part7.ibd 748t1.frm 749EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3; 750id select_type table partitions type possible_keys key key_len ref rows filtered Extra 7511 SIMPLE t1 part7 ALL NULL NULL NULL NULL 5 20.00 Using where 752Warnings: 753Warning 1681 'PARTITIONS' is deprecated and will be removed in a future release. 754Note 1003 /* select#1 */ select (count(0) <> 1) AS `COUNT(*) <> 1` from `test`.`t1` where (`test`.`t1`.`f_int1` = 3) 755# check read single success: 1 756# check read all success: 1 757# check read row by row success: 1 758ALTER TABLE t1 COALESCE PARTITION 1; 759SHOW CREATE TABLE t1; 760Table Create Table 761t1 CREATE TABLE `t1` ( 762 `f_int1` int(11) DEFAULT NULL, 763 `f_int2` int(11) DEFAULT NULL, 764 `f_char1` char(20) DEFAULT NULL, 765 `f_char2` char(20) DEFAULT NULL, 766 `f_charbig` varchar(1000) DEFAULT NULL 767) ENGINE=InnoDB DEFAULT CHARSET=latin1 768/*!50100 PARTITION BY KEY (f_int1) 769(PARTITION p0 ENGINE = InnoDB, 770 PARTITION part1 ENGINE = InnoDB, 771 PARTITION part7 ENGINE = InnoDB) */ 772t1#P#p0.ibd 773t1#P#part1.ibd 774t1#P#part7.ibd 775t1.frm 776EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3; 777id select_type table partitions type possible_keys key key_len ref rows filtered Extra 7781 SIMPLE t1 part7 ALL NULL NULL NULL NULL 7 14.29 Using where 779Warnings: 780Warning 1681 'PARTITIONS' is deprecated and will be removed in a future release. 781Note 1003 /* select#1 */ select (count(0) <> 1) AS `COUNT(*) <> 1` from `test`.`t1` where (`test`.`t1`.`f_int1` = 3) 782# check read single success: 1 783# check read all success: 1 784# check read row by row success: 1 785ALTER TABLE t1 COALESCE PARTITION 1; 786SHOW CREATE TABLE t1; 787Table Create Table 788t1 CREATE TABLE `t1` ( 789 `f_int1` int(11) DEFAULT NULL, 790 `f_int2` int(11) DEFAULT NULL, 791 `f_char1` char(20) DEFAULT NULL, 792 `f_char2` char(20) DEFAULT NULL, 793 `f_charbig` varchar(1000) DEFAULT NULL 794) ENGINE=InnoDB DEFAULT CHARSET=latin1 795/*!50100 PARTITION BY KEY (f_int1) 796(PARTITION p0 ENGINE = InnoDB, 797 PARTITION part1 ENGINE = InnoDB) */ 798t1#P#p0.ibd 799t1#P#part1.ibd 800t1.frm 801EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3; 802id select_type table partitions type possible_keys key key_len ref rows filtered Extra 8031 SIMPLE t1 p0 ALL NULL NULL NULL NULL 10 10.00 Using where 804Warnings: 805Warning 1681 'PARTITIONS' is deprecated and will be removed in a future release. 806Note 1003 /* select#1 */ select (count(0) <> 1) AS `COUNT(*) <> 1` from `test`.`t1` where (`test`.`t1`.`f_int1` = 3) 807# check read single success: 1 808# check read all success: 1 809# check read row by row success: 1 810ALTER TABLE t1 COALESCE PARTITION 1; 811SHOW CREATE TABLE t1; 812Table Create Table 813t1 CREATE TABLE `t1` ( 814 `f_int1` int(11) DEFAULT NULL, 815 `f_int2` int(11) DEFAULT NULL, 816 `f_char1` char(20) DEFAULT NULL, 817 `f_char2` char(20) DEFAULT NULL, 818 `f_charbig` varchar(1000) DEFAULT NULL 819) ENGINE=InnoDB DEFAULT CHARSET=latin1 820/*!50100 PARTITION BY KEY (f_int1) 821(PARTITION p0 ENGINE = InnoDB) */ 822t1#P#p0.ibd 823t1.frm 824EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3; 825id select_type table partitions type possible_keys key key_len ref rows filtered Extra 8261 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 10.00 Using where 827Warnings: 828Warning 1681 'PARTITIONS' is deprecated and will be removed in a future release. 829Note 1003 /* select#1 */ select (count(0) <> 1) AS `COUNT(*) <> 1` from `test`.`t1` where (`test`.`t1`.`f_int1` = 3) 830# check read single success: 1 831# check read all success: 1 832# check read row by row success: 1 833# 2.2.5 COALESCE of last partition --> must fail 834ALTER TABLE t1 COALESCE PARTITION 1; 835ERROR HY000: Cannot remove all partitions, use DROP TABLE instead 836# 2.2.6 Remove partitioning 837ALTER TABLE t1 REMOVE PARTITIONING; 838SHOW CREATE TABLE t1; 839Table Create Table 840t1 CREATE TABLE `t1` ( 841 `f_int1` int(11) DEFAULT NULL, 842 `f_int2` int(11) DEFAULT NULL, 843 `f_char1` char(20) DEFAULT NULL, 844 `f_char2` char(20) DEFAULT NULL, 845 `f_charbig` varchar(1000) DEFAULT NULL 846) ENGINE=InnoDB DEFAULT CHARSET=latin1 847t1.frm 848t1.ibd 849EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3; 850id select_type table partitions type possible_keys key key_len ref rows filtered Extra 8511 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 10.00 Using where 852Warnings: 853Warning 1681 'PARTITIONS' is deprecated and will be removed in a future release. 854Note 1003 /* select#1 */ select (count(0) <> 1) AS `COUNT(*) <> 1` from `test`.`t1` where (`test`.`t1`.`f_int1` = 3) 855# check read single success: 1 856# check read all success: 1 857# check read row by row success: 1 858# 2.2.7 Remove partitioning from not partitioned table --> ???? 859ALTER TABLE t1 REMOVE PARTITIONING; 860ERROR HY000: Partition management on a not partitioned table is not possible 861DROP TABLE t1; 862# Attention: There are unused files. 863# Either the DROP TABLE or a preceding ALTER TABLE 864# <alter partitioning> worked incomplete. 865# We found: 866unified filelist 867--- not determined --- 868DROP VIEW IF EXISTS v1; 869DROP TABLE IF EXISTS t1; 870DROP TABLE IF EXISTS t0_aux; 871DROP TABLE IF EXISTS t0_definition; 872DROP TABLE IF EXISTS t0_template; 873