Managing stored procedures in MySQL with Liquibase has been challenging for me. I could never seem to get it to work the way I wanted because the create or replace option is not supported.

I really wanted to be able to edit a big block of procedure code and use the runOnChange option of liquibase.

I tried using $$ to replace my delimiter but it would always throw an error telling me my syntax was wrong even though it would run in workbench.

Luckily, liquibase code is open source, so I pulled a copy and dug through the code looking for something that would give me a clue as to why the replaced delimiter didn’t work.

Well, it turns out that regular expressions are pulled into the mix inside liquibase, so my delimiter should have probably been $$. I bet if I did that it would work, but I wound up doing something else.

Instead of using $$ I tried useing # and what do you know, it worked.

So, now, instead of trying to use the createProcedure tag in liquibase, I use the sql tag with endDelimiter="#" like this:

<changeSet id="1" author="mrankin" runOnChange="true">
    <sql endDelimiter="#">
        <![CDATA[
            DROP PROCEDURE IF EXISTS my_proc;
            #
            CREATE PROCEDURE my_proc()
                BEGIN
                ...
                END
            #
        ]]>
    </sql>
    <rollback/>
</changeSet>

I can now do a quick edit to the procedure and liquibase will pick it up and run it just fine.