Hi,
I'm tyring to use the dbupgrade-maven-plugin plugin to upgrade a MySQL DB.
However I seem to be having problems with the delimiter command.
The following SQL:
DELIMITER !
DROP PROCEDURE IF EXISTS upgrade_database !
CREATE PROCEDURE upgrade_database() BEGIN
IF NOT EXISTS((
select 'true'
from `information_schema`.`columns`
where `table_name` = 'user' and `column_name` = 'first_name'
))
THEN
alter table `user` add column `first_name` varchar(20) not null default
'';
END IF;
IF NOT EXISTS((
select 'true'
from `information_schema`.`columns`
where `table_name` = 'user' and `column_name` = 'family_name'
))
THEN
alter table `user` add column `family_name` varchar(20) not null default
'';
END IF;
IF NOT EXISTS((
select 'true'
from `information_schema`.`columns`
where `table_name` = 'user' and `column_name` = 'email'
))
THEN
alter table `user` add column `email` varchar(20) not null default '';
END IF;
IF NOT EXISTS((
select 'true'
from `information_schema`.`columns`
where `table_name` = 'user' and `column_name` = 'dob'
))
THEN
alter table `user` add column `dob` datetime not null;
END IF;
END !
CALL upgrade_database() !
DROP PROCEDURE IF EXISTS upgrade_database !
DELIMITER ;
... will perfectly fine if I execute it through MySQL Query Browser. However
if I use the same SQL in my dbupgrade script I get this error:
[ERROR] Failed to execute goal
org.codehaus.mojo:dbupgrade-maven-plugin:1.0-beta-1-SNAPSHOT:filelist-upgrade
(dbupgrade) on project ZenTemplate: Unable to perform file upgrade:
D:\workspaces\poc\ZenTemplate\src\db\upgrade\01\00000003.sql. Unable to
execute: DELIMITER !
[ERROR] DROP PROCEDURE IF EXISTS upgrade_database !
[ERROR] CREATE PROCEDURE upgrade_database() BEGIN
[ERROR]
[ERROR]
[ERROR] IF NOT EXISTS((
[ERROR] select 'true'
[ERROR] from `information_schema`.`columns`
[ERROR] where `table_name` = 'user' and `column_name` = 'first_name'
[ERROR] ))
[ERROR] THEN
[ERROR] alter table `user` add column `first_name` varchar(20) not null
default '' You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'DELIMITER !
[ERROR] DROP PROCEDURE IF EXISTS upgrade_database !
[ERROR] CREATE PROCEDURE upgra' at line 1
[ERROR] -> [Help 1]
[ERROR]
[ERROR] To see the full stack trace of the errors, re-run Maven with the -e
switch.
[ERROR] Re-run Maven using the -X switch to enable full debug logging.
Any ideas why this might be? Is there a work around I can use?
Cheers!
Adam
