MySQL 8.0 报错ERROR 1114 (HY000): The table 'sbtest1' is full

mysql> alter table sbtest1 drop column cityname2;

ERROR1114(HY000): The table'sbtest1'is full

mysql>

做sysbench压力测试的时候报错。原因：

得知是由于内存表的大小超过了规定的范围。经过查看二者值默认均是16M，

需要设置tmp_table_size 大于等于max_heap_table_size。

+---------------------+-----------+

| Variable_name | Value |

+---------------------+-----------+

| max_heap_table_size | 16777216 |

| tmp_table_size | 16777216 |

+---------------------+-----------+

2 rows in set (0.06 sec)

mysql> set max_heap_table_size=167772160;

mysql> set tmp_table_size=167772160;

mysql> show variables like '%table_size%';

+---------------------+----------+

| Variable_name |Value|

+---------------------+----------+

|max_heap_table_size| 16777216 0|

| tmp_table_size |167772160|

+---------------------+----------+

2 rowsinset (0.00 sec)

而二者皆不支持动态修改，修改了不生效，需要写入my.cnf配置文件重启mysql生效。

[mysqld]

max_heap_table_size =32M

tmp_table_size =64M

# /etc/init.d/mysql restart

Shutting down MySQL.. SUCCESS!

Starting MySQL.. SUCCESS!

mysql> show variables like '%table_size%';

+---------------------+----------+

|Variable_name| Value |

+---------------------+----------+

| max_heap_table_size |33554432|

|tmp_table_size| 67108864 |

+---------------------+----------+

2rowsinset (0.01sec)

可以看重启之后修改的参数生效，然后执行删除字段的操作就可以了。