MySQL | The user specified as a definer ('test'@'%') does not exist

Question

In my phpmyadmin i have a database called "testDB" and with my command line i'm trying to export the database, but every time i try to run this command i get mysql error 1449:

mysqldump -u root -p testDB > data-dump.sql

the error is this: "The user specified as a definer ('myuser'@'%') does not exist" when using LOCK TABLES"

I analyzed the sql file and saw that this username appears only once at the bottom of the file:

"/* 50013 DEFINER='myuser'@'%' sql security definer */"

Without export database and without create an new user because i can't, how can i do for resolve this issue?

Is it possible to replace this value with a specific SQL command for my database?

Details of my server:

-Apache/2.4.29

-MariaDB 10.4.18

-PHP 7.4.18

matigo matigo · Accepted Answer · 2021-05-06T12:22:35

The username appears only once at the bottom because mysqldump exited before finishing the data export. There are two possible solutions that I can think of off the top of my head:

If you have a table, routine, and trigger creation file available, you can export just the data:
```
mysqldump -u [user] -p --no-create-info testDB > data-dump.sql
```
If “nobody is watching”, then you can create a 'myuser'@'%' account temporarily, grant the appropriate privileges, perform the dump, then remove the user. You are performing the data export with root, so you have the keys to the castle:
```
mysql > CREATE USER IF NOT EXISTS 'myuser'@'%' IDENTIFIED WITH mysql_native_password BY 'superSecretPassword!123';
mysql > GRANT USAGE ON `testDB`.* TO 'myuser'@'%';
mysql > FLUSH PRIVILEGES;
mysql > exit;
```
Then dump the data:
```
$ mysqldump -u root -p testDB > data-dump.sql
```
Then remove the user account (if necessary):
```
mysql > DROP USER 'myuser'@'%';
mysql > FLUSH PRIVILEGES;
mysql > exit;
```
If you wish to remove the DEFINER line from the SQL export, you can do that after mysqldump completes with sed:
```
sed -i 's/DEFINER=[^*]*\*/\*/g' data-dump.sql
```
If you want to do this “the DBA way”, modify the DEFINER for all objects in the database. This answer on the DBA Stack site can get you started with how to accomplish this goal. Once done, you shouldn’t see the error again.

MySQL | The user specified as a definer ('test'@'%') does not exist

1 Answers