How to solve MySQL “The table is full” error 1114 with Amazon RDS? 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?
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

SHOW VARIABLES LIKE 'tmpdir';


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,



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:


Mar 15, 2012 : Why do MySQL tables crash? How do I prevent it?
Apr 25, 2012 : Very Large Log Files


WHAT CAN YOU DO?

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 !!!