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

mysqldump --innodb-optimize-keys may produce invalid SQL with explicitly named FK constraints

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

Bug Description

Given the following table:

CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  CONSTRAINT `a` FOREIGN KEY (`a`) REFERENCES `t2` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysqldump --innodb-optimize-keys generates the following SQL:

CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `a` FOREIGN KEY (`a`) REFERENCES `t2` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

LOCK TABLES `t2` WRITE;
/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
ALTER TABLE `t2` ADD KEY `a` (`a`);
/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
UNLOCK TABLES;

which fails on import when trying to execute ALTER TABLE with:

ERROR 1280 (42000): Incorrect index name 'a'

The key detail here is that the table definition contains an explicitly named FK constrain, and its name matches the foreign key name.

Tags: rdba

Related branches

lp://staging/~akopytov/percona-server/RDBA-bug1081016-5.1
Alexey Kopytov (community): Needs Fixing
Stewart Smith (community): Approve
Diff: 302 lines (+196/-22)
4 files modified
Percona-Server/client/mysqldump.c (+121/-21)
Percona-Server/configure.in (+1/-1)
Percona-Server/mysql-test/r/percona_mysqldump_innodb_optimize_keys.result (+47/-0)
Percona-Server/mysql-test/t/percona_mysqldump_innodb_optimize_keys.test (+27/-0)
lp://staging/~akopytov/percona-server/RDBA-bug1081016-5.5
Alexey Kopytov (community): Needs Fixing
Stewart Smith (community): Needs Fixing
Diff: 7667 lines (+6948/-183) (has conflicts)
31 files modified
Makefile (+7/-0)
Percona-Server/client/mysql.cc (+32/-0)
Percona-Server/client/mysqldump.c (+126/-22)
Percona-Server/mysql-test/r/percona_mysqldump_innodb_optimize_keys.result (+122/-36)
Percona-Server/mysql-test/r/percona_server_variables_debug.result (+8/-0)
Percona-Server/mysql-test/r/percona_server_variables_release.result (+8/-0)
Percona-Server/mysql-test/suite/innodb/r/percona_changed_page_bmp.result (+4/-0)
Percona-Server/mysql-test/suite/innodb/t/percona_changed_page_bmp-master.opt (+4/-0)
Percona-Server/mysql-test/suite/innodb/t/percona_changed_page_bmp.test (+36/-0)
Percona-Server/mysql-test/t/percona_mysqldump_innodb_optimize_keys.test (+72/-21)
Percona-Server/sql-common/client.c (+28/-0)
Percona-Server/sql/sys_vars.cc (+3722/-0)
Percona-Server/sql/threadpool.h (+72/-0)
Percona-Server/sql/threadpool_common.cc (+277/-0)
Percona-Server/sql/threadpool_unix.cc (+1672/-0)
Percona-Server/storage/innobase/buf/buf0buf.c (+137/-26)
Percona-Server/storage/innobase/buf/buf0lru.c (+118/-40)
Percona-Server/storage/innobase/fil/fil0fil.c (+106/-32)
Percona-Server/storage/innobase/handler/ha_innodb.cc (+1/-0)
Percona-Server/storage/innobase/include/buf0buf.h (+8/-2)
Percona-Server/storage/innobase/include/buf0flu.ic (+7/-0)
Percona-Server/storage/innobase/include/buf0lru.h (+18/-4)
Percona-Server/storage/innobase/include/mtr0mtr.h (+5/-0)
Percona-Server/storage/innobase/mtr/mtr0mtr.c (+51/-0)
UDF/configure.ac (+13/-0)
build/debian/rules (+9/-0)
build/percona-server.spec (+31/-0)
build/percona-shared-compat.spec (+4/-0)
doc/source/management/innodb_kill_idle_trx.rst (+25/-0)
doc/source/performance/handlersocket.rst (+109/-0)
doc/source/performance/threadpool.rst (+116/-0)
lp://staging/~laurynas-biveinis/percona-server/5.6-merge-4
Stewart Smith (community): Approve
Diff: 28652 lines (+8737/-18610)
172 files modified
COPYING.innodb-deadlock-count-patch (+0/-30)
COPYING.show_temp_51 (+13/-0)
Makefile (+2/-2)
Percona-Server/client/mysqldump.c (+121/-21)
Percona-Server/include/errmsg.h (+1/-0)
Percona-Server/libmysql/errmsg.c (+1/-0)
Percona-Server/mysql-test/include/have_pool_of_threads.inc (+0/-4)
Percona-Server/mysql-test/include/log_grep.inc (+0/-41)
Percona-Server/mysql-test/include/percona_query_cache_with_comments.inc (+95/-0)
Percona-Server/mysql-test/include/percona_query_cache_with_comments_begin.inc (+0/-12)
Percona-Server/mysql-test/include/percona_query_cache_with_comments_clear.inc (+5/-0)
Percona-Server/mysql-test/include/percona_query_cache_with_comments_eval.inc (+7/-0)
Percona-Server/mysql-test/include/percona_show_slave_status_nolock.inc (+0/-56)
Percona-Server/mysql-test/r/have_pool_of_threads.require (+2/-0)
Percona-Server/mysql-test/r/percona_bug1170103.result (+19/-0)
Percona-Server/mysql-test/r/percona_bug933969.result (+0/-16)
Percona-Server/mysql-test/r/percona_innodb_deadlock_count.result (+28/-0)
Percona-Server/mysql-test/r/percona_log_slow_admin_statements-config.result (+0/-6)
Percona-Server/mysql-test/r/percona_log_slow_admin_statements-config_foo.result (+7/-0)
Percona-Server/mysql-test/r/percona_log_slow_admin_statements-config_true.result (+0/-6)
Percona-Server/mysql-test/r/percona_log_slow_filter-cl.result (+0/-6)
Percona-Server/mysql-test/r/percona_min_examined_row_limit.result (+0/-35)
Percona-Server/mysql-test/r/percona_mysqldump_innodb_optimize_keys.result (+47/-0)
Percona-Server/mysql-test/r/percona_query_cache_with_comments_crash.result (+21/-0)
Percona-Server/mysql-test/r/percona_query_cache_with_comments_prepared_statements.result (+0/-396)
Percona-Server/mysql-test/r/percona_status_wait_query_cache_mutex.result (+20/-0)
Percona-Server/mysql-test/r/pool_of_threads.result (+2169/-0)
Percona-Server/mysql-test/r/pool_of_threads_high_prio_tickets.result (+2169/-0)
Percona-Server/mysql-test/suite/binlog/r/percona_bug1162085.result (+6/-0)
Percona-Server/mysql-test/suite/binlog/t/percona_bug1162085.test (+30/-0)
Percona-Server/mysql-test/suite/innodb/r/percona_changed_page_bmp.result (+8/-3)
Percona-Server/mysql-test/suite/innodb/t/percona_changed_page_bmp-master.opt (+1/-1)
Percona-Server/mysql-test/suite/innodb/t/percona_changed_page_bmp.test (+16/-3)
Percona-Server/mysql-test/suite/rpl/r/percona_bug860910.result (+0/-23)
Percona-Server/mysql-test/suite/rpl/t/percona_bug860910.test (+0/-38)
Percona-Server/mysql-test/suite/sys_vars/r/extra_max_connections_basic.result (+43/-0)
Percona-Server/mysql-test/suite/sys_vars/r/extra_port_basic.result (+21/-0)
Percona-Server/mysql-test/suite/sys_vars/r/query_exec_id_basic.result (+2/-0)
Percona-Server/mysql-test/suite/sys_vars/r/thread_pool_high_prio_tickets_basic.result (+47/-0)
Percona-Server/mysql-test/suite/sys_vars/r/thread_pool_idle_timeout_basic.result (+47/-0)
Percona-Server/mysql-test/suite/sys_vars/r/thread_pool_max_threads_basic.result (+47/-0)
Percona-Server/mysql-test/suite/sys_vars/r/thread_pool_min_threads_basic.result (+49/-0)
Percona-Server/mysql-test/suite/sys_vars/r/thread_pool_oversubscribe_basic.result (+47/-0)
Percona-Server/mysql-test/suite/sys_vars/r/thread_pool_stall_limit_basic.result (+47/-0)
Percona-Server/mysql-test/suite/sys_vars/t/extra_max_connections_basic.test (+0/-42)
Percona-Server/mysql-test/suite/sys_vars/t/query_exec_id_basic.test (+0/-4)
Percona-Server/mysql-test/suite/sys_vars/t/thread_pool_high_prio_tickets_basic.test (+43/-0)
Percona-Server/mysql-test/suite/sys_vars/t/thread_pool_idle_timeout_basic.test (+0/-43)
Percona-Server/mysql-test/suite/sys_vars/t/thread_pool_min_threads_basic.test (+0/-43)
Percona-Server/mysql-test/suite/sys_vars/t/thread_pool_oversubscribe_basic.test (+0/-43)
Percona-Server/mysql-test/suite/sys_vars/t/thread_pool_size_basic.test (+47/-0)
Percona-Server/mysql-test/suite/sys_vars/t/thread_pool_stall_limit_basic.test (+0/-43)
Percona-Server/mysql-test/t/percona_bug1170103-master.opt (+1/-0)
Percona-Server/mysql-test/t/percona_bug1170103.test (+23/-0)
Percona-Server/mysql-test/t/percona_bug933969.test (+0/-42)
Percona-Server/mysql-test/t/percona_log_slow_admin_statements-config_false.test (+2/-0)
Percona-Server/mysql-test/t/percona_log_slow_admin_statements-config_foo.test (+3/-0)
Percona-Server/mysql-test/t/percona_log_slow_admin_statements-config_true.cnf (+4/-0)
Percona-Server/mysql-test/t/percona_log_slow_slave_statements-cl-master.opt (+0/-1)
Percona-Server/mysql-test/t/percona_log_slow_sp_statements-cl-master.opt (+0/-1)
Percona-Server/mysql-test/t/percona_log_slow_sp_statements-cl.test (+2/-0)
Percona-Server/mysql-test/t/percona_log_slow_verbosity-cl-master.opt (+0/-1)
Percona-Server/mysql-test/t/percona_log_slow_verbosity-cl.test (+0/-2)
Percona-Server/mysql-test/t/percona_mysqldump_innodb_optimize_keys.test (+27/-0)
Percona-Server/mysql-test/t/percona_query_cache_with_comments_prepared_statements.test (+0/-208)
Percona-Server/mysql-test/t/percona_status_wait_query_cache_mutex.test (+0/-35)
Percona-Server/mysql-test/t/percona_xtradb_bug317074.test (+47/-0)
Percona-Server/mysql-test/t/pool_of_threads.cnf (+15/-0)
Percona-Server/mysql-test/t/pool_of_threads_high_prio_tickets.cnf (+16/-0)
Percona-Server/mysql-test/t/pool_of_threads_high_prio_tickets.test (+19/-0)
Percona-Server/mysys/mf_cache.c (+5/-3)
Percona-Server/sql/query_strip_comments.h (+37/-0)
Percona-Server/sql/sys_vars.cc (+7/-0)
Percona-Server/sql/threadpool.h (+72/-0)
Percona-Server/sql/threadpool_common.cc (+367/-0)
Percona-Server/sql/threadpool_unix.cc (+1699/-0)
Percona-Server/sql/threadpool_win.cc (+763/-0)
Percona-Server/storage/innobase/handler/ha_innodb.cc (+4/-0)
Percona-Server/storage/innobase/include/srv0srv.h (+5/-0)
Percona-Server/storage/innobase/log/log0log.cc (+7/-1)
Percona-Server/storage/innobase/read/read0read.cc (+9/-0)
Percona-Server/storage/innobase/row/row0sel.cc (+3/-3)
Percona-Server/storage/innobase/srv/srv0srv.cc (+32/-0)
Percona-Server/storage/innobase/trx/trx0sys.cc (+2/-0)
Percona-Server/storage/innobase/trx/trx0trx.cc (+1/-0)
UDF/configure.ac (+1/-0)
build/build-binary.sh (+1/-0)
build/debian/additions/debian-start (+0/-32)
build/debian/additions/debian-start.inc.sh (+0/-72)
build/debian/compat (+0/-1)
build/debian/rules (+3/-1)
build/percona-server.spec (+15/-10)
build/percona-shared-compat.spec (+1/-1)
build/rpm/mysql-dubious-exports.patch (+0/-127)
doc/source/conf.py (+1/-1)
doc/source/development.rst (+0/-266)
doc/source/diagnostics/user_stats.rst (+0/-242)
doc/source/flexibility/buff_read_ahead_area.rst (+36/-0)
doc/source/management/innodb_expanded_fast_index_creation.rst (+0/-82)
doc/source/management/innodb_fake_changes.rst (+70/-0)
doc/source/management/innodb_kill_idle_trx.rst (+0/-2)
doc/source/percona_xtradb.rst (+13/-0)
doc/source/performance/query_cache_enhance.rst (+123/-0)
doc/source/performance/threadpool.rst (+33/-0)
doc/source/reliability/error_pad.rst (+0/-55)
doc/source/reliability/innodb_corrupt_table_action.rst (+0/-35)
doc/source/upstream-bug-fixes.rst (+40/-16)
python-for-subunit2junitxml/BytesIO.py (+0/-136)
python-for-subunit2junitxml/iso8601/LICENSE (+0/-20)
python-for-subunit2junitxml/iso8601/README (+0/-26)
python-for-subunit2junitxml/iso8601/README.subunit (+0/-5)
python-for-subunit2junitxml/iso8601/setup.py (+0/-58)
python-for-subunit2junitxml/iso8601/test_iso8601.py (+0/-111)
python-for-subunit2junitxml/junitxml/__init__.py (+0/-221)
python-for-subunit2junitxml/junitxml/tests/__init__.py (+0/-16)
python-for-subunit2junitxml/junitxml/tests/test_junitxml.py (+0/-327)
python-for-subunit2junitxml/subunit/__init__.py (+0/-1250)
python-for-subunit2junitxml/subunit/chunked.py (+0/-185)
python-for-subunit2junitxml/subunit/details.py (+0/-119)
python-for-subunit2junitxml/subunit/iso8601.py (+0/-133)
python-for-subunit2junitxml/subunit/progress_model.py (+0/-106)
python-for-subunit2junitxml/subunit/run.py (+0/-73)
python-for-subunit2junitxml/subunit/test_results.py (+0/-492)
python-for-subunit2junitxml/subunit/tests/TestUtil.py (+0/-80)
python-for-subunit2junitxml/subunit/tests/__init__.py (+0/-41)
python-for-subunit2junitxml/subunit/tests/sample-script.py (+0/-21)
python-for-subunit2junitxml/subunit/tests/sample-two-script.py (+0/-7)
python-for-subunit2junitxml/subunit/tests/test_chunked.py (+0/-152)
python-for-subunit2junitxml/subunit/tests/test_details.py (+0/-112)
python-for-subunit2junitxml/subunit/tests/test_progress_model.py (+0/-118)
python-for-subunit2junitxml/subunit/tests/test_subunit_filter.py (+0/-208)
python-for-subunit2junitxml/subunit/tests/test_subunit_stats.py (+0/-84)
python-for-subunit2junitxml/subunit/tests/test_subunit_tags.py (+0/-69)
python-for-subunit2junitxml/subunit/tests/test_tap2subunit.py (+0/-445)
python-for-subunit2junitxml/subunit/tests/test_test_protocol.py (+0/-1299)
python-for-subunit2junitxml/subunit/tests/test_test_results.py (+0/-300)
python-for-subunit2junitxml/testtools/__init__.py (+0/-80)
python-for-subunit2junitxml/testtools/_spinner.py (+0/-316)
python-for-subunit2junitxml/testtools/compat.py (+0/-286)
python-for-subunit2junitxml/testtools/content.py (+0/-238)
python-for-subunit2junitxml/testtools/content_type.py (+0/-33)
python-for-subunit2junitxml/testtools/deferredruntest.py (+0/-335)
python-for-subunit2junitxml/testtools/distutilscmd.py (+0/-62)
python-for-subunit2junitxml/testtools/helpers.py (+0/-64)
python-for-subunit2junitxml/testtools/matchers.py (+0/-785)
python-for-subunit2junitxml/testtools/monkey.py (+0/-97)
python-for-subunit2junitxml/testtools/run.py (+0/-332)
python-for-subunit2junitxml/testtools/runtest.py (+0/-200)
python-for-subunit2junitxml/testtools/testcase.py (+0/-724)
python-for-subunit2junitxml/testtools/testresult/__init__.py (+0/-19)
python-for-subunit2junitxml/testtools/testresult/doubles.py (+0/-111)
python-for-subunit2junitxml/testtools/testresult/real.py (+0/-621)
python-for-subunit2junitxml/testtools/tests/__init__.py (+0/-44)
python-for-subunit2junitxml/testtools/tests/helpers.py (+0/-72)
python-for-subunit2junitxml/testtools/tests/test_compat.py (+0/-257)
python-for-subunit2junitxml/testtools/tests/test_content.py (+0/-223)
python-for-subunit2junitxml/testtools/tests/test_content_type.py (+0/-46)
python-for-subunit2junitxml/testtools/tests/test_deferredruntest.py (+0/-738)
python-for-subunit2junitxml/testtools/tests/test_distutilscmd.py (+0/-90)
python-for-subunit2junitxml/testtools/tests/test_fixturesupport.py (+0/-79)
python-for-subunit2junitxml/testtools/tests/test_helpers.py (+0/-106)
python-for-subunit2junitxml/testtools/tests/test_matchers.py (+0/-695)
python-for-subunit2junitxml/testtools/tests/test_monkey.py (+0/-167)
python-for-subunit2junitxml/testtools/tests/test_run.py (+0/-77)
python-for-subunit2junitxml/testtools/tests/test_runtest.py (+0/-300)
python-for-subunit2junitxml/testtools/tests/test_spinner.py (+0/-332)
python-for-subunit2junitxml/testtools/tests/test_testresult.py (+0/-1372)
python-for-subunit2junitxml/testtools/tests/test_testsuite.py (+0/-53)
python-for-subunit2junitxml/testtools/tests/test_testtools.py (+0/-1143)
python-for-subunit2junitxml/testtools/tests/test_with_with.py (+0/-42)
python-for-subunit2junitxml/testtools/testsuite.py (+0/-87)
python-for-subunit2junitxml/testtools/utils.py (+0/-13)
Revision history for this message
Alexey Kopytov (akopytov) wrote :

See also bug #1092189.

Revision history for this message
Ryan Huddleston (rshuddleston) wrote :

Note until this is fixed you should not use --innodb-optimize-keys on tables with foreign keys. Otherwise it will duplicate your keys the first time it's dumped restored and result in the above error the second time it's dumped restored.

Ryan Huddleston (rshuddleston)
tags: added: rdba
Revision history for this message
Stewart Smith (stewart) wrote :

Merged into 5.1.68-14.6 release branch (before trunk, which is why I'm marking it Fix Released now)

Revision history for this message
Stewart Smith (stewart) wrote :

setting as fixed Released as merged into 5.5.30-30.2 release branch (before trunk, as Jenkins has backlog)

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-605

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.