mysql提示 The table 'tmpxmldata' is full

[yyj135]

开发环境：
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供我参考一下。

请问是什么原因？我该如何设置？谢谢！

[rucypli]

show create table tmpxmldata看看还是不是memory引擎的

[ACMAIN_CHM]

检查你的 max_heap_table_size 系统变量。

[yyj135]

TO rucypli:是memory引擎
TO ACMAIN_CHM: max_heap_table_size 536870912

[rucypli]

你是怎么判断 传入的sql语句才1M

[珠海-天堂]

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

[WWWWA]

检查
tmp_table_size、max_heap_table_size参数

修改大一点试试

[yyj135]

TO:rucypli
用length函数就可以，另外我将这些sql语句保存为sql文件也就1M

[珠海-天堂]

楼主提供下插入的语句看看吧。

“另外我将这些sql语句保存为sql文件也就1M”
这句话有误，我举个反例

insert into a (texts) select repeat('a',10000000);

这个会写入1000W个A，写入数据库的大小和这条SQL语句文本的大小完全不同了。

[yyj135]

TO zuoxingyu2012:
sql语句太长了，贴不了。不过大概格式是这样的：

-- 创建临时表
 DROP TABLE IF EXISTS tmpXmldata;
      CREATE TEMPORARY TABLE tmpXmldata (
      RowNum  INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
      XmlTagId INT ,
      XmlDataId_C VARCHAR(255) NOT NULL ,  
      TagValue VARCHAR(10000),
      ContentWhUrl VARCHAR(2000),  
      -- DisplayName VARCHAR(1024),
      ParentId_C VARCHAR(255) NOT NULL,
      LinkDocId VARCHAR(20),
      INDEX idx_tmpxmldata_XmlDataId_C (XmlDataId_C),
      INDEX idx_tmpxmldata_ParentId_C (ParentId_C),
      INDEX idx_tmpXmldata_xmltagid (xmltagid)
     ) ENGINE=MEMORY ;
INSERT INTO tmpXmldata (
  XmlDataId_C,
  XmlTagId,
  TagValue,
  ContentWhUrl,
  ParentId_C,
  LinkDocId
) 
VALUES
  (
    '194301889',
    '750001000',
    '',
    '',
    '0',
    ''
  ),
  (
    'inputid194301890',
    '750002000',
    '2012-07-26T13:50:41',
    '',
    '194301889',
    ''
  )

后面就是很长的sql语句。