#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> key_it(alter_info->key_list);
- List_iterator<Alter_drop> 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; });
Attachment: [text/bzr-bundle] bzr/jon.hauglid@oracle.com-20110510093822-i2aome7mz9h20hm0.bundle
| Thread |
|---|
| • bzr commit into mysql-trunk branch (jon.hauglid:3388) Bug#11762345 | Jon Olav Hauglid | 10 May |
