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

Question

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?

After thinking about this a moment and the simplest course of action was to add the missing account to the database and the error went away. No complicated procedure needed. If you can add the account then try that first. — user1794918, Dec 29 '18 at 16:28
I had same error when I was trying restore some views, I remove "DEFINER=`root`@`%`" form onlyViews.sql file that I had, and problem solved. If you have very big .sql file you can open and edit it through specefiv file editors. — MrSalesi, Aug 18 '21 at 10:41
Why definer needs to be defined to do any simple db changes, who designs that stuff this way. — kenorb, Mar 01 '23 at 17:05

score 746 · Answer 1 · edited May 25 '23 at 14:15

746

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;

MariaDb version:

GRANT ALL PRIVILEGES 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.

edited May 25 '23 at 14:15

Eugene Kaurov

2,356
28
39

answered Oct 31 '13 at 12:58

Federico J.

15,388
6
32
51

1

*.* and grant option are not required. – helpse Jun 05 '14 at 04:58
@Simon East: You have made a lovely editing, thank you very much for improving so much the answer. – Federico J. Mar 16 '16 at 08:10
I suggest adding, restart mySQL instance after running the "UPDATE `mysql`.`proc` p SET definer = 'user@%' WHERE definer='root@%'" query since the definers to the procedures are only refreshed then. – johan May 05 '16 at 10:01
5

I think it's easier to add a meaningless users, because the next time you do a dbdump and import it, you wont need to do the views/procedures editing again – DarkMukke Oct 07 '16 at 08:47
@anon, if you want to add how to do it with MySQL Workbench, please, do it in another answer and link it here on the comments, or as an update bellow the main body of the answer. Otherwise, it's opening the door to anyone explaining how to do it with his/her favorite tool. – Federico J. Jul 02 '17 at 20:32
Add backticks around view name, justi n case it has spaces (legal in MySQL). – Marco Marsala Oct 12 '17 at 10:12
Thank you so much, this solved it for me. (solution number 2 in your answer!) – Vladimir Despotovic Oct 16 '17 at 13:02
1

Thanks, I've just dropped the table with the problem, removed the `DEFINER=\`user\`@\`host\`` and re-imported it. Worked like a charm. :ok_hand: – giovannipds Mar 21 '18 at 13:31
For me the user who created the trigger was missing so updating the trigger with an existing user resolved the issue. Thanks – Raj Rajeshwar Singh Rathore Nov 22 '18 at 09:58
second step worked fo me - simple and effective thanks – Zabs Aug 23 '19 at 10:24
Thank you! I used the second option (*2. Create the missing user*). Saved my life! – Victor Nov 26 '19 at 06:26
1

Almost complete: Part I needs similar stanza for _triggers_ table. – Otheus Feb 28 '20 at 18:32
for mysql version 8.0.21 it throws syntax error because of `IDENTIFIED BY 'complex-password'`. – Savitoj Cheema Oct 16 '20 at 16:19
For MySQL 8.0.22 I get `ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist` when executing the above commands. – Matthew Knill Dec 09 '20 at 05:44
6

As mentioned by @Otheus the answer is incomplete. DEFINER can be a part of not only VIEWS and ROUTINES, but also EVENTS and TRIGGERS. To see where the missing definer is used you may use this query: `SELECT DEFINER,'events' FROM INFORMATION_SCHEMA.EVENTS union SELECT DEFINER,'routines' FROM INFORMATION_SCHEMA.ROUTINES union SELECT DEFINER,'triggers' FROM INFORMATION_SCHEMA.TRIGGERS union SELECT DEFINER, 'views' FROM INFORMATION_SCHEMA.VIEWS; ` – ualinker Jul 05 '21 at 13:27
Starting with MySQL 8 you no longer can (implicitly) create a user using the GRANT command. See this thread https://stackoverflow.com/a/50197630/276263 – krishnakumarp Nov 22 '21 at 15:42
I use ```sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' -i dumpfile.sql ``` to remove the definer – m50 Sep 07 '22 at 13:57
I still get "The user specified as a definer xy does not exist" after executing the ALTER statement which was generated with your SQL. – Black Feb 07 '23 at 16:37

score 156 · Answer 2 · edited Mar 16 '16 at 01:21

156

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

edited Mar 16 '16 at 01:21

Simon East

55,742
17
139
133

answered May 01 '12 at 17:08

Dave Z Dopson

1,579
1
9
3

4

In addition you will need to grant at least the `SELECT` and `EXECUTE` privileges to the added user. I ran into this when I exported a DB backup from one server to another where the user that created the routines didn't exist on the test server. – drew010 Dec 07 '12 at 20:26
7

