Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 '12 at 16:44
    
This is a vital question for the MySQL RDS Community. +1 !!! –  RolandoMySQLDBA Dec 17 '12 at 19:32

2 Answers 2

up vote 0 down vote accepted

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."

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.

share|improve this answer
    
I increased storage to 25Gb and its working now. There was not enough space for temp files. Thanks Michael. –  Ruchit Patel Dec 19 '12 at 11:18
    
@RuchitPatel Did you have downtime while increasing your disk space? –  hafichuk Sep 13 '13 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. –  Ruchit Patel Sep 24 '13 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

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,

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:

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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