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

[9 Jan 2015 23:35] Jeremy Tinley
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.
[10 Jan 2015 20:40] Miguel Solorzano
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 >