mysqldump --innodb-optimize-keys breaks with foreign keys
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.1 |
Invalid
|
Undecided
|
Unassigned | |||
5.5 |
Triaged
|
Medium
|
Unassigned | |||
5.6 |
Triaged
|
Medium
|
Unassigned | |||
5.7 |
Triaged
|
Medium
|
Unassigned |
Bug Description
With expand_
mysql> show global variables like '%version%';
+------
| Variable_name | Value |
+------
| innodb_version | 5.6.16-rel64.1 |
| protocol_version | 10 |
| slave_type_
| version | 5.6.16-64.1-rel64.1 |
| version_comment | Percona Server with XtraDB (GPL), Release rel64.1, Revision 563 |
| version_
| version_compile_os | Linux |
+------
mysql> show create table tbl1\G
*******
Table: tbl1
Create Table: CREATE TABLE `tbl1` (
`id_primary` int(11) unsigned NOT NULL AUTO_INCREMENT,
`a` int(11) unsigned DEFAULT NULL,
`b` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`id_primary`),
UNIQUE KEY `a_b_tbl2_unique` (`b`,`a`),
KEY `a` (`a`),
CONSTRAINT `tbl1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `tbl2` (`a`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `tbl1_ibfk_2` FOREIGN KEY (`b`) REFERENCES `tbl3` (`b`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
mysql [localhost] {msandbox} (test) > show create table tbl2\G
*******
Table: tbl2
Create Table: CREATE TABLE `tbl2` (
`id_primary` int(11) unsigned NOT NULL AUTO_INCREMENT,
`a` int(11) unsigned NOT NULL,
`id_address` int(11) unsigned NOT NULL,
`old_a` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id_primary`),
UNIQUE KEY `a_unique` (`a`),
KEY `id_address` (`id_address`),
KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
mysql [localhost] {msandbox} (test) > show create table tbl3\G
*******
Table: tbl3
Create Table: CREATE TABLE `tbl3` (
`id_primary` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) unsigned DEFAULT NULL,
`id_transaction` int(11) DEFAULT NULL,
`id_address` int(11) unsigned NOT NULL,
`id_service` varchar(40) NOT NULL DEFAULT '',
`old_b` varchar(40) DEFAULT NULL,
`is_manship` tinyint(1) DEFAULT '0',
PRIMARY KEY (`id_primary`),
UNIQUE KEY `b_unique` (`b`),
KEY `id_service` (`id_service`),
KEY `id_address` (`id_address`),
KEY `id_transaction` (`id_transaction`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
$ /root/download/
$ [root@centos63 msb_ps5_6_16]# ./use test < test56.sql
ERROR 1215 (HY000) at line 53: Cannot add foreign key constraint
This differs with bug#1092189 as in this test case it contains unique keys also on tables.
summary: |
- expand_fast_index_creation and mysqldump + expand_fast_index_creation and mysqldump breaks with foreign keys |
tags: | added: expand-fast-index-creation |
summary: |
- expand_fast_index_creation and mysqldump breaks with foreign keys + mysqldump --innodb-optimize-keys breaks with foreign keys |
tags: |
added: mysqldump-opt-keys removed: expand-fast-index-creation |
I was able to reproduce the same issue with above scenario. So the problem/bug is,
if we'll take dump of tables which contains foreign key by using --innodb- optimize- keys option then while reloading it, will give error that "Cannot add foreign key constraint". Without that option, we are able to dump and reload the same tables.
[root@centos65 ~]# mysqldump --innodb- optimize- keys test > test56.sql
[root@centos65 ~]#
[root@centos65 ~]# mysql nil < test56.sql
ERROR 1215 (HY000) at line 52: Cannot add foreign key constraint
[root@centos65 ~]# mysqldump test > test561.sql
[root@centos65 ~]#
[root@centos65 ~]# mysql nil < test561.sql
[root@centos65 ~]#
Also, as per doc, "mysqldump –innodb- optimize- keys ignores foreign keys because InnoDB requires a full table rebuild on foreign key changes. So adding them back with a separate ALTER TABLE after restoring the data from a dump would actually make the restore slower " www.percona. com/doc/ percona- server/ 5.5/management/ innodb_ expanded_ fast_index_ creation. html
http://
But, I don't know how it ignores foreign keys because as per dump, we can see them with CREATE TABLE statement.
DROP TABLE IF EXISTS `tbl1`; set_client */; set_client = utf8 */; set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client = @@character_
/*!40101 SET character_
CREATE TABLE `tbl1` (
`id_primary` int(11) unsigned NOT NULL AUTO_INCREMENT,
`a` int(11) unsigned DEFAULT NULL,
`b` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`id_primary`),
CONSTRAINT `tbl1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `tbl2` (`a`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `tbl1_ibfk_2` FOREIGN KEY (`b`) REFERENCES `tbl3` (`b`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*!40101 SET character_
--
-- Dumping data for table `tbl1`
--
LOCK TABLES `tbl1` WRITE;
/*!40000 ALTER TABLE `tbl1` DISABLE KEYS */;
ALTER TABLE `tbl1` ADD UNIQUE KEY `a_b_tbl2_unique` (`b`,`a`), ADD KEY `a` (`a`);
/*!40000 ALTER TABLE `tbl1` ENABLE KEYS */;
UNLOCK TABLES;
This might be a bug too.