0

Could not save product in Magento 2.1.9 after copying the database from another Magento 2.1.9 shop and changing the mysql user (f.e. if you copy your develop database to production server, the credentials do necessarily not match).

Error while saving a product: SQLSTATE[42000]: Syntax error or access violation: 1142 TRIGGER command denied to user olduser@host ...

1 Answer 1

0

Solution: Recreate the triggers in the new database.

Dump triggers: mysqldump -unewuser -p --triggers --add-drop-trigger --no-create-info --no-data --no-create-db --skip-opt newdatabase > triggers.sql

Open the triggers.sql file in an text editor and replace olduser with newuser or replace via shell: sed -i -e 's/olduser/newuser/g' triggers.sql

Afterwards delete the triggers via phpmyadmin or SQL (UNTESTED, from here: https://stackoverflow.com/a/30339930/5031113): SELECT Concat('DROP TRIGGER ', Trigger_Name, ';') FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = 'your_schema';

Then import the triggers via: mysql -unewuser -p newdatabase < triggers.sql

Everything should be fine now.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.