mysqldump –innodb-optimize-keys confusion
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-
Let assume we have the table:
CREATE TABLE `test` (
`CountryId` int(11) NOT NULL,
`CurrencyId` int(11) NOT NULL,
PRIMARY KEY (`CountryId`
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-
$ 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://
First it says:
---
A new option, --innodb-
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-
---
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.
Related branches
- Laurynas Biveinis (community): Approve
-
Diff: 101 lines (+32/-7)3 files modifieddoc/source/index.rst (+2/-2)
doc/source/management/innodb_buffer_pool_shm.rst (+23/-0)
doc/source/management/innodb_expanded_fast_index_creation.rst (+7/-5)
- Laurynas Biveinis (community): Approve
-
Diff: 115 lines (+4/-28)2 files modifieddoc/source/management/innodb_buffer_pool_shm.rst (+0/-26)
doc/source/management/innodb_expanded_fast_index_creation.rst (+4/-2)
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).