Avoid "table full error" appearing in MySQL

There are several ways in which a table full error occurs:

• You are using a MySQL server below version 3.23, and the "in-memory" temporary table exceeds the tmp_table_size byte. To avoid this problem, use the "-O tmp_table_size=val" option to mysqld increase the size of the temporary table, or use the SQL option Sql_big_tables before the problematic query is issued.

You can also use the--big-tables option to start the mysqld. It is exactly the same as using sql_big_tables for all queries.

The problem should no longer occur since MySQL 3.23. If an in-memory temporary table exceeds tmp_table_size, the server automatically converts it to a disk-based myisam table.

• You are using the InnoDB table and are beyond the InnoDB table space. In this case, the workaround is to increase the InnoDB tablespace.

• You are using a ISAM or MyISAM table on an operating system that supports only 2GB files, and the data file or index file reaches that limit.

• You are using the MyISAM table, and the table requires more space than the internal pointer allows. If you do not specify a max_rows table when you create the table, MySQL uses the myisam_data_pointer_size system variable. The default value is 6 bytes, which is sufficient to hold 65536TB of data.

With this statement, you can check the maximum data/index size:

SHOW TABLE STATUS FROM database LIKE 'tbl_name';
也可以使用myisamchk -dv /path/to/table-index-file。

If the pointer size is too small, use ALTER TABLE to correct the problem:

ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;

You should specify avg_row_length only for tables that have a BLOB or text column. In this case, MySQL cannot optimize the space needed only based on the number of rows.