endDelimiter issues Mysql

General Discussion
1

Hello,

I’m wondering if there are any known/outstanding issues using ‘endDelimiter’ with MySQL?

I am including this endDelimiter with an sqlFile changeset, and was expecting the liquibase updateSQL to emit a ‘DELIMITER’ line, but no such luck, and the generated SQL fails.

I’m using liquibase via maven, and have tried a number of versions - 3.5.x, 3.7.x, up to 3.8.9. Also tinkered with splitStatements on/off and different endDelimiter characters.

Anyways, as mentioned, I am curious if this is a known issue (haven’t had any luck spotting current bugs in the liquibase github / jira / these forums). It seems hard to believe I’m the first to encounter if it spans 3.5.x to 3.8.9.

2

Hi Mark11,

Could you include the changeset that is giving you a problem? 

I am looking at the documentation: https://www.liquibase.org/documentation/changes/sql_file.html and it appears that we only have 2 choices for ending, and really if you don’t use it, looks like it defaults to semicolon.

Thanks,

Ronak

3
Hi RonakRham,
Thanks for your reply.

Changeset:
--  Changeset com/eyereturn/operations/DBOP-394-dev_segment_budget_tracking_last_update_spoofer/db.changelog.yaml::DBOP-394-DEVONLY-update-segment-budget-tracking-last-update::mstoute
CREATE DEFINER=`root`@`%`
  EVENT IF NOT EXISTS `dev_segment_budget_tracking_sum_updater`
    ON SCHEDULE
        EVERY 2 MINUTE STARTS '2019-05-27 13:48:28'
    ON COMPLETION NOT PRESERVE
    ENABLE
    COMMENT 'DEV ONLY!'
    DO BEGIN
    update operations.segment_budget_tracking_sum set last_updated = now() where 1=1;
END /

4
Hi Mark11,

Looking internally but if you see this before I get the answer, could you tell me what in the resulting SQL is wrong?
Is it this line:
and the generated SQL fails.
I made an assumption that there is an error.  Is there an error?  Or is it just that the generated SQL is wrong?

Thanks for your patience,

Ronak
5
Hi Ronak,
Sorry about the delay.

The "every 2 minutes starts" part is fine.

The issue (I think) is just that it's missing a "DELIMITER /" statement ahead of the "CREATE" statement. I can fix the SQL by adding "DELIMITER /" manually. So this code below succeeds, I've only added the "DELIMITER /" line.

DELIMITER /
CREATE DEFINER=`root`@`%` EVENT `dev_segment_budget_tracking_sum_updater`
    ON SCHEDULE
        EVERY 2 MINUTE STARTS '2019-05-27 13:48:28'
    ON COMPLETION NOT PRESERVE
    ENABLE
    COMMENT 'DEV ONLY!'
    DO BEGIN
    update operations.segment_budget_tracking_sum set last_updated = now() where 1=1;
END/
The Specific error raised by the DB is: "/* SQL Error (1064): 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 9 */". This is where it encounters the default delimiter ";"  ("...where 1=1;").

Also as an aside, I noticed if I set "$$" as the endDelimiter, LB also includes a ";" after my custom delimiter. So the last line emitted in that case looks like "END$$;". This isn't the direct issue at hand but it is a different behavior than by using only "/" as the endDelimiter.
6

Hi Steve Z, Thanks for your reply.

  1. To be clear, I have tried running it without modification, both “manually” by copying the complete output of an updateSQL (including the inserts to DATABASECHANGELOG etc.) into a SQL client, and also by running it via update command. In both cases, I get the syntax error at the “;” character. So, I have tried what you suggest, but it does not succeed without modification.
  1. Interesting - thank you! It was just an oddity I noticed when playing around, but the explanation makes sense :slight_smile:
7

Thanks for getting back Mark11!

Am discussing this with more knowledgeable folks internally :slight_smile:

8

Hi Mark11,

  1. The reason you don’t need the “DELIMITER /” statement is because it is already defined as a changeSet attribute ">