mysql提示 The table ‘tmpxmldata’ is full
开发环境：
mysql: 5.5.8
os: win2003 SP2
内存：8G
问题：
我在存储过程中使用临时表:ENGINE=MEMORY,执行动态sql语句，但很奇怪传入的sql语句才1M就提示
The table ‘tmpxmldata’ is full
下面是我的参数设置：
“Variable_name” “Value”
“tmp_table_size” “805306368”
“max_heap_table_size” “536870912”
最好能够提供my.ini供我参考一下。
请问是什么原因？我该如何设置？谢谢！
show create table tmpxmldata看看还是不是memory引擎的
检查你的 max_heap_table_size 系统变量。
你是怎么判断 传入的sql语句才1M
楼主提供下插入的语句看看吧。
“另外我将这些sql语句保存为sql文件也就1M”
这句话有误，我举个反例
insert into a (texts) select repeat(‘a’,10000000);
这个会写入1000W个A，写入数据库的大小和这条SQL语句文本的大小完全不同了。
mysql> create table t(a varchar(8000)) engine=memory;
Query OK, 0 rows affected (0.14 sec)
mysql> show variables like ‘%heap%’;
+———————+———-+
| Variable_name | Value |
+———————+———-+
| max_heap_table_size | 16777216 |
+———————+———-+
1 row in set (0.00 sec)
mysql> insert into t select repreat(‘a’,8000);
ERROR 1305 (42000): FUNCTION db1.repreat does not exist
mysql> insert into t select repeat(‘a’,8000);
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into t select * from t;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into t select * from t;
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into t select * from t;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into t select * from t;
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> insert into t select * from t;
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> insert into t select * from t;
Query OK, 32 rows affected (0.00 sec)
Records: 32 Duplicates: 0 Warnings: 0
mysql> insert into t select * from t;
Query OK, 64 rows affected (0.00 sec)
Records: 64 Duplicates: 0 Warnings: 0
mysql> insert into t select * from t;
Query OK, 128 rows affected (0.00 sec)
Records: 128 Duplicates: 0 Warnings: 0
mysql> insert into t select * from t;
Query OK, 256 rows affected (0.01 sec)
Records: 256 Duplicates: 0 Warnings: 0
mysql> insert into t select * from t;
Query OK, 512 rows affected (0.01 sec)
Records: 512 Duplicates: 0 Warnings: 0
mysql> insert into t select * from t;
Query OK, 1024 rows affected (0.02 sec)
Records: 1024 Duplicates: 0 Warnings: 0
mysql> insert into t select * from t;
ERROR 1114 (HY000): The table ‘t’ is full
mysql> insert into t select * from t;
ERROR 1114 (HY000): The table ‘t’ is full
mysql> insert into t select * from t;
ERROR 1114 (HY000): The table ‘t’ is full
mysql> select count(*) from t;
+———-+
| count(*) |
+———-+
| 2064 |
+———-+
1 row in set (0.11 sec)
mysql> show table status like ‘t’;
+——+——–+———+————+——+—————-+————-+—————–+————–+———–+—————-+———-
