MySQL Bugs: #3706: ALTER TABLE MODIFY COLUMN foo IF NOT EXIST

Bug #3706	ALTER TABLE MODIFY COLUMN foo IF NOT EXIST
Submitted:	11 May 2004 0:58	Modified:	21 Feb 2007 13:35
Reporter:	Jonathan Lampe	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
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.

Thank you for the reasonable feature request.

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)

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.

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):

http://www.cryer.co.uk/brian/mysql/howto_add_column_unless_exists.htm