MySQL Bugs: #54927: dropping and adding an index in one command can fail in innodb plugin 1.0.7

Bug #54927	dropping and adding an index in one command can fail in innodb plugin 1.0.7
Submitted:	1 Jul 2010 6:44	Modified:	23 May 2011 17:33
Reporter:	Ben Krug	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.1 (plugin 1.07, 1.0.9)	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
This is a bug in online alter, I believe.  It affects plugin 1.0.7 but not 1.0.6.
It seems related to earlier bugs, 49838 and 51451, and is the cause of 53241.

By earlier fixes, using a statement like
ALTER TABLE t drop index my_index (a, b), add index my_index (b, a);
will no longer fail.  I believe checks are in place for whether the
key name is used twice in the statement, etc...

This is a very specific set of events, but in
1.0.6, the following succeeds, but in 1.0., it fails

CREATE TABLE t (
	id int NOT NULL,
	a  int,
	b  int,
	PRIMARY KEY (id),
	INDEX a (a)
 ) ENGINE=innodb;

ALTER TABLE t DROP INDEX a, ADD INDEX a (b, a);
ALTER TABLE t DROP INDEX a, ADD INDEX (a, b);

The last statement gives the error:
ERROR 1280 (42000): Incorrect index name 'a'

Notice that I am changing the order of the columns, and
also that I do *not* name the key in the second statement,
although InnoDB (or MySQL) will try to name it 'a',
after the first column's name.

(You can see this by using two statements instead
of one:

ALTER TABLE t DROP INDEX a;
ALTER TABLE t ADD INDEX (a, b);
SHOW INDEXES FROM t;

As mentioned, this is the cause of the test suite
failure in bug 53241.

In reviewing 49838 and 51451 some related patches were
pushed to 1.0.6.  Not sure why this works in 1.0.6 but
not in 1.0.7.  I tested with 5.1.41 for 1.0.6,
but 5.1.46 for 1.0.7. so maybe this is a regression
caused by the patch for 49838, which was pushed into 5.1.46?

How to repeat:
Use InnoDB plugin 1.0.7 and repeat:

CREATE TABLE t (
	id int NOT NULL,
	a  int,
	b  int,
	PRIMARY KEY (id),
	INDEX a (a)
 ) ENGINE=innodb;

ALTER TABLE t DROP INDEX a, ADD INDEX a (b, a);
ALTER TABLE t DROP INDEX a, ADD INDEX (a, b);

Suggested fix:
As mentioned in the earlier bugs, DROPs should be
done before ADDs.

similar error in bug #54606

Verified just as described with current 5.1.49 from bzr (plugin 1.0.9):

77-52-227-51:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.49-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table t;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t (
    -> id int NOT NULL,
    -> a  int,
    -> b  int,
    -> PRIMARY KEY (id),
    -> INDEX a (a)
    ->  ) ENGINE=innodb;
Query OK, 0 rows affected (0.05 sec)

mysql> 
mysql> ALTER TABLE t DROP INDEX a, ADD INDEX a (b, a);
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t DROP INDEX a, ADD INDEX (a, b);
ERROR 1280 (42000): Incorrect index name 'a'

It seems there might be an issue with original fix with #49838, when index name is not supplied, is_index_maintenance_unique() would just return false:

mysql> ALTER TABLE t DROP INDEX a, ADD INDEX (a, b);

Breakpoint 5, is_index_maintenance_unique (table=0x9019710, 
    alter_info=0xa7f34660) at sql_table.cc:5586
5586	  List_iterator<Key> key_it(alter_info->key_list);
(gdb) n
5587	  List_iterator<Alter_drop> drop_it(alter_info->drop_list);
(gdb) 
5590	  while ((key= key_it++))
(gdb) 
5592	    if (key->name)
(gdb) 
5590	  while ((key= key_it++))
(gdb) 
5605	  return FALSE;
(gdb) 
5606	}
(gdb) p key
$1 = 0

In this case, the new index name would default to "a" even it is not supplied, however, is_index_maintenance_unique() would fail to detect this possible name conflict in "alter drop add", and did not copy the table:

