Percona Server with XtraDB

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

Reported by Alexey Kopytov on 2011-09-16
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Server
Undecided
Alexey Kopytov
5.1
Undecided
Alexey Kopytov
5.5
Undecided
Alexey Kopytov
Rnt-5.1
Undecided
Alexey Kopytov

Bug Description

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.

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

Other bug subscribers