MySQL Bugs: #62214: Adding a column X after a column that were renamed

Bug #62214	Adding a column X after a column that were renamed
Submitted:	19 Aug 2011 17:04	Modified:	24 Aug 2011 18:23
Reporter:	Mahomedalid Pacheco	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.2.34	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	add column, change, model, synchronize

Description:
If I add a column X after a column Y that were renamed the synchronize SQL of a table try to add the column after the column X but with the old name, so this cause a ERROR: Error 1054: Unknown column 'X' in 'table'

How to repeat:
Add a table <test> with 3 fields:

id SMALLINT
column_x VARCHAR(5)

Synchronize it.

Add a column y (column_y VARCHAR(5)) after column_x, rename column_x to column_z.

id SMALLINT
column_z VARCHAR(5)
column_y VARCHAR(5)

The SQL for synchronize the model will be:

ALTER TABLE db.test 
ADD COLUMN column_y AFTER column_z,
CHANGE COLUMN column_x column_z VARCHAR(5).

Synchronize it.

Suggested fix:
Two suggested fixes:

- Do the change's before the add columns
- Use the old name when add columns

Hello,
Thanks for your report.

May you please let us know what version of the Workbench are you using and what happen if you change the lines on your script:

ALTER TABLE db.test 
CHANGE COLUMN column_x column_z VARCHAR(5),
ADD COLUMN column_y AFTER column_z.

Changing the version of the bug,.

ALTER TABLE db.test 
CHANGE COLUMN column_x column_z VARCHAR(5) DEFAULT NULL,
ADD COLUMN column_y VARCHAR(5) DEFAULT NULL AFTER column_z.

Your SQL query has been executed successfully

Thank you for the bug report.

Sorry edited wrong bug opening again.

Hello, can you please share what server version are you using?, also please paste the info you get from the Workbench when you select Help ->System Info.

Thanks in advance

Well, I'm using multiple servers and everyone has the same error. This is one of them:

Server version: 5.0.45-log Mandriva Linux - MySQL Standard Edition (GPL)

About WB:

MySQL Workbench CE for Linux/Unix version 5.2.34
Configuration Directory: /home/mpacheco/.mysql/workbench
Data Directory: /usr/share/mysql-workbench
Cairo Version: 1.10.0
Rendering Mode: OpenGL is not available on this system, so Native is used for rendering.
OS: Linux 2.6.35-30-generic
CPU: 2x AMD Athlon(tm) 64 X2 Dual Core Processor 4000+ 1000.000 MHz, 2.0 GB RAM

Hi,

Can you please update your server version and try again?

Thanks again

Same error in Server version: 5.1.49-1ubuntu8.1 (Ubuntu)

Sorry, in server version: 5.1.49-1ubuntu8.1 (Ubuntu) is not happening only in server version 5.0. My mistake.

OK, just to verify, your problem was solved with the server upgrade right?

Well, I can't upgrade this server, because is in production, so if there is no other solution I think I will live with this problem.

We verified that the problem is solved on 5.0.90 (if you can't upgrade your server to the newest version), we can't fix the problem on previous versions because are not supported anymore.

Thanks for reporting your bug.