endDelimiter issues Mysql

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.

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

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 /

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

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:

Thanks for getting back Mark11!

Am discussing this with more knowledgeable folks internally :slight_smile:

Hi Mark11,

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