1# 2# Bug#11766879/Bug#60106: DIFF BETWEEN # OF INDEXES IN MYSQL VS INNODB, 3# PARTITONING, ON INDEX CREATE 4# Bug#12696518: MEMORY LEAKS IN HA_PARTITION (VALGRIND TESTS ON TRUNK) 5# 6CREATE TABLE t1 ( 7id bigint NOT NULL AUTO_INCREMENT, 8time date, 9id2 bigint not null, 10PRIMARY KEY (id,time) 11) ENGINE=InnoDB DEFAULT CHARSET=utf8 12/*!50100 PARTITION BY RANGE(TO_DAYS(time)) 13(PARTITION p10 VALUES LESS THAN (734708) ENGINE = InnoDB, 14PARTITION p20 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */; 15INSERT INTO t1 (time,id2) VALUES ('2011-07-24',1); 16INSERT INTO t1 (time,id2) VALUES ('2011-07-25',1); 17INSERT INTO t1 (time,id2) VALUES ('2011-07-25',1); 18CREATE UNIQUE INDEX uk_time_id2 on t1(time,id2); 19ERROR 23000: Duplicate entry '2011-07-25-1' for key 'uk_time_id2' 20SELECT COUNT(*) FROM t1; 21COUNT(*) 223 23SHOW CREATE TABLE t1; 24Table Create Table 25t1 CREATE TABLE `t1` ( 26 `id` bigint(20) NOT NULL AUTO_INCREMENT, 27 `time` date NOT NULL DEFAULT '0000-00-00', 28 `id2` bigint(20) NOT NULL, 29 PRIMARY KEY (`id`,`time`) 30) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 31/*!50100 PARTITION BY RANGE (TO_DAYS(time)) 32(PARTITION p10 VALUES LESS THAN (734708) ENGINE = InnoDB, 33 PARTITION p20 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 34DROP TABLE t1; 35call mtr.add_suppression("nnoDB: Error: table `test`.`t1` .* Partition.* InnoDB internal"); 36# 37# Bug#55091: Server crashes on ADD PARTITION after a failed attempt 38# 39SET @old_innodb_file_format = @@global.innodb_file_format; 40SET @old_innodb_file_per_table = @@global.innodb_file_per_table; 41SET @old_innodb_strict_mode = @@global.innodb_strict_mode; 42SET @@global.innodb_file_format = Barracuda, 43@@global.innodb_file_per_table = ON, 44@@global.innodb_strict_mode = ON; 45# Connection con1 46CREATE TABLE t1 (id INT NOT NULL 47PRIMARY KEY, 48user_num CHAR(10) 49) ENGINE = InnoDB 50KEY_BLOCK_SIZE=4 51PARTITION BY HASH(id) PARTITIONS 1; 52t1#P#p0.ibd 53t1.frm 54t1.par 55SHOW CREATE TABLE t1; 56Table Create Table 57t1 CREATE TABLE `t1` ( 58 `id` int(11) NOT NULL, 59 `user_num` char(10) DEFAULT NULL, 60 PRIMARY KEY (`id`) 61) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=4 62/*!50100 PARTITION BY HASH (id) 63PARTITIONS 1 */ 64SET GLOBAL innodb_file_per_table = OFF; 65# Connection con2 66LOCK TABLE t1 WRITE; 67# ALTER fails because COMPRESSED/KEY_BLOCK_SIZE 68# are incompatible with innodb_file_per_table = OFF; 69ALTER TABLE t1 ADD PARTITION PARTITIONS 1; 70ERROR HY000: Table storage engine for 't1' doesn't have this option 71t1#P#p0.ibd 72t1.frm 73t1.par 74# This SET is not needed to reproduce the bug, 75# it is here just to make the test case more realistic 76SET innodb_strict_mode = OFF; 77ALTER TABLE t1 ADD PARTITION PARTITIONS 2; 78Warnings: 79Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. 80Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4. 81Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. 82Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4. 83Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. 84Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4. 85t1.frm 86t1.par 87ALTER TABLE t1 REBUILD PARTITION p0; 88Warnings: 89Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. 90Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4. 91UNLOCK TABLES; 92SHOW CREATE TABLE t1; 93Table Create Table 94t1 CREATE TABLE `t1` ( 95 `id` int(11) NOT NULL, 96 `user_num` char(10) DEFAULT NULL, 97 PRIMARY KEY (`id`) 98) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=4 99/*!50100 PARTITION BY HASH (id) 100PARTITIONS 3 */ 101DROP TABLE t1; 102# Connection default 103SET @@global.innodb_strict_mode = @old_innodb_strict_mode; 104SET @@global.innodb_file_format = @old_innodb_file_format; 105SET @@global.innodb_file_per_table = @old_innodb_file_per_table; 106