mysqldump: Got error: 1449:

Question

mysqldump: Got error: 1449: The user specified as a definer('root'@'192.200.1.16') does not exist when using LOCK TABLES

kindly give the solution on above error.

possible duplicate of [MySQL error 1449: The user specified as a definer does not exist](http://stackoverflow.com/questions/10169960/mysql-error-1449-the-user-specified-as-a-definer-does-not-exist) — Joe Murray, May 01 '15 at 13:33

score 103 · Answer 1 · edited Jul 21 '21 at 15:55

103

Its better to use first mysqldump with --single-transaction, like:

mysqldump --single-transaction -u root -p mydb > mydb.sql

If above not working try below one.

You have to replace the definer's for that procedures/methods, and then you can generate the dump without error.

You can do this like:

UPDATE `mysql`.`proc` p SET definer = 'root@localhost' WHERE definer='root@192.200.1.16'

3rd party edit

For mysql 8.0 the table proc does no longer exist. Try

 SELECT * FROM information_schema.routines;

edited Jul 21 '21 at 15:55

surfmuggle

5,527
7
48
77

answered Oct 27 '14 at 08:10

prashant thakre

5,061
3
26
39

What if it is a table that you need to change a definer for and not a procedure, view, or method? – Nick Rolando Jul 08 '16 at 18:36
7

Thanks! It worked! Also, if anyone is interested, [here](https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_single-transaction) is some documentation on the `--single-transaction` option. – schro's cat Aug 18 '17 at 03:20
Specifically (from the docs): "The `--single-transaction` option and the `--lock-tables` option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly. " So I guess it removes any LOCK TABLES setting for that transaction, thus there is no longer any conflict? That's how I read it anyway.. – SherylHohman Aug 24 '19 at 19:25
1

Thanks, --single-transaction worked. You saved me. – supunbatagoda Sep 22 '22 at 11:10

score 4 · Answer 2 · edited May 05 '23 at 09:38

I faced the same problem after I copied all the views and tables from another host.

It worked after I used the query from this answer to change all definers in my database.

Copied here for convenience:

  SELECT CONCAT("ALTER DEFINER=`youruser`@`host` VIEW ", 
                table_name, 
                " AS ", 
                view_definition, ";") 
    FROM information_schema.views 
    WHERE table_schema='your-database-name';

score 2 · Answer 3 · answered Mar 15 '22 at 05:09

2

mysqldump -u user -ppassword --lock-tables=false --default-auth=mysql_native_password dbname > dump.sql

answered Mar 15 '22 at 05:09

y durga prasad

1,184
8
11

score 0 · Answer 4 · answered Aug 24 '16 at 21:29

I had a similar problem, the problem was that wanted to migrate a database from one instance to another but in the dump also were procedures that referred to other databases so I marked that mistake and corrected by modifying the procedures or failing to remove some.

score 0 · Answer 5 · answered Aug 04 '21 at 11:43

The most updated answer mentions using --single-transaction make sure that when you are restoring that backup you will face that error again so the best solution is to alter the definers, if user is missing add definer for the any admin user which is being used currently.

score -6 · Answer 6 · edited Mar 25 '15 at 09:52

-6

try this:

mysqldump -h hostname -u thomas -p -x dbname >  xxx_2015_03_25.sql

edited Mar 25 '15 at 09:52

Code Lღver

15,573
16
56
75

answered Mar 25 '15 at 07:12

thomas

1
1

1

Need to give more information to resolve the query, just taking dump is not going to resolve the issue. – Ashutosh Nigam Mar 25 '15 at 07:45
1

this answer dose not support this question. – shgnInc Sep 16 '15 at 06:38
this answer is off-topic – Pierre Aug 04 '21 at 11:48

mysqldump: Got error: 1449:

6 Answers6

3rd party edit