Thanks, this was helpful. Often, when migrating or deploying using mysqldump the user that created the VIEW, TRIGGER or PROCEDURE (the definer) may not be the same on the target system. In that case just recreating the procedure, trigger or view (`DROP` then re-`CREATE`) using a valid user on the target system should do the trick. – Eric Kigathi Sep 03 '13 at 15:57
42

you can also change who the definer is to an existing user: `UPDATE mysql.proc SET definer = 'my_new_user@localhost' WHERE db = 'mydatatbase';` – Feb 24 '14 at 17:03
1

Exactly in my case i had a table with trigger that was pointing to a DEFINER user that was deleted. Updating the trigger user solved the problem. – Miguel Sep 27 '17 at 16:49
You also need to give permission to that user :) `GRANT ALL ON *.* TO 'someuser'@'%' IDENTIFIED BY 'complex-password'; FLUSH PRIVILEGES;` – Victor Nov 26 '19 at 06:32

score 60 · Answer 3 · answered Mar 01 '16 at 05:00

60

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

answered Mar 01 '16 at 05:00

Hussain Rahimi

802
8
19

4

This is the actual solution to the question, rather than creating user and grant permission. just change the definer. – Ankit Chauhan May 08 '17 at 12:25
Is there any way to find all triggers in database ? – Mrugesh Mistry Jul 16 '19 at 15:21
3

Find all triggers: SHOW TRIGGERS – JerzySkalski Sep 11 '19 at 09:38
From the command line 'show triggerss', from PhpMyAdmin select the database and then on the top right of navbar click on the triggers – Hussain Rahimi Nov 21 '19 at 04:56
by this approach if u have multiple triggers u need to modify and update all those triggers better approach is to create the user and give the privileges and done – Sami Ullah Feb 06 '22 at 05:42
This is really helpful. Best and easy solution. – Ishpreet Sep 14 '22 at 16:18

score 53 · Answer 4 · answered Jul 23 '18 at 20:06

53

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.

answered Jul 23 '18 at 20:06

artamonovdev

2,260
1
29
33

Not sure why this did not work for me, I had to manually remove all the triggers in mySQL workbench. – user752746 Mar 12 '19 at 20:45
When I ran the command, I got the below message on the console. The mysql_upgrade client is now deprecated. The actions executed by the upgrade client are now done by the server. – daydreamer Oct 09 '20 at 13:26
Upgrading from MySQL 5.6 to MySQL 5.7.33, this command did the job and solved the issue ! Thanks for this solution. This is much simpler and reassuring than tampering with the db information schema. Too bad that (on Ubuntu 20.04) the package upgrade does not natively perform this command after each upgrade of MySQL binaries... – CuriousFab Jun 02 '21 at 09:58
it is not advisable to run this on mariaDB, we have issue after running that – ITW Aug 06 '21 at 03:08

score 40 · Answer 5 · answered May 18 '14 at 12:44

40

Create the deleted user like this :

mysql> create user 'web2vi';

or

mysql> create user 'web2vi'@'%';

answered May 18 '14 at 12:44

Kevin

6,539
5
44
54

3

after create that missed user, encountered another error: `ERROR 1142 (42000): TRIGGER command denied to user 'web2vi'@'%' for table 'foo'` and should add this command `grant all on *.* to 'web2vi'@'%' identified by ''` after creating user – zhuguowei Feb 02 '16 at 09:49

score 39 · Answer 6 · answered Jan 09 '13 at 21:24

39

If the user exists, then:

mysql> flush privileges;

answered Jan 09 '13 at 21:24

BroknDodge

651
6
9

score 28 · Answer 7 · edited May 15 '17 at 10:49

28

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.

edited May 15 '17 at 10:49

J. Steen

15,470
15
56
63

answered Sep 26 '16 at 11:27

Muhammad Azeem

1,129
1
12
16

3

Be careful: MySQL users are case sensitive. – Alessio Cantarella Jan 08 '19 at 17:34
1

I needed to `flush privileges` after this and it works. Thanks. – Victor Jun 04 '20 at 09:11

score 19 · Answer 8 · answered Jan 27 '16 at 19:23

19

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.

answered Jan 27 '16 at 19:23

Selvamani

7,434
4
32
43

score 15 · Answer 9 · answered Nov 27 '14 at 07:31

15

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.

answered Nov 27 '14 at 07:31

Chris Poirier

151
1
2

2

Triggers was the issue, I updated the definer in the triggers section. no more issues. – Darius Sep 04 '15 at 06:47
Thanks, it's very helpful. Also need to update views. – toxxxa Oct 31 '15 at 17:54
Indeed very helpful :) I would never find that alone. – ElChupacabra Oct 11 '16 at 07:16

score 12 · Answer 10 · answered Jan 11 '16 at 11:17

12

quick fix to work around and dump the file:

