Using triggers with different users (MySQL error 1449)

Question

Question: How can I export the database contents (including triggers) from my dev system and import the data to the live server without running into error 1449 when a trigger gets triggered?

In a recent php project I am making extensive use of mysql triggers but ran into a problem when deploying my database from dev to live system.

E.g. one of my triggers is defined as follows (output generated by using mysqldump)

DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER update_template BEFORE UPDATE ON template
FOR EACH ROW BEGIN
  SET new.mod_date := now();
END */;;
DELIMITER ;

That trigger was defined on my dev system using the user root@localhost which creates the DEFINER=root@localhost clause in above statement.

root@localhost does not exist as a user on the live server which causes the following error when ever the trigger gets triggered (e.g. by using update templates set...) by the live systems user

1449: The user specified as a definer('root'@'localhost') does not exist

Currently I use mysqldump --add-drop-table --user=root -p my_project > export.sql for export and mysql -u devuser -p my_project < export.sql for importing data.

Export/import works flawless. The error occurs only in cases when I manipulate the data via sql and a trigger gets involved.

Edit:

MySQL version is 5.5.47 (live and dev)

What versions of MySQL uses?. You can use [`--skip-definer`](http://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_skip-definer) since version 5.7.8 of MySQL. — wchiquito, Mar 03 '16 at 15:57
Thank you for the hint, unfortunately not an option for me but added it to http://stackoverflow.com/questions/9446783/remove-definer-clause-from-mysql-dumps/ — maxhb, Mar 03 '16 at 16:07

score 1 · Answer 1 · answered Apr 07 '16 at 16:44

Once you've exported to export.sql, next you'll need to sanitize your trigger lines using regex via sed, like this:

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' export.sql

This works on Linux, but if you're on Mac OS X, the built-in sed command won't work. You'll need to first brew install gnu-sed, then use gsed instead of sed in the above command.

score 1 · Answer 2 · answered Aug 07 '19 at 10:17

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

DELIMITER ;

score 0 · Answer 3 · answered Dec 01 '17 at 08:16

0

Use the following sed command to remove the DEFINER part from the dump file and then import it to your live server.

sed 's/\sDEFINER=`[^`]*`@`[^`]*`//' -i dumpfile.sql

The triggers will then be created by the user importing the dump file by default.

answered Dec 01 '17 at 08:16

Siddharth Nayar

836
7
6

Using triggers with different users (MySQL error 1449)

3 Answers3