#
# Bug#11766879/Bug#60106: DIFF BETWEEN # OF INDEXES IN MYSQL VS INNODB,
#                         PARTITONING, ON INDEX CREATE
#
call mtr.add_suppression("contains 2 indexes inside InnoDB, which is different from the number of indexes 1 defined in the MySQL");
CREATE TABLE t1 (
id bigint NOT NULL AUTO_INCREMENT,
time date,
id2 bigint not null,
PRIMARY KEY (id,time)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE(TO_DAYS(time))
(PARTITION p10 VALUES LESS THAN (734708) ENGINE = InnoDB,
PARTITION p20 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;
INSERT INTO t1 (time,id2) VALUES ('2011-07-24',1);
INSERT INTO t1 (time,id2) VALUES ('2011-07-25',1);
INSERT INTO t1 (time,id2) VALUES ('2011-07-25',1);
CREATE UNIQUE INDEX uk_time_id2 on t1(time,id2);
ERROR 23000: Duplicate entry '2011-07-25-1' for key 'uk_time_id2'
SELECT COUNT(*) FROM t1;
COUNT(*)
3
DROP TABLE t1;
call mtr.add_suppression("nnoDB: Error: table `test`.`t1` .* Partition.* InnoDB internal");
#
# Bug#55091: Server crashes on ADD PARTITION after a failed attempt
#
SET @old_innodb_file_format = @@global.innodb_file_format;
SET @old_innodb_file_per_table = @@global.innodb_file_per_table;
SET @old_innodb_strict_mode = @@global.innodb_strict_mode;
SET @@global.innodb_file_format = Barracuda,
@@global.innodb_file_per_table = ON,
@@global.innodb_strict_mode = ON;
# Connection con1
CREATE TABLE t1 (id INT NOT NULL
PRIMARY KEY,
user_num CHAR(10)
) ENGINE = InnoDB
KEY_BLOCK_SIZE=4
PARTITION BY HASH(id) PARTITIONS 1;
t1#P#p0.ibd
t1.frm
t1.par
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `user_num` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=4
/*!50100 PARTITION BY HASH (id)
PARTITIONS 1 */
SET GLOBAL innodb_file_per_table = OFF;
# Connection con2
LOCK TABLE t1 WRITE;
# ALTER fails because COMPRESSED/KEY_BLOCK_SIZE
# are incompatible with innodb_file_per_table = OFF;
ALTER TABLE t1 ADD PARTITION PARTITIONS 1;
ERROR HY000: Got error 1478 from storage engine
t1#P#p0.ibd
t1.frm
t1.par
# This SET is not needed to reproduce the bug,
# it is here just to make the test case more realistic
SET innodb_strict_mode = OFF;
ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
Warnings:
Warning	1478	InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
Warning	1478	InnoDB: ignoring KEY_BLOCK_SIZE=4.
Warning	1478	InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
Warning	1478	InnoDB: ignoring KEY_BLOCK_SIZE=4.
Warning	1478	InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
Warning	1478	InnoDB: ignoring KEY_BLOCK_SIZE=4.
t1.frm
t1.par
ALTER TABLE t1 REBUILD PARTITION p0;
Warnings:
Warning	1478	InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
Warning	1478	InnoDB: ignoring KEY_BLOCK_SIZE=4.
UNLOCK TABLES;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `user_num` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=4
/*!50100 PARTITION BY HASH (id)
PARTITIONS 3 */
DROP TABLE t1;
# Connection default
SET @@global.innodb_strict_mode = @old_innodb_strict_mode;
SET @@global.innodb_file_format = @old_innodb_file_format;
SET @@global.innodb_file_per_table = @old_innodb_file_per_table;
SET NAMES utf8;
CREATE TABLE `t``\""e` (a INT, PRIMARY KEY (a))
ENGINE=InnoDB
PARTITION BY RANGE (a)
SUBPARTITION BY HASH (a)
(PARTITION `p0``\""e` VALUES LESS THAN (100)
(SUBPARTITION `sp0``\""e`,
SUBPARTITION `sp1``\""e`),
PARTITION `p1``\""e` VALUES LESS THAN (MAXVALUE)
(SUBPARTITION `sp2``\""e`,
SUBPARTITION `sp3``\""e`));
INSERT INTO `t``\""e` VALUES (0), (2), (6), (10), (14), (18), (22);
START TRANSACTION;
# con1
SET NAMES utf8;
START TRANSACTION;
# default connection
UPDATE `t``\""e` SET a = 16 WHERE a = 0;
# con1
UPDATE `t``\""e` SET a = 8 WHERE a = 22;
UPDATE `t``\""e` SET a = 12 WHERE a = 0;
# default connection
SELECT lock_table, COUNT(*) FROM INFORMATION_SCHEMA.INNODB_LOCKS
GROUP BY lock_table;
lock_table	COUNT(*)
`test`.`t``\""e` /* Partition `p0``\""e`, Subpartition `sp0``\""e` */	2
set @old_sql_mode = @@sql_mode;
set sql_mode = 'ANSI_QUOTES';
SELECT lock_table, COUNT(*) FROM INFORMATION_SCHEMA.INNODB_LOCKS
GROUP BY lock_table;
lock_table	COUNT(*)
"test"."t`\""""e" /* Partition "p0`\""""e", Subpartition "sp0`\""""e" */	2
set @@sql_mode = @old_sql_mode;
UPDATE `t``\""e` SET a = 4 WHERE a = 22;
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
# First table reported in 'SHOW ENGINE InnoDB STATUS'
SHOW ENGINE InnoDB STATUS;
Type	Name	Status
InnoDB		index `PRIMARY` of table `test`.`t``\""e` /* Partition `p0``\""e`, Subpartition `sp0``\""e` */
set @old_sql_mode = @@sql_mode;
set sql_mode = 'ANSI_QUOTES';
SHOW ENGINE InnoDB STATUS;
Type	Name	Status
InnoDB		index `PRIMARY` of table `test`.`t``\""e` /* Partition `p0``\""e`, Subpartition `sp0``\""e` */
set @@sql_mode = @old_sql_mode;
# con1
ROLLBACK;
# default connection
DROP TABLE `t``\""e`;
SET NAMES DEFAULT;