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 ~]#
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.