5

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
CodeBird
  • 3,883
  • 2
  • 20
  • 35
  • What does the following query ouput SHOW TABLE STATUS LIKE 'XXX'; Take a look at data_length and max_data_length Also I found another topic on Stackoverflow, might be useful http://stackoverflow.com/questions/730579/error-1114-hy000-the-table-is-full – Dylan Feb 18 '14 at 10:15
  • @Dylan I update my question with the query you asked for. And already checked the post you suggested. I am using innodb_file_per_table=1 already as for the 512M whatever I think that's old info – CodeBird Feb 18 '14 at 10:23
  • Possible duplicate of [ERROR 1114 (HY000): The table is full](https://stackoverflow.com/questions/730579/error-1114-hy000-the-table-is-full) – Uberhumus Apr 01 '19 at 15:49

1 Answers1

6

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
CodeBird
  • 3,883
  • 2
  • 20
  • 35
  • 5
    Better to add it to configuration file. Add `tmpdir = /home/mysql/tmp` to section `[mysqld]` in `/etc/my.cnf` and restart MySQL – Amil Waduwawara Jul 12 '16 at 04:49