testing | debian or ubuntu vps | free php script to display ads in a div | testing


A Workaround for mysqldump Error 1449

Trying to dump mysql databases (especially after mysql 5.1), it is possible to get the error

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

(another user may appear instead of 'root' and another address or IP instead of '%' )

This error is produced, because MySQL (latest 5.* versions) exports DEFINER with views or procedures, which might be not the same in production (usually a common mysql user) and developer machines (usually user root). These lines in mysqldump files start with 50013 like:

/*!50013 DEFINER=`some_user`@`some_host` SQL SECURITY DEFINER */

As far as I know, there is not yet a flag for mysqldump to avoid DEFINER export. So, a solution is to replace




The dump file can be imported afterwards to the developer machine.

You may use sed, or an editor suitable to manage large files. I prefer the cross platform Tea editor or VIM or Cream. See also.

Hope to find it helpful. Your comments are welcomed!

Sign-up for our free email newsletter. Get updates when new tutorials and tips are published. You can unsubscribe anytime with a click.

Your comments are welcomed!

This site actively encourages commenting on any post. Comments are not pre-moderated, but this community does not tolerate direct or indirect attacks, name-calling or insults. Please, read terms of use and Comment Policy at privacy policy.

More tips