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';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+----------
---------+-------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_ti
| Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+----------
---------+-------------+------------+-------------------+----------+----------------+---------+
| t | MEMORY | 10 | Fixed | 2064 | 8003 | 16793728 | 16766285 | 0 | 0 | NULL | 2012-07-2
13:57:34 | NULL | NULL | latin1_swedish_ci | NULL | | |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+----------
---------+-------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.04 sec)
Data_length = 16793728 >16777216
[解决办法]
检查
tmp_table_size、max_heap_table_size参数
修改大一点试试
