I am trying to add a new column in a table and the query giving me this error 1114.

My RDS instance size is small. 1.7 GB memory, 1 ECU (1 virtual core with 1 ECU) Table i am trying to modify is having 5 Million rows and 7GB in size. Disk size of instance is 15GB (40% free).

Its critical time for us as we are stuck, AWS RDS not allowing access to all parameters of mysql configurations. How do i get rid of this problem?

  • what is the storage engine?? If InnoDB check innodb_data_file_path variable once. and look is it auto extend and innodb file per table is on or off.....
    – Vinay
    Dec 17, 2012 at 16:44
  • This is a vital question for the MySQL RDS Community. +1 !!! Dec 17, 2012 at 19:32

2 Answers 2


If you only have 40% of 15 GB = 6 GB free, you aren't going to be able to have two copies of a 7 GB table on the instance at the same time, whether you use ALTER TABLE (which usually creates an entire copy of the table and then replaces the existing table with it, as RolandoMySQLDBA explained) or create another table and insert the data.

It sounds like your instance doesn't have enough storage to do either.

You should be able to increase the available storage on a running instance from the management console by selecting the instance, choosing "Modify," changing the amount of storage allocated, and clicking "ok." See:

enter image description here


I don't know how RDS handles this in the background, so I don't know if your database will become unavailable for a short time during the change.

This operation will warn you about potential performance degradation while the instance is being modified, but you should find that the instance remains available and accessible and that within a few minutes the operation is complete without any disruption of your instance's availability.

  • I increased storage to 25Gb and its working now. There was not enough space for temp files. Thanks Michael. Dec 19, 2012 at 11:18
  • @RuchitPatel Did you have downtime while increasing your disk space?
    – hafichuk
    Sep 13, 2013 at 18:25
  • @hafichuk It didn't took much time for me, took around a minute. During that i didn't check if db was down or not. Sep 24, 2013 at 12:44

Here is the Problem: Whenever you run ALTER TABLE, there must be a temp table used. The temp table is not being written to your data folder. It is most likely written to /rdsdbdata/tmp. Please this query


This will give you an idea where that folder is. Again, by default, it is /rdsdbdata/tmp.

Why would that halt things for mysqld? Blame it on MyISAM. Why MyISAM? Here is something to consider: According to MySQL 5.0 Certification Study Guide,

enter image description here

bulletpoint #11 says the following on Pages 408,409 Section 29.2:

If you run out of disk space while adding rows to a MyISAM table, no error occurs. The server suspends the operation until space becomes available, and then completes the operation.

When you run out of disk space, do not just shutdown or kill mysql. The count of the open file handles in any currently used MyISAM will not have been cleared. Thus, the MyISAM table is marked crashed. If you can free up disk space in the data volume with mysqld still running, mysqld will soldier on once disk space is made available. Please note that mysqld itself did not crash. It simply enters a state of suspended animation until free space materializes.

In your case, just kill the ALTER TABLE. The temp table, which uses MyISAM, simply locked up the disk space of /rdsdbdata/tmp. Killing the ALTER TABLE will get rid of the temp table and free up mysqld to continue with life as usual.

I have explained this in my past posts:


You may have to perform the ALTER TABLE yourself in manual stages.

For example, let's suppose you have this table:

CREATE TABLE ruchit_table
    id int not null auto_increment,
    field1 INT NOT NULL,
    field2 VARCHAR(20),
    primary key (id)

and you want to run ALTER TABLE ADD COLUMN field3 CHAR(5);

Here are the steps to perform this inside your datadir:

CREATE TABLE ruchit_table_new LIKE ruchit_table;
ALTER TABLE ruchit_table_new ADD COLUMN field3 CHAR(5);
INSERT INTO ruchit_table_new SELECT id,field1,field2 FROM ruchit_table;
ALTER TABLE ruchit_table RENAME ruchit_table_old;
ALTER TABLE ruchit_table_new RENAME ruchit_table;
DROP TABLE ruchit_table_old;

Note that

  • The temp table's location is in the datadir
  • Performed the ALTER TABLE
    • Added the new column to an empty temp table
    • Loaded the temp table
    • Got rid of the old table

These are things mysqld does internally with ALTER TABLE. These steps simply emulate this and does this emulation in the datadir.

Give it a Try !!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.