MySQL driver doesn't support DELIMITER statement

[abhisheks-cuelogic]

Hello,

I am trying to migrate stored procedures with dbmate but seems like it doesn't support it. I am getting below error while executing dbmate up command:

sql file content for stored procedure: DELIMITER // DROP PROCEDURE IF EXISTS foobar // create procedure foobar (in var1 int) begin select var1 + 2 as result; END // DELIMITER ;

ERROR: Applying: 20180907100150_create_store_procedure.sql Error: 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 // DROP PROCEDURE IF EXISTS foobar // create procedure foobar (in var1' at line 1

[amacneil]

This doesn't seem like an issue with stored procedures, the error message coming directly from mysql appears to be related to the delimiter syntax.

What happens if you execute this script directly using the mysql command?

[john-landgrave]

I'm running into this as well. Works perfectly in MySQL workbench, but fails in dbmate up.

Ultimately we were trying to change the DELIMITER to run a CREATE FUNCTION statement, but on MySQL 5.7.25 we were able to just use a ; on the END statement as well as throughout the function body and everything appears to have just worked.

[amacneil]

I think it's probably an issue coming from the go MySQL driver. It might be ignoring the delimiter syntax and trying to send the whole statement through to MySQL.

[fourcube]

I've just googled a bit and this seems to be a feature of the MySQL command line client and various other tools. It's not part of the SQL spec that a MySQL server understands, so this might be out of scope for dbmate as well.

[junaideqbal]

I'm also facing the same issue. How soon this get resolved?

@amacneil I'm trying to run this file/migration. any solution for this?

DROP TRIGGER IF EXISTS `green_production`.`Customers_BEFORE_INSERT`;

DELIMITER $$
USE `green_production` $$
CREATE DEFINER = CURRENT_USER TRIGGER `green_production`.`Customers_BEFORE_INSERT` BEFORE INSERT ON `Customers` FOR EACH ROW
BEGIN
	SET new.name = CONCAT(new.firstName, ' ', new.lastName);
END$$
DELIMITER ;
DROP TRIGGER IF EXISTS `green_production`.`Customers_BEFORE_UPDATE`;

DELIMITER $$
USE `green_production`$$
CREATE DEFINER = CURRENT_USER TRIGGER `green_production`.`Customers_BEFORE_UPDATE` BEFORE UPDATE ON `Customers` FOR EACH ROW
BEGIN
	SET new.name = CONCAT(new.firstName, ' ', new.lastName);
END$$
DELIMITER ;

Error

Applying: 20200221064402_customers_trigger_before_insert_update.sql
Error: 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 $$
USE `green_production`$$
CREATE DEFINER = CURRENT_USER TRIGG' at line 5

[dossy]

Unfortunate response: https://github.com/go-sql-driver/mysql/issues/351

The delimiter is not a feature of the SQL query language that the server understands. It is a feature of the mysql commandline client and can only be used there.

[zionsky79]

I'm also facing the same issue. How soon this get resolved?

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

[john-landgrave]

I doubt we're looking at this one getting resolved, @zionsky79, the workaround here is to rewrite your queries so that you don't need to use the DELIMITER statement, which is almost always possible but may require some acrobatics.