No image

Solved! AWS RDS Import .. you need .. SUPER privilege(s)

Oct 2019: If you are installing Magento 2.3.x, and get an error that eludes to MySQL and you need (at least one of) the SUPER privilege(s) for this operation, uncheck the install of the module called: Magento_GoogleShoppingAds this is done from the advanced options during the Magento install wizard.You can now proceed to successfully install.

Otherwise please continue with the article below:

Many aMiSTACX customers are migrating from other hosting companies or other AWS stacks. Following our DIY migration path is most of the time flawless; however, you may encounter one weird frustrating anomaly along the way when it comes to importing your database to RDS.

“Access denied; you need (at least one of) the SUPER privilege(s) for this operation”.

Not to get into the specifics, you can research yourself. Let’s just get to the fix!

Step 1. Create a new RDS parameter group.

Step 2. In the new parameter group search and set this log_bin_trust_function_creators to 1

Step 3. Attach this new parameter group to the RDS instance and reboot the instance.

Step 4. All of our new stacks have the Peal library already installed, but if yours does not then install it:

sudo apt install libwww-perl -y

Step 5. Run this perl code to massage the Definer out of your dumped Database:

perl -pe 's/\sDEFINER=`[^`]+`@`[^`]+`//' < mysqldump.sql > mysqldump.fixed.sql

[Use your file naming convention.]

Step 6. Run the import with the new fixed:

sudo mysql -u USER -h -p DATABASE < mysqldump.fixed.sql

Step 7. Don’t forget to delete the dumps and the fix dbs! Save a copy on your local if you need 😉

That’s it. In like Flynn 🙂

~ Lead_Robot