Magento 2: Fixing Mysql Triggers After Changing Database User

Published by John on October 2, 2020 Under Magento

Recently, while working on upgrading a Magento 2.3.2 site to Magento 2.3.5, I did multiple test runs on the staging site, by copying the production database over to staging, then doing the upgrade and working through some compatibility issues.

I probably did it 5 or 6 times and everything was fine, until I tried to save a product. I got the following error:

SQLSTATE[42000]: Syntax error or access violation: 1142 TRIGGER command denied to user 'production_database_user'@'localhost' for table 'catalog_product_entity_media_gallery_value', query was: DELETE FROM `catalog_product_entity_media_gallery_value` WHERE (value_id = 123 AND entity_id = 546 AND store_id = 0)

The reason for this error is that there are several triggers setup during install and database updates and they are tied to a specific database user. When you just export/import the database, the trigger user doesn’t change, so if you are using a different MariaDB user for your new site, who does not have permission on the database, then you will get a permission error when the trigger attempts to run.

An easy fix, which hopefully I will remember to do next time is to do a find/replace on the database before importing and replace the triggers with the new MYSQL user. However, another way of fixing is to dump the triggers only from the database, change the user, then reimport.

As always, before you do anything make a backup of your database.

If you aren’t comfortable restoring your database from a backup, please don’t attempt this!

How to Fix Magento 2 Trigger Access Violation

The below steps should work for you, make sure to change your database name/user accordingly.

  1. Make a backup of your database
  2. Dump only the triggers from your database: 
    mysqldump -u magento_staging_user -p --quick --no-create-db --no-create-info --no-data --triggers --routines --no-data --skip-opt --add-drop-trigger --create-options --add-drop-table magento_staging_database  > ~/magento_staging_database_triggers_.sql
  3. Use an editor or sed to find/replace all instances of your old database user with the new. In this example, we would be replacing ‘production_database_user’ with ‘magento_staging_user’
  4. Import the updated file back into database*: mysql -u magento_staging_user -p magento_staging_database < ~/magento_staging_database_triggers_.sql

* Important, review the file to make sure you are not dropping any tables or data


No Comments |

Add a Comment