Fix MySQL Stored Procedures Import Error in cPanel

Fix MySQL Stored Procedures Import Error in cPanel

When working with MySQL databases that include stored procedures, it's common to encounter errors during import in shared hosting environments with cPanel. These errors are usually related to the DEFINER parameter, which specifies the owner of the procedure, and can prevent both the import and subsequent modification of these database objects.

This issue is especially common when migrating a database from a local or development server to your shared hosting, since exported SQL files usually contain references to the root@localhost user, which is not available in shared environments for security reasons.

The most common error you'll see when trying to import stored procedures with an incorrect DEFINER is:

  1. Error #1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Additionally, when the SQL file contains lines such as:

  1. CREATE DEFINER=`root`@`localhost` PROCEDURE `my_procedure`(

The import will be rejected because you don't have the necessary privileges to create objects with a DEFINER different from your own.

Another common symptom is that, even if you manage to import the procedures somehow, you won't be able to modify or delete them from phpMyAdmin afterward, receiving access denied messages.

Why does this happen?

In Shared Hosting environments, users don't have SUPER privileges in MySQL for security reasons. This means that:

  • You cannot create database objects specifying a DEFINER different from your current user
  • The root@localhost user is restricted to server administrators only
  • Procedures created with an incorrect DEFINER become "orphaned" and cannot be modified

Solution!

Preparing the SQL file

Before importing your database, you need to edit the SQL file to fix the problematic DEFINER references.

Option A: Completely remove the DEFINER (Recommended)

  • Open your SQL file in a text editor such as Notepad++, Sublime Text, or VS Code
  • Find all occurrences of: DEFINER=root@localhost``
  • Replace them with empty text (delete them completely)
  • Save the modified file

Option B: Change to the correct user

If you prefer to keep the DEFINER, change it to your cPanel database user:

  • Find: DEFINER=root@localhost``
  • Replace with: DEFINER=your_cpanel_user@localhost``

Your cPanel user generally follows the format: cpanel_user_dbname

2. Verify user privileges

Before proceeding with the import:

  • Go to cPanel → MySQL Databases
  • Verify that your user has "ALL PRIVILEGES" assigned to the database
  • If not, add them from the user privileges section

3. Import the database

Once the file has been edited:

  • Access phpMyAdmin from cPanel
  • Select your database
  • Go to the "Import" tab
  • Select your modified SQL file
  • Run the import

The stored procedures will now be created correctly with your user as the DEFINER.

It's important to keep in mind that on Shared Hosting:

  • You won't be able to run GRANT commands to assign specific privileges
  • phpMyAdmin users are dynamically generated, which may cause some procedures to not be visible between sessions
  • Procedures created with your user will work correctly for your web applications