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
DEFINER=`some_user`@`some_host`
with
DEFINER=CURRENT_USER
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.
Entrepreneur | Full-stack developer | Founder of MediSign Ltd. I have over 15 years of professional experience designing and developing web applications. I am also very experienced in managing (web) projects.
