Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysqldump without "CREATE ALGORITHM" and "DEFINER"

In my database backups and export I get something like the following:

/*!50001 DROP TABLE `vTime`*/;
/*!50001 DROP VIEW IF EXISTS `vTime`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`db_user`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `vTime` AS select ... */;
  1. This causes problems when restoring since the "CREATE ALGORITHM" and "DEFINER" parts fail when trying to restore. Is there a way to make mysqldump just do a simple CREATE VIEW instead?

  2. Bonus question: what do the /*!50001 parts mean?

EDIT: I should add that this is the error I'm getting:

#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation

EDIT 2: It appears that only the /*!50013 DEFINER= part is failing.

like image 302
Sarke Avatar asked Sep 19 '14 01:09

Sarke


Video Answer


1 Answers

The /*!50001 part is a version number.

The question was already answered here: MySQL flags in dumps

The main issue of removing the DEFINER is best done with a script. Some examples already online:

https://dbperf.wordpress.com/2010/04/12/removing-definer-from-mysql-dump/

Remove DEFINER clause from MySQL Dumps

like image 173
mba12 Avatar answered Sep 17 '22 16:09

mba12