Skip navigation links

Additional languages
B.1.2.13 Can't create/write to file »
Section Navigation      [Toggle]

B.1.2.12. The table is full

The effective maximum table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits. The following table lists some examples of operating system file-size limits. This is only a rough guide and is not intended to be definitive. For the most up-to-date information, be sure to check the documentation specific to your operating system.

Operating System File-size Limit
Win32 w/ FAT/FAT32 2GB/4GB
Win32 w/ NTFS 2TB (possibly larger)
Linux 2.2-Intel 32-bit 2GB (LFS: 4GB)
Linux 2.4+ (using ext3 filesystem) 4TB
Solaris 9/10 16TB
MacOS X w/ HFS+ 2TB
NetWare w/NSS filesystem 8TB

Windows users, please note that FAT and VFAT (FAT32) are not considered suitable for production use with MySQL. Use NTFS instead.

On Linux 2.2, you can get MyISAM tables larger than 2GB in size by using the Large File Support (LFS) patch for the ext2 filesystem. Most current Linux distributions are based on kernel 2.4 or higher and include all the required LFS patches. On Linux 2.4, patches also exist for ReiserFS to get support for big files (up to 2TB). With JFS and XFS, petabyte and larger files are possible on Linux.

For a detailed overview about LFS in Linux, have a look at Andreas Jaeger's Large File Support in Linux page at http://www.suse.de/~aj/linux_lfs.html.

If you do encounter a full-table error, there are several reasons why it might have occurred:

  • The InnoDB storage engine maintains InnoDB tables within a tablespace that can be created from several files. This allows a table to exceed the maximum individual file size. The tablespace can include raw disk partitions, which allows extremely large tables. The maximum tablespace size is 64TB.

    If 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. See Section 13.2.7, “Adding and Removing InnoDB Data and Log Files”.

  • You are using MyISAM tables on an operating system that supports files only up to 2GB in size and you have hit this limit for the data file or index file.

  • You are using a MyISAM table and the space required for the table exceeds what is allowed by the internal pointer size. MyISAM creates tables to allow up to 4GB by default (256TB as of MySQL 5.0.6), but this limit can be changed up to the maximum allowable size of 65,536TB (2567 – 1 bytes).

    If you need a MyISAM table that is larger than the default limit and your operating system supports large files, the CREATE TABLE statement supports AVG_ROW_LENGTH and MAX_ROWS options. See Section 12.1.5, “CREATE TABLE Syntax”. The server uses these options to determine how large a table to allow.

    If the pointer size is too small for an existing table, you can change the options with ALTER TABLE to increase a table's maximum allowable size. See Section 12.1.2, “ALTER TABLE Syntax”. 

    ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;

    You have to specify AVG_ROW_LENGTH only for tables with BLOB or TEXT columns; in this case, MySQL can't optimize the space required based only on the number of rows.

    To change the default size limit for MyISAM tables, set the myisam_data_pointer_size, which sets the number of bytes used for internal row pointers. The value is used to set the pointer size for new tables if you do not specify the MAX_ROWS option. The value of myisam_data_pointer_size can be from 2 to 7. A value of 4 allows tables up to 4GB; a value of 6 allows tables up to 256TB.

    You can check the maximum data and index sizes by using this statement: 

    SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';

    You also can use myisamchk -dv /path/to/table-index-file. See Section 12.5.4, “SHOW Syntax”, or Section 4.6.3, “myisamchk — MyISAM Table-Maintenance Utility”.

    Other ways to work around file-size limits for MyISAM tables are as follows:

  • You are using the NDB storage engine, in which case you need to increase the values for the DataMemory and IndexMemory configuration parameters in your config.ini file. See Section 17.3.5.1, “Data Node Configuration Parameters”.

  • You are using the MEMORY (HEAP) storage engine; in this case you need to increase the value of the max_heap_table_size system variable. See Section 5.1.3, “System Variables”.


User Comments

Posted by Ignacio Gutierrez on November 2 2006 5:55pm[Delete] [Edit]

If you reach the limit of 2Gb in data, use Delete command to delete information and if you are using MyIsam Engine, don't forget to use Optimize Table, to reclaim unused space and reduce file sizes.

Posted by Dan Moore on May 22 2007 3:36pm[Delete] [Edit]

Good information here: http://jeremy.zawodny.com/blog/archives/000796.html

Posted by Josh Vickery on February 22 2008 12:10pm[Delete] [Edit]

If you get an error like The table '#sql-4e0b_462' is full when attempting to run an alter or optimize statement on an InnoDb table check your innodb_data_file_path setting. It is possible to set a Max on that file, in which case the InnoDb table can fill up even though you have plenty of diskspace and you will get the above error.

Since the error message is reported on a temporary table, I found this difficult to track down.

Add your own comment.