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?

Is there possibly a lot of updates on the table while you're doing the ALTER TABLE? — Julie Pelletier
I also checked the SHOW PROCESS LIST which shows the DB is not busy — I.83
How about ulimit -a from the process that starts the MySQL daemon? Did you look at the memory usage as it was processing, before it failed? — Julie Pelletier
If you did not change any settings in my.cnf, you should write a bug with RDS, claiming that their default settings can lead to "out of memory". — Rick James

Cory Thorson Cory Thorson · Accepted Answer · 2017-09-07T16:21:45

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

AWS RDS out of memory error when adding column

2 Answers