0

I have a web server that serves Magento2 and a MySQL Server that hosts the database and both of these servers are in a single ubuntu instance.

I would like to move the MySQL server to a separate RDS MySQL instance, so I have dumped the database with mysqldump as root user and while uploading it to RDS MySQL instance I get:

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

I have checked for the solution and removed DEFINER with sed 's/\sDEFINER=[^]*@[^]*//g' -i dumpedfile.sql and after removing DEFINER the upload worked without any error.

My question is, what exactly definer is, is it safe to remove it from the dumped file of a Magento 2 database?

0

This isn't precisely a Magento question, but I think Google answers your question in a slightly different context. I'd work on synchronizing users between database instances before migration rather than simply remove that value:

DEFINER clause

Because a MySQL migration job doesn't migrate user data, sources that contain metadata defined by users with the DEFINER clause will fail when invoked on the new Cloud SQL replica, as the users don't yet exist there.

You can identify which DEFINER values exist in your metadata by using these queries. Check if there are entries for either root%localhost or users that don't exist in the target instance.

SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.EVENTS;

SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.ROUTINES;

SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.TRIGGERS;

SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.VIEWS;

If your source database does contain this metadata you can do one of the following:

Update the DEFINER clause to INVOKER on your source MySQL instance prior to setting up your migration job. Create the users on your target Cloud SQL instance before starting your migration job. Create a migration job without starting it. That is, choose Create instead of Create & Start.

Create the users from your source MySQL instance on your target Cloud SQL instance using the Cloud SQL API or UI.

Start the migration job from the migration job list or the specific job's page.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.