Navigation

	Home Page
	Index
	All Pages

Quick Search
Advanced Search »

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

Error When Migration MySQL Database to Amazon RDS

Overview¶

When migrating a MySQL database I received the following error: "Error Code: 1227. Access denied; you need (at least one of) the SUPER privilege(s) for this operation". This article provides the resolution to this issue.

It is important to note the approach I was using to migrate the database. The source database was hosted on Host Gator, and I used their Export Database feature (found in the "phpMyAdmin" section) to generate a SQL script file. When I ran the SQL script file against the target database, which was hosted on Amazon RDS, I received the error noted above. Using Amazon's Database Migration Service was not an option, as I wanted to migrate the database without taking down the website that depended on it. This requires turning on binary logging for the source database, which was not possible in this case because of the hosting arrangement with Host Gator.

Solution¶

The problem lies in the fact that the CREATE statements included a DEFINER clause (see below) for a user which didn't exist in the target database.

CREATE DEFINER=`myuser`@`localhost` PROCEDURE ...

The solution was to remove all the DEFINER clauses. This is easily accomplished via a global search and replace of "CREATE DEFINER=`myuser`@`localhost`" with "CREATE".