MySQLSyntaxErrorException with Liquibase sqlFile

Question

When I run a Liquibase changeSet of type 'sqlFile' for inserting data, I get a MySQLSyntaxErrorException. My query is correct since it can be run without any error within MySQL workbench.

Here is my Liquibase changeSet:

<changeSet id="1" author="me" >
         <sqlFile dbms="mysql"
            path="insert_my_data.sql"
            relativeToChangelogFile="true"
            splitStatements="true"
            stripComments="true"
            endDelimiter="EOF" />
    </changeSet>

Here is the sql query:

INSERT INTO table1 (table1col1, table1col2)
VALUES ('table1col1value', 'table1col2value')
;

-- link to the latest value of table1
INSERT INTO table2 (table2col1, table2col2)
VALUES ('table2col1value',
(SELECT table1.id FROM table1 ORDER BY table1.id DESC LIMIT 1))
;

Here is the error:

    at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:301)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:107)
    at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1251)
    at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1234)
    at liquibase.changelog.ChangeSet.execute(ChangeSet.java:554)
    ... 53 common frames omitted
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 INTO table2 (table2col1, table2col2)
VALUES (' at line 5
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2483)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2441)
    at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745)
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95)
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
    at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:299)
    ... 58 common frames omitted

Do you know if Liquibase is able to handle such a nested query?

I don't think that there is anything wrong with any of the queries in isolation. The MySQL exception quotes the 2nd insert query from the beginning indicating that the syntax error happens at the insert keyword. Therefore I believe that liquibase may not be able to execute multiple sql queries in one go. Based on the error message it seems that it uses jdbc for connecting to MySQL. The following SO question has answers how to configure jdbc to execute multiple sql statements in one go: https://stackoverflow.com/questions/10797794/multiple-queries-executed-in-java-in-single-statement — Shadow, Jun 11 '19 at 11:53
I added allowMultiQueries=true to my JDBC options, but I still get the same error. I think I will simply use a Liquibase syntax like several '' — RotS, Jun 11 '19 at 12:17

ScaisEdge · Answer 1 · 2019-06-11T12:04:25.400

0

You could try a insert select this way

INSERT INTO table2 (col1, col2)
select  'table2col1value' , id 
FROM table1 
ORDER BY table1.id DESC 
LIMIT 1

edited Jun 11 '19 at 12:04

answered Jun 11 '19 at 11:44

ScaisEdge

131,976
10
91
107

Unfortunately, I get the same error using the alternative syntax you proposed: MySQLSyntaxErrorException: 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 INTO table2 (table2col1, table2col2) SELECT table1.id' at line 5 – RotS Jun 11 '19 at 11:56
are you sure the column col1 sis a string and the column col2 is valid for id? ..you are trying to insert a string ('table2col1value') in col1 – ScaisEdge Jun 11 '19 at 11:58
Yes, it's a linking table, so fields are actually ids. I tested the exact same query in MySQL workbench and it worked. – RotS Jun 11 '19 at 12:02
my code is not like the code you have in comment error msg .. check better – ScaisEdge Jun 11 '19 at 12:06

score 0 · Answer 2 · answered Jun 11 '19 at 13:12

I finally used <changeSet><sql> instead of <changeSet><sqlFile> with the exact same queries, and it worked:

    <changeSet id="1" author="me">
        <sql> 
INSERT INTO table1(table1col1, table1col2)
VALUES (table1col1value, table1col2value)
;
        </sql>
        <sql>
INSERT INTO table2 (table2col1, table2col2)
VALUES (table2col1value,
(SELECT table1.id FROM table1 ORDER BY table1.id DESC LIMIT 1))
;
        </sql>
    </changeSet>

MySQLSyntaxErrorException with Liquibase sqlFile

2 Answers2