mysqldump --innodb-optimize-keys breaks with foreign keys

[Muhammad Irfan]

With expand_fast_index_creation it fails to restore. It errors "Cannot add foreign key constraint".

mysql> show global variables like '%version%';
+-------------------------+-----------------------------------------------------------------+
| Variable_name | Value |
+-------------------------+-----------------------------------------------------------------+
| innodb_version | 5.6.16-rel64.1 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.16-64.1-rel64.1 |
| version_comment | Percona Server with XtraDB (GPL), Release rel64.1, Revision 563 |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+-----------------------------------------------------------------+

mysql> show create table tbl1\G
*************************** 1. row ***************************
       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
*************************** 1. row ***************************
       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
*************************** 1. row ***************************
       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/ps5.6.16/bin/mysqldump -umsandbox -pmsandbox -S /tmp/mysql_sandbox5616.sock --innodb-optimize-keys test > test56.sql

$ [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.

[Nilnandan Joshi (nilnandan-joshi)]

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 "
http://www.percona.com/doc/percona-server/5.5/management/innodb_expanded_fast_index_creation.html

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`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
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_set_client = @saved_cs_client */;

--
-- 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.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-152