Skip to main content

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

Austin Lowery
  • Updated

Question

How do you fix routines/stored procedures so they can be manipulated via phpMyAdmin?

 

Answer

You see an error like the following:

CONFIG_TEXT: 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, where "cpanelusername" refers to the cPanel account username:

CONFIG_TEXT: cpanelusername@localhost

If the DEFINER is not set to that user, you cannot edit that routine/stored procedure in phpMyAdmin. cPanel uses the account username to access the database resources in phpMyAdmin. You can verify the current definers within the database by using the following MySQL export command. 

Note: You will want to replace DATABASE_NAME with the actual database you're reviewing. 

# mysqldump --single-transaction --no-data --no-create-info --routines --skip-triggers DATABASE_NAME

Use the following instructions 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; however, you could opt to create a backup of just the database using 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.

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.
  7. Log out of the account's cPanel, and then log back in to verify that the routines/stored procedures can now be manipulated via phpMyAdmin.
Command line
  1. Log in to the server via SSH or WHM's Terminal as the root user.

  2. Export the procedures to a SQL file. 

    Note: You need to replace "DATABASE_NAME" in the command with the actual database name that needs to be modified.

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

  3. Open the resulting storedProcedures.sql file in a text editor.

  4. Locate all DEFINER statements and update them to use the cPanel account 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:

    Note: You need to replace "DATABASE_NAME" in the command with the actual database name that you want to import the storedProcedures.sql file into.

    # mysql DATABASE_NAME < storedProcedures.sql

  7. Log out of the account's cPanel, and then log back in to verify that the routines/stored procedures can now be manipulated via phpMyAdmin.

Related articles

Comments

0 comments

Article is closed for comments.

%