Percona Server with XtraDB

mysqldump --innodb-optimize-keys may produce invalid SQL with explicitly named FK constraints

Reported by Alexey Kopytov on 2012-11-20
30
This bug affects 6 people
Affects Status Importance Assigned to Milestone
Percona Server
Status tracked in 5.6
5.1
High
Alexey Kopytov
5.5
High
Alexey Kopytov
5.6
High
Laurynas Biveinis

Bug Description

Given the following table:

CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  CONSTRAINT `a` FOREIGN KEY (`a`) REFERENCES `t2` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysqldump --innodb-optimize-keys generates the following SQL:

CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `a` FOREIGN KEY (`a`) REFERENCES `t2` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

LOCK TABLES `t2` WRITE;
/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
ALTER TABLE `t2` ADD KEY `a` (`a`);
/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
UNLOCK TABLES;

which fails on import when trying to execute ALTER TABLE with:

ERROR 1280 (42000): Incorrect index name 'a'

The key detail here is that the table definition contains an explicitly named FK constrain, and its name matches the foreign key name.

Alexey Kopytov (akopytov) wrote :

See also bug #1092189.

Ryan Huddleston (rshuddleston) wrote :

Note until this is fixed you should not use --innodb-optimize-keys on tables with foreign keys. Otherwise it will duplicate your keys the first time it's dumped restored and result in the above error the second time it's dumped restored.

tags: added: rdba
Stewart Smith (stewart) wrote :

Merged into 5.1.68-14.6 release branch (before trunk, which is why I'm marking it Fix Released now)

Stewart Smith (stewart) wrote :

setting as fixed Released as merged into 5.5.30-30.2 release branch (before trunk, as Jenkins has backlog)

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers