You may see an error like the following if you need to apply the fix outlined in this article:
No routine is exportable. Required privileges may be lacking
- or -
The user specified as a definer (''mysqlusername''@''localhost'') does not exist
The DEFINER that is configured for a routine/stored procedure limits what users can make edits. PHPMyAdmin makes use of a mysql user in the following format:
If the DEFINER is not set to that user, you cannot make edits to that routine / stored procedure in PHPMyAdmin.
Use the following guide to fix the DEFINERs that are set in your routines.
First, create a backup of the account so that you may restore it if something goes wrong. It would be prudent to create a full account backup, but you could opt to create a backup of just the database with MySQLDump.
In order to edit the DEFINERS you can use PHPMyAdmin or do so via the command line.
Method #1 - Edit the DEFINERS via PHPMyAdmin
- Login to WHM as the root user
- Open phpMyAdmin,
- Click the + symbol next to your database
- Click on Procedures
- From the Routines list, click edit next to the name of each stored procedure.
- Modify the Definer line so that the user is set to the cPanel username
Method #2 - Edit the DEFINERS via the command line
- Login to the server via SSH or Terminal as the root user.
- Export the procedures to a sql file
mysqldump --single-transaction --no-data --no-create-info --routines --skip-triggers DATABASE_NAME > storedProcedures.sql
- Open the resulting storedProceedures.sql file in a text editor.
- Locate all of the DEFINER statements and update them so that they use the cPanel username. For example:
- Save the file with your text editor.
- Then import the file into the database to save the changes that you made:
mysql DATABASE_NAME < storedProcedures.sql