mysql 报错 ：The table xxx is full

############################################

现象：

用户执行的sql语句报错：
Cause: java.sql.SQLException: The table '/home/work/mysql_3306//tmp/#sql117f0c_db7113_a4' is full

 

原因：参数internal_tmp_mem_storage_engine是默认值TempTable，当临时表大小超过 temptable_max_ram+temptable_use_mmap=1G+1G=2GB的设置时，sql报错。

mysql> show variables like '%temptable_max%';
+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| temptable_max_mmap | 1073741824 |
| temptable_max_ram  | 1073741824 |
+--------------------+------------+
2 rows in set (0.00 sec)

mysql>

 

mysql> show variables like '%internal%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+
1 row in set (0.01 sec)

 

 

解决：修改参数internal_tmp_mem_storage_engine=MEMORY，当临时文件超过tmp_table_size=512MB时，使用磁盘文件，避免由于设定的内存不足而退出。

mysql> set global  internal_tmp_mem_storage_engine=memory;
Query OK, 0 rows affected (0.00 sec)

 

在/home/work/mysql_3306/conf/my.cnf配置文件中添加

[mysqld]

internal_tmp_mem_storage_engine=memory