Liquibase updateSQL for mysql with stored routines

Question

My migrations are working perfectly but now my deployment strategy changed so I have to generate complete schema using updateSQL command . But it seems it does not add pre/post delimiters around a round e.g

DROP TRIGGER IF EXISTS `labels_before_ins_tr`;

CREATE DEFINER='root'@'localhost' TRIGGER `labels_before_ins_tr` BEFORE INSERT ON `labels` FOR EACH ROW BEGIN
 ...
END 

DROP TRIGGER IF EXISTS `labels_before_upd_tr`;

What I expected was:

DROP TRIGGER IF EXISTS `labels_before_ins_tr`;
delimiter ;;        
CREATE DEFINER='root'@'localhost' TRIGGER `labels_before_ins_tr` BEFORE INSERT ON `labels` FOR EACH ROW BEGIN
 ...
END 
;;
delimiter ;    

DROP TRIGGER IF EXISTS `labels_before_upd_tr`;

How we can generate this behaviour using liquibase updateSQL . Just to note mysql migrations are working perfectly when using update command

One of my migration files for trigger look like this:

<changeSet id="1376897476-30" author="sakhunzai" runOnChange="true" >
 <sql>DROP TRIGGER IF EXISTS `labels_before_ins_tr`</sql>        
 <sqlFile endDelimiter="" splitStatements="false" path="sql/30_labels_before_ins_tr.sql" relativeToChangelogFile="true" />
 <rollback>DROP TRIGGER `labels_before_ins_tr`</rollback>           
</changeSet>

Edit:

I tweaked the migration file to generate the desired output :

<changeSet id="1376897476-30" author="sakhunzai" runOnChange="true" >
     <sql endDelimiter="" splitStatements="false">DROP TRIGGER IF EXISTS `labels_before_ins_tr`; delimiter //</sql>        
     <sqlFile endDelimiter=" //" splitStatements="false" path="sql/30_labels_before_ins_tr.sql" relativeToChangelogFile="true" />
     <sql endDelimiter="" splitStatements="false">delimiter  ;</sql>
     <rollback>DROP TRIGGER `labels_before_ins_tr`</rollback>           
    </changeSet>

Now the output from updateSQL command looks good but update command give this error :

Unexpected error running Liquibase: Error executing SQL DROP TRIGGER IF EXISTS `labels_before_ins_tr`; delimiter //: 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 //' at line 1 

The sql statement DROP TRIGGER IF EXISTSlabels_before_ins_tr; delimiter // is correct in the contenxt of mysql, however update command fails probably it expects ; or even // to terminate the statement

Finally I have only one options i.e to user a special context to be used with updateSQL command to add delimiter , that seems ugly unless I have proper solution.

Answer 1

I came around this solution finally to generate valid sql, which works with both update and updateSQL commands.

 <changeSet id="1376897476-30" author="sakhunzai" runOnChange="true" >
 <sql endDelimiter=";\ndelimiter $$">DROP TRIGGER IF EXISTS `labels_before_ins_tr`</sql>        
 <sqlFile endDelimiter="\n$$\ndelimiter ;" splitStatements="false" path="sql/30_labels_before_ins_tr.sql" relativeToChangelogFile="true" />
 <rollback>DROP TRIGGER `labels_before_ins_tr`</rollback>           
</changeSet>

To the output from updateSQL will be

DROP TRIGGER IF EXISTS `labels_before_ins_tr`;
delimiter $$        
CREATE DEFINER='root'@'localhost' TRIGGER `labels_before_ins_tr` BEFORE INSERT ON `labels` FOR EACH ROW BEGIN
 ...
END 
$$
delimiter ;