1

Moving an old app that was set up 8 years ago. I've run mysqldump and now I'm trying to load it into a new MySQL database running on AWS RDS.

There was a mismatch in types. There was a text field that has too much information in it, but apparently the limits were not strictly enforced on the old database. I think I can fix this if I can do this:

set innodb_strict_mode = OFF;

I read this carefully and followed the directions:

Why is innodb strict mode not enabling?

But if I try:

set innodb_strict_mode = OFF;

I get:

ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation

I've spent hours trying to figure out if there is a way I can get the right privileges on RDS, but I can't find anything. Does anyone know?

This page makes it sound hopeless:

https://aws.amazon.com/premiumsupport/knowledge-center/mysqldump-error-rds-mysql-mariadb/

It says:

"The command for the line referenced in the error message requires SUPER privilege(s) that aren't provided in RDS DB instances."

If that is true, then is there some work around? I can't simply throw away millions of records.

0

1 Answer 1

1

Yes, you can set this via a param group. See Working With Param Groups

tldr;

  1. create new param group
  2. edit innodb_strict_mode, set to 0 for off, 1 for on
  3. modify db instance and apply new param group to it
  4. reboot instance

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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