CODEWITHSUNDEEP
triggersmysqldelimiter
R D
R D

Reputation: 29

mysqldump trigger delimiter syntax

I am using mysqldump to do backups which include triggers and it results with the following delimiter syntax errors when doing an import:

ERROR at line 1059: DELIMITER must be followed by a delimiter character or string

ERROR 1064 (42000) at line 1060: You have an error with your SQL syntax; check the manual that corresponds to your MySQL server version from the right syntax to use near `` at line 5

Line 1059 sets the delimiter before the trigger statment: DELIMITER ;;

Line 1060 is: /!50003 CREATE/ /!50017 DEFINER=user@%/ /*!50003 TRIGGER database.t_auditclients

The triggers were put in with DELIMITER $$. However, when backed up the resulting sql file has DELIMITER ;; instead of $$. I have manually changed the DELIMITER ;; to DELIMITER $$ in the mysqldump files and successfully imported using mysqlworkbench. Is there a way to make mysqldump output the original delimiters used to created the trigger?

Thanks

Upvotes: 3

Views: 4461

Answers (2)

Sebastian Cardona Osorio
Sebastian Cardona Osorio

Reputation: 485

In my case, the trigger that caused the problem didn't have the BEGIN and END statements. So I applied the corresponding DROP TRIGGER and CREATE TRIGGER, after that I made again a backup that latter restored without problems. i.e:

DROP TRIGGER `incorrect_trg1`;
DELIMITER ;;

CREATE DEFINER = `root`@`localhost` TRIGGER `incorrect_trg1` BEFORE INSERT ON `table1` FOR EACH ROW 
BEGIN
SET NEW.col = DATE_FORMAT(NEW.col,'%Y%m');
END;;

Upvotes: 0

CommaToast
CommaToast

Reputation: 12178

A solution on Linux is to run the following command:

sed -i -- 's/^..!50003\sCREATE.....!50017\sDEFINER=.root...[^`]*`.....!50003\s\([^;]*\)/CREATE DEFINER=CURRENT_USER \1/g;s/^\s*\([^\*]*;\{0,1\}\)\s\{0,1\}\*\/;;$/\1;;/g' fileName.sql

This will create a new file where the root@localhost or whatever has been taken out and replaced by CREATE DEFINER=CURRENT_USER and all the 50003/50017 crap is gone and it just works. Hallelujah!

Note: to get this to work on OS X, you cannot use the built-in version of sed. You may brew install gnu-sed, then open a new terminal window, and do the above command except use gsed instead of sed.

Then to run the SQL of course the easiest thing is just go into the mysql terminal, USE a db if necessary, then SOURCE fileName.sql ... for great profit.

Note: many Bothans died to bring us this information.

Upvotes: 4

Related Questions