Formatted sql with mySQL generates "mysql syntax error"

General Discussion
1

Hi

I’m trying to get Liquibase 3.0.2 running with formatted sql


in my sql file I have

  2. -liquibase formatted sql

  4. --changeset jvddrift:1 dbms:MySQL
  5. CREATE TABLE 'department' ('id' int(11) NOT NULL,'name' varchar(50) NOT NULL,'active' bit(1) DEFAULT '1',PRIMARY KEY ('id'));
This file is included in a changeset.xml file as follows
  2. <?xml version="1.0" encoding="UTF-8"?>
  3.  
  4. <databaseChangeLog
  5.   xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  6.   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  7.   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
  8.          http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.0.xsd">
  9.  
  10.    
  11.   

howevere when I run liquibase, I get the following error

  1.  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 '--liquibase formatted sql
Running the create query in MySQL (5.5.28) works without any problem


I’m using Liquibase for the first time, so I have no idea on what is going wrong here. 

Can anyone help?




2

No replies yet, so I will add some information. 

I have now tried to run the example given at  … 9 more


  • I really hope someone can point me in the right direction!


    Regards

    Joost
    • 3

    Thanks for the output, that helps. You said you’re using liquibase 3.0.5 but there is no 3.0.5. Do you mean 2.0.5? Or one of the 3.0 versions?


    Nathan

    4

    Sorry about the confusion. I’ve edited my first post.
    I tried both 3.0.2 and 2.0.5.
    The log is from the 3.0.2 version.

    Regards
    Joost

    5

    Same problem

    6

    Formatted sql changelogs need to start with --liquibase formatted sql as the very first line. Your example had a line before and just one “-” not two. If it doesn’t see that, it assumes it is standard SQL and tries to execute everything including the comments.


    Nathan

    7

    Hi Nathan,


    thanks for the reply. The same occurs when using the example given on the liquibase site. I’m using 3.0.2. 


    changelog.xml

    2. <?xml version="1.0" encoding="UTF-8"?>
    3.  
    4. <databaseChangeLog
    5.   xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    6.   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    7.   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
    8.          http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.0.xsd">
    9.  
    10.    
    11.   
    liquibase.sql
    2. --liquibase formatted sql

    4. --changeset jvddrift:1 
    5. create table test1 (
    6.     id int primary key,
    7.     name varchar(255)
    8. );
    logfile
    2. SEVERE 28-8-13 8:26:liquibase: Change Set c:/temp/liquibase/liquibase.sql::raw::includeAll failed.  Error: Error executing SQL --liquibase formatted sql

    4. --changeset jvddrift:1 
    5. create table test1 (
    6.     id int primary key,
    7.     name varchar(255)
    8. );: 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 '--liquibase formatted sql

    10. --changeset jvddrift:1 
    11. create table test1 (
    12.     id i' at line 1
    13. liquibase.exception.DatabaseException: Error executing SQL --liquibase formatted sql

    15. --changeset jvddrift:1 
    16. create table test1 (
    17.     id int primary key,
    18.     name varchar(255)
    19. );: 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 '--liquibase formatted sql

    21. --changeset jvddrift:1 
    22. create table test1 (
    23.     id i' at line 1
    24. <span class="Apple-tab-span" --username=root --password=xxxxxx update


    Regards

    Joost

    8

    Hi Nathan,

    Sorry, apparently I was nog logged in, above example is mine. 


    Some how a blank line is added when I paste the code. liqubase.sql DOES look like this


    1. );

    The same goes for changelog.xml, no first blank line


    Regards

    Joost

    9

    Hi Nathan,

    still no luck with 3.0.3

    1. mysql-connector-java-5.0.8

      2. Regards

      Joost


    10

    Those changelogs are working fine for me testing against 3.0.3. Try that when I get it released later today.

    Nathan

    11

    Hi


    OK, I will test it. Is my commandline call correct?


    Regards

    Joost


    12

    Yeah, the command line looks right.


    Nathan

    13

    Hi Nathan


    See attachment,


    Regards

    Joost 

    14

    It still seems to be working fine for me. Can you zip up your actual changelog files and attach them or email them to me at nathan.voxland@liquibase.org?

    Nathan

    15

    Thanks, I can reproduce it now.  Debugging the code, it looks like a problem with UTF-8 with BOM formatted .sql files. I logged https://liquibase.jira.com/browse/CORE-1434 and applied a fix to the 3.x branch for 3.0.5. 


    For now, if you convert your sql file to ascii or BOM-less UTF8 it will work just fine.


    Nathan

    16

    I can confirm that converting the files to BOM-less UTF8 fixes the issue for versions 2.0.5, 3.0.2 and 3.0.3


    Thanks for the help!


    Regards

    Joost


    17

    Liquibase Community 4.1.1 by Datical

    I’ve also come across this problem. With my senior collgeue’s favor, he put
    – changeset db:[…]
    in front of every sql script. That makes the problem disappear