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 9 [Failed SQL

Liquibase Development
1

We’re attempting to create a stored procedure using Liquibase scripts… Able to execute DDL, DQL, DML, DCL like queries using liquibase but we’re encountering difficulties creating the stored procedure.
Below are the screenshots of the XML and SQL files we’re using, and following that is the error trace we’re encountering during execution. Could you please assist us in identifying if we’re overlooking anything in the XML file or elsewhere?
SQL Version: mysql Ver 8.2.0

Exception Trace:
Invocation of init method failed; nested exception is liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for change set database/release_0.0.5.1/changeset/1_insert_debug_log_proc.xml::release_0.0.5.1_1::sheshank@backbase.com:
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 9 [Failed SQL: (1064) CREATE PROCEDURE insert_debug_log_proc(
IN p_procedure_name VARCHAR(255),
IN p_procedure_invocation_id VARCHAR(255),
IN p_log_type VARCHAR(255),
IN p_log_message TEXT
)
BEGIN
INSERT INTO PROCEDURE_DEBUG_LOGS (procedure_name, procedure_invocation_id, log_type, log_message)
VALUES (p_procedure_name, p_procedure_invocation_id, p_log_type, p_log_message)]

Below is my stored procedure.

use x.......;

CREATE PROCEDURE insert_debug_log_proc(IN p_procedure_name VARCHAR(255),IN p_procedure_invocation_id VARCHAR(255), IN p_log_type VARCHAR(255), IN p_log_message TEXT)
BEGIN
INSERT INTO PROCEDURE_DEBUG_LOGS (procedure_name, procedure_invocation_id, log_type, log_message) VALUES (p_procedure_name, p_procedure_invocation_id, p_log_type, p_log_message);
END;

xml file

Create debug logs procedure

Please help me here, unable to resolve this error from so long time

2

It helps to see the entire .xml file, but basically you need to set the endDelimiter for the changeset since Liquibase uses the semicolon by default, which doesn’t work for procedures, functions, packages, etc. It can be any character not in your code.

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">

<changeSet id="create_procedure" author="BOB" runOnChange="true">
  <sql endDelimiter="/">
    create or replace procedure test_proc is
      v_dummy1 date;
    begin
      --This is select 1
      select sysdate into v_dummy1 from "DUAL";
    end test_proc;
    /  
  </sql>
  <rollback>
    DROP PROCEDURE test_proc;
  </rollback>
</changeSet>

</databaseChangeLog>
3
<?xml version="1.0" encoding="UTF-8"?> 

<changeSet id="xrelease" author="BOB" runOnChange="true">
    <comment>Create debug logs procedure</comment>
    <sql endDelimiter="/">
        use `custom-schema-dd`;
        create or replace procedure insert_debug_log_proc is
            p_procedure_name VARCHAR(255),
            p_procedure_invocation_id VARCHAR(255),
            p_log_type VARCHAR(255),
            p_log_message TEXT;
        begin
            insert into procedure_debug_logs (procedure_name, procedure_invocation_id, log_type, log_message) values (p_procedure_name, p_procedure_invocation_id, p_log_type, p_log_message);
        end insert_debug_log_proc;
        /
    </sql>
    <rollback>
        <sqlFile path="rback/1_drop_insert_debug_log_proc.sql"/>
    </rollback>
</changeSet>

@daryldoak , Made the xml changes as suggested , but still facing below issue after executing.

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 ‘create or replace procedure insert_debug_log_proc is
p_procedure’ at line 2 [Failed SQL: (1064) use custom-schema-dd;
create or replace procedure insert_debug_log_proc is
p_procedure_name VARCHAR(255),
p_procedure_invocation_id VARCHAR(255),
p_log_type VARCHAR(255),
p_log_message TEXT;
begin
insert into procedure_debug_logs (procedure_name, procedure_invocation_id, log_type, log_message) values (p_procedure_name, p_procedure_invocation_id, p_log_type, p_log_message);
end insert_debug_log_proc;]
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredMethodElement.resolveMethodArguments(AutowiredAnnotationBeanPostProcessor.java:774) ~[spring-beans-5.3.29.jar:5.3.29]

Please help

4

That statement is causing your issue. It is separate statement within the changeset, and since you defined “/” as the end-delimiter for the changeset, that statement must also be delimited with “/”, not semicolon.

5 
    <sql endDelimiter="/">
        use `custom-schema-dd`
        /
        create procedure insert_debug_log_proc(
            p_procedure_name VARCHAR(255),
            p_procedure_invocation_id VARCHAR(255),
            p_log_type VARCHAR(255),
            p_log_message TEXT
        )

        begin

            insert into procedure_debug_logs (procedure_name, procedure_invocation_id, log_type, log_message) values (p_procedure_name, p_procedure_invocation_id, p_log_type, p_log_message);

        end insert_debug_log_proc;

        /
    </sql>

Refracted as above, getting same exception till

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 ‘insert_debug_log_proc’ at line 9 [Failed SQL: (1064) create procedure insert_debug_log_proc(
p_procedure_name VARCHAR(255),
p_procedure_invocation_id VARCHAR(255),
p_log_type VARCHAR(255),
p_log_message TEXT
)
begin
insert into procedure_debug_logs (procedure_name, procedure_invocation_id, log_type, log_message) values (p_procedure_name, p_procedure_invocation_id, p_log_type, p_log_message);
end insert_debug_log_proc;]
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredMethodElement.resolveMethodArguments(AutowiredAnnotationBeanPostProcessor.java:774) ~[spring-beans-5.3.29.jar:5.3.29]

6
Create debug logs procedure use `custom-schema-dd` / create procedure insert_debug_log_proc is p_procedure_name VARCHAR(255), p_procedure_invocation_id VARCHAR(255), p_log_type VARCHAR(255), p_log_message TEXT; begin insert into procedure_debug_logs (procedure_name, procedure_invocation_id, log_type, log_message) values (p_procedure_name, p_procedure_invocation_id, p_log_type, p_log_message); end insert_debug_log_proc; / 
<rollback>
    <sqlFile path="rback/1_drop_insert_debug_log_proc.sql"/>
</rollback>

Refracted as above.

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 ‘insert_debug_log_proc’ at line 9 [Failed SQL: (1064) create procedure insert_debug_log_proc(
p_procedure_name VARCHAR(255),
p_procedure_invocation_id VARCHAR(255),
p_log_type VARCHAR(255),
p_log_message TEXT
)
begin
insert into procedure_debug_logs (procedure_name, procedure_invocation_id, log_type, log_message) values (p_procedure_name, p_procedure_invocation_id, p_log_type, p_log_message);
end insert_debug_log_proc;]
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredMethodElement.resolveMethodArguments(AutowiredAnnotationBeanPostProcessor.java:774) ~[spring-beans-5.3.29.jar:5.3.29]

7

Still facing the same issue after having delimeter at use statement.
Create debug logs procedure

use custom-schema-dd
/
create or replace procedure insert_debug_log_proc is
p_procedure_name VARCHAR(255),
p_procedure_invocation_id VARCHAR(255),
p_log_type VARCHAR(255),
p_log_message TEXT;
begin
insert into procedure_debug_logs (procedure_name, procedure_invocation_id, log_type, log_message) values (p_procedure_name, p_procedure_invocation_id, p_log_type, p_log_message);
end insert_debug_log_proc;
/



Exception:
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 ‘insert_debug_log_proc’ at line 9 [Failed SQL: (1064) create procedure insert_debug_log_proc(
p_procedure_name VARCHAR(255),
p_procedure_invocation_id VARCHAR(255),
p_log_type VARCHAR(255),
p_log_message TEXT
)
begin
insert into procedure_debug_logs (procedure_name, procedure_invocation_id, log_type, log_message) values (p_procedure_name, p_procedure_invocation_id, p_log_type, p_log_message);
end insert_debug_log_proc;]
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredMethodElement.resolveMethodArguments(AutowiredAnnotationBeanPostProcessor.java:774) ~[spring-beans-5.3.29.jar:5.3.29]

8

@daryldoak Any help here please

9

The error is stating that you have a sql syntax error in the procedure:

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 ‘insert_debug_log_proc’ at line 9

I don’t know anything about mysql, so unfortunately I won’t be able to help with the syntax issue.

I’d recommend removing the “use” statement and focus instead on the procedure. then once the procedure works, add the “use” back in.