Skip navigation links
B.1.2.12 Can't create/write to file »
Section Navigation      [Toggle]

B.1.2.11. The table is full

The maximum effective 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:

  • You are using a MySQL server older than 3.23 and an in-memory temporary table becomes larger than tmp_table_size bytes. To avoid this problem, you can use the --tmp_table_size=val option to make mysqld increase the temporary table size or use the SQL option SQL_BIG_TABLES before you issue the problematic query. See 項12.5.3. 「SET 構文」.

    You can also start mysqld with the --big-tables option. This is exactly the same as using SQL_BIG_TABLES for all queries.

    As of MySQL 3.23, this problem should not occur. If an in-memory temporary table becomes larger than tmp_table_size, the server automatically converts it to a disk-based MyISAM table.

  • 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 項13.5.7. 「InnoDB データとログ ファイルの追加と削除」.

  • 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 256GB by default, 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 項12.1.8. 「CREATE TABLE 構文」. 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 項12.1.2. 「ALTER TABLE 構文」. 

    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 項12.5.4. 「SHOW 構文」, or 項7.4. 「myisamchk — MyISAM テーブル メンテナンス ユーティリティ」.

    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 項14.4.5.1. 「データノードの設定パラメータ」.

  • 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 項4.2.3. 「システム変数」.