Once upon a time, I moved a MySQL database and the stored procedures stopped working. The website relying on them was effectively crippled because it was a data-driven website.
When exporting a MySQL database in phpMyAdmin, there are Procedure options under Custom. So I knew that they could be exported and presumably imported as well. I checked within the migrated MySQL database itself, and sure enough, the Procedures were there.
So why were they not working?
I contacted my VPS support and they claimed that it was not possible to migrate stored procedures and that they all had to be manually re-created. I thought to myself, “bullshit!”
Like a good tech, I consulted The Oracle and eventually came across this gem:
UPDATE `mysql`.`proc` p SET definer = 'new-username@localhost' WHERE definer='old-username@localhost';
It has always amazed me just how many errors I have come across in my career that almost always (and I’m not talking debugging code here) are due to permission problems. Sure enough, it was a permissions problem in this instance!
And in mere moments, my MySQL procedures (and the website using them) were working again!
Thanks VPS support Oracle 🙂