Jasinski Technical Wiki

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

PoweredBy

Error When Migration MySQL Database to Amazon RDS

RSS
Modified on Mon, Jun 20, 2016, 1:39 PM by Administrator Categorized as Amazon Web Services, MySQL

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".

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.