Liquibase: SQL syntax error in a MySQL stored procedure

Question

I tried to run the MySQL stored procedure SQL script by Liquibase, but never worked.

The content of db.storedprocedure.xml:

<changeSet author="zzz" id="1" runOnChange="true" runInTransaction="true">
    <sqlFile path="changelogs/change_03.sql"
         relativeToChangelogFile="true"
         endDelimiter="$$"
         stripComments="false"
         splitStatements="false"/>
</changeSet>

The content of my SQL file change_03.sql:

$$

CREATE PROCEDURE `liqui01`.`User_Search`(
    INOUT id INT,
    OUT name VARCHAR(50)
)
BEGIN

    SET @sql = CONCAT("SELECT id, name FROM user WHERE da.MarketId = ", id );

    PREPARE stmt from @sql;
    EXECUTE stmt;

END$$

It shows the error like:

Unexpected error running Liquibase: 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 '$$ ...

I've tried to change the "$$" to other delimiters, or put the SQL inside <sql> tags of the XML file, all didn't work.

Any advise would be appreciated!

Update 1

@Shadow has given the correct answer (unfortunately I cannot mark it as the answer because it's in the comments), removing the delimeter lines from the sql scripts will make it work, thanks for him!

Now the question is: How to use "endDelimiter" parameter?

Why do you have an $$ at the start of the stored proc? It is not required. Also note, that this query does not need a prepared statement (this is not related to the error message you received) — Shadow, Sep 07 '17 at 17:10
@Shadow Hi man, you saved my time! Still wonder why "endDelimiter" doesn't work, and why they provide such a parameter ... I copied the script from other stored procedure, and removed some other parts just for testing liquibase, but forget removing the "prepare stmt" part :) — , Sep 07 '17 at 19:36
Please refer to the complete procedure -> https://stackoverflow.com/questions/1267172/mysql-delimiter-error/67905510#67905510 — Ishaq Khan, Jun 09 '21 at 14:04

score 0 · Answer 1 · answered Sep 08 '17 at 02:05

0

liquibase internals add the delimiter command to begin of command, you must remove or comment the fist line with $$:

-- $$

CREATE PROCEDURE `liqui01`.`User_Search`(

...

END$$

answered Sep 08 '17 at 02:05

Ivan Cachicatari

4,212
2
21
41

1

thanks for you advice, but unfortunately it doesn't work, the error message is a little different: ... use near '' at line 12 ... (not "near '$$' ...") – Sep 08 '17 at 14:59
delete all characters before `CREATE PROCEDURE` and run liquibase. – Ivan Cachicatari Sep 08 '17 at 23:43
Thanks Ivan, but that doesn't work, it shows the same error as in my previous comment. – Sep 11 '17 at 14:00

score 0 · Answer 2 · answered Jan 23 '19 at 19:56

I searched the internet for a solution. It appears Liquibase will create stored procedures just fine on the update command. But falls apart on the updateSQL command as it outputs invalid SQL.

My solution was to add comments to the changesets sql tag. With the aim of using regex to sanitize the output sql.

The .sql file I am using has some keywords liquibase does not like e.g. DELIMITER, so I use <modifysql> and <regExpReplace> to lint / sanitize the .sql file. This way, the .sql file is consumable by liquibase and runs successfully on update without errors.

However, we'd still output invalid SQL on updateSQL. To resolve this, I use another regex search/replace on the invalid outputted .sql file looking for the comments; -- DELIMITER $$ etc.

Example:

<changeSet id="Test1" author="author_name" context="master" runOnChange="true">
    <validCheckSum>any</validCheckSum>
    <sql><![CDATA[
        DROP PROCEDURE IF EXISTS `sp_myProcedure`;
        -- DELIMITER $$
    ]]></sql>
    <createProcedure path="yourpath/procs/sp_myProcedure.sql" relativeToChangelogFile="true" />
    <sql><![CDATA[
        -- $$ DELIMITER;
    ]]></sql>
    <modifySql>
        <regExpReplace replace="[^}]+CREATE PROCEDURE" with="CREATE PROCEDURE"/>
    </modifySql>
    <modifySql>
        <regExpReplace replace="END\s*\$\$[^}]*" with="END"/>
    </modifySql>
</changeSet>

score 0 · Answer 3 · answered May 23 '22 at 05:14

Please refer the official Liquibase documentation link : here which mentions about escaping the delimiter character $$ like \$\$.

When setting an end-delimiter, note that certain DBMS and operating systems require delimiter values to be escaped. For example, a $$ end-delimiter with mysql requires escaping as: end-delimiter="$$".

Liquibase: SQL syntax error in a MySQL stored procedure

Update 1

3 Answers3