MySQL 5.7 stored procedure error DELIMITER' at line 1 [Failed SQL: (1064) DELIMITER]

Community Answers
1

Hi,

I am using sql formatted file to create stored procedure using liquibasse 4.3.2. Below is the example content of my sql formatted file (I have replaced table names etc with xxxx).

--liquibase formatted SQL
--changeset xxxxx:version1 runOnChange:true
DELIMITER  ;
USE liquibase;
DROP PROCEDURE IF EXISTS test_procedure;
DELIMITER $$
CREATE DEFINER = 'test_procedure'@'localhost' PROCEDURE `test_procedure`(xxxx VARCHAR(21844))                               
sp: BEGIN
    IF (UPPER(xxxx) LIKE '%;%' OR
        UPPER(xxxxxx) LIKE '%CHANGE%' OR
        UPPER(xx) LIKE '%WHILE%'
    ) THEN
        SELECT 'ERROR' AS msg;
        LEAVE sp;
    END IF;

    SET @sql_part1 = "SELECT
        * from test_table";

    SET @sql_part2 = ")
        AND xxxx = 'test';

    SET @sql = CONCAT( @sql_part1, xxxxxxxx, @sql_part2 );
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
END$$
DELIMITER;

The error I am getting is below:-

liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for change set myChangelogStor.sql::version1::xxxxxx:
     Reason: liquibase.exception.DatabaseException: 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 [Failed SQL: (1064) DELIMITER]**
	at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:124)

I have also tried using different values for for e.g. endDelimiter:"$$" or endDelimiter:$$ and without this but I get the same error. Please help how I can use liquibase to run sql formatted file to create stored procedure in MySQL 5.7?
Thanks
Rahul

2

As a test I removed all the DELIMITER statements and kept everything else as is and now I am getting another syntax related error

[2021-07-16 11:11:32] FINE [liquibase.executor] Executing with the 'jdbc' executor
[2021-07-16 11:11:32] INFO [liquibase.lockservice] Successfully released change log lock
Unexpected error running Liquibase: Migration failed for change set myChangelogStor.sql::2::XXXXX:
     Reason: liquibase.exception.DatabaseException: 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 '' at line 69 [Failed SQL: (1064) CREATE DEFINER = 'test_procedure'@'localhost' PROCEDURE `test_procedure`(xxxx VARCHAR(21844))  
sp: BEGIN

So why this is giving syntax error? Is there anything I can add to get pass through this syntax error? Appreciate any help. Thanks Rahul

3

Try using the logLevel=info or debug which should echo the executed sql in the Liquibase output.

4

I have used logLevel=debug only when I am getting sql error. This works fine when I execute it via MySQL client but it fails when using liquibase.

5

Also when I use updateSQL and use the generate sql statements directly on the MySQL prompt the procedure is successfully created but it fails when executed via update in liquibase with error

Unexpected error running Liquibase: Migration failed for change set myChangelogStor.sql::2::xxxxxx:
     Reason: liquibase.exception.DatabaseException: 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 'DILIMITER ;
USE liquibase;
DROP PROCEDURE IF EXISTS xxxxxxxxxxxxxxx;
DE' at line 1 [Failed SQL: (1064) DILIMITER ;
6

I fixed it by removing word DELIMITER in my sql which is not needed by Liquibase.

7

That’s super awesome. Great, you were able to solve the issue and Thanks so much for sharing the solution. This will help our community friends.

Thanks!
Rakhi Agrawal