AWS RDS out of memory error when adding column

Question

We got MySQL database on AWS RDS with innodb engine, the MySQL version is 5.6.19.

When trying to add a column in a table, we get the error message below:

ERROR 1041 (HY000): Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space

The script we run to alter table is below: ALTER TABLE mytablename ADD COLUMN temp_colume varchar(255) NULL AFTER temp_firstcolumn;

Our RDS is on db.m3.2xlarge with 30GB memory: Our innodb buffer size is DBInstanceClassMemory*3/4 ~= 24GB

We can successfully re-creating the table with the column changes already made to it, but we are getting error when altering tables.

Does anyone meet the same issue?

Answer 1

I've seen inplace alters in RDS fail lately. AWS support recommended modifying the alter table statement to look like this:

ALTER TABLE tbl ADD COLUMN abc varchar(123) AFTER zyx, ALGORITHM=COPY

The secret is to add

, ALGORITHM=COPY

to the end as a work around.

You could also failover the RDS instance https://dev.mysql.com/doc/refman/5.7/en/alter-table.html