mysqldump --innodb-optimize-keys handles AUTO_INCREMENT columns inefficiently

[Alexey Kopytov]

When mysqldump --innodb-optimize-keys encounters an AUTO_INCREMENT column in SHOW CREATE TABLE, it correctly marks that column, so that when subsequent key specifications involving that column are not optimized away, as MySQL requires AUTO_INCREMENT columns to be indexed, and removing them from CREATE TABLE would result in an invalid SQL.

However, it avoids optimizing all keys indexing that column from , even though it would be sufficient to avoid optimization just for the first one.

For example: the following table definition:

CREATE TABLE t2 (
       id INT NOT NULL AUTO_INCREMENT,
       uid INT NOT NULL,
       PRIMARY KEY (id),
       KEY k1 (id),
       KEY k2 (id)
) ENGINE=InnoDB;

results in the following output by mysqldump --innodb-optimize-keys:

DROP TABLE IF EXISTS `t2`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `k1` (`id`),
  KEY `k2` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

LOCK TABLES `t2` WRITE;
/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
UNLOCK TABLES;

Obviously, creating the table with just the primary key would be sufficient, and keys 'k1' and 'k2' can be created after loading the data.

[Shahriyar Rzayev (rzayev-sehriyar)]

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