mysql - 亚马逊 RDS : ER_RECORD_FILE_FULL: The table is full occured while creating temporary table in stored procedure

Question

我已在 Amazon RDS 上托管我的数据库。它是 db.r3.xlarge 实例，具有 60GB 磁存储(40GB 可用)。最近，我编写了一个存储过程，它以 MEDIUMTEXT 逗号分隔字符串的形式获取用户输入，对其进行解析并将值插入到临时表中。这是代码:

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

这个存储过程调用如下

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

它在我的开发实例上运行良好，在生产实例的主要情况下也运行良好。但有些是由于错误“ER_RECORD_FILE_FULL:表‘tmp_phone’已满。

正如我之前提到的，我有 40GB 的可用空间。我在参数组中设置了VARIABLES，如下:

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

我是否应该增加 max_heap_table_size 因为我的最大可能值应该是 16mb。

Answer 1

查看您正在生成的 SQL:

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

现在看看正确的语法:

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

接下来看看空间要求... max_heap_table_size 限制任何后续 CREATEd MEMORY 表的大小。 128K 会将您限制为只有几千行。 MEDIUMTEXT 听起来您可能还需要更多。

将 max_heap_table_size 和 tmp_table_size (此处不相关)设置为 RAM 的 1% 左右(在您的情况下为 300M)是合理的；当然是 16M 的 MEDIUMTEXT(或者更多一点的开销)。