Bug #60650 Forward ingeneer SQL ALTER Script may provokes 1054 error
Submitted: 25 Mar 2011 20:06 Modified: 30 Jul 2014 18:53
Reporter: Jean Ponchon Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Workbench: Modeling Severity:S3 (Non-critical)
Version:WB 5.2.31 with 5.1.49-1ubuntu8.1 server OS:Any
Assigned to: CPU Architecture:Any
Tags: 1054, add, after, alter, change, column, column, forward, ingeneer

[25 Mar 2011 20:06] Jean Ponchon
Description:
Using a script generated by "File-> Export -> Forward ingeneer SQL ALTER Script" on MySQL Workbench ( v5.2.31 tested on ubuntu) I found this situation :

Executing a ALTER query with both :

- Adding a column "A" after column "B" 
*and*
- Change on column "B"

Provokes an error 1054 (Unknown column)

How to repeat:
# Sample table :
CREATE TABLE IF NOT EXISTS `aspect` (
  `name` varchar(255),
  `picture` varchar(255)
) ENGINE=InnoDB;

# Simplified ALTER script generated using "Forward ingeneer SQL ALTER Script" :
ALTER TABLE  `aspect` 
ADD COLUMN  `type` TEXT AFTER `name` ,
CHANGE COLUMN  `name`  `name` varchar(255) NOT NULL AFTER `picture`;

# Return : Error 1054 - Unknown column 'name' in 'aspect'

Suggested fix:
Could be resolved by splitting alter query in generated ALTER script :

# Alter table :
ALTER TABLE  `aspect` ADD COLUMN  `type` TEXT AFTER `name`;
ALTER TABLE  `aspect` CHANGE COLUMN  `name`  `name` varchar(255) NOT NULL AFTER `picture`;
[25 Mar 2011 20:08] Jean Ponchon
Tags added
[26 Mar 2011 6:56] Valeriy Kravchuk
Do you have any newer version of MySQL server to test this? I do not have problem executing generated ALTER:

ALTER TABLE `test`.`b` ADD COLUMN `a` VARCHAR(45) NULL  AFTER `id` , CHANGE COLUMN `id` `id` VARCHAR(20) NOT NULL DEFAULT 'abc'  ;

on recent 5.1.56. This problem can be a result of server bug.
[30 Mar 2011 11:07] Jean Ponchon
@Valeriy Kravchuk :

I tried my sample sql script on Windows with MySQL 5.5.10 : the error is the same.

You right, your sample code is working : My description needs to be refined.
As in my sample code, the bug occurs when you append a column after a column moved in the same alter query. This is probably not a good way to write alter script, but the "Forward ingeneer SQL ALTER Script" utility generate this kind of alter query.
[10 Apr 2011 11:05] Valeriy Kravchuk
Please, send initial .mwb file and describe what exact changes in table structure in this .mwb should be made to demonstrate the problem.
[10 May 2011 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[11 Jul 2011 9:45] Adam S
I am experiencing the same issue.  I found a question on StackOverflow that outlines the case in which it occurs so I will submit it as a test case:
http://stackoverflow.com/questions/6248224/mysql-unknown-column-error-when-using-alter-don...

I am using MySql 5.5.8 on windows.  Everything is innoDB and utf8_gerneral_ci
[12 Jul 2011 8:52] Valeriy Kravchuk
Adam,

If you suspect a server bug here (as it seems), please, check if it is repeatable with recent server version, 5.5.14.
[12 Aug 2011 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[13 Jun 2013 21:10] Ryan Heathcote
I have experienced this issue with a forward engineer script.
Schema was generated with Schema Sync 0.9.1.
MySQL server version 5.5.31

I think this should be considered a server bug, rather than an issue with the tool that generates the engineer script. I may be wrong, and I'm no expert, nor have I read deeply, but the alter table syntax allows multiple alter specifications in the same alter table statement. This issue is preventing that from being so.
[30 Jul 2014 18:52] Alfredo Kojima
Seems this is an old server bug that was fixed in 5.6.4:
http://bugs.mysql.com/bug.php?id=34972
[30 Jul 2014 18:53] Alfredo Kojima
Closing as Not a Bug, since this is a server bug, which was already fixed.