Abort MySQL script conditionally

Question

Given: MySQL database. Sometimes db schema changes and updates are rolled out (in the form of sql script). In order to guarantee correct order of updates applied (no duplicate updates, no updates missing, etc) I plan to deploy the following solution:

• CREATE TABLE meta (name TEXT PRIMARY KEY, val TEXT);
• INSERT INTO meta VALUES ('version','0');

Each update script carries a version N which is assigned sequentially. Before executing updates the script checks that the meta.version matches the previous script version N-1. After executing updates meta.version is updated to N. I do not need to protect against multiple scripts running in parallel.

The question: How to check the version and abort the script if it doesn't match? I figured out that executing

CALL `raise error`

will break the script, but how to execute it conditionally depending on meta.version? No stored procedures allowed. Meaningful error message is a plus. This doesn't provide a suitable solution.

Answer 1

I think this would best be solved in a shell script, or installer app. You cannot really have any control constructs in SQL script.

A very simple solution would be to create a script that generates the command line for the actual script to execute based on a select on the meta table.

So lets say you have this script, gen_upgrade_command.sql:

select concat(
       'mysql -u<user> -p<password> -h<host> -e"SOURCE upgrade'
   ,    val + 1
   ,   '.sql"'
   )
from meta;

Which you run like this

mysql -u<user> -p<password> -h<host> -Nrs < gen_upgrade_command.sql > do_upgrade.bat

Now, do_upgrade.bat contains this generated command line:

mysql -u<usere> -p<password> -h<host> -e"SOURCE upgrade1.sql"

and running do_upgrade.bat will run upgrade1.sql

Of course you can modify the original script to not select any row at all too, that's just up to you.

Answer 2

Ok, new Answer. It's not so much that the bounty makes me more creative, rather I feel less of a restraint to suggest a solution I don't consider straight :)

(I am still assuming you want to avoid stored routines at all cost)

Solution #1: using prepared statements

Suppose your uprades script is a simple:

ALTER TABLE t ADD COLUMN c INT DEFAULT 1

Then how about this:

SET @version := '1';

SELECT CASE val
           WHEN @version THEN 
               'ALTER TABLE t ADD COLUMN c INT DEFAULT 1'
           ELSE 'SELECT ''Wrong version. Nothing to upgrade.'''
       END
INTO   @stmt
FROM   meta 
WHERE  name = 'Version';

PREPARE stmt FROM @stmt;
EXECUTE stmt;

Obvious drawbacks:

• sever clutter and overhead as you have to write this for each individual statement (PREPARE cannot batch multiple statements)
• additional annoyance because you have to write each statement from your upgrade script as a string...yuck
• not all statements can be written and executed with PREPARE

Solution #2: usa one prepared statement to KILL the connection

You already pointed out that you dislike the solution you linked too...is that because of the KILL statement, or because of the stored function? If it's because of the stored function, you can get around that:

SET @version := '1';

SELECT CASE val 
           WHEN @version THEN 'SELECT ''Performing upgrade...'''
           ELSE CONCAT('KILL CONNECTION', connection_id())
       END
INTO   @stmt
FROM   meta
WHERE  name = 'Version';

PREPARE stmt FROM @stmt;
EXECUTE stmt;  -- connection is killed if the version is not correct.

-- remainder of the upgrade script goes here...

• mysql must be started with the --skip-reconnect option to ensure that no statements can be executed if the connection is killed

I tried to work around this drawback, and use PREPARE for other things that would block out the current connection, such as dropping the user and revoking his privileges. Unfortunately, that does not work as intended (no, also not after FLUSH PRIVILEGES)

Solution #3: use mysql proxy to intercept

Yet another solution: use a proxy. With mysql proxy (see: http://forge.mysql.com/wiki/MySQL_Proxy), you can write a custom intercept script in lua that interprets your custom commands. Using that you could add the necessary control structures. Drawback: you now have to design your own mini language, and learn lua to interpret it :)