1--source include/have_partition.inc 2--disable_warnings 3DROP TABLE IF EXISTS t1, t2; 4--enable_warnings 5 6# These tests is only useful when running on MyISAM, 7# due to DATA/INDEX directory, non transactional behavior, tests with MyISAM 8# files etc. 9 10let $MYSQLD_DATADIR= `SELECT @@datadir`; 11 12--echo # 13--echo # BUG#11933226 - 60681: CHECKSUM TABLE RETURNS 0 FOR PARTITIONED TABLE 14--echo # 15CREATE TABLE t1 ( 16 i INT 17) 18ENGINE=MyISAM 19PARTITION BY RANGE (i) 20(PARTITION p3 VALUES LESS THAN (3), 21 PARTITION p5 VALUES LESS THAN (5), 22 PARTITION pMax VALUES LESS THAN MAXVALUE); 23INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6); 24CHECKSUM TABLE t1; 25ALTER TABLE t1 CHECKSUM = 1; 26CHECKSUM TABLE t1 EXTENDED; 27--echo # Before patch this returned 0! 28CHECKSUM TABLE t1; 29SHOW CREATE TABLE t1; 30DROP TABLE t1; 31 32--echo # Same test without partitioning 33CREATE TABLE t1 ( 34 i INT 35) ENGINE=MyISAM; 36SHOW CREATE TABLE t1; 37INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6); 38CHECKSUM TABLE t1; 39ALTER TABLE t1 CHECKSUM = 1; 40CHECKSUM TABLE t1 EXTENDED; 41CHECKSUM TABLE t1; 42SHOW CREATE TABLE t1; 43DROP TABLE t1; 44 45# 46# Disabled by WL#946: binary format for timestamp column is not compatible. 47# So the trick with replacing FRM file does not work any more. 48#--echo # 49#--echo # Bug#50036: Inconsistent errors when using TIMESTAMP 50#--echo # columns/expressions 51# 52#--echo # Added test with existing TIMESTAMP partitioning (when it was allowed). 53#CREATE TABLE t1 (a TIMESTAMP) 54#ENGINE = MyISAM 55#PARTITION BY HASH (UNIX_TIMESTAMP(a)); 56#INSERT INTO t1 VALUES ('2000-01-02 03:04:05'); 57#--sorted_result 58#SELECT * FROM t1; 59#FLUSH TABLES; 60#--echo # replacing t1.frm with TO_DAYS(a) which was allowed earlier. 61#--remove_file $MYSQLD_DATADIR/test/t1.frm 62#--copy_file std_data/parts/t1TIMESTAMP.frm $MYSQLD_DATADIR/test/t1.frm 63#--echo # Disable warnings, since the result would differ when running with 64#--echo # --ps-protocol (only for the 'SELECT * FROM t1' statement). 65#--disable_warnings 66#--sorted_result 67#SELECT * FROM t1; 68#--enable_warnings 69#--replace_result MyISAM <curr_engine> InnoDB <curr_engine> 70#SHOW CREATE TABLE t1; 71#INSERT INTO t1 VALUES ('2001-02-03 04:05:06'); 72#--sorted_result 73#SELECT * FROM t1; 74#SELECT a, hex(weight_string(a)) FROM t1; 75#ALTER TABLE t1 ADD PARTITION PARTITIONS 2; 76#--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR 77#ALTER TABLE t1 78#PARTITION BY RANGE (TO_DAYS(a)) 79#(PARTITION p0 VALUES LESS THAN (10000), 80# PARTITION p1 VALUES LESS THAN (MAXVALUE)); 81#SHOW CREATE TABLE t1; 82#CREATE TABLE t2 LIKE t1; 83#SHOW CREATE TABLE t2; 84#DROP TABLE t2; 85#CREATE TABLE t2 SELECT * FROM t1; 86#DROP TABLE t2; 87#ALTER TABLE t1 PARTITION BY HASH (UNIX_TIMESTAMP(a)); 88#SHOW CREATE TABLE t1; 89#ALTER TABLE t1 ADD PARTITION PARTITIONS 2; 90#SHOW CREATE TABLE t1; 91#--sorted_result 92#SELECT * FROM t1; 93#DROP TABLE t1; 94 95--echo # 96--echo # Bug#31931: Mix of handlers error message 97--echo # 98--error ER_MIX_HANDLER_ERROR 99CREATE TABLE t1 (a INT) 100PARTITION BY HASH (a) 101( PARTITION p0 ENGINE=MyISAM, 102 PARTITION p1); 103--error ER_MIX_HANDLER_ERROR 104CREATE TABLE t1 (a INT) 105PARTITION BY LIST (a) 106SUBPARTITION BY HASH (a) 107( PARTITION p0 VALUES IN (0) 108( SUBPARTITION s0, SUBPARTITION s1 ENGINE=MyISAM, SUBPARTITION s2), 109 PARTITION p1 VALUES IN (1) 110( SUBPARTITION s3 ENGINE=MyISAM, SUBPARTITION s4, SUBPARTITION s5 ENGINE=MyISAM)); 111 112--echo # 113--echo # Bug#49161: Out of memory; restart server and try again (needed 2 bytes) 114--echo # 115CREATE TABLE t1 (a INT) 116ENGINE = MyISAM 117PARTITION BY HASH (a); 118FLUSH TABLES; 119--remove_file $MYSQLD_DATADIR/test/t1.par 120--replace_result $MYSQLD_DATADIR ./ 121CHECK TABLE t1; 122--error ER_FAILED_READ_FROM_PAR_FILE 123SELECT * FROM t1; 124--replace_result $MYSQLD_DATADIR ./ 125DROP TABLE t1; 126--remove_file $MYSQLD_DATADIR/test/t1#P#p0.MYI 127--remove_file $MYSQLD_DATADIR/test/t1#P#p0.MYD 128 129--echo # 130--echo # Bug#50392: insert_id is not reset for partitioned tables 131--echo # auto_increment on duplicate entry 132CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY) 133ENGINE = MyISAM; 134SET INSERT_ID= 13; 135INSERT INTO t1 VALUES (NULL); 136SET INSERT_ID= 12; 137--echo # For transactional engines, 12 will not be inserted, since the failing 138--echo # statement is rolled back. 139--error ER_DUP_ENTRY 140INSERT INTO t1 VALUES (NULL), (NULL), (NULL); 141SHOW CREATE TABLE t1; 142INSERT INTO t1 VALUES (NULL); 143--echo # NOTE: 12 exists only in non transactional engines! 144SELECT * FROM t1; 145DROP TABLE t1; 146CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY) 147ENGINE = MyISAM 148PARTITION BY KEY(a); 149SET INSERT_ID= 13; 150INSERT INTO t1 VALUES (NULL); 151SET INSERT_ID= 12; 152--error ER_DUP_ENTRY 153INSERT INTO t1 VALUES (NULL), (NULL), (NULL); 154SHOW CREATE TABLE t1; 155INSERT INTO t1 VALUES (NULL); 156SELECT * FROM t1; 157DROP TABLE t1; 158# 159# Bug#30102: rename table does corrupt tables with partition files on failure 160# 161--echo # Bug#30102 test 162CREATE TABLE t1 (a INT) 163ENGINE = MyISAM 164PARTITION BY RANGE (a) 165(PARTITION p0 VALUES LESS THAN (6), 166 PARTITION `p1....................` VALUES LESS THAN (9), 167 PARTITION p2 VALUES LESS THAN MAXVALUE); 168# partition p1 is 't1#P#p1' + @002e * 20 = 107 characters + file ending 169# total path lenght of './test/t1#P#p1@002e@002e<...>@002e.MY[ID]' is 118 chars 170--echo # List of files in database `test`, all original t1-files here 171--list_files $MYSQLD_DATADIR/test t1* 172INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); 173--echo # Renaming to a file name where the first partition is 250 chars 174--echo # and the second partition is 350 chars 175# 7,7 avoids the error message, which is not deterministic. 176--error 7,7 177RENAME TABLE t1 TO `t2_new..............................................end`; 178# 1234567890123456789012345678901234567890123456 179--echo # List of files in database `test`, should not be any t2-files here 180--list_files $MYSQLD_DATADIR/test t2* 181--echo # List of files in database `test`, should be all t1-files here 182--list_files $MYSQLD_DATADIR/test t1* 183--sorted_result 184SELECT * FROM t1; 185--echo # List of files in database `test`, should be all t1-files here 186--list_files $MYSQLD_DATADIR/test t1* 187--echo # Renaming to a file name where the first partition is 156 chars 188--echo # and the second partition is 256 chars 189# 7,7 avoids the error message, which is not deterministic. 190--error 7,7 191RENAME TABLE t1 TO `t2_............................_end`; 192# 1234567890123456789012345678 193# 7 + 4 + 5 + 28 * 5 = 16 + 140 = 156 194--echo # List of files in database `test`, should not be any t2-files here 195--list_files $MYSQLD_DATADIR/test t2* 196--echo # List of files in database `test`, should be all t1-files here 197--list_files $MYSQLD_DATADIR/test t1* 198--sorted_result 199SELECT * FROM t1; 200DROP TABLE t1; 201--echo # Should not be any files left here 202--list_files $MYSQLD_DATADIR/test t1* 203--list_files $MYSQLD_DATADIR/test t2* 204--echo # End of bug#30102 test. 205 206--echo # Test of post-push fix for bug#11766249/59316 207CREATE TABLE t1 (a INT, b VARCHAR(255), PRIMARY KEY (a)) 208ENGINE = MyISAM 209PARTITION BY RANGE (a) 210(PARTITION p0 VALUES LESS THAN (0) MAX_ROWS=100, 211 PARTITION p1 VALUES LESS THAN (100) MAX_ROWS=100, 212 PARTITION pMax VALUES LESS THAN MAXVALUE); 213INSERT INTO t1 VALUES (1, "Partition p1, first row"); 214DROP TABLE t1; 215 216--echo # 217--echo # MDEV-10418 Assertion `m_extra_cache' failed 218--echo # in ha_partition::late_extra_cache(uint) 219--echo # 220 221CREATE TABLE t1 (f1 INT) ENGINE=MyISAM; 222INSERT INTO t1 VALUES (1),(2); 223 224CREATE TABLE t2 (f2 INT) ENGINE=MyISAM PARTITION BY RANGE(f2) (PARTITION pmax VALUES LESS THAN MAXVALUE); 225INSERT INTO t2 VALUES (8); 226 227CREATE ALGORITHM = MERGE VIEW v AS SELECT f2 FROM t2, t1; 228 229UPDATE v SET f2 = 1; 230 231SELECT * FROM t2; 232 233DROP VIEW v; 234DROP TABLE t2; 235DROP TABLE t1; 236 237--echo # 238--echo # bug#11760213-52599: ALTER TABLE REMOVE PARTITIONING ON NON-PARTITIONED 239--echo # TABLE CORRUPTS MYISAM 240--disable_warnings 241DROP TABLE if exists `t1`; 242--enable_warnings 243CREATE TABLE `t1`(`a` INT)ENGINE=myisam; 244ALTER TABLE `t1` ADD COLUMN `b` INT; 245CREATE UNIQUE INDEX `i1` ON `t1`(`b`); 246CREATE UNIQUE INDEX `i2` ON `t1`(`a`); 247ALTER TABLE `t1` ADD PRIMARY KEY (`a`); 248--error ER_PARTITION_MGMT_ON_NONPARTITIONED 249ALTER TABLE `t1` REMOVE PARTITIONING; 250CHECK TABLE `t1` EXTENDED; 251DROP TABLE t1; 252