Could not create trigger on MySQL about sql-migrate

As reference for others searching how to migrate MySQL triggers and landing on this issue page.

DELIMITER is a command only understood by MySQL client. You need to get rid of it in order to have sql-migrate sucessfully deploy the migration scripts.

Procedure:

• First, make sure you have multiStatements enabled in your DSN. For example, in dbconfig.yml you would define a datasource like this: datasource: 'user:pass@tcp(db_host:db_port)/my_db?parseTime=true&multiStatements=true'.

• Second, remove all DELIMITER commands from your sql code. For example, code looking like this:

DELIMITER $$
USE `my_db`$$
CREATE DEFINER = CURRENT_USER TRIGGER `my_db`.`my_table_BEFORE_INSERT` 
BEFORE INSERT ON `my_table` 
FOR EACH ROW
BEGIN
	IF NEW.blah IS NULL THEN
		SET NEW.blah = '';
	END IF;
END$$

must be cleaned-up to look like this:

-- +migrate StatementBegin
USE `my_db`;
CREATE DEFINER = CURRENT_USER TRIGGER `my_db`.`my_table_BEFORE_INSERT` 
BEFORE INSERT ON `my_table` 
FOR EACH ROW
BEGIN
	IF NEW.blah IS NULL THEN
		SET NEW.blah = '';
	END IF;
END;
-- +migrate StatementEnd

Note that there is no DELIMITER $$ present and all occurrences of $$ were replaced with ;

[kjda]

same here!

[neoramos]

Hello,
Thank you for this great tool.
I was wondering if anyone has found a solution for migrating triggers.
This works from Sequel Pro IDE on a MySQL:

DROP TRIGGER IF EXISTS surveys.Survey_BEFORE_UPDATE;

DELIMITER $$
USE `surveys`$$
CREATE DEFINER = CURRENT_USER TRIGGER `surveys`.`Survey_BEFORE_UPDATE` BEFORE UPDATE ON `Survey` FOR EACH ROW
BEGIN
	IF NEW.status != OLD.status OR 
       NEW.surveyGroupID != OLD.surveyGroupID OR 
       NEW.surveyMonth != OLD.surveyMonth OR 
       NEW.surveyYear != OLD.surveyYear THEN
		INSERT INTO SurveyHistory (sequenceID,surveyGroupID,surveyYear,surveyMonth,`status`,statusAt,sentAt,lastActiveAt)
		VALUES (OLD.id,OLD.surveyGroupID,OLD.surveyYear,OLD.surveyMonth,OLD.`status`,OLD.statusAt,OLD.sentAt,OLD.lastActiveAt);
	END IF;
END$$
DELIMITER ;`