Percona Server with XtraDB

mysqldump --innodb-optimize-keys --no-data results in no keys

Reported by Bill Karwin on 2012-04-26
10
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Percona Server
Status tracked in 5.6
5.1
Low
Alexey Kopytov
5.5
Low
Alexey Kopytov
5.6
Low
Laurynas Biveinis

Bug Description

Tested with mysqldump from 5.1.61-rel13.2-log Percona Server (GPL), 13.2, Revision 430
Linux: CentOS release 6.2 2.6.32-220.13.1.el6.x86_64

When using mysqldump --innodb-optimize-keys --no-data, all secondary key definitions are lost.

Steps to reproduce:

1. Create an ordinary table with a secondary key and one sample row.

mysql> create table test.foo (id serial primary key, c int, key(c));
mysql> insert into test.foo values (1, 1234);

2. Dump table with standard key optimization.
The keys are defined inside the CREATE TABLE statement.
There is a block of statements for "Dumping data for table `foo`", including LOCK TABLES and INSERT and UNLOCK TABLES.

$ mysqldump test foo > test-foo-1.sql

3. Dump table with standard key optimization, but no data.
The keys are defined inside the CREATE TABLE statement.
The block for "Dumping data" is omitted. No LOCK TABLES, no INSERTs, no UNLOCK TABLES.

$ mysqldump --no-data test foo > test-foo-2.sql

4. Dump table with Percona key optimization, with data.
The secondary key definition is deferred to the "Dumping data" section, between INSERT and UNLOCK TABLES.

$ mysqldump --innodb-optimize-keys test foo > test-foo-3.sql

5. Dump table with Percona key optimization, with no data.
All the secondary key definitions are missing, because they are part of the omitted block for "Dumping data".

$ mysqldump --innodb-optimize-keys --no-data test foo > test-foo-4.sql

You could argue that there is no reason to separate secondary key definition when using --no-data, because it takes virtually no time to create the indexes on empty tables anyway.

But if I want to create empty tables, then use mysqlimport to load data from flat files, then create secondary indexes on the tables to benefit from fast index creation. It would be useful if mysqldump --innodb-optimize-keys --no-data were to output the ALTER TABLE ADD KEY statements, so I could grep them out and run them after importing the data.

Ryan Huddleston (rshuddleston) wrote :

Work around:

mysqldump --innodb-optimize-keys -w "1=0" test foo | grep ^ALTER

summary: - mysqldump --innodb-optimize-keys --no-data results inno keys
+ mysqldump --innodb-optimize-keys --no-data results in no keys
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers