MySQL Bugs: #57497: drop/create on same col name doesn't work when indexed

Bug #57497	drop/create on same col name doesn't work when indexed
Submitted:	16 Oct 2010 14:27	Modified:	16 Oct 2010 18:10
Reporter:	Ryan Huddleston	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.1.49, 5.5.7-bzr	OS:	Any (Linux, Mac OS X)
Assigned to:		CPU Architecture:	Any

Description:
If you try to drop add a column in one alter statement it will fail if the column being dropped is indexed. In addition the error string that comes back isn't related to the problem

How to repeat:
mysql> create table foo (foo smallint, key foo$foo (foo));
Query OK, 0 rows affected (2.57 sec)

mysql> alter table foo drop foo, add foo int;
ERROR 1089 (HY000): Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys

Suggested fix:
process without error

This works for me (slightly different identifiers):

alter table `test`.`test` drop column `new`, add column `new` int NULL after `content`;

even this does

alter table `test`.`test` drop `new`, add `new` bigint NULL; 

(as long as datatypes for 'new' before and after are not identical - if they are it will also succeed but with '0 rows affected; ie. the server does nothing)

sorry .. please ignore .. I forgot the index!

yes .. also my Win64 5.1.51 server does the same!

Query : alter table `test`.`test` drop `new`, add `new` bigint NULL

Error Code : 1089
Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys

Verified with current mysql-5.5 from bzr also:

macbook-pro:5.5 openxs$ bin/mysql -uroot testReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -AWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 7
Server version: 5.5.7-rc-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>  create table foo (foo smallint, key foo$foo (foo));
Query OK, 0 rows affected (0.78 sec)

mysql> alter table foo drop foo, add foo int;
ERROR 1089 (HY000): Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys
mysql> alter table foo drop index foo$foo;
Query OK, 0 rows affected (0.44 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table foo drop foo, add foo int;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

Still present in:

* 5.5.60
* 5.6.40
* 5.7.22
* 8.2.11