Mysql – Error: The user specified as a definer (‘Admin’@’%’) does not exist

MySQLmysql-workbench

Error de SQL (1449): The user specified as a definer ('Admin'@'%') does not exist

I have this error when I'm trying to update a table. I don't know what I must do, because this error only happens with one table, but I don't want to damage other tables and the database.

I've searched how to fix it and I've executed this query:

Transact-SQL: show procedure status

But it doesn't show me the definer of my database.

What do I have to do without damaging the database?

Best Answer

Thanks, I executed this query and it worked. We need change the word 'someuser' for 'Admin' in my case.

GRANT ALL ON *.* TO 'someuser'@'%' IDENTIFIED BY 'complex-password';
FLUSH PRIVILEGES

Related Solutions

Mysql – How to fix the definer problem The user specified as a definer (‘fred’@’192.168.0.%’) does not exist

If you will recreate all your views this way (and will create all new ones with this syntax):

CREATE
SQL SECURITY INVOKER
    VIEW applications
AS     
SELECT id, product_id
FROM modules     WHERE item_type='application';

you will only need to make sure a user on another instance where you restored your database has grants to EXECUTE on your views and SELECT on tables involved.

Typical database user in a web application will have SELECT, INSERT, UPDATE, DELETE, EXECUTE until your web application creates database objects.

You do not need 'fred' to be a database user anywhere in this case even though this database user created this view on your web site.

Mysql – Database user specified as a definer

Long story short: You do not have the user 'lumicrm'@'%' on the DB Server.

SELECT user,host FROM mysql.user WHERE user='lumicrm';

Let's say you see the user 'lumicrm'@'localhost'

You have to create the user so that the view can be recognized. There are three(3) things you can try

Try This #1

SHOW GRANTS FOR CURRENT_USER();

Given the output of that query:

Copy the output of that query to a Text File
Change the host portion of the userhost to '%';
Copy and Paste into a MySQL Session to create the View.

Try This #2

CREATE TABLE mysql.lumicrm LIKE mysql.user;
INSERT INTO mysql.lumicrm SELECT * FROM mysql.user WHERE user='lumicrm';
UPDATE mysql.lumicrm SET host='%';
INSERT INTO mysql.user SELECT * FROM mysql.lumicrm;
FLUSH PRIVILEGES;
DROP TABLE mysql.lumicrm;

Now you have the MySQL user 'lumicrm'@'localhost' hacked in

Try This #3

Dump the Views to a Text File. Here are my posts on how to do this:

Jun 26, 2011 : Modify DEFINER on Many Views
Jan 19, 2012 : Will mysqldump command also backup all the views that are present in DB or there is a special command for it?
May 02, 2012 : Problem with View When Restoring a MySQL Backup

Edit the user for the View with the appropriate username. Copy the View Creation Code and Paste into a MySQL Session to create the View.