Add column safely in MySQL and other idempotent db migrations
With all the NoSQL hype nowadays this post regarding MySQL probably appears a bit archaic. But this is what kept me busy last two days.
In our current Rails project we agreed with admins to use DDL and SQL scripts to implement the db structure- (and sometimes db content-) adjustments.
- as little downtime as possible and sometimes even less is required
- if off time is approved, then such upgrade is done in the night
- developers have no access to production system
Advantages of SQL
Using hand crafted DDL and SQL scripts gives following advantages:
- our admins have better feeling while being able to run single statements from the migration script and recover in trivial cases
- when data changes, e.g. normalization/denormalization needed, the SQL statements are much faster than Rails code involving creation of huge amounts of Ruby objects
I also wanted to make the scripts idempotent so in case there were some problems and the structure/data conversion run only partially it would be possible to do some changes and rerun the whole script. It should be easy, I thought, just prepend every change statement with an if. With Oracle or Microsoft SQL Server it is just a matter of seconds.
Searching the internet does not help in case of MySQL. The official documentation is insufficient and the signal to noise ratio in MySQL relatet forums is lower, than for every other technology I’ve ever seen. Even stackoverflow contains unprecise or unhelpful.
It took me some time to explore all the ways and byways of MySQL programming. The problems I had to deal with:
IFstatements only work in stored procedures, not when run directly, e.g. in mysql client
- more elegant and concise
SHOW COLUMNSdoes not work in stored procedure
- the syntax for delimiting statements is strange in MySQL, so you have to redefine the delimiter to be able to create stored procedures. Do not forget to switch the delimiter back!
- INFORMATIONAL_SCHEMA is global for all databases, do not forget to filter on
DATABASE()returns the name of the currently selected database.
Here is the working solution (tried out on MySQL 5.0 for Solaris):
DELIMITER $$ DROP PROCEDURE IF EXISTS upgrade_database_1_0_to_2_0 $$ CREATE PROCEDURE upgrade_database_1_0_to_2_0() BEGIN -- rename a table safely IF NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='my_old_table_name') ) THEN RENAME TABLE my_old_table_name TO my_new_table_name, END IF; -- add a column safely IF NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND COLUMN_NAME='my_additional_column' AND TABLE_NAME='my_table_name') ) THEN ALTER TABLE my_table_name ADD my_additional_column varchar(2048) NOT NULL DEFAULT ''; END IF; END $$ CALL upgrade_database_1_0_to_2_0() $$ DELIMITER ;