permissions – MySQL error 1449: The user specified as a definer does not exist

permissions – MySQL error 1449: The user specified as a definer does not exist

This commonly occurs when exporting views/triggers/procedures from one database or server to another as the user that created that object no longer exists.

n

You have two options:

n

1. Change the DEFINER

n

This is possibly easiest to do when initially importing your database objects, by removing any DEFINER statements from the dump.

n

Changing the definer later is a more little tricky:

n

How to change the definer for views

n

    n

  1. Run this SQL to generate the necessary ALTER statements

    n

    SELECT CONCAT(ALTER DEFINER=`youruser`@`host` VIEW , ntable_name,  AS , view_definition, ;) nFROM information_schema.views nWHERE table_schema=your-database-name;n
  2. n

  3. Copy and run the ALTER statements

  4. n

n

How to change the definer for stored procedures

n

Example:

n

UPDATE `mysql`.`proc` p SET definer = [email protected]% WHERE [email protected]%n

n

Be careful, because this will change all the definers for all databases.

n

2. Create the missing user

n

n

If youve found following error while using MySQL database:

n

The user specified as a definer ([email protected]%) does not exist`n

n

Then you can solven it by using following :

n

GRANT ALL ON *.* TO [email protected]% IDENTIFIED BY complex-password;nFLUSH PRIVILEGES;n

n

n

From http://www.lynnnayko.com/2010/07/mysql-user-specified-as-definer-root.html

n

This worked like a charm – you only have to change someuser to the name of the missing user. On a local dev server, you might typically just use root.

n

Also consider whether you actually need to grant the user ALL permissions or whether they could do with less.

The user who originally created the SQL view or procedure has been deleted. If you recreate that user, it should address your error.

permissions – MySQL error 1449: The user specified as a definer does not exist

I got the same error after updating mysql.

n

The error has been fixed after this command:

n

mysql_upgrade -u rootn

n

n

mysql_upgrade should be executed each time you upgrade MySQL. Itn checks all tables in all databases for incompatibilities with then current version of MySQL Server. If a table is found to have an possible incompatibility, it is checked. If any problems are found,n the table is repaired. mysql_upgrade also upgrades the system tablesn so that you can take advantage of new privileges or capabilities thatn might have been added.

n

Leave a Reply

Your email address will not be published.