delimiter does not work in migration

[pigeonhands]

Bug Description

DELIMITER does not work in migration scripts, so stored procedures/functions cant be declared.

Minimal Reproduction

Inserting the following sql into any migration sql script

DELIMITER $$
SELECT 10$$
DELIMITER ;

causes the migration to fail with:

error: while executing migrations: error returned from database: 1064 (42000): 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 $$
SELECT 10$$
DELIMITER' at line 1

Executing the same script in mysqlsh works fine.

Info

• SQLx version: 0.6.2
• SQLx features enabled: ["runtime-tokio-rustls", "mysql", "offline", "uuid"]
• Database server and version: MySQL 8.0
• Operating system: windows
• rustc --version: rustc 1.62.0 (a8314ef7d 2022-06-27)

[abonander]

delimiter is a command of the MYSQL client, not the server: https://dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html

It should not be necessary with SQLx as the migration machinery does not split on semicolons but passes the whole file as a single command.

[kulak]

MySQL stored procedures and functions get tricky due to widespread examples how to create them involve use of DELIMITER. That includes even official documentation examples.

The workaround for migrations is to use a dedicated migration file per stored procedure or function.

I just tested this workaround solution with this library and https://github.com/golang-migrate/migrate/releases and both worked.

I think the issue can be closed, but this recommendation shall be stated somewhere in documentation.

[abonander]

The documentation you linked explains it though:

The example uses the mysql client delimiter command to change the statement delimiter from ; to // while the procedure is being defined. This enables the ; delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql itself. See Section 27.1, “Defining Stored Programs”.

As does the linked manual page (https://dev.mysql.com/doc/refman/8.4/en/stored-programs-defining.html):

If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.

I'd accept a PR to add hints in different places or even a full example, but this definitely seems like an "RTFM" kind of situation.