Skip to content

MySQL Error 1449

Error 1449

There was an error occurred during a migration:

Got error: 1449: The user specified as a definer ('user'@'10.1.23.%') does not exist when using LOCK TABLES

It's because your target database has no account called user'@'10.1.23.%, you should change or remove the definer clauses in the dump file before import.

Solutions

I know you can change the definer to an existing user or create the missing account instead. But I decide to remove DEFINER clause from the dumping, either by vi editor or sed command.

vi Editor

In vi editor, issuing the following command:

:%s//*!50017 DEFINER=`w*`@`10.1.23.%`*///g

sed Filter

Under root's prompt, please use sed command to filter out unnecessary strings and export to a SQL file:

[root@localhost ~]# mysqldump -u'root' -p dbname1 | sed -e 's//*!50017 DEFINER=`w*`@`10.1.23.%`*///g' > mysqldump.sql

The way we remove DEFINER clause from dumping output by sed command is extremely flexible for later operations.

Piping Output

Since the synchronization between source and target database could be a routine operation, you can pipe the output to the target database instead of a dump file for convenience, which can be executed without asking:

[root@localhost ~]# mysqldump -u'root' -p'password' dbname1 | sed -e 's//*!50017 DEFINER=`w*`@`10.1.23.%`*///g' | mysql -u'user' -p'password' -h 'db.example.com' dbname2

With the sanitized result, the definer will be switched to the importer's account during migration.

Self-Import

It's also useful to correct the definitions in the same database by self-import. For example, if you'd like to change the definer from 'root' to 'user', you can filter out the definer clause and then import the sanitized dump result by 'user'.

[root@localhost ~]# mysqldump -u'root' -p'password' dbname1 | sed -e 's//*!50017 DEFINER=`w*`@`10.1.23.%`*///g' | mysql -u'user' -p'password' dbname1

Leave a Reply

Your email address will not be published. Required fields are marked *