You see an error like the following:
No routine is exportable. Required privileges may be lacking
- or -
The user specified as a definer (''mysqlusername''@''localhost'') does not exist
The DEFINER configured for a routine/stored procedure limits which 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 you can 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. You can read here how to create these backups:
- How to generate a backup of my entire cPanel account from the cPanel interface
- MySQL Backups with MySQLDump
You can use either PHPMyAdmin or the command line to edit the DEFINERS.
Method #1 - Edit the DEFINERS via PHPMyAdmin
- Log in 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
- Log in 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.
- Import the file into the database to save the changes that you made:
mysql DATABASE_NAME < storedProcedures.sql