Recently I got an error on the website something like this:
Unable to execute INSERT statementUnable to execute INSERT statement
SQLSTATE: Syntax error or access violation: 1142 TRIGGER command denied to user 'xxxxxxxx'@'localhost' for table 'miscellaneous'
It’s pretty clear what the error message means (the user doesn’t have permission to use the TRIGGER) but what was strange is the user
xxxxxxxx doesn’t exist on the server, it should be
xxxxxxxx_admin. It turns out that while updating the production server recently and moving MySQL up to version 8.0.28 I had installed a different db user. I hadn’t realised that the TRIGGER had a user name as part of it’s definition (its ‘defined user’).
Now, I don’t know a lot about TRIGGERs but I do remember setting one up long ago (like 10 years ago) to add a last update timestamp to a column but I had no idea how to edit a TRIGGER. Turns out you can’t.
One has to DROP TRIGGER and CREATE TRIGGER with a different definer.
First I need to find the existing trigger, and
SHOW TRIGGERS is a thing apparently. We need to run:
SHOW TRIGGERS FROM `database_name` LIKE "%miscellaneous%";
which will print out the trigger.
We can now recreate it with something like this:
DROP TRIGGER `miscellaneous_BINS`;
And that seemed to do the trick.