A.2.11 The table is full Error

There are a couple of different cases when you can get this error:

• You are using an older MySQL version (before 3.23.0) when an in-memory temporary table becomes larger than tmp_table_size bytes. To avoid this problem, you can use the -O tmp_table_size=# option to make mysqld increase the temporary table size or use the SQL option BIG_TABLES before you issue the problematic query. See section 5.5.6 SET Syntax. You can also start mysqld with the --big-tables option. This is exactly the same as using BIG_TABLES for all queries. In MySQL Version 3.23, in-memory temporary tables will automatically be converted to a disk-based MyISAM table after the table size gets bigger than tmp_table_size.
• You are using InnoDB tables and run out of room in the InnoDB tablespace. In this case the solution is to extend the InnoDB tablespace.
• You are using ISAM or MyISAM tables on an OS that only supports files of 2G in size and you have hit this limit for the datafile or index file.
• You are using MyISAM tables and the needed data or index size is bigger than what MySQL has allocated pointers for. (If you don't specify MAX_ROWS to CREATE TABLE MySQL will only allocate pointers to hold 4G of data). You can check the maximum data/index sizes by doing

  SHOW TABLE STATUS FROM database LIKE 'table_name';
  

  or using myisamchk -dv database/table_name. If this is the problem, you can fix it by doing something like:

  ALTER TABLE table_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;
  

  You only have to specify AVG_ROW_LENGTH for tables with BLOB/TEXT fields as in this case MySQL can't optimise the space required based only on the number of rows.

User Comments

I did an update on 1,400,000 records using two non-temporary InnoDB tables within a longer transaction (which also uses temporary tables):

"update import_clearing t1 inner join import_sendung_neu t2 on t1.paketnummer=t2.paketnummer
set t1.send_id=t2.send_id;"

I got a MySQL 4.0.12 Error 1114: "The table 't1' is full".

"SET SESSION BIG_TABLES = 1;" did not solve the problem. I have 14GB of free InnoDB tablespace. Seems there are other non-documented reasons which make this error raise.

The update-problem shall be fixed in 4.0.13

The above worked fine for me but you have to look on your disk space - mySQL ist creating a temporary database for this command with the same size as the original - my system ran out of disk space the first time i used the command.

I have the same problem with MySql 4.0.12. I am running an update query in a table of around 1.300.000 records and it keeps giving the same error message. I tries all of the above methods but non worked. Apparently, something is wrong.

Ditto, on 4.0.10. I'm suspecting the temporary tables are becoming too large

I'm having same problem with MySql 4.0.12, on a MyISAM table under windows. Happens while trying to update large number of rows. Updating a smaller number of rows at a time, avoided the message.
David

Add your own comment.