MySQL – ERROR 1153 During mysqldump Restoration on Amazon RDS

amazon-rdsMySQL

I am trying to import a table on an Amazon RDS server using mysqldump. During restoration, i get the following error message:

ERROR 1153 (08S01) at line 925: Got a packet bigger than 'max_allowed_packet' bytes

I then add --max_allowed_packet=128M to the restoration command, and now get the following error:

ERROR 2006 (HY000) at line 925: MySQL server has gone away

Here is the mysqldump restoration command:

mysql --max_allowed_packet=128M -uroot -p -h db-name.123456789012.us-east-1.rds.amazonaws.com dbname < /srv/tmp/dbname.sql

And when i try to set the max_allowed_packet and net_buffer_length globally during the current session without having to restart the RDB instance, i get the following error:

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

Attempt to add 'SUPER' privilege to the user also fails.

Best Answer

In order to change a dynamic option in Amazon RDS, you will have to modify the DB Parameter associated with the DB Instance.

Edit the option max_allowed_packet value, setting it to 1073741824.

Once you save it, it should be applied immediately since it is a dynamic option.

Afterwards, you can verify the change by logging into the RDS instance and running

SELECT @@global.max_allowed_packet;

Once verified, you may proceed to load your mysqldump.

Related Question