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.
Please keep in mind that the modification and maintenance of database configurations such as the DEFINER statements of your database are tasks that are best handled by a systems administrator with the skills, training, and expertise required to do so for you. Although this is an administrative task that is not related to cPanel, we would like to offer the following guide as a courtesy.
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.
Edit the DEFINERS via PHPMyAdmin
- 1.Login to WHM as the root user
- 2. Open phpMyAdmin,
- 3. Click the + symbol next to your database
- 4. Click on Procedures
- 5. From the Routines list, click edit next to the name of each stored procedure.
- 6. Modify the Definer line so that the user is set to the cPanel username
Edit the DEFINERS via the command line
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 MySQL user. For example:
Save the file with your text editor.
Then import the file into the database:
mysql DATABASE_NAME < storedProcedures.sql