A collection of computer systems and programming tips that you may find useful.
Brought to you by Craic Computing LLC, a bioinformatics consulting company.

Tuesday, December 14, 2010

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

Now, if you need to use the Views in your copy of the database then you need to either create that user locally and leave the lines, or change the user in those lines to one that does exist locally.



Unknown said...

Wow! This blog looks exactly like my old one! It’s on a totally different topic but it has pretty much the same layout and design. Great choice of colors!advertising | advertisement | production houses in pakistan | pakistani matrimony

Unknown said...

At this time it sounds like Movable Type is the preferred blogging platform out there right now. (From what I've read) Is that what you're using in your blog?
Advertising agencies in London

Archive of Tips