Error cause by DEFINER in MySQL-dumps

When importing MySQL- dumps of a Magento database sometimes you get an error message like that (although you can connect to the database via shell f.e.):

ERROR 1044 (42000) at line 592: Access denied for user 'sheldon'@'localhost' to database 'magento'

In these cases you’ ll recognize that the user, who tries to connect, is wrong (not the user defined in the mysql-call).

This is caused by  DEFINER declarations in the create statements of the MySQL-dump that Magento exports (or mysqldump too).
For example:

/*!50003 CREATE*/ /*!50017 DEFINER=`sheldon`@`localhost`*/ /*!50003 TRIGGER trg_catalog_category_entity_after_insert

The problem can be solved by removing the wrong DEFINER declaration via sed:

$> gunzip <  local_magento_20141209.sql.gz | sed 's/\/\*!50017 DEFINER=`.*`@`.*`\*\///' | mysql -u DBUSER --password=PASSWORD -h localhost DBNAME

Another good idea is it to remove it during mysqldump:

$> mysqldump -u "DBUSER" --opt --single-transaction --password="PASSWORD" -h "HOST" "DBNAME" | sed -E 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' | gzip -9 > local_magento_20141209.sql.gz;

This replaces the DEFINER row with this:

/*!50003 CREATE*/ /*!50017 DEFINER=CURRENT_USER*/ /*!50003 TRIGGER trg_catalog_category_entity_after_insert

So you can import the dump on every machine with every user.

Leave a Reply

Your email address will not be published.

 

This site uses Akismet to reduce spam. Learn how your comment data is processed.