From: Jon Olav Hauglid Date: May 10 2011 9:38am Subject: bzr commit into mysql-trunk branch (jon.hauglid:3388) Bug#11762345 List-Archive: http://lists.mysql.com/commits/136975 X-Bug: 11762345 Message-Id: <201105100938.p4A9cYkg008693@acsmt356.oracle.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============7089593848217805072==" --===============7089593848217805072== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///export/home/x/mysql-trunk-bug11762345/ based on revid:serge.kozlov@stripped 3388 Jon Olav Hauglid 2011-05-10 Bug#11762345 54927: DROPPING AND ADDING AN INDEX IN ONE COMMAND CAN FAIL IN INNODB PLUGIN 1.0 If a single ALTER TABLE statement drops and adds an index with the same name, the operation has to be done using full table copy rather than changing the table in-place. This problem was partly fixed by an index name check added in the patch for Bug#49838. However, a problem remainded if the new index was not given an explicit name. In such cases the index is automatically given the same name as the first column in the index defintion. If this name happened to be the same name as the index to be dropped, the match would not be detected by the fix for Bug#49838. This caused ALTER TABLE to fail with "Incorrect index name". This patch fixes this problem by postponing the index name check until after unnamed new indexes have been given a name. This allows the check to catch situations described above and force the use of full table copy (rather than aborting with "Incorrect index name"). Test case added to innodb_mysql_lock.test. modified: mysql-test/r/innodb_mysql_lock.result mysql-test/suite/innodb/r/innodb-index.result mysql-test/suite/innodb/r/innodb.result mysql-test/suite/innodb/t/innodb-index.test mysql-test/suite/innodb/t/innodb.test mysql-test/t/innodb_mysql_lock.test sql/sql_table.cc === modified file 'mysql-test/r/innodb_mysql_lock.result' --- a/mysql-test/r/innodb_mysql_lock.result 2011-04-13 18:43:08 +0000 +++ b/mysql-test/r/innodb_mysql_lock.result 2011-05-10 09:38:22 +0000 @@ -170,3 +170,13 @@ id value 1 12345 COMMIT; DROP TABLE t1; +# +# Bug#11762345 54927: DROPPING AND ADDING AN INDEX IN ONE +# COMMAND CAN FAIL IN INNODB PLUGIN 1.0 +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (id int, a int, b int, PRIMARY KEY (id), +INDEX a (a)) ENGINE=innodb; +ALTER TABLE t1 DROP INDEX a, ADD INDEX a (b, a); +ALTER TABLE t1 DROP INDEX a, ADD INDEX (a, b); +DROP TABLE t1; === modified file 'mysql-test/suite/innodb/r/innodb-index.result' --- a/mysql-test/suite/innodb/r/innodb-index.result 2010-10-07 11:57:34 +0000 +++ b/mysql-test/suite/innodb/r/innodb-index.result 2011-05-10 09:38:22 +0000 @@ -434,7 +434,6 @@ t3 CREATE TABLE `t3` ( KEY `c` (`c`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 alter table t2 drop index b, add index (b); -ERROR 42000: Incorrect index name 'b' show create table t2; Table Create Table t2 CREATE TABLE `t2` ( @@ -445,8 +444,8 @@ t2 CREATE TABLE `t2` ( `e` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `dc` (`d`,`c`), - KEY `b` (`b`), KEY `c` (`c`), + KEY `b` (`b`), CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`b`) ON DELETE CASCADE, CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`c`) REFERENCES `t3` (`c`), CONSTRAINT `t2_ibfk_3` FOREIGN KEY (`d`) REFERENCES `t4` (`d`) === modified file 'mysql-test/suite/innodb/r/innodb.result' --- a/mysql-test/suite/innodb/r/innodb.result 2011-02-21 02:57:30 +0000 +++ b/mysql-test/suite/innodb/r/innodb.result 2011-05-10 09:38:22 +0000 @@ -704,7 +704,6 @@ select count(*) from t1 where cat_code=' count(*) 0 alter table t1 drop index sca_pic, add index (sca_pic, cat_code); -ERROR 42000: Incorrect index name 'sca_pic' alter table t1 drop index sca_pic; alter table t1 add index (sca_pic, cat_code); select count(*) from t1 where sca_code='PD' and sca_pic is null; @@ -1673,7 +1672,7 @@ variable_value - @innodb_rows_deleted_or 71 SELECT variable_value - @innodb_rows_inserted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_inserted'; variable_value - @innodb_rows_inserted_orig -1066 +1069 SELECT variable_value - @innodb_rows_updated_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_updated'; variable_value - @innodb_rows_updated_orig 866 === modified file 'mysql-test/suite/innodb/t/innodb-index.test' --- a/mysql-test/suite/innodb/t/innodb-index.test 2010-07-16 21:00:50 +0000 +++ b/mysql-test/suite/innodb/t/innodb-index.test 2011-05-10 09:38:22 +0000 @@ -131,8 +131,6 @@ show create table t4; --error ER_CANT_CREATE_TABLE alter table t3 add constraint dc foreign key (a) references t1(a); show create table t3; -# this should be fixed by MySQL (see Bug #51451) ---error ER_WRONG_NAME_FOR_INDEX alter table t2 drop index b, add index (b); show create table t2; --error ER_ROW_IS_REFERENCED_2 === modified file 'mysql-test/suite/innodb/t/innodb.test' --- a/mysql-test/suite/innodb/t/innodb.test 2011-02-21 02:57:30 +0000 +++ b/mysql-test/suite/innodb/t/innodb.test 2011-05-10 09:38:22 +0000 @@ -452,8 +452,6 @@ alter table t1 add index sca_pic (cat_co select count(*) from t1 where sca_code='PD' and sca_pic is null; select count(*) from t1 where cat_code='E'; -# this should be fixed by MySQL (see Bug #51451) ---error ER_WRONG_NAME_FOR_INDEX alter table t1 drop index sca_pic, add index (sca_pic, cat_code); alter table t1 drop index sca_pic; alter table t1 add index (sca_pic, cat_code); === modified file 'mysql-test/t/innodb_mysql_lock.test' --- a/mysql-test/t/innodb_mysql_lock.test 2011-04-13 18:43:08 +0000 +++ b/mysql-test/t/innodb_mysql_lock.test 2011-05-10 09:38:22 +0000 @@ -311,6 +311,25 @@ DROP TABLE t1; disconnect con1; +--echo # +--echo # Bug#11762345 54927: DROPPING AND ADDING AN INDEX IN ONE +--echo # COMMAND CAN FAIL IN INNODB PLUGIN 1.0 +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (id int, a int, b int, PRIMARY KEY (id), + INDEX a (a)) ENGINE=innodb; + +ALTER TABLE t1 DROP INDEX a, ADD INDEX a (b, a); +# This used to fail +ALTER TABLE t1 DROP INDEX a, ADD INDEX (a, b); + +DROP TABLE t1; + + # Check that all connections opened by test cases in this file are really # gone so execution of other tests won't be affected by their presence. --source include/wait_until_count_sessions.inc === modified file 'sql/sql_table.cc' --- a/sql/sql_table.cc 2011-05-06 12:56:36 +0000 +++ b/sql/sql_table.cc 2011-05-10 09:38:22 +0000 @@ -4965,23 +4965,26 @@ err: */ static bool -is_index_maintenance_unique (TABLE *table, Alter_info *alter_info) +is_index_maintenance_unique (TABLE *table, KEY *key_info_buffer, + uint *index_drop_buffer, uint index_drop_count, + uint *index_add_buffer, uint index_add_count) { - List_iterator key_it(alter_info->key_list); - List_iterator drop_it(alter_info->drop_list); - Key *key; - - while ((key= key_it++)) + KEY *add_key; + KEY *drop_key; + uint *idx_add_p; + uint *idx_drop_p; + + for (idx_add_p= index_add_buffer; + idx_add_p < index_add_buffer + index_add_count; idx_add_p++) { - if (key->name.str) + add_key= key_info_buffer + *idx_add_p; + if (add_key->name) { - Alter_drop *drop; - - drop_it.rewind(); - while ((drop= drop_it++)) + for (idx_drop_p= index_drop_buffer; + idx_drop_p < index_drop_buffer + index_drop_count; idx_drop_p++) { - if (drop->type == Alter_drop::KEY && - !my_strcasecmp(system_charset_info, key->name.str, drop->name)) + drop_key= table->key_info + *idx_drop_p; + if (!my_strcasecmp(system_charset_info, add_key->name, drop_key->name)) return TRUE; } } @@ -6277,15 +6280,9 @@ bool mysql_alter_table(THD *thd,char *ne */ new_db_type= create_info->db_type; - if (is_index_maintenance_unique (table, alter_info)) - need_copy_table= ALTER_TABLE_DATA_CHANGED; - if (mysql_prepare_alter_table(thd, table, create_info, alter_info)) goto err; - if (need_copy_table == ALTER_TABLE_METADATA_ONLY) - need_copy_table= alter_info->change_level; - set_table_default_charset(thd, create_info, db); if (thd->variables.old_alter_table @@ -6297,6 +6294,8 @@ bool mysql_alter_table(THD *thd,char *ne need_copy_table= ALTER_TABLE_DATA_CHANGED; else { + need_copy_table= alter_info->change_level; + Alter_table_change_level need_copy_table_res; /* Check how much the tables differ. */ if (mysql_compare_tables(table, alter_info, @@ -6307,7 +6306,12 @@ bool mysql_alter_table(THD *thd,char *ne &index_add_buffer, &index_add_count, &candidate_key_count, FALSE)) goto err; - + + if (is_index_maintenance_unique (table, key_info_buffer, + index_drop_buffer, index_drop_count, + index_add_buffer, index_add_count)) + need_copy_table= ALTER_TABLE_DATA_CHANGED; + DBUG_EXECUTE_IF("alter_table_only_metadata_change", { if (need_copy_table_res != ALTER_TABLE_METADATA_ONLY) goto err; }); --===============7089593848217805072== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/jon.hauglid@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: jon.hauglid@stripped # target_branch: file:///export/home/x/mysql-trunk-bug11762345/ # testament_sha1: 7adbb21f822c0eb253c4dea5c4af5e0746c95b66 # timestamp: 2011-05-10 11:38:26 +0200 # base_revision_id: serge.kozlov@stripped\ # liiegqtwc4lrs0md # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWSiVe98ABzx/gFyyACB6f/// f//eYP////BgDcu+7etZ7u7xkPRQoVUJ3fB2X19JDlpyd7bnOdWwD010JJKeowTKTaabJqYxU9T9 KNG1MjNMUZAAYRoAEpAJggaRiqP1T0antCJoAAaA000GEaGINAgJTI2oGgG1GgAD1AAAAAABJqSE yQnlJ+pPGmqfpMKeU9JtTxIaAyAAGg2oAikQmUPST9TKeaSZGg9Q9IeoDQAAAA9TDUEkQmQCZNAJ k0CaaTTU0zUempiaAGQAPURQMIZWLLM47mnxsV3msKrLS4wZKY2CUedtZ+CY/xZTr5zksmxaj4ao QZNyyY/RkW4Oc6CB51wVnLtaqV29Xr4TDHh3bWdLwp/P5NAbyTi3a0JPZvqXv5pBy39uwoafKDys LniWPGCOxpcL5E/5bI8M/lvSBTSN4yHAwdREOLh9ioO2jsgOoe43jrKxz8cN08+IZh2fHdTtk9Un FIrEYLFWQU3/4Ia+PTzcmPgbvxKMbkhGS1SY5IjoIIglCPCoGqiok9HG96PN55ZmDLJmcbqzts8z 4XZnFlCZMrLvOjpD6prt6PsJ0FF5YA6FxHuJ1/Mx9Faoi6mOsHLjznjMLPVnwTyA7L4hQ5TVnxzG 6D5EoHv16/N7+x62+OYPL5Eux/tu7nQswDjFbIJM54YgJxjbSdl31aoomdjcEiVjMjcn73UtarZm ZnU2hMGKYwmpg7BhJ1kSRDkGcxEoWlNtOLgHI3j0WUB6Td+NwiUKFIr2VlspsavW5+ast1YqwkhL digaHe3b8sZ8eHVp1cnLqyBAehwa2NcMaZ8QFUcVO5bDxHGQMdJRCPyqKuqGgDzhrE1kwkCja7M6 xumFakcFwXeFgcwRTFAmJVGaQAoYBojPUSJSGtZzIW4D40G2kismHeCWZ2nexgB+gRbP1rfQD+Iu yu7mIPR7hSWh6u+qJYHeXz2maDM0nw5246KVyvy8ZKbK4ZJDOkDq3YeejMzP3DEIM1VVnDkkTHxZ SW82w0UaNdpkSbUiyQplNGAHR0wutJeX7BMQEgKmcoohPGBULyutoBY9UBhOOUKVQAVCBtA4lgTc NldLKplQUIVWlpAIgRU3EETELUpsWK4rIh2HcWkiReW0xsO0DAQb1L5RNYi4aLu7B2DWsM4UqNes qSaPNsCwQMvs0C4kRHFRhBs8Ohu7Y1uiIJfx+ZO9GTwIlLzQFT+lY5j2P9u3NvLR8yOZw5FwRMBm vLZug/DezM0pDL152Cmd5YF8zI1azdoaih+IDXEJhiEMzvzqO+jnJdC6u127Q0ScDUeLle5RM+2B KB9p6Hu2ZIxy2Ue/g8HfO6sQTfWBBQIjGAwznUQGRyiVeqBOJ3BpAlUQKFDu0qDSnLfh1mrCoNRr DNMV2hU+GAFcgwJnCIjtx/ASKiZccRBMc9LT5bA6me4mbCR8xyOlm7htbdqOEHiZVklQpVAcqNbU HqNsyo5TgG0qHGkULr9dKqiATVWBecbBzBwdi7sAjM0EHWywkREmoWY8B2fNlD4BRIfgam+vZ2HL bctrTPivcZBsJjOdhGaBiY4pFOsNCZbAmNkrDbtsW43pwvTFG2lsbHmFfLQrqMg5BwhsWGAjzGWs iVjaAM4qyyg+0CRoayZwTFev0uRS3YX2u+pPTdazJmZXDjpBCI+u0p41mamaToSnBZmucrZk9l5I gOGWmQAfeZEjIzE3gd19CoEmwyM1zjvK5sXD3ctCMr3LPmCpG0bAuM1oXBiKBx5QNTXHExJmJrqd vDUfABTCxaJwKbIEAMC9VjSKwa1s6tZPqQcYzURhoBgUHMay6nhtMqFgDFzEzcKXnE11eGjawdho NQHSBhVms3GRRHlaog4qUedJbYQKjbXWpk17pOdxW1qlFAGBccgNTPhMuDIwwIiBC6lIhYYVLlEK Pa2M4WdIxHcIl4v3wtdCU6RYYAUNzMC4UCiMopIWUHiA4CxWyIRJ7m1tcNQY1sv/FHSkPtrNDURN ZjiVkCJXXEgVlgg6hqUZSSwGyHYUi2JHOxplTgQepAG+gkeAJAhGRxDQveW+XXzxUZ2wisbkS4MM awDvmYxL9kuFxFFVUKV7JKkpPObOuTgzwGIcpwlZp2YZKFvKLcvuYxBB/GB5HVBu4iuTjrSIIWIj ULTosaIdf7mHzGwMFPM2LSjS1hgCGb9XaIEkfQt+1RUtaMjmGvlJu6KbC62Dn0WwYXCWs1OOmQzL X9M38lJLqmWQ7bxC7S1uZoMjDQMDAwjJgCV5JuG5sblGmBWnTpiYpETFIrI7Sw/jQFwqW5L8DXJM 1pkEPAODXpCejl8tFUq9VBQtwTxk6PbhSfedddtTHFFcJDukyO6S+9RUccvTRJk4yjIKGe1kHzuO Jj4EPtLEExknifSA59pWY4RJEJtNppH3NptedGFIokqHGDFPjAV6+WJT0f3uei4tNpsMyNloj7zo vd+qri2iKJgDb0N0iJak51sKiogXgvo1QDx+YaxZEZgnhzHjF43ixX6cTwxRA5U13c5cKz3yDoUA kC8/2+BvL/VM/YUHdcSCD8TyTzHAcleHc0zmhYJi3ol4JH63Jrkd6R5MhGXPRYh1l50DbHeNDQgc 0sWO+kDxgNeRHgLHUFGtupSOAiU7UvAaN5EKqMzN51GBidJ1g12P95c2OFQrQxm+LHRKEAFD5z0K 9iEu1y4tGFzoq2saTDMnG82Tjdi+c1U21ms7Bvsx6DXSMEHZag4hDlUkshkg5hmMCqrLN4K0ZB7c TieIGaQdTxvbwyM9RHlCjDlBwA8gHPaB0u9xhTcQ7ZLdnfaGy7Ts4EhsNXH0m9yc94vZcYDJRaqh Ub0l2uuG8MFeo6i1sGgoZIfdGSjQW5+m2jlNpbzLe7wtkw9s+8wEFVhQcwOaUkpW+DpNJKxIuFig tmdhv0g1NnE9mWZiSw2e3qxOJ0PMXq51/ngbD9FT1V3c2h7HO3gtm5oEW4QNFtqAkmQeoMlxJuSY MjsjAavgu5qiHZBqxR2X0PrYAfBKlSy9sRG4ikk9rYUlSkRVl9bNDnUxAWDqDIcCd4DMaCwqwFpE d8YgAjOQgkJVMOKwGGSN0CtIr+6WhLUlcwbT1nNKB/pg93ibIEOBUF4ujncpswh9RmeZCw1PWCI6 9ua/qVHUx7z5gZjuOggfiHcdDMu6QDEo1L2yA2ANd0oSdB1SyuJzkC8Ml4JVKDIIZOgTjsZY8QVa VZqEuO7vlXJOZPDwN30/mJ+wDqXrxm3LxPFCIk6Bp5CKQFvY0E3y1uiqoN8b6JvcGnetY/U+JFFH 1nqoUz6iJNBqx8efvTTMyBucW4Q6SRkQR0WCM2paJcSkWUuJN4ss5OAyWHf5bZg30yXhbcypa6Km peI8mziWw4CfhoZmoBjcDMUr0iGrBSmATT8utsGU6PlJrC3gMlkQsJiz2VAmh4SjYOvKUbxWuZ3P G1LVcUYzyaL6Jbe1SBLeIPqB4GBkdGjofSsQCAgYCflvNZsy+sps/PPlbYBhLQ+j6AJfA0S5nkIL Nd5i8BnzgfGV99PhQhpGSzdicEnVE5GTmvMdyBuCZ5BiAFkvj74VkkhyodYwrMEeY0kIuQih6+6d LzMCmIJKoZKKaiwGd23mB2SO0eU5zXZ6lrQtyJK6sALMYhLXhdShCHa0dk51GyAWFrqwMQ7gFIQ4 QPZSKbE3ez6pivNmis+96968jMU3GojZ2xFgktPUyTIGUwMiIZcAp1YvNJ10n0sieN1UuI4bNd4X bzkdNgC65Mtx1D0L06bRxHvOD/cyqzB1MQGaXw3RzlsPIxPAnigIpb+EQLEd2W7gXrj8C5e/gs66 0lBihad6pxhGDUTLWSJ0cuo9BjxeDM0HFohyztfw6aK2hXvZjcD2uBs1pRlS0Q0UvqwFkyzOBrO1 fBr+ogdxzIMmQF8MhG7GMkvOXTbxG54hutiQeLm2qTMyZIgIxKrZUQ9xhLZaJg/rERUO4r6MVzQc SDU96MsnvodnLOxLgAzl4k2icFkyKxj7n2txu3jwg0YdCaW6RqETTZM9ZjXBBeeiiYlhl1Otszz9 SR96G1AkFxrIMd7YuGN4xNVQxRZMnKCDRn3ye+VBfxDxjvXBP7TG2qIj2EnNRgcFa52s/IqL2QtC /sglsgyMGeGjCzNBQB7pm28Va6s3yxtCgMLyxzgCOUxxBXrMrgPeK+CuXxDVx/+LuSKcKEgUSr3v gA== --===============7089593848217805072==--