代码改变世界

解决mysql表已满的错误

2010-12-06 13:13  Loning  阅读(1514)  评论(0编辑  收藏  举报

最近自己的vps总是出现网站不能访问,一片空白的问题,在检查日志文件后发现php及nginx都没有出问题,于是分析了一下mysql的日志,的确是出错了,

101206 12:49:03 [ERROR] /usr/local/mysql/libexec/mysqld: The table 'cache' is full101206 12:49:06 [ERROR] /usr/local/mysql/libexec/mysqld: The table 'cache' is full101206 12:49:21 [ERROR] /usr/local/mysql/libexec/mysqld: The table 'cache' is full

一堆表满错误,于是google了下,以下是解决方法。

配置文件在 /etc/my.cnf

   After using MySQL memory engine table as middle cache table, we got this error when application inserting the data to the table. Seems there is some limits on the table. After searching on Google, we know that we need to change some default settings.

    The global system variable "max_heap_table_size" define the maximum size memeory table can reach. The default values is 16384 (Maybe it's OS dependant). So you can change it with the following steps.

    Run the following command to change it at system level.

set global max_heap_table_size=1048576000

    Then modify the MySQL configuration file, adding a new line at the end. So when next time database get restarted, the change can be permanent.

max_heap_table_size=1048576000

    Finally, you need to reconnect to MySQL, and rebuild all the tables.

ALTER TABLE ... ENGINE MEMORY;

    The last step is to let application reconnect to MySQL database, to take the change effective.