Liquibase生成的变更日志引发了SQL语法异常。

huangapple 未分类评论112阅读模式
英文:

Liquibase generated change log throws SQL Syntax Exception

问题

我使用Liquibase的generateChangeLog命令为现有数据库生成了一个变更日志,但是当我尝试运行它时,会抛出一个SQLSyntaxErrorException错误。

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

变更日志:

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

我尝试以不同的格式(xml、yaml、sql)生成文件,但是所有的格式都出现了相同的错误,即使语法是有效的。

我认为生成的日志字符集可能是一个问题,所以我尝试将以下内容添加到连接URL中。

&useJvmCharsetConverters=true

我还确保文件是UTF-8编码(无BOM),在看到此问题后进行了尝试。

运行的是Liquibase 3.6.3版本。

非常感谢您提供帮助!

附加的主变更日志:

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'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;

答案1

得分: 0

Sure, here's the translated content:

尝试使用 'updateSQL' 命令从更改日志生成 SQL,并检查生成的脚本是否存在语法错误。

updateSQL 命令的文档链接:
https://docs.liquibase.com/commands/community/updatesql.html

英文:

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

答案2

得分: 0

所以最终我找到了问题所在。在主日志(第一个变更集)中我声明了- sqlFile:,它期望的是纯SQL(而不是变更集)。

当Liquibase生成一个SQL文件时,它会使用双破折号--而且没有空格来声明变更集参数(名称等)。这不是一个有效的SQL注释,但它是一个有效的Liquibase格式。因此,Liquibase生成的SQL失败了,而且yaml/xml格式总是会失败,因为它们不是SQL。

解决方案是改为使用Liquibase的include:标签来声明文件,这样可以正确读取生成的SQL。链接

英文:

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

huangapple
  • 本文由 发表于 2020年8月14日 19:13:55
  • 转载请务必保留本文链接:https://java.coder-hub.com/63411691.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定