ERROR 1114 (HY000): The table 'XXX' is full

Question

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

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.

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

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.

EDIT: 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

On the working server:

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

Answer 1

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.

first I created /home/mysql/tmp directory

  mkdir /home/mysql/tmp

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

  chown mysql:mysql /home/mysql/tmp

then I stopped mysql server

  service mysql stop

then started it again with the following command:

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