Percona Server moved to https://jira.percona.com/projects/PS

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

Bug #989253 reported by Bill Karwin
14
This bug affects 3 people
Affects Status Importance Assigned to Milestone
Percona Server moved to https://jira.percona.com/projects/PS
Fix Released
Low
Laurynas Biveinis
Percona Server moved to https://jira.percona.com/projects/PS 5.6.11-60.3
5.1
Fix Released
Low
Alexey Kopytov
Percona Server moved to https://jira.percona.com/projects/PS 5.1.68-14.6
5.5
Fix Released
Low
Alexey Kopytov
Percona Server moved to https://jira.percona.com/projects/PS 5.5.30-30.2
5.6
Fix Released
Low
Laurynas Biveinis
Percona Server moved to https://jira.percona.com/projects/PS 5.6.11-60.3

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.

Related branches

lp:~akopytov/percona-server/bug989253-5.1
Sergei Glushchenko (community): Approve (g2)
Diff: 170 lines (+95/-23)
3 files modified
Percona-Server/client/mysqldump.c (+38/-23)
Percona-Server/mysql-test/r/percona_mysqldump_innodb_optimize_keys.result (+36/-0)
Percona-Server/mysql-test/t/percona_mysqldump_innodb_optimize_keys.test (+21/-0)
lp:~akopytov/percona-server/bug989253-5.5
Sergei Glushchenko (community): Approve (g2)
Diff: 170 lines (+95/-23)
3 files modified
Percona-Server/client/mysqldump.c (+38/-23)
Percona-Server/mysql-test/r/percona_mysqldump_innodb_optimize_keys.result (+36/-0)
Percona-Server/mysql-test/t/percona_mysqldump_innodb_optimize_keys.test (+21/-0)
lp:~laurynas-biveinis/percona-server/5.6-merge-3
Alexey Kopytov (community): Approve
Percona core: Pending requested
Diff: 19408 lines (+2919/-15492)
66 files modified
Percona-Server/client/mysqldump.c (+35/-4)
Percona-Server/mysql-test/r/create_delayed.result (+3/-0)
Percona-Server/mysql-test/r/percona_bug1127008.result (+28/-0)
Percona-Server/mysql-test/r/percona_innodb_version.result (+1/-1)
Percona-Server/mysql-test/r/percona_mysqldump_innodb_optimize_keys.result (+0/-443)
Percona-Server/mysql-test/suite/innodb/r/percona_bug_1133926.result (+4/-0)
Percona-Server/mysql-test/suite/innodb/t/percona_bug_999147-master.opt (+0/-1)
Percona-Server/mysql-test/suite/sys_vars/r/expand_fast_index_creation_basic.result (+6/-0)
Percona-Server/mysql-test/suite/sys_vars/r/innodb_corrupt_table_action_basic.result (+49/-0)
Percona-Server/mysql-test/t/create_delayed.test (+34/-0)
Percona-Server/mysql-test/t/percona_bug1127008.test (+28/-0)
Percona-Server/mysql-test/t/percona_innodb_version.test (+1/-1)
Percona-Server/mysql-test/t/percona_mysqldump_innodb_optimize_keys.test (+0/-227)
Percona-Server/scripts/fill_help_tables.sql (+2177/-9)
Percona-Server/sql/mdl.cc (+5/-1)
Percona-Server/sql/sql_base.cc (+92/-16)
Percona-Server/sql/sql_base.h (+2/-1)
Percona-Server/sql/sql_db.cc (+1/-1)
Percona-Server/sql/sql_insert.cc (+4/-0)
Percona-Server/sql/sql_parse.cc (+8/-1)
Percona-Server/sql/sql_table.cc (+5/-1)
Percona-Server/storage/innobase/btr/btr0btr.cc (+19/-23)
Percona-Server/storage/innobase/btr/btr0cur.cc (+52/-61)
Percona-Server/storage/innobase/btr/btr0pcur.cc (+6/-7)
Percona-Server/storage/innobase/btr/btr0sea.cc (+1/-4)
Percona-Server/storage/innobase/buf/buf0buf.cc (+31/-16)
Percona-Server/storage/innobase/buf/buf0rea.cc (+2/-4)
Percona-Server/storage/innobase/fil/fil0fil.cc (+26/-32)
Percona-Server/storage/innobase/fsp/fsp0fsp.cc (+15/-33)
Percona-Server/storage/innobase/handler/ha_innodb.cc (+4/-2)
Percona-Server/storage/innobase/include/btr0btr.ic (+1/-1)
Percona-Server/storage/innobase/include/buf0buf.ic (+1/-7)
Percona-Server/storage/innobase/include/fut0fut.ic (+1/-4)
Percona-Server/storage/innobase/include/srv0srv.h (+16/-0)
Percona-Server/storage/innobase/row/row0ins.cc (+5/-4)
Percona-Server/storage/innobase/row/row0merge.cc (+3/-2)
Percona-Server/storage/innobase/row/row0sel.cc (+6/-5)
build/build-binary.sh (+38/-1)
build/debian/additions/innotop/InnoDBParser.pm (+0/-1089)
build/debian/additions/innotop/changelog.innotop (+0/-318)
build/debian/additions/innotop/innotop (+0/-9485)
build/debian/additions/innotop/innotop.1 (+0/-2086)
build/debian/percona-server-client-5.6.dirs (+0/-1)
build/debian/percona-server-client-5.6.docs (+0/-1)
build/debian/percona-server-client-5.6.install (+0/-3)
build/debian/percona-server-client-5.6.lintian-overrides (+0/-2)
build/debian/percona-server-server-5.6.postinst (+9/-1)
build/percona-server.spec (+26/-27)
build/percona-shared-compat.spec (+4/-0)
doc/Makefile (+0/-145)
doc/source/diagnostics/innodb_deadlock_count.rst (+0/-57)
doc/source/diagnostics/innodb_show_lock_names.rst (+0/-24)
doc/source/diagnostics/innodb_show_status.rst (+0/-475)
doc/source/diagnostics/misc_info_schema_tables.rst (+0/-48)
doc/source/diagnostics/mysql_syslog.rst (+0/-43)
doc/source/diagnostics/process_list.rst (+0/-53)
doc/source/diagnostics/show_engines.rst (+0/-40)
doc/source/diagnostics/slow_extended_55.rst (+0/-377)
doc/source/diagnostics/thread_based_profiling.rst (+0/-17)
doc/source/diagnostics/user_stats.rst (+0/-242)
doc/source/installation/apt_repo.rst (+65/-0)
doc/source/installation/yum_repo.rst (+81/-0)
doc/source/release-notes/Percona-Server-5.6.5-60.0.rst (+0/-26)
doc/source/release-notes/Percona-Server-5.6.6-60.1.rst (+0/-18)
doc/source/release-notes/release-notes_index.rst (+11/-0)
doc/source/upstream-bug-fixes.rst (+13/-1)
Revision history for this message
Ryan Huddleston (rshuddleston) wrote :

Work around:

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

Raghavendra D Prabhu (raghavendra-prabhu)
summary: - mysqldump --innodb-optimize-keys --no-data results inno keys
+ mysqldump --innodb-optimize-keys --no-data results in no keys
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-1912

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

Other bug subscribers

Remote bug watches

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