MySQL Bugs: #75473: ALTER with ADD AFTER followed by Change fails

Bug #75473	ALTER with ADD AFTER followed by Change fails
Submitted:	9 Jan 2015 23:35	Modified:	10 Jan 2015 20:40
Reporter:	Jeremy Tinley	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.5.38	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
http://bugs.mysql.com/bug.php?id=34972

This fix should be backported to MySQL 5.5. It's still an issue.

mysqldiff generates ALTER statements to bring two differing schemas in sync and generates statements that produce this result.

How to repeat:
Issue an ALTER TABLE adding a column AFTER a column that is having it's definition changed.

Suggested fix:
Backport the fix.

Thank you for the bug report.

C:\dbs>net start mysqld56
The MySQLD56 service is starting..
The MySQLD56 service was started successfully.

C:\dbs>56

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --debug-info --prompt="mysql 5.6 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.23 Source distribution 2014.12.02

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql 5.6 > USE test
Database changed
mysql 5.6 > CREATE TABLE `people` (
    ->   id int(10) unsigned NOT NULL auto_increment,
    ->   name char(100) NOT NULL default '',
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=ucs2;
Query OK, 0 rows affected (0.25 sec)

mysql 5.6 > ALTER TABLE `people` ADD token char(16) NOT NULL DEFAULT "" AFTER name, MODIFY name char(100) NOT NULL DEFAULT "" AFTER id;
Query OK, 0 rows affected (0.39 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.6 > exit
Bye

C:\dbs>net start mysqld55
The MySQLD55 service is starting.
The MySQLD55 service was started successfully.

C:\dbs>55

C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.42 Source distribution 2014.12.02

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql 5.5 > USE test
Database changed
mysql 5.5 > CREATE TABLE `people` (
    ->   id int(10) unsigned NOT NULL auto_increment,
    ->   name char(100) NOT NULL default '',
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=ucs2;
Query OK, 0 rows affected (0.09 sec)

mysql 5.5 > ALTER TABLE `people` ADD token char(16) NOT NULL DEFAULT "" AFTER name, MODIFY name char(100) NOT NULL DEFAULT "" AFTER id;
ERROR 1054 (42S22): Unknown column 'name' in 'people'
mysql 5.5 >