Reputation: 6251
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
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