| Bug #49838 | DROP INDEX and ADD UNIQUE INDEX for same index may corrupt definition at engine | ||
|---|---|---|---|
| Submitted: | 21 Dec 2009 7:04 | Modified: | 18 Jun 2010 1:22 |
| Reporter: | Yasufumi Kinoshita | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 5.1.41 + InnoDB Plugin 1.0.6, 5.5.1 | OS: | Any |
| Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
| Tags: | Contribution | ||
[21 Dec 2009 8:19]
Valeriy Kravchuk
Thank you for the problem report and suggested fix. Verified also with recent 5.5.1-m2 on Linux:
openxs@suse:/home2/openxs/dbs/trunk> bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.1-m2-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE `ac_tables` (
-> `ac_id` int(11) NOT NULL,
-> `tbl_id` int(11) NOT NULL,
-> `tbl` smallint(6) NOT NULL,
-> `alias` varchar(32) CHARACTER SET ascii NOT NULL,
-> `join_def_enum` varchar(255) CHARACTER SET ascii DEFAULT NULL,
-> `join_to_tbl_idx` int(11) DEFAULT NULL,
-> `join_type` tinyint(4) DEFAULT NULL,
-> `join_fltr_expr` mediumtext COLLATE utf8_unicode_ci,
-> KEY `ac_tables$ac_id$tbl_id` (`ac_id`,`tbl_id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.10 sec)
mysql> ALTER TABLE ac_tables DROP INDEX ac_tables$ac_id$tbl_id, ADD UNIQUE INDEX
-> ac_tables$ac_id$tbl_id (ac_id,tbl_id);
ERROR 1173 (42000): This table type requires a primary key
[21 Dec 2009 12:07]
Marko Mäkelä
I do not have commit access to sql_table.cc, which is outside InnoDB. This is a DDL bug. When testing the fix, please remember to test foreign key constraints as well. See also Bug #47622, which is an InnoDB bug.
[21 Dec 2009 17:38]
MySQL Verification Team
It is possible that this bug occurs in the code that promotes UNIQUE index on NOT NULL columns to Primary Key, when table does not have Primary Key defined, which is a case in this test case.
[11 Mar 2010 12:06]
Marko Mäkelä
This is related to Bug #51451.
[11 Mar 2010 12:44]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/102998 3388 Georgi Kodinov 2010-03-11 Bug #49838: DROP INDEX and ADD UNIQUE INDEX for same index may corrupt definition at engine Fixed the order in which indexes are added/dropped during in-place (a.k.a fast) ALTER TABLE ADD/DROP INDEX .... Now the code first drops all indexes that need to be dropped and then creates the ones that need to be created instead of the opposite. This allows re-definition of indexes in a single statement and matches the non-inplace ALTER TABLE's behavior.
[11 Mar 2010 13:08]
Konstantin Osipov
Fix: don't do online alter if both add and drop index are specified on the same column
[11 Mar 2010 15:01]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/103021 3388 Georgi Kodinov 2010-03-11 Bug #49838: DROP INDEX and ADD UNIQUE INDEX for same index may corrupt definition at engine If a single ALTER TABLE contains both DROP INDEX and ADD INDEX using the same index name (a.k.a. index modification) we need to disable in-place alter table because we can't ask the storage engine to have two copies of the index with the same name even temporarily (if we first do the ADD INDEX and then DROP INDEX) and we can't modify indexes that are needed by e.g. foreign keys if we first do DROP INDEX and then ADD INDEX. Fixed the problem by disabling in-place ALTER TABLE for these cases.
[17 Mar 2010 14:19]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/103602 3388 Georgi Kodinov 2010-03-17 Bug #49838: DROP INDEX and ADD UNIQUE INDEX for same index may corrupt definition at engine If a single ALTER TABLE contains both DROP INDEX and ADD INDEX using the same index name (a.k.a. index modification) we need to disable in-place alter table because we can't ask the storage engine to have two copies of the index with the same name even temporarily (if we first do the ADD INDEX and then DROP INDEX) and we can't modify indexes that are needed by e.g. foreign keys if we first do DROP INDEX and then ADD INDEX. Fixed the problem by disabling in-place ALTER TABLE for these cases.
[17 Mar 2010 14:20]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/103603 3405 Georgi Kodinov 2010-03-17 Bug #49838: DROP INDEX and ADD UNIQUE INDEX for same index may corrupt definition at engine If a single ALTER TABLE contains both DROP INDEX and ADD INDEX using the same index name (a.k.a. index modification) we need to disable in-place alter table because we can't ask the storage engine to have two copies of the index with the same name even temporarily (if we first do the ADD INDEX and then DROP INDEX) and we can't modify indexes that are needed by e.g. foreign keys if we first do DROP INDEX and then ADD INDEX. Fixed the problem by disabling in-place ALTER TABLE for these cases.
[26 Mar 2010 8:20]
Bugs System
Pushed into 5.5.4-m3 (revid:alik@sun.com-20100326080914-2pz8ns984e0spu03) (version source revid:alexey.kopytov@sun.com-20100320202342-3oapaq7r0t6qhexq) (merge vers: 5.5.3-m2) (pib:16)
[26 Mar 2010 8:24]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100326081116-m3v4l34yhr43mtsv) (version source revid:alik@sun.com-20100325072612-4sds00ix8ajo1e84) (pib:16)
[26 Mar 2010 8:29]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100326081944-qja07qklw1p2w7jb) (version source revid:alik@sun.com-20100325073410-4t4i9gu2u1pge7xb) (merge vers: 6.0.14-alpha) (pib:16)
[6 Apr 2010 7:56]
Bugs System
Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:joro@sun.com-20100317141846-es0qyf5zcqb0hu1c) (merge vers: 5.1.46) (pib:16)
[8 Apr 2010 8:01]
Jon Stephens
Documented bugfix in the 5.1.46, 5.4.4, and 6.0.14 changelogs, as follows:
Performing a single in-place ALTER TABLE containing ADD INDEX
and DROP INDEX options that used the same index name could
result in a corrupt table definition file. Now such ALTER TABLE
statements are no longer performed in place.
Closed.
[8 Apr 2010 15:09]
Jon Stephens
Moved 5.5.4 changelog entry to 5.5.5 changelog.
[17 Jun 2010 11:45]
Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:joro@sun.com-20100317141846-es0qyf5zcqb0hu1c) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:23]
Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:joro@sun.com-20100317141846-es0qyf5zcqb0hu1c) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:10]
Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:joro@sun.com-20100317141846-es0qyf5zcqb0hu1c) (merge vers: 5.1.46) (pib:16)
Description: ALTER TABLE table DROP INDEX xxx, ADD UNIQUE INDEX xxx (...); may corrupt table or table definition in the storage engine, if the operation is tried to do as "fast index creation". How to repeat: mysql> CREATE TABLE `ac_tables` ( `ac_id` int(11) NOT NULL, `tbl_id` int(11) NOT NULL, `tbl` smallint(6) NOT NULL, `alias` varchar(32) CHARACTER SET ascii NOT NULL, `join_def_enum` varchar(255) CHARACTER SET ascii DEFAULT NULL, `join_to_tbl_idx` int(11) DEFAULT NULL, `join_type` tinyint(4) DEFAULT NULL, `join_fltr_expr` mediumtext COLLATE utf8_unicode_ci, KEY `ac_tables$ac_id$tbl_id` (`ac_id`,`tbl_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; Query OK, 0 rows affected (0.01 sec) mysql> ALTER TABLE ac_tables DROP INDEX ac_tables$ac_id$tbl_id, ADD UNIQUE INDEX ac_tables$ac_id$tbl_id (ac_id,tbl_id); ERROR 1173 (42000): This table type requires a primary key (Maybe corrupt at the moment....) mysql> ALTER TABLE ac_tables DROP INDEX ac_tables$ac_id$tbl_id; ERROR 1173 (42000): This table type requires a primary key Suggested fix: At mysql_alter_table() in sql_table.cc, "drop index of the fast creation" is always after "add index of the fast creation". If swapped their order, the corruption seems never to occur. Swap the part " if (index_add_count) { ... } if (index_drop_count) { ... } " to " if (index_drop_count) { ... } if (index_add_count) { ... } "