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?
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:
http://docs.amazonwebservices.com/AmazonRDS/latest/UserGuide/USER_ScalingStorage.html
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.
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 FilesYou 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
datadirALTER 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 !!!
