我正在为MySQL数据库设置第一个Liquibase Maven项目。 在创建触发器方面一切正常。
我认为这是由于Liquibase和JDBC无法正确处理多条SQL语句所致,但我无法确定自己漏了什么。
下面是我的POM依赖:
<dependency>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-parent</artifactId>
<version>3.5.3</version>
<type>pom</type>
</dependency>
<dependency>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-core</artifactId>
<version>3.5.3</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.46</version>
</dependency>
<dependency>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-maven-plugin</artifactId>
<version>3.6.3</version>
</dependency>
这是我的Liquibase包含文件
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.0.xsd">
<changeSet id="event_horizon_1_0_0" author="lmtyler" dbms="mysql">
<sql>
drop trigger if exists ai_event_approval;
</sql>
</changeSet>
<changeSet id="event_horizon_1_0_1" author="lmtyler" dbms="mysql">
<sqlFile splitStatements="false" stripComments="false" endDelimiter="DONE" path="01__ai_event_approval.sql" relativeToChangelogFile="true" />
</changeSet>
</databaseChangeLog>
这是我的SQL文件。
CREATE DEFINER ='evclient'@'%' TRIGGER ai_event_approval
AFTER INSERT
ON event_approval
FOR EACH row
begin
insert into event_approval_log (rowAction,
actionTs,
event,
requestorEmail,
requestReason,
statusType,
approverUserId,
approverReason,
lastChangTs)
values ('I',
current_timestamp(6),
new.event,
new.requestorEmail,
new.requestReason,
new.statusType,
new.approverUserId,
new.approverReason,
new.lastChangTs);
end;
# DONE
我期望通过设置splitStatements、stripComments和endDelimiter,可以让Liquibase通过JDBC将整个SQL作为一个整体发送。但是我却收到了以下错误提示:
[ERROR] Failed to execute goal org.liquibase:liquibase-maven-plugin:3.6.3:update (default) on project event-horizon-mysql: Error setting up or running Liquibase: Migration failed for change set /Users/lmtyler/OneDrive - Walmart Inc/workspace/code/event-horizon/event-horizon-mysql/src/main/java/resources/liquibase/schema/triggers/02__au_event_approval.sql::event_horizon_1_0_1::lmtyler:
[ERROR] Reason: liquibase.exception.DatabaseException: 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 '' at line 23 [Failed SQL: CREATE TRIGGER au_event_approval
[ERROR] AFTER UPDATE
[ERROR] ON event_approval
[ERROR] FOR EACH row
[ERROR] begin
[ERROR] insert into event_approval_log (rowAction,
[ERROR] actionTs,
[ERROR] event,
[ERROR] requestorEmail,
[ERROR] requestReason,
[ERROR] statusType,
[ERROR] approverUserId,
[ERROR] approverReason,
[ERROR] lastChangTs)
[ERROR] values ('U',
[ERROR] current_timestamp(6),
[ERROR] new.event,
[ERROR] new.requestorEmail,
[ERROR] new.requestReason,
[ERROR] new.statusType,
[ERROR] new.approverUserId,
[ERROR] new.approverReason,
[ERROR] new.lastChangTs)]
updateSQL
而不是update
来运行liquibase时会有所帮助。这样liquibase就不会直接在数据库上运行更改,而是将它们打印到std。这样您就可以检查您的SQL文件如何被解析了。 - JensupdateSQL
并检查了输出文件。它们生成了正确的 SQL 结果... new.lastChangTs); end;
但是当我改回update
时,它仍然会截断 SQL 中的; end;
并出现与上述相同的错误。 - Mike