MySQL Stored Procedures

A stored procedure in a MySQL database is a query or sequence of queries that operates on your data and returns some set of results. It can be a good way to automate reporting and other common queries.

When importing a database with stored procedures, you may see lines like this:-
DEFINER=root@localhost
or
DEFINER=username@localhost

The DEFINER for each stored procedure needs to be set to the MySQL user that you are importing the database as, so if this is username_db123, then change the lines in your .sql dump to say:
DEFINER=username_db123@localhost

The import of the stored procedures should then complete successfully. One issue with stored procedures is that you won't be able to edit them from PHP MyAdmin when logged in to cPanel, because the cPanel PHP MyAdmin user is dynamically generated and doesn't match the security definer. To edit a stored procedure, the easiest way in cPanel/PHP MyAdmin is to dump the procedure, edit the SQL and then drop and re-import the specific procedure.


Did this page help you?