Problem with Formatted SQL stored procedure and Mysql 5.6

General Discussion
1

Hi to all i’am a new user of liquibase have a problem with “formatted sql” for generate a stored procedure in Mysql (5.6):


i have this “changeLog.xml”


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

 

<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-3.0.xsd">

 

   

  


whit this “test.sql”  


----------------------------------------------------------------------------------------------

–liquibase formatted sql

–changeset test:201

delimiter # 

DROP PROCEDURE IF EXISTS sayHelloWorld;

            create procedure sayHelloWorld()

            BEGIN

            SELECT ‘Hello World From The Database!’;

            END; #


delimiter ;


–rollback DROP PROCEDURE IF EXISTS sayHelloWorld;


–changeset test:202

delimiter # 

DROP PROCEDURE IF EXISTS sayHelloWorld;

create procedure sayHelloWorld()

BEGIN

SELECT ‘Hello World From a MySql Database!’;

END; #


delimiter ;

-----------------------------------------------------------------------------------------------------------




 i have this error:


Liquibase update Failed: Error executing SQL delimiter #

DROP PROCEDURE IF EXISTS sayHelloWorld: You have an error in your SQL syntax; ch

eck the manual that corresponds to your MySQL server version for the right synta

x to use near 'delimiter #

DROP PROCEDURE IF EXISTS sayHelloWorld’ at line 1


my log is 


--------------------------------------------------------------------------------------------------------------


SEVERE 25/06/13 12.27:liquibase: Error executing SQL delimiter # 

DROP PROCEDURE IF EXISTS sayHelloWorld: 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 # 

DROP PROCEDURE IF EXISTS sayHelloWorld’ at line 1

liquibase.exception.MigrationFailedException: Migration failed for change set src/test.sql::201::test:

     Reason: liquibase.exception.DatabaseException: Error executing SQL delimiter # 

DROP PROCEDURE IF EXISTS sayHelloWorld: 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 # 

DROP PROCEDURE IF EXISTS sayHelloWorld’ at line 1:

          Caused By: Error executing SQL delimiter # 

DROP PROCEDURE IF EXISTS sayHelloWorld: 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 # 

DROP PROCEDURE IF EXISTS sayHelloWorld’ at line 1:

          Caused By: 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 # 

DROP PROCEDURE IF EXISTS sayHelloWorld’ at line 1

… 9 more



I use the script in the workbench and i have no problem(i tried to change the script with remove the word delimiter but this not solve the issue).There is some one that can help me thanks por for the patience. 

2

Many many thanks ,it’s work for me.

3

I think the “delimiter” business is not a proper statement that MySQL understands, but an extension that some of the tools (workbench, mysqldump) use when parsing a multi-statement input. Liquibase has its own way of doing the same thing, so try this:

  1. –liquibase formatted sql
  2. –changeset me:test1 runOnChange:true endDelimiter:#
  3. DROP PROCEDURE IF EXISTS sayHelloWorld;
  4. #
  5. CREATE PROCEDURE sayHelloWorld()
  6. BEGIN
  7.     SELECT ‘Hello World From a MySql Database!’;
  8. END
  9. #

4

It’s actually a regular expression, so try:
$$

5

Does anyone happen to know how I can make the endDelimiter:$$ ?  I’ve tried it with and without quotes and it doesn’t seem to work.  If I change it to a # it works but a $ or two $$ it doesn’t.  I see the same type of error as mentioned above.  Here is a snippet of my code.  Thanks!

 

–liquibase formatted sql

–changeset molson:AccountDisc endDelimiter:$$

 


DROP PROCEDURE IF EXISTS AccountDisc $$
CREATE DEFINER=cr_api@%</a>  PROCEDURE AccountDisc(
 IN v_transaction_id VARCHAR(100),
.

.

.

 
6

Thank you so much!!  That worked

7

By the way, what was the fix you made here ? I was also facing the same issue.