Open pigeonhands opened 1 year ago
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.
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.
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.
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
causes the migration to fail with:
Executing the same script in
mysqlsh
works fine.Info
0.6.2
["runtime-tokio-rustls", "mysql", "offline", "uuid"]
MySQL 8.0
windows
rustc --version
:rustc 1.62.0 (a8314ef7d 2022-06-27)