Liquibase generated change log throws SQL Syntax Exception

Question

I've generated a change log for an existing database using Liquibase's generateChangeLog command. However when I try to run this an SQLSyntaxErrorException is thrown.

Caused by: java.sql.SQLSyntaxErrorException: 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 'databaseChangeLog:
- changeSet:
    id: 1595846089000-1
    author: A (gen' at line 1
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.21.jar:8.0.21]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.21.jar:8.0.21]
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.21.jar:8.0.21]
    at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764) ~[mysql-connector-java-8.0.21.jar:8.0.21]
    at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648) ~[mysql-connector-java-8.0.21.jar:8.0.21]
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95) ~[HikariCP-3.4.5.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-3.4.5.jar:na]
    at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:352) ~[liquibase-core-3.6.3.jar:na]
    ... 31 common frames omitted

Change log

databaseChangeLog:
- changeSet:
    id: 1595846089000-1
    author: A123456 (generated)
    changes:
    - createTable:
        columns:
        - column:
            constraints:
              primaryKey: true
            name: id
            type: VARCHAR(36)
        - column:
            name: message
            type: LONGTEXT
        - column:
            name: output
            type: LONGTEXT
        - column:
            name: result
            type: VARCHAR(16)
        tableName: task

I've tried generating the file in different formats (xml, yaml, sql) but all of them fail with the same error even though the syntax is valid.

I thought the generated log charset could be an issue so I've tried adding the following to the connection URL.

&useJvmCharsetConverters=true

I've also ensured the file is UTF-8 (without bom) after seeing this question.

Running Liquibase 3.6.3

Any help would be greatly appreciated!

Edit to add master change log:

databaseChangeLog:
- changeSet:
    id: 1
    author: A123456
    dbms: mysql
    labels: initial-migration
    preConditions:
    - onFail: MARK_RAN
    - onError: MARK_RAN
    - not:
        tableExists:
          schemaName: pd
          tableName: task
    changes:
    - sqlFile:
        path: initial-migration.yaml
        relativeToChangelogFile: true

Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set classpath:/db/changelog/db.changelog-master.yaml::1::A123456:
     Reason: liquibase.exception.DatabaseException: You have an error in your SQL syntax;

I'm not directly running the command, liquibase is imported in the pom but I assume it would be "liquibase migrate"? It's using a master change log (added to the question) — Andrew Myers, Aug 14 '20 at 12:17

score 0 · Answer 1 · answered Aug 16 '20 at 07:43

0

Try generating sql from change log using 'updateSQL' command and checking the generated scripts for syntax errors.

doc link for updateSQL command https://docs.liquibase.com/commands/community/updatesql.html

answered Aug 16 '20 at 07:43

vamsikurre

309
3
5

Thank you for the suggestion! I've just posted the solution above – Andrew Myers Aug 16 '20 at 20:35

score 0 · Answer 2 · answered Aug 16 '20 at 20:34

So eventually I figured out the problem. Where I had declared - sqlFile: in the master log (first change set) it's expecting pure SQL (not change sets).

Where Liquibase generates an SQL file it declares change set parameters (name etc) with a double hyphen -- and no space. This isn't a valid SQL comment but it's a valid Liquibase format. Thus the Liquibase generated SQL failed, and yaml/xml formats would always fail as they're not SQL.

The solution is to declare the file with Liquibase's include: tag instead, this reads the generated SQL correctly. Link

Liquibase generated change log throws SQL Syntax Exception

2 Answers2