MySQL Bugs: #60650: Forward ingeneer SQL ALTER Script may provokes 1054 error

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

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`;

Tags added

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.

@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.

Please, send initial .mwb file and describe what exact changes in table structure in this .mwb should be made to demonstrate the problem.

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".

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

Adam,

If you suspect a server bug here (as it seems), please, check if it is repeatable with recent server version, 5.5.14.

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".

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.

Seems this is an old server bug that was fixed in 5.6.4:
http://bugs.mysql.com/bug.php?id=34972

Closing as Not a Bug, since this is a server bug, which was already fixed.