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

Bug #1081003 reported by Alexey Kopytov on 2012-11-20
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Server moved to https://jira.percona.com/projects/PS
Fix Released
Undecided
Alexey Kopytov
5.1
Fix Released
Undecided
Alexey Kopytov
5.5
Fix Released
Undecided
Alexey Kopytov

Bug Description

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.

Related branches

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

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

Other bug subscribers