How to fix routines/stored procedures so they can be manipulated via PHPMyAdmin

Introduction

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:

cpanelusername@localhost

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.

Procedure

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

1. Log in 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.

Method #2 - Edit the DEFINERS via the command line

1. Log in to the server via SSH or Terminal as the root user.
2. Export the procedures to a sql file:

   mysqldump --single-transaction --no-data --no-create-info --routines --skip-triggers DATABASE_NAME > storedProcedures.sql

3. Open the resulting storedProceedures.sql file in a text editor.
4. Locate all of the DEFINER statements and update them so that they use the cPanel username. For example:

   CREATE DEFINER=`cpanelusername`@`localhost`

5. Save the file with your text editor.
6. Import the file into the database to save the changes that you made:

   mysql DATABASE_NAME < storedProcedures.sql