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:
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?
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
to the end as a work around.
You could also failover the RDS instance