Changing Delimiter w/ MySql

[sjernigan]

I have a schema migration and requires setting some default values. I'm using a function to help set those values. The function works if entered on the CLI. However, it does not when I run it in a goose migration. As an example, the migration looks like

-- +goose Up
-- +goose StatementBegin
DELIMITER | 
-- +goose StatementEnd

-- +goose StatementBegin
CREATE FUNCTION my_func( str CHAR(255) ) RETURNS CHAR(255) DETERMINISTIC
BEGIN 
  RETURN "Dummy Body"; 
END | 
-- +goose StatementEnd

-- +goose StatementBegin
DELIMITER ; 
-- +goose StatementEnd

select my_func("123") from dual;
-- +goose Down

When run, goose replies

2018/10/31 15:03:35 goose run: FAIL 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 'DELIMITER | 
-- +goose StatementEnd' at line 2, quitting migration

Seems like I tried various combinations of other delimiter characters, adding and removing the goose comments. Anything else I should try?

[VojtechVitek]

We're not using MySQL in https://github.com/pressly. Seeking help from the community on this one.

[VojtechVitek]

@sjernigan looks like the SQL parser refactor might have fixed this issue. Unit test returns correct number of SQL statements. Could you pls test out v2.7.0-rc1 release candidate and report back?

[sjernigan]

Please reopen. Still doesn't work.

2019/03/07 11:04:48 goose run: FAIL 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 'DELIMITER |
-- +goose StatementEnd' at line 3, quitting migration

Regardless, thanks for asking.

[DeanPDX]

Hey @sjernigan did you ever figure this out? I'm running in to the same issue and having a hard time believing we are the only two people who have attempted to create stored procs in our migrations.

[VojtechVitek]

Please submit a new issue and provide a reproducer.

[DeanPDX]

Hey @VojtechVitek I was able to figure this out and there is no issue, as it turns out. I'm relatively new to MySQL (by way of many years of MSSQL) and didn't realize at first that DELIMITER is actually a feature of the MySQL client - much like the GO keyword in MSSQL. The official documentation doesn't really mention it but this StackOverflow answer sums things up really well:

Note that the DELIMITER keyword is a function of the command line mysql client (and some other clients) only and not a regular MySQL language feature. It won't work if you tried to pass it through a programming language API to MySQL. Some other clients like PHPMyAdmin have other methods to specify a non-default delimiter.

The only use of DELIMITER is to "redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server". So, use +goose StatementBegin/End as a replacement for DELIMITER. @sjernigan, to fix your problem, refactor your migration to the following:

-- +goose Up
-- +goose StatementBegin
DROP FUNCTION IF EXISTS my_func; -- Just to be safe
-- +goose StatementEnd

-- +goose StatementBegin
CREATE FUNCTION my_func( str CHAR(255) ) RETURNS CHAR(255) DETERMINISTIC
BEGIN 
  RETURN "Dummy Body"; 
END
-- +goose StatementEnd

-- +goose Down
DROP FUNCTION IF EXISTS my_func;

I tested this exact migration in my MySQL project and it worked as intended. Hope this helps.

[VojtechVitek]

Awesome. Thanks for the feedback!