Comment 1 for bug 1310617

Revision history for this message
Nilnandan Joshi (nilnandan-joshi) wrote : Re: expand_fast_index_creation and mysqldump

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.