How to solve error importing .sql file - You have an error in your SQL syntax; SQL SECURITY DEFINER

i run mysqldump to export my wordpress database (mysql 5.7) into .sql file, then i do importing back .sql to new database (other server), but it shows an error something like this:

ERROR 1064 (42000) at line 2334: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '172.31.%` SQL SECURITY DEFINER */

solution:
remove any DEFINER statement that exist in your .sql file.


sed -i 's/DEFINER=[^*]*\*/\*/g' mydump.sql

that solutions i found in stackoverflow:
https://stackoverflow.com/questions/9446783/remove-definer-clause-from-mysql-dumps

Тэги:

#mysql #mysqldump #export #import #error #definer #sql_security_definer #.sql #file #ubuntu #terminal #database #rdbms #aws #ec2_instance

Скачать видео