如何使用Liquibase和mysql创建触发器来修复SQL语法错误 [英] How to fix SQL syntax error using liquibase and mysql to create trigger
问题描述
我正在为MySQL数据库设置第一个liquibase maven项目.完全可以创建触发器.
I am setting up first liquibase maven project for a MySQL DB. Been fine up to creating the triggers.
我认为这是liquibase和JDBC无法正确处理多语句SQL的问题,但我无法弄清缺少的内容.
I believe it is an issue with liquibase and JDBC not correctly handling a multiple statement SQL, but I cannot figure out what I am missing.
这是我的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包含文件
This is my liquibase include file
<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来将整个SQL作为一个JDBC发送.
I expected that by setting the splitStatements, stripComments, and endDelimiter that I would get liquibase to send the whole SQL as one thru JDBC.
但是我收到此错误
[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)]
推荐答案
一个晚上的睡眠以及@Jens的建议,我终于明白了.
A nights sleep along with @Jens suggestion I finally see the issue.
首先,如果您看到我的帖子,则错误不是我所认为的SQL.我以为是引发错误的 AFTER INSERT
,但是是 AFTER UPDATE
First if you see my post the error was not for the SQL I thought.
I thought it was the AFTER INSERT
that was throwing the error, but it was the AFTER UPDATE
关键是要确保设置 splitStatements:false
,不需要设置 endDelimiter
.
The key was to make sure to set splitStatements:false
, setting the endDelimiter
was not needed.
以下是两个有效的示例:
Here are two examples that work:
<changeSet id="event_horizon_1_0_1" author="lmtyler" dbms="mysql">
<sqlFile splitStatements="false" stripComments="false" path="01__ai_event_approval.sql" relativeToChangelogFile="true"/>
</changeSet>
使用此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;
这里使用的是SQL格式
And here is using the SQL format
--changeset lmtyler:event_horizon_1_0_1 dbms:mysql splitStatements:false
CREATE TRIGGER au_event_approval
AFTER UPDATE
ON event_approval
FOR EACH row
begin
insert into event_approval_log (rowAction,
actionTs,
event,
requestorEmail,
requestReason,
statusType,
approverUserId,
approverReason,
lastChangTs)
values ('U',
current_timestamp(6),
new.event,
new.requestorEmail,
new.requestReason,
new.statusType,
new.approverUserId,
new.approverReason,
new.lastChangTs);
end;
这篇关于如何使用Liquibase和mysql创建触发器来修复SQL语法错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!