Is there a workaround for JDBC w/liquibase and MySQL session variables & client side SQL instructions

Slowly building a starter changeSet xml file for one of three of my employer's primary schema's. The only show stopper has been incorporating the sizable library of MySQL stored procedures to be managed by liquibase.

One sproc has been somewhat of a pain to deal with: The first few statements go like

use TargetSchema;
select "-- explanatory inline comment thats actually useful --" into vDummy;

set @@session.sql_mode='TRADITIONAL' ;

drop procedure if exists adm_delete_stats ;

delimiter $$

create procedure adm_delete_stats(
...rest of sproc

I cut out the use statement as its counter-productive, but real issue is the set @@session.sql_mode statement which causes an exception like

 liquibase.exception.MigrationFailedException: Migration failed for change set ./foobarSchema/sprocs/adm_delete_stats.xml::1293560556-151::dward_autogen dward:
 Reason: liquibase.exception.DatabaseException: Error executing SQL ...

And then the delimiter statement is another s开发者_运维知识库tumbling block.

Doing do dilligence research I found this rejected MySQL bug report here and this MySQL forum thread that goes a little bit more in depth to the problem here.

Is there anyway I can use the sproc scripts that currently exist with Liquibase or would I have to re-write several hundred stored procedures?

I've tried createProcedure, sqlFile, and sql liquibase tags without much luck as I think the core issue is that set, delimiter, and similar SQL commands are meant to be interpreted and acted upon by the client side interpreter before being delivered to the server.

Yes, I think the problem is that your script is assuming it will run through the mysql client which has additional capabilities not present in JDBC.

Liquibase will split your statements on the delimiter (defaults to ; but can be changed with the delimiter attribute) then feeds each statement to the database. If you specify $$ as the delimiter, you can remove the "delimiter $$" line, but each line before it will need to have the ; replaced with $$. Besides that, there is other client-specific SQL like the @@session line. I think that may be unnecessary without going through the client, but I am not completely sure what it does.

You should be able to get your procedures to work, but it will take some re-writing.

If you would rather not re-write all your procedures, you could use something like the executeCommand tag which would allow you to call the mysql client and feed in your existing script. You loose some things liquibase gives you like the updateSQL mode and you owuld have to make sure the mysql client exists everywhere you run your changelog from, but it would keep you from having to rewrite your scripts.

This worked for me: http://comments.gmane.org/gmane.comp.db.liquibase.user/480

In short it says this:

<changeSet id="123321-4" author="ehrhardt">
  <sql>DROP PROCEDURE IF EXISTS curdemo;</sql>
  <sql splitStatements="false" stripComments="false">
    <![CDATA[ 
      CREATE PROCEDURE curdemo()
      BEGIN
        DECLARE done INT DEFAULT 0;
        blah..
        blah..
      END;
    ]]>
  </sql>
  <sql>call curdemo();</sql>
</changeSet>