If you encounter an error like the following while doing a mysql import mysqldump, there are DEFINER statements in the database dump file. The easy way to get past this problem is to remove the DEFINER statements from the Mysql dump file.

The error message you’ll see is:

ERROR 1227 (42000) at line 3269: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

The easiest way to remove them is to run the following command:

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

After that, re-run the database import.

Why does this happen when mysqldump?

During exports, stored procedures and triggers are exported with references to the current username. This happens independent of how you back up or export your database. The username at time of export is custom to your hosting setup. When you then try to import this dump on another server, where you most often use another username to connect to the database, the username in the database dump file does not match the currently active user.

IDCloudHost | SSD Cloud Hosting Indonesia
Previous ArticleNext Article

Leave a Reply

Your email address will not be published. Required fields are marked *