Avoid "table full error" appearing in MySQL

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.