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 🙂