--innodb-optimize-keys does not work correctly with table without PRIMARY KEY

[Alexey Kopytov]

The mysqldump option --innodb-optimize-keys does not work correctly with tables where the first UNIQUE key on non-nullable columns was picked as the clustered index by InnoDB in the absence of a PRIMARY KEY.

Example:

mysql> CREATE TABLE t1 (a INT NOT NULL, UNIQUE KEY (a)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW COLUMNS FROM t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | int(11) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

Output of mysqldump --innodb-optimize-keys:

CREATE TABLE `t1` (
  `a` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
...
ALTER TABLE `t1` ADD UNIQUE KEY `a` (`a`);

The optimization should detect such tables and keep the unique key in CREATE TABLE.

[Shahriyar Rzayev (rzayev-sehriyar)]

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