The endDelimiter SQL attribute

The endDelimiter attribute can be set in a sql or sqlFile Change Type to override the default value of ;. The endDelimiter can be set to '' or to a character other than ; to indicate the end of the SQL statement. The attribute is generally used to prevent incomplete SQL statements from being processed when the SQL files contain objects referencing a ; which Liquibase treats as the default end delimiter.

There is a corresponding rollbackEndDelimiter that applies specifically to rollback for sql and sqlFile Change Types.

The endDelimiter can also be set in the liquibase.properties file by setting delimiter=<string>.

Uses

Below are some examples where endDelimiters would be specified:

Use endDelimiter in SQL for PROCEDURE and FUNCTION

An endDelimiter may be used when the changeset contains SQL to create a stored procedure or function that contains the default ‘;’ end delimiter. To avoid incomplete statements being sent to the database, the changeset must be marked to have a different endDelimiter.

Use endDelimiter for some DBMS systems to run multiple statements

The endDelimiter must be specified for some dbms systems to run multiple statements.

Setting the endDelimiter attribute

Escaping an endDelimiter

When setting an endDelimiter please note that certain DBMS and operating systems require delimiter values to be escaped. For example, a $$ endDelimiter with mysql would need to be escaped as: endDelimiter="\$\$". The Liquibase migration error you would see to indicate the delimiter needs to be escaped would be:

Unexpected error running Liquibase: Migration failed for change set <changelog>::<id>::<author>:
 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 '$$$