Vultr DocsLatest Content
Associated Doc

How to Fix MySQL "Error 1227 (42000): Access denied" for DEFINER Permissions?

Updated on 15 September, 2025

A guide to resolving MySQL permission errors when importing databases with DEFINER clauses that reference non-existent or unauthorized user accounts.

An ERROR 1227 (42000) typically occurs during MySQL import operations when the dump file contains DEFINER clauses referencing MySQL user accounts that either:

  • Do not exist on the target MySQL server, or
  • Lack the required permissions to create or modify database objects such as views, stored procedures, triggers, or events.

When MySQL attempts to create these objects using the specified DEFINER account, it verifies the account’s existence and privileges. If the checks fail, the import results in ERROR 1227. This commonly happens when migrating databases between different environments or servers with differing user accounts and privileges.

To resolve this error, try the following:

1. Remove DEFINER Clauses from the Dump File

The simplest and safest approach is to strip all DEFINER clauses from the dump file before importing. This causes MySQL to create objects under the importing user’s privileges instead of a potentially invalid or unauthorized DEFINER.

You can do this with a command-line text-processing utility like sed:

console 
$ sed -i 's/\sDEFINER=`[^`]*`@`[^`]*`//g' yourdumpfile.sql

This regular expression matches all DEFINER clauses and removes them in place. This ensures compatibility regardless of the users existing on the target server.

2. Generate a New Dump Without DEFINER Clauses

If you have control over the dump generation, create the dump without DEFINER clauses using:

Note
Replace <username>, <hostname>, <port> and <database_name> with your actual MySQL username, host address, and database name respectively before running the command. For security reasons, omit the password value from the command and enter it when prompted.
console 
$ mysqldump --user="<username>" --password --host="<hostname>" --port=<port> --set-gtid-purged=OFF --databases <database_name> > vultr-db-export.sql
  • --set-gtid-purged=OFF avoids GTID-related privilege statements which can cause import issues.

This method prevents DEFINER-related errors entirely by not including DEFINER statements in the export.

3. Import Using an Administrative User

Use a MySQL user with sufficient privileges, such as vultradmin, to perform the import:

console 
$ mysql --user=vultradmin --password --host="<hostname>" --port=<port> < vultr-db-export.sql

Admin users have the necessary SUPER or SYSTEM_VARIABLES_ADMIN privileges to bypass restrictions on DEFINER clauses, allowing the import to complete successfully.