mysqldump --innodb-optimize-keys --no-data results in no keys
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Server moved to https://jira.percona.com/projects/PS |
Fix Released
|
Low
|
Laurynas Biveinis | ||
5.1 |
Fix Released
|
Low
|
Alexey Kopytov | ||
5.5 |
Fix Released
|
Low
|
Alexey Kopytov | ||
5.6 |
Fix Released
|
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-
When using mysqldump --innodb-
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-
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-
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-
Related branches
- Sergei Glushchenko (community): Approve (g2)
-
Diff: 170 lines (+95/-23)3 files modifiedPercona-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)
- Sergei Glushchenko (community): Approve (g2)
-
Diff: 170 lines (+95/-23)3 files modifiedPercona-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)
- Alexey Kopytov (community): Approve
- Percona core: Pending requested
-
Diff: 19408 lines (+2919/-15492)66 files modifiedPercona-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)
summary: |
- mysqldump --innodb-optimize-keys --no-data results inno keys + mysqldump --innodb-optimize-keys --no-data results in no keys |
Work around:
mysqldump --innodb- optimize- keys -w "1=0" test foo | grep ^ALTER