SQL Syntax error or access violation: 1142 TRIGGER command denied to user

Question

If I want to update a configurable product I do get an error message:

SQLSTATE[42000]: Syntax error or access violation: 1142 TRIGGER command denied to user 'dbadmin_magento'@'localhost' for table 'catalog_product_entity_url_key', query was: INSERT INTO `catalog_product_entity_url_key` (`entity_type_id`,`attribute_id`,`store_id`,`entity_id`,`value`) VALUES (?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE `value` = VALUES(`value`)

I already checked the grants:

mysql> show grants;
+------------------------------------------------------------------------------------------------------------------------+
| Grants for dbadmin_magento@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dbadmin_magento'@'localhost' IDENTIFIED BY PASSWORD '*1234556789012345678912345678234567234563' |
| GRANT ALL PRIVILEGES ON `dbadmin\_magento\_prod`.* TO 'dbadmin_magento'@'localhost'                                    |
+------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Does anyone has an idea how to fix it? I ran out of ideas.

Answer 1

I learned the database user has changed and I didn't notice/check it:(

If you do not have enough permission to update the trigger/db user (or don't know how to do it), here is an easy solution:

1. dump database

2. replace the old db user with the new one (run in command line)

   sed -i -- 's/olderDatabaseUser/newDatabaseUser/g' db_backup.sql
   

3. reimport the database

Note: This command is going to replace every string "olderDatabaseUser" with "newDatabaseUser" in the entire sql dump. You can only use it if your db username is really unique and not something like "magento". Otherwise you gonna break your dump.

Answer 2

If you created the grant recently you may not have flushed the privileges:

> FLUSH PRIVILEGES;

Otherwise, the issue here is the Magento Enterprise requires MySQL Triggers to run the indexing in the background. The triggers are created with the original user of the database and then when mysqldump is run it recreates the function with that user.

If the user does not exist, the dump can complete but will give warnings. It's only when the triggers are executed to you receive warnings of this type.

My suggestion is to drop all MySQL functions and reimport them/recreate them with the appropriate user.