CODEWITHSUNDEEP
mysqlamazon-web-servicesstored-proceduresamazon-rdstemp-tables
Manish Sapkal
Manish Sapkal

Reputation: 6251

Amazon RDS : ER_RECORD_FILE_FULL: The table is full occured while creating temporary table in stored procedure

I have hosted my database on Amazon RDS. It is db.r3.xlarge instance and having 60Gb magnetic storage (40gb free). Recently I have written one stored procedure which gets user input as MEDIUMTEXT comma separated string, parsed it and insert values into temporary table. Here is the code: 

CREATE DEFINER=`ntadmin`@`%` PROCEDURE `sp_test`(
    IN  cStr      MEDIUMTEXT,   -- 16777215  --LONGTEXT 4294967295
)   DETERMINISTIC
BEGIN

    DROP TEMPORARY TABLE IF EXISTS temp_phone;
    CREATE TEMPORARY TABLE temp_phone (phone VARCHAR(20) NOT NULL) ENGINE = MEMORY;

    SET @sql = CONCAT(" INSERT IGNORE INTO tmp_phone(phone) VALUES ", cStr);
    PREPARE stmt FROM @sql; 
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

and this stored procedure call like this

CALL sp_test("'1234','4567','897458','5784585','453434'");
Note: parameter (cStr) may be come vary vary long string.

It's working fine on my development instance and also fine in major case on production instance. but some this it's through error "ER_RECORD_FILE_FULL: The table 'tmp_phone' is full.

As I mentioned earlier, I have 40gb of free space. I have set VARIABLES in parameter group is as follows :

innodb_file_per_table = ON 
innodb_data_file_path = ibdata1:12M:autoextend
max_heap_table_size   = 128000
tmp_table_size        = 128000

Should I have to increase max_heap_table_size because my maximum possible value should come 16mb in size.

Upvotes: 0

Views: 2090

Answers (1)

Rick James
Rick James

Reputation: 142560

Look at the SQL you are generating:

INSERT IGNORE INTO tmp_phone(phone)
    VALUES '1234','4567','897458','5784585','453434'

Now look at the correct syntax:

INSERT IGNORE INTO tmp_phone(phone)
    VALUES ('1234'),('4567'),('897458'),('5784585'),('453434')

Next look at the space requirements... max_heap_table_size limits the size of any subsequently CREATEd MEMORY table. The 128K will limit you to only a few thousand rows. It sounds from MEDIUMTEXT that you may need a lot more.

It is reasonable to set max_heap_table_size and tmp_table_size (which is not relevent here) to about 1% of RAM (300M in your case); certainly 16M of MEDIUMTEXT (or a bit more for overhead).

Upvotes: 2

Related Questions