MySql Error 'max_allowed_packet' bytes during import SQL script on database hosted on AWS RDS

Question

I was facing an issue during import SQL file on MySql database which is on AWS RDS, during import SQL file initial table was imported then suddenly it shows an error.

I know this error and I can solve this by increasing max_allowed_packet size in MySQL config but is not local Mysql is AWS RDS show how can I do?

Collinsia

Answer 1

After reviewing this article https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html I understand what I have to do and how.

Here are the following steps.

1) Go to RDS page and click on "Parameter groups" text located in the left navigation.

2) Now create a new group by clicking on "Create parameter group" orange button.

3) Fillup parameter group form "Parameter group family" should be MySql version of your RDS instance than "Group name" and "Description" whatever you want.

4) New "Parameter group" was created as "test" now edit it and increase "max-allowed-packet" by entering "10000000" (10 MB) value should be in Bytes after entering value don't forgot to click on "Save changes" button and make sure value is between 1024 Bytes to 1073741824 Bytes (1073 MB)

5) Now you can see your changes by click on "Preview changes" button.

6) Now we have to change "Parameter group" of our RDS instance. SO, modify an instance and select "Parameter groups" which we created.

7) Now after modify RDS instance you should to Reboot it to apply your changes.

Collinsia

Thanks for the very thorough post! – Cathepsin

Great answer, a couple of things to note in case folks are curious like me. Yes you need to create a new parameter group, you can't edit the default, and yes you need to reboot, parameter group doesn't go into effect otherwise – Walleye

N.B. if you are updating a non-default parameter group attached to the db, no reboot is required. – Bangkok

With mariaDB in RDS, I can confirm that with non-default parameter group attached to the db, no reboot is required. – Mandorla

Answer 2

Go to RDS, select Parameter Groups and create new group/edit existing one. Search for max_allowed_packet and enter new value for it. Attach this parameter group to RDS instance.

Almena