MySQL Data export/import probelm with SQL SECURITY DEFINER

MySQL Data export/import probelm with SQL SECURITY DEFINER

Importing a MySQL database dump from a client into my system I got this error:

$ mysql -u root -p  example_db < example_db_dump.sql 
Enter password: 
ERROR 1449 (HY000) at line 5172: The user specified as a definer ('smith'@'localhost') does not exist

Running 'grep' for that user turned up a bunch of lines like this:

/*!50013 DEFINER=`smith`@`localhost` SQL SECURITY DEFINER */

These are created on the 'donor' MySQL system when creating one or more views of the data. That user does not exist on my system and so MySQL complains.

I don't care about those views so the easiest way to deal with this issue is to remove these '50013' lines. You can do that with 'sed':

$ sed '/\*\!50013 DEFINER/d' example_db_dump.sql > example_db_dump_clean.sql

You need to drop the new database as the import process partially worked, re-create it and then you can reimport:

$ mysqladmin -u root -p drop example_db
$ mysqladmin -u root -p create example_db
$ mysql -u root -p example_db < example_db_dump_clean.sql