horizon mysql,如何使用Liquibase和mysql创建触发器来修复SQL语法错误

I am setting up first liquibase maven project for a MySQL DB. Been fine up to creating the triggers.

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.

Here are my pom dependancies

org.liquibase

liquibase-parent

3.5.3

pom

org.liquibase

liquibase-core

3.5.3

mysql

mysql-connector-java

5.1.46

org.liquibase

liquibase-maven-plugin

3.6.3

This is my liquibase include file

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">

drop trigger if exists ai_event_approval;

This is my sql file

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

I expected that by setting the splitStatements, stripComments, and endDelimiter that I would get liquibase to send the whole SQL as one thru JDBC.

But I am getting this error

[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)]

解决方案

A nights sleep along with @Jens suggestion I finally see the issue.

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

The key was to make sure to set splitStatements:false, setting the endDelimiter was not needed.

Here are two examples that work:

with this sql file

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;

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;