mysqldump --single-transaction -u root -p xyz_live_db > xyz_live_db_bkup110116.sql

answered Jan 11 '16 at 11:17

Developer

3,857
4
37
47

2

this does not work. the definer is contained in the dump. – phil294 Sep 18 '17 at 07:58
If you use mysqlpump with a "p" instead of a "d", you can use --skip-definer – Wouter Jan 21 '19 at 10:32
@lyhong I don't have a detailed explanation, but apparently `--single-transaction` changes the way Lock Tables is implemented during a dump. Or something like that. I don't remember where I read it, but that helped me feel comfortable with "just throwing the flag in". I'm also uneasy with unexplained 'just do this' "answers". Either way, it worked for my case. – SherylHohman Aug 24 '19 at 19:08
The `--single-transaction` seems to resolve my issue – kamasuPaul Jul 18 '23 at 07:50

score 11 · Answer 11 · answered May 22 '17 at 16:32

11

grant all on *.* to 'username'@'%' identified by 'password' with grant option;

example:

grant all on *.* to 'web2vi'@'%' identified by 'password' with grant option;

answered May 22 '17 at 16:32

mesutpiskin

1,771
2
26
30

If I grant all privileges to a 'user' @ 'all ips', then what about security??!! – Mohsen Abasi Feb 07 '18 at 15:00
@MohsenAbasi This is an example for the development environment. This user can be the system administrator. The prod environment needs to be more careful. – mesutpiskin Feb 08 '18 at 06:33

c-toesca · Answer 12 · 2013-10-09T18:59:35.630

9

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.

edited Oct 09 '13 at 18:59

answered Oct 09 '13 at 18:52

c-toesca

947
1
11
13

score 8 · Answer 13 · answered Apr 16 '12 at 07:27

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)

score 7 · Answer 14 · answered Dec 02 '15 at 15:27

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.

score 7 · Answer 15 · edited Aug 24 '19 at 19:01

7

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.

edited Aug 24 '19 at 19:01

SherylHohman

16,580
17
88
94

answered Sep 14 '18 at 10:39

TS Guhan

195
2
10

Thanks for the tip, I was able to manually remove all the triggers in mySQL workbench. – user752746 Mar 12 '19 at 20:46
This was indeed a trigger problem for me, I had to remove and recreate them all – paul.ago Jun 28 '19 at 16:25
is there any other solution than recreating triggers? i'm using test dumps sometimes twice a day. this would disrupt my main processes – redestructa Sep 04 '19 at 11:26
@TS Guhan did you re-add the triggers after you removed them manually? – MailBlade Apr 11 '20 at 06:27

score 4 · Answer 16 · edited Apr 13 '17 at 12:42

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

score 4 · Answer 17 · answered Aug 23 '13 at 06:20

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.

score 4 · Answer 18 · answered Apr 28 '15 at 13:16

4

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".

answered Apr 28 '15 at 13:16

Allan Felipe Murara

516
3
14

score 3 · Answer 19 · answered Apr 16 '12 at 07:28

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.

score 3 · Answer 20 · answered Dec 25 '12 at 15:04

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.

i am using toad, cn i delete and recreate using that only os should i login as rooy from terminal and then only do?? — Vasanth Nag K V, Apr 30 '14 at 08:44
Went through all other answers, more errors to deal with with no time for it. Needed a very quick solution. At the end, I've removed all the useless triggers, don't need them anyway on the dev env (db imported from prod), and it worked. Why these stupid errors prevent user from doing any simple db updates. It's nonsense. — kenorb, Mar 01 '23 at 17:06

score 3 · Answer 21 · edited Mar 01 '23 at 17:09

3

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)

edited Mar 01 '23 at 17:09

kenorb

155,785
88
678
743

answered Jul 31 '13 at 15:32

user1016736

390
4
7

score 3 · Answer 22 · answered Feb 25 '15 at 07:41

3

In my case, the table had a trigger with a DEFINER user that didn't exist.

answered Feb 25 '15 at 07:41

jbaylina

4,408
1
30
39

2

right on the nail specially when the app is transfered from a server to another one – zardilior Mar 19 '15 at 07:18

score 3 · Answer 23 · answered Jun 22 '21 at 11:07

3

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';

answered Jun 22 '21 at 11:07

Gerard de Visser

7,590
9
50
58

score 2 · Answer 24 · edited Jul 23 '15 at 20:13

2

You can try this:

$ mysql -u root -p 
> grant all privileges on *.* to `root`@`%` identified by 'password'; 
> flush privileges;

edited Jul 23 '15 at 20:13

kenorb

155,785
88
678
743

answered Apr 21 '15 at 04:05

Sukamdani Barli

36
2

score 2 · Answer 25 · answered Aug 29 '21 at 09:26

