mysqldump –innodb-optimize-keys confusion

Bug #1092189 reported by Roman Vynar
14
This bug affects 3 people
Affects Status Importance Assigned to Milestone
Percona Server moved to https://jira.percona.com/projects/PS
Fix Released
Medium
Hrvoje Matijakovic
5.1
Fix Released
Medium
Hrvoje Matijakovic
5.5
Fix Released
Medium
Hrvoje Matijakovic

Bug Description

Mysql version: Percona-Server-server-55-5.5.28-rel29.1.335.rhel6.x86_64

Let assume we have the table:
CREATE TABLE `test` (
  `CountryId` int(11) NOT NULL,
  `CurrencyId` int(11) NOT NULL,
  PRIMARY KEY (`CountryId`,`CurrencyId`),
  KEY `FK_test_Currency` (`CurrencyId`),
  CONSTRAINT `FK_test_Country` FOREIGN KEY (`CountryId`) REFERENCES `Country` (`CountryId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_test_Currency` FOREIGN KEY (`CurrencyId`) REFERENCES `Currency` (`CurrencyId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1

$ mysqldump foo test > /tmp/test.dump
$ mysql foo < /tmp/test.dump
$ mysqldump foo test --innodb-optimize-keys > /tmp/CountryCurrency.dump2
$ mysql foo < /tmp/test.dump2
ERROR 1280 (42000) at line 40: Incorrect index name 'FK_test_Currency'

Basically the problem is that a foreign key will automatically create a KEY implicitly if one isn't defined so you can't really so if you add the key later it will already exists. I'm guessing you should disable the feature on tables with foreign keys or at least on those columns that are part of the foreign key.

Also the documentation conflicts with itself here:
http://www.percona.com/doc/percona-server/5.5/management/innodb_expanded_fast_index_creation.html

First it says:
---
A new option, --innodb-optimize-keys, was implemented in mysqldump. It changes the way InnoDB tables are dumped, so that secondary and foreign keys are created after loading the data, thus taking advantage of fast index creation. More specifically:

KEY, UNIQUE KEY, and CONSTRAINT clauses are omitted from CREATE TABLE statements corresponding to InnoDB tables.
An additional ALTER TABLE is issued after dumping the data, in order to create the previously omitted keys.
---

Then later it says:
---
mysqldump –innodb-optimize-keys ignores foreign keys because InnoDB requires a full table rebuild on foreign key changes. So adding them back with a separate ALTER TABLE after restoring the data from a dump would actually make the restore slower;
---

Notice if you leave off KEY `FK_test_Currency` (`CurrencyId`), line when creating it automatically re-creates.
If the KEY name was different it wouldn't error but in that case you would end up with a duplicate index.

Please correct the documentation confusion and investigate the problem with incorrect indexes after the optimization.

Tags: mysqldump

Related branches

Revision history for this message
Alexey Kopytov (akopytov) wrote :

The first part of the report about the incorrect index name error on import is a duplicate of bug #1081016.

Let's keep this one as a request to fix documentation. It should say " It changes the way InnoDB tables are dumped, so that secondary are created after loading the data, thus taking advantage of fast index creation" (i.e. remove foreign keys from that statement).

Changed in percona-server:
status: New → Triaged
importance: Undecided → Medium
assignee: nobody → Hrvoje Matijakovic (hrvojem)
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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

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

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.