TRIGGER command denied to user

Recently I got an error on the website something like this:

Unable to execute INSERT statementUnable to execute INSERT statement

followed by

SQLSTATE[42000]: 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`;
CREATE DEFINER=`xxxxxxxx_admin`@`localhost`
TRIGGER `miscellaneous_BINS`
BEFORE INSERT ON `miscellaneous`
FOR EACH ROW 
SET NEW.created = CURRENT_TIMESTAMP;

And that seemed to do the trick.

Another, more complex, article helped me here at Atlassian and of course there are the MySQL manual pages

End of file