Liquibase throws error when yaml file containing SQL has semi-colons

0
votes

I'm using Liquibase to update some tables and I seem to run into an error that whenever my .yaml file has a ; in it, it throws the error (only put in part of it as it just repeats the same error message):

SEVERE 1/11/18 11:39 AM: liquibase: _master.xml: Tables/Team/Team.updateTeamNames.yaml::1::mayj4: Ch ange Set Tables/Team/Team.updateTeamNames.yaml::1::mayj4 failed. Error: You have an error in your S QL syntax; check the manual that corresponds to your MySQL server version for the right syntax to us e near 'UPDATE Team SET Name ='B' WHERE Name ='Y'; UPDATE Team SET Name ='C' WHERE Name ' at line 1 [Failed SQL: UPDATE Team SET Name = 'A' WHERE Name = 'X'; UPDATE Team SET Name ='B' WHERE Name ='Y'; UPDATE Team SET Name ='C' WHERE Name ='Z'] liquibase.exception.DatabaseException: You have an error in your SQL syntax; check the manual that c orresponds to your MySQL server version for the right syntax to use near 'UPDATE Team SET Name ='B' WHERE Name ='Y'; UPDATE Team SET Name ='C' WHERE Name ' at line 1 [Failed SQL: UPDATE Team SET Name = 'A' WHERE Name = 'X'; UPDATE Team SET Name ='B' WHERE Name ='Y'; UPDATE Team SET Name ='C' WHERE N ame ='Z']

This is the command I am using as well:

liquibase --driver=com.mysql.cj.jdbc.Driver --classpath=C:\mysql-connector-java-8.0.13 --logLevel=severe --changeLogFile=_master.xml --url="jdbc:mysql://localhost:3306/resource?useSSL=false&allowPublicKeyRetrieval=true" --username=root --password=badpassword123 update

Here is the .yaml file which has the full SQL.

---
databaseChangeLog:
- changeSet:
    author: mayj4
    id: 1
    changes:
        - sql:
            sql: 
                UPDATE Team SET Name = 'A' WHERE Name = 'X';
                UPDATE Team SET Name ='B' WHERE Name ='Y';
                UPDATE Team SET Name ='C' WHERE Name ='Z';

What I'm using

  • Liquibase 3.6.2
  • MySQL Connector Java 8.0.13
  • MySQL Workbench & Server 8.0.13

I've tried adding splitStatements: true to the .yaml but this makes no difference as suggested by the Liquibase documentation.

Update 1 It seems when even removing the semi-colons it still fails, so would be to do with running multiple queries.

2
mysqlliquibase
Either you obguscate your logs without anonimizing yaml or I just dont see related queries ;( – Antoniossss
Oh man I am so silly, thank you for pointing that out! Updated it so it is more clear – Troy Poulter

2 Answers

0
votes

Maybe something like this:

         - UPDATE Team SET Name = 'Maria' WHERE Name = 'Samurai';
         - UPDATE Team SET Name ='Ray' WHERE Name ='Knights';
         - UPDATE Team SET Name ='Stephen' WHERE Name ='Vikings';

or 

   - sql:
        sql: 
             sql1
   - sql: 
        sql:
             sql2

and so on.

dash - indicates that entry is part of sequence, adn since you want to execute sequence of SQLs this should help. (you can try some variansts, but - syntax is the way to go.) Personaly, your syntax look strange (as why should I nest sql inside sql) but I am not an expers since I havent use liquidbase with yml configuration.

0
votes

You can try using '>' to merge multiple sql statements into single executable statement.

- changeSet:
  author: Bob
  id: 1
  changes:
    - sql:
        comment: DML statements to rectify previously added names
        dbms: mysql
        sql: >
            UPDATE Team SET Name = 'A' WHERE Name = 'X';
            UPDATE Team SET Name ='B' WHERE Name ='Y';
            UPDATE Team SET Name ='C' WHERE Name ='Z';