MyISAM storage engine of MySQL fixes and modifies the maximum file size _ MySQL

MySQL MyISAM storage engine repairs and modifies the maximum file size bitsCN.com

Two days ago, there was a major fault in the production database about the MySQL storage engine.

This fault involves the MySQL 4.1.20 version of MyISAM storage engine, a table of over million.

Fault symptom:

There is no problem when performing simple SELECT queries, but an error is reported when performing connection queries or other complex queries: the specific error code is forgotten due to time issues. Errors recorded in system logs include:

We can guess from the error message that the storage engine has a problem.

Go to the corresponding Database Directory and check the data file size, which is 4 GB. this is not surprising.

By default, the maximum file size of a table created in MySQL 4 database is 4 GB, which means that the table is full and data cannot be inserted. MAX_ROWS = 1000000000 AVG_ROW_LENGTH = 15000 when creating a table.

Execute the myisamchk-dvv table name to display the attribute information:

Then, change the maximum file size of the table.

Alter table table_name MAX_ROWS = 1000000000 AVG_ROW_LENGTH = 15000;

It takes about several hours to execute this modification.

Check the modification result:

Where: Datafile length 4095052564 = 4G

Max datafile length 281474976710654 is much larger than G.

MySQL version 5 supports Max datafile length 281474976710654 by default. you do not need to add the MAX_ROWS = 1000000000 AVG_ROW_LENGTH = 15000 parameter when creating a table.

BitsCN.com