+  if (is_index_maintenance_unique (table, alter_info)) <== not find conflict
+    need_copy_table= ALTER_TABLE_DATA_CHANGED;  <== false, proceed to FIC
+

So FIC does the add first and drop index later, this is now becomes same issue as #49838.

mysql> ALTER TABLE t DROP INDEX a, ADD INDEX (a, b);

call stack:

270					return(ER_WRONG_NAME_FOR_INDEX);
(gdb) where
#0  innobase_check_index_keys (key_info=0x901aea0, num_of_keys=1, 
    table=0x9018a50) at handler/handler0alter.cc:270
#1  0x08431950 in ha_innobase::add_index (this=0x9018c58, table=0x9019710, 
    key_info=0x901aea0, num_of_keys=1) at handler/handler0alter.cc:678
#2  0x08369f29 in mysql_alter_table (thd=0x8fed3e8, new_db=0x901a548 "test", 
    new_name=0x901a348 "t", create_info=0xa7f34370, table_list=0x901a370, 
    alter_info=0xa7f34660, order_num=0, order=0x0, ignore=false)
    at sql_table.cc:7277
#3  0x0821d760 in mysql_execute_command (thd=0x8fed3e8) at sql_parse.cc:2902
#4  0x08227479 in mysql_parse (thd=0x8fed3e8, 
    inBuf=0x901a2a0 "ALTER TABLE t DROP INDEX a, ADD INDEX (a, b)", length=44, 
    found_semicolon=0xa7f35058) at sql_parse.cc:5971
#5  0x082194af in dispatch_command (command=COM_QUERY, thd=0x8fed3e8, 
    packet=0x9012241 "ALTER TABLE t DROP INDEX a, ADD INDEX (a, b)", 
    packet_length=44) at sql_parse.cc:1233
#6  0x082185a3 in do_command (thd=0x8fed3e8) at sql_parse.cc:874

Now, in terms of the different behavior from latest plugin, the error reported (ER_WRONG_NAME_FOR_INDEX) in this case is added in bug #51451:

+		/* Check that the same index name does not already exist. */
+
+		for (const dict_index_t* index
+			     = dict_table_get_first_index(table);
+		     index; index = dict_table_get_next_index(index)) {
+
+			if (0 == strcmp(key.name, index->name)) {
+				my_error(ER_WRONG_NAME_FOR_INDEX, MYF(0),
+					 key.name);

This explains why this error had not been seen prior to this patch checked in.

However, this check is added in light of the "alter... drop...add" issue (we do add first then drop) had been addressed (in #49838). So this check should still be there to catch any such possible cases.

In summary,  #49838 should be enhanced to address default index name.

Noted in 5.6.3 changelog.

For an InnoDB table, dropping and adding an index in a single ALTER
TABLE statement could fail. 

CHANGESET - http://lists.mysql.com/commits/137700

I get that there's a proper fix coming in v5.6.x.

Can someone explain the in-the-meantime workaround a bit more simply?

If I do this (generated/exported by MySQL Workbench):

CREATE TABLE IF NOT EXISTS t (
 user VARCHAR(255) NOT NULL DEFAULT '',
 host VARCHAR(255) NOT NULL,
 PRIMARY KEY (user) ,
 CONSTRAINT fk_t_host1
 FOREIGN KEY (host )
 REFERENCES host (user )
 ON DELETE NO ACTION
 ON UPDATE NO ACTION
) ENGINE = InnoDB, COMMENT = 'test' ;
CREATE INDEX fk_t_host1 ON t (host ASC) ;

I get this

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

So, on version 5.5.x -- what's the workaround?

Hello!

You can use the old_alter_table system variable as a workaround.
http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_old-alter-table

For example:
SET old_alter_table=1
CREATE INDEX ...
SET old_alter_table=0

Alternatively, you can use ALTER TABLE with the FORCE option.
For example:
ALTER TABLE t1 DROP INDEX a, ADD INDEX (a, b), FORCE;

Both have the effect of creating/dropping indexes using a temporary table
(instead of doing them directly on the table). This was in effect how we
fixed the bug for 5.6.

Hope this helps!