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
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:
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,
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;
- The temp table's location is in the
- Performed the
- 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
Give it a Try !!!