MySQL Derterministic migration script

Question

I am currently looking a way to have my database under version control. To achieve so, I wanted to have deterministic procedures that can only be run only once (with corresponding undo).

I have a problem building my first script which is riddled with small bugs.

Here are the 3 main parts :

Condition to execute query (if field doesn't exists)

SELECT *
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'my_database'
  AND TABLE_NAME = 'my_table'
  AND COLUMN_NAME = 'full_name'

The table alteration:

ALTER TABLE
  my_table
  ADD full_name VARCHAR(255) NOT NULL;

And finally the data migration

UPDATE candidat dest JOIN candidat src ON dest.id = src.id
      SET dest.full_name = CONCAT(src.first_name, ' ', IF(src.middle_name='', '', CONCAT(src.middle_name, ' ')), src.last_name);

I'am trying to make this work in this form:

DELIMITER $$
DROP PROCEDURE IF EXISTS migration_001;

CREATE PROCEDURE migration_001()

BEGIN
  IF NOT EXISTS (
    SELECT *
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = 'my_database'
      AND TABLE_NAME = 'my_table'
      AND COLUMN_NAME = 'full_name') 
   THEN

     ALTER TABLE
      my_table
      ADD full_name VARCHAR(255) NOT NULL;

    UPDATE candidat dest JOIN candidat src ON dest.id = src.id
          SET dest.full_name = CONCAT(src.first_name, ' ', IF(src.middle_name='', '', CONCAT(src.middle_name, ' ')), src.last_name);
  END IF
END;
$$

Current error I am getting:

1064 : ...  right syntax to use near 'CREATE PROCEDURE migration_001() BEGIN IF NOT EXISTS ( SELECT * ' at line 3

Can anyone point me in the right direction for solving this?

BTW I am using 5.5.16-log - MySQL Community Server.

How do you know there are bugs - are you getting errors/incorrect data? Can you post them? — PinnyM, Jan 10 '13 at 20:51
Could it be related to `DELIMTER ##`? I think that the second line should look like `DROP PROCEDURE IF EXISTS migration_001##` since the delimiter is no longer `;`. — ESG, Jan 10 '13 at 21:31
You have a DROP PROCEDURE written in before the CREATE PROCEDURE. If you want to drop it before running the create, then end with your $$ delimiter. What you have between the DELIMITER command and the ending $$ is not valid as a single MySQL statement. — scwagner, Jan 10 '13 at 21:31

score 1 · Accepted Answer · answered Jan 10 '13 at 21:36

1

Change the order of

DELIMITER $$

and

DROP PROCEDURE IF EXISTS migration_001;

Currently you are using the wrong delimiter to drop the procedure.

answered Jan 10 '13 at 21:36

Andreas Wederbrand

38,065
11
68
78

MySQL Derterministic migration script

1 Answers1