2

For me, removing the '' from the DEFINER did the trick.
DEFINER = user@localhost

answered Aug 29 '21 at 09:26

Lakindu Hewawasam

680
4
9

score 1 · Answer 26 · answered Apr 30 '12 at 19:00

1

Go into the edit routine section and and at the bottom, change Security Type from Definer to Invoker.

answered Apr 30 '12 at 19:00

user1174436

19
2

5

Go to where? In which software? – kenorb Nov 15 '13 at 09:42
@kenorb, in phpMyAdmin you can change MySQL stored routines (procedures and functions), e.g. Security Type. – Mikl Jul 23 '15 at 18:21

score 1 · Answer 27 · edited Feb 02 '14 at 14:16

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.

helpse · Answer 28 · 2014-07-09T21:15:40.277

1

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';

edited Jul 09 '14 at 21:15

answered Jun 05 '14 at 05:04

helpse

1,518
1
18
29

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'grant all on 'mytable'.* to 'myuser' identified by 'mypass';' at line 1 – Cerin Jun 27 '14 at 17:37
@Cerin, Just change '' around mytable to ``. My answer aims to help people with this problem.. Think of reconsidering your downvote.. – helpse Jul 09 '14 at 21:17

score 1 · Answer 29 · answered Sep 14 '15 at 09:38

1

when mysql.proc is empty, but system always notice "user@192.168.%" 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!

answered Sep 14 '15 at 09:38

zhi.yang

425
5
10

score 1 · Answer 30 · answered Dec 07 '15 at 15:16

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

score 1 · Answer 31 · answered Jan 25 '16 at 14:17

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.

score 1 · Answer 32 · edited Feb 12 '16 at 19:21

1

Try the following:

mysqldump --routines --single-transaction -u root -proot portalv3 > c:\portal.sql

edited Feb 12 '16 at 19:21

Kara

6,115
16
50
57

answered Feb 12 '16 at 18:45

Wilson Carlos Gomes Martins

29
1

1

This answer could use some explanation. What does this statement do, in which environment must it be executed, what does it solve, what should be done next? – trincot Feb 12 '16 at 21:14

score 1 · Answer 33 · answered Dec 05 '16 at 06:51

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.

score 1 · Answer 34 · answered Jan 27 '21 at 08:27

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.

score 0 · Answer 35 · answered May 14 '15 at 12:33

0

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!

answered May 14 '15 at 12:33

Metalmania

1

score 0 · Answer 36 · answered Jun 20 '18 at 08:01

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

score 0 · Answer 37 · answered Oct 28 '20 at 19:02

0

In my case, deleting all the views solved the issue.

DROP VIEW view_name;

answered Oct 28 '20 at 19:02

Francesco Borzi

56,083
47
179
252

score 0 · Answer 38 · answered May 29 '23 at 08:13

I it is too late to answer, but this works for me. My scenario is I exported a function from the production db, and then imported it on my local machine. Now, what I did to make it work are:

Copy the whole sql code of the function.
Delete the imported function on your local machine.
Create a new function on your local machine.
Paste the code that you copied.

P.S. You can also just copy the code from production, and follow Step 3-4.

score -1 · Answer 39 · answered May 18 '19 at 13:19

-1

Try this This is simple solution

mysql -u root -p
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

more at https://stackoverflow.com/a/42183702/5407056

answered May 18 '19 at 13:19

Latief Anwar

1,833
17
27

This didn't do anything for me. I suggest you add in which cases it does and does not work. – David Mar 20 '21 at 13:23

score -2 · Answer 40 · answered Sep 24 '16 at 14:37

-2

you can create user with name web2vi and grant all privilage

answered Sep 24 '16 at 14:37

Jethik

1,860
1
22
26

Better to just grant the privileges that are actually needed. – Mark Fisher Jul 25 '19 at 09:39
Thank you for this solution. This solved the problem for me on MySQL 8, I just created the missing user. In this case I can use the user that was created but you can always delete the user after you are done with whatever it is you are working on that checks for the user. – Ralph Ritoch Jun 26 '20 at 10:42

FantomX1 · Answer 41 · 2019-10-05T14:13:11.783

-2

// update all or particular procedures to your wanted, use existing user (my case -root)

1) 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)

2) FLUSH PRIVILEGES; // or restart the server

edited Oct 05 '19 at 14:13

answered Jul 08 '19 at 21:49

FantomX1

1,577
2
15
23

how can I vote my reply? The 3rd time this solution helped to solve me this issue when I came back here, though I ll check once more if there are not similar replies – FantomX1 Nov 26 '19 at 10:21

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

41 Answers41

1. Change the DEFINER

How to change the definer for views

How to change the definer for stored procedures

2. Create the missing user

Linked

Related