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.

Context

  • 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

MySQL solution

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.

In fact, there is an IF NOT EXISTS clause for CREATE TABLE in MySQL. But unfortunately there is no one for ALTER TABLE ADD COLUMN.

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:

  • IF statements only work in stored procedures, not when run directly, e.g. in mysql client
  • more elegant and concise SHOW COLUMNS does 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 TABLE_SCHEMA=DATABASE(). 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 ;

Comments

blog comments powered by Disqus