mysql揭示 The table ‘tmpxmldata’ is full

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’;
+——+——–+———+————+——+—————-+————-+—————–+————–+———–+—————-+———-

喜欢 (0)赏

[搞代码]

分享 (0)