Submitted: 11 May 2004 0:58 Modified: 21 Feb 2007 13:35
Reporter: Jonathan Lampe Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any

[11 May 2004 0:58] Jonathan Lampe
An "ALTER TABLE modify column foo IF NOT EXIST..." type syntax would be neat for install-script writers who have to change existing columns in a database but want to avoid reapplying changes made by previous upgrades.  

For example, I tend to use scripts like this in my upgrade procedures.  This is SAFE because the same script can be used to upgrade a version 2 machine to version 3 and reinstall a version 3 edition.  However, the version 3 person (the re-installer) gets hit with database conversions.  

#Version 2 to 3
ALTER TABLE yuck modify column foo int(4);

The reason I don't just want to detect the current version and key off of that is that the reason many people are re-installing in the first place is that one or more ALTER TABLE steps in the upgrade failed.  

This particular feature would cause MySQL to only apply the requested ALTER statement if the arguments did NOT match the current specifications of this particular field.

How to repeat:
Create a two column table.  Add about 100K entries.  Modify the width of column 2 using an ALTER TABLE command.  (Wait a few seconds.)  Issue the same ALTER TABLE command again and MySQL will run through each column again, even though the current column definition is fine.  

Suggested fix:
Only apply an ALTER TABLE...modify column command if the requested specification is different than the current specification.
[21 Feb 2007 13:35] Sveta Smirnova
Thank you for the reasonable feature request.
[16 Dec 2007 7:17] Adam Dorian
Hi. I was trying to find out a way I can CONDITIONALLY run an alter 'CHANGE COLUMN' statement based upon whether it has already been run before.  This is a very important, common practise for large scale applications where scripts need to be re-runabble as part of the deploy process.  

So I looked up the bug http://bugs.mysql.com/bug.php?id=15037 which states that the IF statement doesnt work as documented (Which it doesnt). Then I came here to find that there is also no 'IF NOT EXIST' syntax for column based.  So my question si. In MySQL, how is one supposed to run the following query ONLY if it already hasnt been run before?! Is there any way? 

ALTER TABLE my_table CHANGE COLUMN old_name new_name VARCHAR(100)
[30 Sep 2008 9:15] Konstantin Osipov
There is a quite viable workaround now with stored procedure continue handlers.
Wrap the statement into a stored procedure and install a condition handler for 'no such column' condition, and you get the desired behavior.
[28 Mar 2012 10:40] nerijus navickas
This functionality still not available in 5.1 :( 
It gets quite complicated when you need to maintain existing systems. Believe me I am with big TV company now. 

For example in production system I have no rights to create SP. As you can't create safe-to-run multiple times script it gets feeling that mySql is not enterprise ready yet. Is it so hard to implement? 

Found solution (workaround) here (SP based):