MySQL error 1449: The user specified as a definer does not exist
MysqlPermissionsMysql Problem Overview
When I run the following query I get an error:
SELECT
`a`.`sl_id` AS `sl_id`,
`a`.`quote_id` AS `quote_id`,
`a`.`sl_date` AS `sl_date`,
`a`.`sl_type` AS `sl_type`,
`a`.`sl_status` AS `sl_status`,
`b`.`client_id` AS `client_id`,
`b`.`business` AS `business`,
`b`.`affaire_type` AS `affaire_type`,
`b`.`quotation_date` AS `quotation_date`,
`b`.`total_sale_price_with_tax` AS `total_sale_price_with_tax`,
`b`.`STATUS` AS `status`,
`b`.`customer_name` AS `customer_name`
FROM `tbl_supplier_list` `a`
LEFT JOIN `view_quotes` `b`
ON (`b`.`quote_id` = `a`.`quote_id`)
LIMIT 0, 30
The error message is:
#1449 - The user specified as a definer ('web2vi'@'%') does not exist
Why am I getting that error? How do I fix it?
Mysql Solutions
Solution 1 - Mysql
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.
You have two options:
1. Change the DEFINER
This is possibly easiest to do when initially importing your database objects, by removing any DEFINER
statements from the dump.
Changing the definer later is a more little tricky:
How to change the definer for views
-
Run this SQL to generate the necessary ALTER statements
SELECT CONCAT("ALTER DEFINER=
youruser
@host
VIEW ", table_name, " AS ", view_definition, ";") FROM information_schema.views WHERE table_schema='your-database-name'; -
Copy and run the ALTER statements
How to change the definer for stored procedures
Example:
UPDATE `mysql`.`proc` p SET definer = 'user@%' WHERE definer='root@%'
Be careful, because this will change all the definers for all databases.
2. Create the missing user
> If you've found following error while using MySQL database:
> The user specified as a definer ('someuser'@'%') does not exist` > > Then you can solve > it by using following : > > GRANT ALL ON . TO 'someuser'@'%' IDENTIFIED BY 'complex-password'; FLUSH PRIVILEGES;
From http://www.lynnnayko.com/2010/07/mysql-user-specified-as-definer-root.html
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
.
Also consider whether you actually need to grant the user ALL
permissions or whether they could do with less.
Solution 2 - Mysql
The user who originally created the SQL view or procedure has been deleted. If you recreate that user, it should address your error.
Solution 3 - Mysql
I got the same error after updating mysql.
The error has been fixed after this command:
mysql_upgrade -u root
> mysql_upgrade should be executed each time you upgrade MySQL. It > checks all tables in all databases for incompatibilities with the > current version of MySQL Server. If a table is found to have a > possible incompatibility, it is checked. If any problems are found, > the table is repaired. mysql_upgrade also upgrades the system tables > so that you can take advantage of new privileges or capabilities that > might have been added.
Solution 4 - Mysql
Follow these steps:
- Go to PHPMyAdmin
- Select Your Database
- Select your table
- On the top menu Click on 'Triggers'
- Click on 'Edit' to edit trigger
- Change definer from [user@localhost] to root@localhost
Hope it helps
Solution 5 - Mysql
If the user exists, then:
mysql> flush privileges;
Solution 6 - Mysql
Create the deleted user like this :
mysql> create user 'web2vi';
or
mysql> create user 'web2vi'@'%';
Solution 7 - Mysql
Solution is just a single line query as below :
grant all on *.* to 'ROOT'@'%' identified by 'PASSWORD' with grant option;
Replace ROOT
with your mysql user name.
Replace PASSWORD
with your mysql password.
Solution 8 - Mysql
Fixed by running this following comments.
grant all on *.* to 'web2vi'@'%' identified by 'root' with grant option;
FLUSH PRIVILEGES;
if you are getting some_other
instead of web2vi
then you have to change the name accordingly.
Solution 9 - Mysql
For future googlers: I got a similar message trying to update a table in a database that contained no views. After some digging, it turned out I had imported triggers on that table, and those were the things defined by the non-existant user. Dropping the triggers solved the problem.
Solution 10 - Mysql
grant all on *.* to 'username'@'%' identified by 'password' with grant option;
example:
grant all on *.* to 'web2vi'@'%' identified by 'password' with grant option;
Solution 11 - Mysql
I had the same problem with root user ans it worked for me when I replaced
root@%
by
root@localhost
So, if the user 'web2vi' is allowed to connect from 'localhost', you can try:
web2vi@localhost
I'm connected remotely to the database.
Solution 12 - Mysql
quick fix to work around and dump the file:
mysqldump --single-transaction -u root -p xyz_live_db > xyz_live_db_bkup110116.sql
Solution 13 - Mysql
The user 'web2vi' does not exist on your mysql server.
See http://dev.mysql.com/doc/refman/5.1/en/error-messages-server.html#error_er_no_such_user
If that user does exist, check what servers it can access from, although I would have thought that would be a different error (EG you might have web2vi@localhost, but you are accessing the db as web2vi@% (At anything)
Solution 14 - Mysql
This happened to me after moving the DB from one server to another server. Initially, the definer was using localhost and the user. On the new server we don't have that user, and host had also been changed. I took a back up of that particular table and removed all the triggers manually from phpmyadmin. After that it has been working fine for me.
Solution 15 - Mysql
> Why am I getting that error? How do I fix it?
I spent a hour before found a decision for a problem like this. But, in my case, I ran this:
mysql> UPDATE `users` SET `somefield` = 1 WHERE `user_id` = 2;
ERROR 1449 (HY000): The user specified as a definer ('root'@'%') does not exist
If you really want to find the problem, just run this commands one by one:
SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;
SHOW TRIGGERS;
SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';
...and, after each of them, look for the field 'definer'.
In my case it was bearded old trigger, that somebody of developers forgot to delete.
Solution 16 - Mysql
I had your very same problem minutes ago, I ran into this issue after deleting an unused user from mysql.user table, but doing an alter view fixed it, here is a handy command that makes it very simple:
SELECT CONCAT("ALTER DEFINER=`youruser`@`host` VIEW ",
table_name," AS ", view_definition,";") FROM
information_schema.views WHERE table_schema='databasename'
Mix this with the mysql command line (assuming *nix, not familiar with windows):
> echo above_query | mysql -uuser -p > alterView.sql
> mysql -uuser -ppass databasename < alterView.sql
Note: the command generates and extra SELECT CONCAT on the file, making mysql -uuser -ppass databasename < alterView.sql
fail if you don't remove it.
Source: https://dba.stackexchange.com/questions/4129/modify-definer-on-many-views
Solution 17 - Mysql
My 5 cents.
I had same error while I tried to select from a view.
However problem appears to be that this view, selected from another view that was restored from backup from different server.
and in fact, YES, user was invalid, but was not obvious where to from the first look.
Solution 18 - Mysql
Try to set your procedure as
SECURITY INVOKER
Mysql default sets procedures security as "DEFINER" (CREATOR OF).. you must set the security to the "invoker".
Solution 19 - Mysql
Your view, "view_quotes" may have been copied from a different database where "web2vi" is a valid user into a database where "web2vi" is not a valid user.
Either add the "web2vi" user to the database or alter the view (normally removing the DEFINER='web2vi'@'%' part and executing the script will do the trick)
Solution 20 - Mysql
In my case, the table had a trigger with a DEFINER user that didn't exist.
Solution 21 - Mysql
From MySQL reference of CREATE VIEW
:
> The DEFINER and SQL SECURITY clauses specify the security context to be used when checking access privileges at view invocation time.
This user must exist and is always better to use 'localhost' as hostname. So I think that if you check that the user exists and change it to 'localhost' on create view you won't have this error.
Solution 22 - Mysql
The problem is clear - MySQL cannot find user specified as the definer.
I encountered this problem after synchronizing database model from development server, applying it to localhost, making changes to the model and then reapplying it to localhost. Apparently there was a view (I modified) defined and so I couldn't update my local version.
How to fix (easily):
Note: it involves deleting so it works just fine for views but make sure you have data backed-up if you try this on tables.
- Login to database as root (or whatever has enough power to make changes).
- Delete view, table or whatever you are having trouble with.
- Synchronize your new model - it will not complain about something that does not exist now. You may want to remove SQL SECURITY DEFINER part from the item definition you had problems with.
P.S. This is neither a proper nor best-all-around fix. I just posted it as a possible (and very simple) solution.
Solution 23 - Mysql
You can try this:
$ mysql -u root -p
> grant all privileges on *.* to `root`@`%` identified by 'password';
> flush privileges;
Solution 24 - Mysql
You can change the definer for a specific database to an existing user:
UPDATE mysql.proc SET definer = 'existing_user@localhost' WHERE db = 'database_name';
Solution 25 - Mysql
Go into the edit routine section and and at the bottom, change Security Type from Definer to Invoker.
Solution 26 - Mysql
One or several of your views where created/registered by another user. You'll have to check the owner of the view and:
- Recreate the user; as the other answers say. or
- Recreate the views that where created by the user
'web2vi'
using ALTER VIEW
I had this problem once.
I was trying to migrate views, from BD1 to BD2, using SQLYog. SQLYog recreated the views in the other DataBase (DB2), but it kept the user of BD1 (they where different). Later I realized that the views I was using in my query were having the same error as you, even when I wasn't creating any view.
Hope this help.
Solution 27 - Mysql
If this is a stored procedure, you can do:
UPDATE `mysql`.`proc` SET definer = 'YournewDefiner' WHERE definer='OldDefinerShownBefore'
But this is not advised.
For me, better solution is to create the definer:
create user 'myuser' identified by 'mypass';
grant all on `mytable`.* to 'myuser' identified by 'mypass';
Solution 28 - Mysql
when mysql.proc is empty, but system always notice "[email protected].%" for table_name no exist,you just root in mysql command line and type:
CHECK TABLE `database`.`table_name` QUICK FAST MEDIUM CHANGED;
flush privileges;
over!
Solution 29 - Mysql
in my case I had a trigger on that table that I could not update data getting the same error.
> MySQL error 1449: The user specified as a definer does not exist
the solution was to delete the triggers on that table and recreate them again, this fixed the issue, since the the trigger was made with another user from another server, and the user name changed on the new server after changing hosting company . that's my 2 cents
Solution 30 - Mysql
i came here for the same problem, i couldn't find anywhere in my code where a certain user was making the action. apparently it was from a trigger that was using a user which was long deleted (db was restored from an older version) so in case you are puzzled as i were, take a look at your db events/triggers/routines. hope this will help someone.
Solution 31 - Mysql
Try the following:
mysqldump --routines --single-transaction -u root -proot portalv3 > c:\portal.sql
Solution 32 - Mysql
This happened to me after I imported a dump on Windows 10 with MYSQL Workbench 6.3 Community, with "root@% does not exist". Even though the user existed. First I tried to comment out the DEFINER however, this did not work. I then did a string replace on "root@%" with "root@localhost" and reimported the dump. This did the trick for me.
Solution 33 - Mysql
I have tried the above methods, but feels like repetitive action when creating the view. I got the same issue while updating the views of the imported database.
You can simply overcome the issue in LOCAL by simply creating the User with the create privilege.
Solution 34 - Mysql
For me, removing the ''
from the DEFINER did the trick.
DEFINER = user@localhost
Solution 35 - Mysql
The database user also seems to be case-sensitive, so while I had a root'@'% user I didn't have a ROOT'@'% user. I changed the user to be uppercase via workbench and the problem was resolved!
Solution 36 - Mysql
As addition, to change definer for TRIGGERS (ALTER does not work), you can do it like this:
Generate a DROP and a CREATE command for every Trigger:
SELECT CONCAT("DROP TRIGGER ", trigger_name, ";", " CREATE TRIGGER ", TRIGGER_NAME, " AFTER ", EVENT_MANIPULATION, " ON ", EVENT_OBJECT_SCHEMA, ".", EVENT_OBJECT_TABLE, " FOR EACH ROW ", ACTION_STATEMENT, ";") AS sqlCommand FROM information_schema.triggers WHERE EVENT_OBJECT_SCHEMA = "yourdatabase";
Execute it in a foreach. I use this in my app when I take the production database to my development machine and go it with a foreach over all commands and recreate the triggers automatically. This gives me the option to automate it.
Example in PHP/Laravel:
$this->info('DROP and CREATE TRIGGERS');
$pdo = DB::connection()->getPdo();
$sql = 'SELECT CONCAT("DROP TRIGGER ", trigger_name, ";", " CREATE TRIGGER ", TRIGGER_NAME, " AFTER ", EVENT_MANIPULATION, " ON ", EVENT_OBJECT_SCHEMA, ".", EVENT_OBJECT_TABLE, " FOR EACH ROW ", ACTION_STATEMENT, ";") AS sqlCommand FROM information_schema.triggers WHERE EVENT_OBJECT_SCHEMA = "mydatabase";';
$stmt = $pdo->prepare($sql, [PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true]);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt->closeCursor();
foreach($result as $rs){
$pdo = DB::unprepared($rs['sqlCommand']);
break;
}
Hint: I have to do it with pdo because of the mysql buffer query problem, described here
Solution 37 - Mysql
Try this This is simple solution
mysql -u root -p
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Solution 38 - Mysql
In my case, deleting all the views solved the issue.
DROP VIEW view_name;
Solution 39 - Mysql
// update all or particular procedures to your wanted, use existing user (my case -root)
UPDATE mysql.proc p SET definer = 'root@%' WHERE 1=1 LIMIT 1000;
(limit clause is for the reason that various mysql versions complains when is updated not restricted with limit or no where condition is used)
FLUSH PRIVILEGES; // or restart the server
Solution 40 - Mysql
you can create user with name web2vi
and grant all privilage