# Formatted sql with mySQL generates "mysql syntax error"

**URL:** <https://forum.liquibase.org/t/formatted-sql-with-mysql-generates-mysql-syntax-error/2316>\
**Category:** General Discussion\
**Created:** [July 12, 2013, 10:27pm UTC](https://forum.liquibase.org/t/formatted-sql-with-mysql-generates-mysql-syntax-error/2316 "2013-07-12T22:27:00Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![un1373642478919r83id](https://avatars.discourse-cdn.com/v4/letter/u/51bf81/32.png) [@un1373642478919r83id](https://forum.liquibase.org/u/un1373642478919r83id)\
**Post date:** [July 12, 2013, 10:27pm UTC](https://forum.liquibase.org/t/formatted-sql-with-mysql-generates-mysql-syntax-error/2316/1 "2013-07-12T22:27:00Z")

</div>

Hi

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

in my sql file I have

1. 
2. -liquibase formatted sql
3.   
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
1. 
2. \<?xml version="1.0" encoding="UTF-8"?\>
3. &nbsp;
4. \<databaseChangeLog
5. &nbsp; xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
6. &nbsp; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
7. &nbsp; xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
8. &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.0.xsd](http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.0.xsd)"\>
9. &nbsp;
10. &nbsp; &nbsp; 
11. &nbsp;&nbsp;
12. 

  

howevere when I run liquibase, I get the following error

1. &nbsp;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.&nbsp;

Can anyone help?

---

<div class="post-metadata">

**Author:** ![un1373642478919r83id](https://avatars.discourse-cdn.com/v4/letter/u/51bf81/32.png) [@un1373642478919r83id](https://forum.liquibase.org/u/un1373642478919r83id)\
**Post date:** [July 12, 2013, 10:27pm UTC](https://forum.liquibase.org/t/formatted-sql-with-mysql-generates-mysql-syntax-error/2316/2 "2013-07-12T22:27:00Z")

</div>

No replies yet, so I will add some information.&nbsp;

I have now tried to run the example given at&nbsp;[… 9 more](http://www.liquibase.org/documentation/sql_format.html)

- [  

 

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

 

 

Regards

 

Joost

 

 

](http://www.liquibase.org/documentation/sql_format.html)

---

<div class="post-metadata">

**Author:** ![nvoxland](https://avatars.discourse-cdn.com/v4/letter/n/87869e/32.png) [@nvoxland](https://forum.liquibase.org/u/nvoxland)\
**Post date:** [July 12, 2013, 10:27pm UTC](https://forum.liquibase.org/t/formatted-sql-with-mysql-generates-mysql-syntax-error/2316/3 "2013-07-12T22:27:00Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![un1373642478919r83id](https://avatars.discourse-cdn.com/v4/letter/u/51bf81/32.png) [@un1373642478919r83id](https://forum.liquibase.org/u/un1373642478919r83id)\
**Post date:** [July 12, 2013, 10:27pm UTC](https://forum.liquibase.org/t/formatted-sql-with-mysql-generates-mysql-syntax-error/2316/4 "2013-07-12T22:27:00Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forum.liquibase.org/u/Guest)\
**Post date:** [July 12, 2013, 10:27pm UTC](https://forum.liquibase.org/t/formatted-sql-with-mysql-generates-mysql-syntax-error/2316/5 "2013-07-12T22:27:00Z")

</div>

Same problem

---

<div class="post-metadata">

**Author:** ![nvoxland](https://avatars.discourse-cdn.com/v4/letter/n/87869e/32.png) [@nvoxland](https://forum.liquibase.org/u/nvoxland)\
**Post date:** [July 12, 2013, 10:27pm UTC](https://forum.liquibase.org/t/formatted-sql-with-mysql-generates-mysql-syntax-error/2316/6 "2013-07-12T22:27:00Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forum.liquibase.org/u/Guest)\
**Post date:** [July 12, 2013, 10:27pm UTC](https://forum.liquibase.org/t/formatted-sql-with-mysql-generates-mysql-syntax-error/2316/7 "2013-07-12T22:27:00Z")

</div>

Hi Nathan,

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

changelog.xml

1. 
2. \<?xml version="1.0" encoding="UTF-8"?\>
3. &nbsp;
4. \<databaseChangeLog
5. &nbsp; xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
6. &nbsp; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
7. &nbsp; xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
8. &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.0.xsd](http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.0.xsd)"\>
9. &nbsp;
10. &nbsp; &nbsp; 
11. &nbsp;&nbsp;
12. 

liquibase.sql
1. 
2. --liquibase formatted sql
3.   
4. --changeset jvddrift:1&nbsp;
5. create table test1 (
6. &nbsp; &nbsp; id int primary key,
7. &nbsp; &nbsp; name varchar(255)
8. );

logfile
1. 
2. SEVERE 28-8-13 8:26:liquibase: Change Set c:/temp/liquibase/liquibase.sql::raw::includeAll failed. &nbsp;Error: Error executing SQL --liquibase formatted sql
3.   
4. --changeset jvddrift:1&nbsp;
5. create table test1 (
6. &nbsp; &nbsp; id int primary key,
7. &nbsp; &nbsp; 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
9.   
10. --changeset jvddrift:1&nbsp;
11. create table test1 (
12. &nbsp; &nbsp; id i' at line 1
13. liquibase.exception.DatabaseException: Error executing SQL --liquibase formatted sql
14.   
15. --changeset jvddrift:1&nbsp;
16. create table test1 (
17. &nbsp; &nbsp; id int primary key,
18. &nbsp; &nbsp; 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
20.   
21. --changeset jvddrift:1&nbsp;
22. create table test1 (
23. &nbsp; &nbsp; id i' at line 1
24. \<span class="Apple-tab-span" --username=root --password=xxxxxx update

Regards

Joost

---

<div class="post-metadata">

**Author:** ![un1373642478919r83id](https://avatars.discourse-cdn.com/v4/letter/u/51bf81/32.png) [@un1373642478919r83id](https://forum.liquibase.org/u/un1373642478919r83id)\
**Post date:** [July 12, 2013, 10:27pm UTC](https://forum.liquibase.org/t/formatted-sql-with-mysql-generates-mysql-syntax-error/2316/8 "2013-07-12T22:27:00Z")

</div>

Hi Nathan,

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

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

---

<div class="post-metadata">

**Author:** ![un1373642478919r83id](https://avatars.discourse-cdn.com/v4/letter/u/51bf81/32.png) [@un1373642478919r83id](https://forum.liquibase.org/u/un1373642478919r83id)\
**Post date:** [July 12, 2013, 10:27pm UTC](https://forum.liquibase.org/t/formatted-sql-with-mysql-generates-mysql-syntax-error/2316/9 "2013-07-12T22:27:00Z")

</div>

Hi Nathan,  
  
still no luck with 3.0.3

1. mysql-connector-java-5.0.8
  

Regards  
  
Joost

---

<div class="post-metadata">

**Author:** ![nvoxland](https://avatars.discourse-cdn.com/v4/letter/n/87869e/32.png) [@nvoxland](https://forum.liquibase.org/u/nvoxland)\
**Post date:** [July 12, 2013, 10:27pm UTC](https://forum.liquibase.org/t/formatted-sql-with-mysql-generates-mysql-syntax-error/2316/10 "2013-07-12T22:27:00Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![un1373642478919r83id](https://avatars.discourse-cdn.com/v4/letter/u/51bf81/32.png) [@un1373642478919r83id](https://forum.liquibase.org/u/un1373642478919r83id)\
**Post date:** [July 12, 2013, 10:27pm UTC](https://forum.liquibase.org/t/formatted-sql-with-mysql-generates-mysql-syntax-error/2316/11 "2013-07-12T22:27:00Z")

</div>

Hi

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

Regards

Joost

---

<div class="post-metadata">

**Author:** ![nvoxland](https://avatars.discourse-cdn.com/v4/letter/n/87869e/32.png) [@nvoxland](https://forum.liquibase.org/u/nvoxland)\
**Post date:** [July 12, 2013, 10:27pm UTC](https://forum.liquibase.org/t/formatted-sql-with-mysql-generates-mysql-syntax-error/2316/12 "2013-07-12T22:27:00Z")

</div>

Yeah, the command line looks right.

Nathan

---

<div class="post-metadata">

**Author:** ![un1373642478919r83id](https://avatars.discourse-cdn.com/v4/letter/u/51bf81/32.png) [@un1373642478919r83id](https://forum.liquibase.org/u/un1373642478919r83id)\
**Post date:** [July 12, 2013, 10:27pm UTC](https://forum.liquibase.org/t/formatted-sql-with-mysql-generates-mysql-syntax-error/2316/13 "2013-07-12T22:27:00Z")

</div>

Hi Nathan

See attachment,

Regards

Joost&nbsp;

---

<div class="post-metadata">

**Author:** ![nvoxland](https://avatars.discourse-cdn.com/v4/letter/n/87869e/32.png) [@nvoxland](https://forum.liquibase.org/u/nvoxland)\
**Post date:** [July 12, 2013, 10:27pm UTC](https://forum.liquibase.org/t/formatted-sql-with-mysql-generates-mysql-syntax-error/2316/14 "2013-07-12T22:27:00Z")

</div>

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](mailto:nathan.voxland@liquibase.org)?  
  
Nathan

---

<div class="post-metadata">

**Author:** ![nvoxland](https://avatars.discourse-cdn.com/v4/letter/n/87869e/32.png) [@nvoxland](https://forum.liquibase.org/u/nvoxland)\
**Post date:** [July 12, 2013, 10:27pm UTC](https://forum.liquibase.org/t/formatted-sql-with-mysql-generates-mysql-syntax-error/2316/15 "2013-07-12T22:27:00Z")

</div>

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

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

Nathan

---

<div class="post-metadata">

**Author:** ![un1373642478919r83id](https://avatars.discourse-cdn.com/v4/letter/u/51bf81/32.png) [@un1373642478919r83id](https://forum.liquibase.org/u/un1373642478919r83id)\
**Post date:** [July 12, 2013, 10:27pm UTC](https://forum.liquibase.org/t/formatted-sql-with-mysql-generates-mysql-syntax-error/2316/16 "2013-07-12T22:27:00Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Lysander](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.liquibase.org/lysander/32/353_2.png) [@Lysander](https://forum.liquibase.org/u/Lysander)\
**Post date:** [March 26, 2021, 8:58am UTC](https://forum.liquibase.org/t/formatted-sql-with-mysql-generates-mysql-syntax-error/2316/17 "2021-03-26T08:58:00Z")

</div>

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