错误1114(HY000):表XXX已满 [英] ERROR 1114 (HY000): The table 'XXX' is full

问题描述

我知道之前曾问过这个问题，但似乎无济于事.

I know this question was asked before, but nothing seems to help.

2-3天前，我遇到了这个问题，向服务器寻求支持，他们告诉我，我必须将mysql从/var移到/home，这是我所有磁盘空间所在的位置，这就是我所做的.然后我运行了插入操作，一切正常，直到今天当我完成数据插入操作时，我想向表中添加索引，两分钟后运行ALTER查询时，我又得到了这个错误.

I had this issue 2 - 3 days ago, asked server support, and they told me that I have to move mysql from /var to /home where all my disk space is, and that's what I did. Then I ran my inserts and everything was going fine, until today when I finished inserting my data, and I want to add indexes to my table, when I run the ALTER query 2 minutes later I get this error back.

我将innodb_file_per_table = 1放在my.cnf中并重新启动了mysql，但这没有帮助.

I've put innodb_file_per_table=1 in my.cnf and restarted mysql but it didn't help.

当前，我们在另一台服务器上运行，所有数据都很好，并且mysql在大约250GB的数据库表上运行良好，我在该服务器上进行了检查以比较2台服务器的设置，似乎没有什么不同我.

Currently we're running on another server, where all the data is good, and mysql is running fine with a DB table of about 250GB, I checked on that server to compare the settings of the 2 servers, nothing seems different to me.

显示表状态，例如"whois_main"

SHOW TABLE STATUS LIKE 'whois_main'

Name        Engine  Version Row_format  Rows        Avg_row_length  Data_length     Max_data_length     Index_length    Data_free   Auto_increment  Create_time             Update_time     Check_time  Collation         Checksum  Create_options  Comment     
whois_main  InnoDB  10      Compact     140859771   694             97812217856     0                   6618562560      6291456     191781518       2014-02-13 16:45:16     NULL            NULL        utf8_general_ci   NULL

在工作服务器上:

Name        Engine  Version Row_format  Rows        Avg_row_length  Data_length     Max_data_length     Index_length    Data_free   Auto_increment  Create_time             Update_time     Check_time  Collation         Checksum  Create_options  Comment     
whois_main  InnoDB  10      Compact     140472243   694             97812217856     0                   6618562560      6291456     191781518       2013-11-19 15:39:38     NULL            NULL        utf8_general_ci   NULL

推荐答案

问题来自mysql，使用/var/tmp作为tmpdir，它不足以复制表并创建所需的索引.我通过将tmpdir位置更改为包含更多空间的位置来修复它.

Issue was from mysql using /var/tmp as tmpdir which is not big enough to copy the table and create the needed indexes. I fixed it by changing tmpdir location to a place that contains more space.

首先我创建了/home/mysql/tmp目录

first I created /home/mysql/tmp directory

  mkdir /home/mysql/tmp

然后我将该目录的所有者更改为mysql:mysql

then I changed the owner of that directory to mysql:mysql

  chown mysql:mysql /home/mysql/tmp

然后我停止了mysql服务器

then I stopped mysql server

  service mysql stop

然后使用以下命令再次启动它:

then started it again with the following command:

  service mysql start --tmpdir=/home/mysql/tmp

这篇关于错误1114(HY000):表XXX已满的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！