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

    • Related Articles

    • How to Install cPanel on Your Server or VPS: Updated Guide by Distribution

      cPanel & WHM is the most widely used control panel in the web hosting industry. If you have a dedicated server or VPS with Webzi (or any other provider), this guide shows you how to install cPanel from scratch on the currently supported operating ...

    • How to Manually Move a cPanel Account to Another Partition

      cPanel includes a native feature called rearrange that allows moving accounts between partitions from WHM. However, this process requires enough free space on the source partition to create a temporary copy, and when the partition is nearly full, the ...

    • How to install and configure Nginx as a reverse proxy in cPanel/WHM 🚀

      What is Nginx and why use it with cPanel? Nginx is a high-performance web server that can function as a reverse proxy in front of Apache on cPanel servers. This configuration allows Nginx to handle static files (images, CSS, JavaScript) while Apache ...

    • Fix: Service Unavailable Error on CloudLinux Servers

      The "Service Unavailable" error is one of the most common issues on shared hosting servers running CloudLinux with CageFS and mod_lsapi. This error indicates that the server could not process the visitor's request, usually because the PHP backend ...

    • How to Export and Import a MySQL Database Using Command Line

      Having an up-to-date backup of your database is essential to protect your critical information and ensure business continuity. In this tutorial, we will show you how to export a database using the command line. It is important to perform a complete ...