Bug #20164 Table corruption
Submitted: 31 May 2006 10:32 Modified: 31 May 2006 11:50
Reporter: Paul O'Connor Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:mysql Ver 14.7 Distrib 4.1.12, for pc-l OS:Linux (ubuntu linux 2.6.12-10-386)
Assigned to: CPU Architecture:Any

[31 May 2006 10:32] Paul O'Connor
Description:
I had a problem with a table in a large database corrupting itself constantly.

The table is isam, 2,543,535 rows and was corrupting as soon as I had run "myisamchk -r" or "myisamchk -o" to repair it. The table is used as read only for users, and is only updated by myself. When being read, the table wass fine, but as soon as I had tried to insert data it collapsed again.

After hunting around for a good while, someone had found a solution to this problem:

ALTER TABLE <table_name> MAX_ROWS=1000000000 AVG_ROW_LENGTH=15000;

This stopped the corruption, but I need to know if this is an issue that has been fixed in subsequent releses. I have found nothing related to the issue in the buglist (possibly my oversight), so was wondering if it is something that has been fixed, or is yet unknown?

If you require more info, please ask and I will assist if i can.

Many thanks.

How to repeat:
Populate a large isam database with a couple of million rows, and it seems to happen (have had this happen on a couple of tables now and the only major similarity was that they were all large, though not large file sizes - MYI file for most affected atble was 960MB)

Suggested fix:
Not entirely sure of the problem, only have a (hackish) solution. a guess would be something to do with default settings being changed.
[31 May 2006 11:02] Tonci Grgin
Hi Paul. Thanks for your problem report.
Can you execute 'show table status like "your_table_name";' and post the result?
You are probably hitting the limit of default pointer size, which until MySQL 5.0.12 was 4bytes and now it's 6.
[31 May 2006 11:11] Paul O'Connor
+-----------------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+-----------------------------------------+---------+
| Name                        | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options                          | Comment |
+-----------------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+-----------------------------------------+---------+
| entity_regulator_permission | MyISAM |       9 | Dynamic    | 2543535 |            201 |   512936560 | 281474976710655 |    981867520 |         0 |              4 | 2006-05-30 16:44:14 | 2006-05-30 23:00:01 | 2006-05-30 16:46:04 | latin1_swedish_ci |     NULL | max_rows=100000000 avg_row_length=15000 |         |
+-----------------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+-----------------------------------------+---------+

is after repair (and working) and :

+-----------------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name                        | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment |
+-----------------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| entity_regulator_permission | MyISAM |       9 | Dynamic    | 3498427 |            215 |   754500624 |      4294967295 |   1278832640 |         0 |              1 | 2006-05-26 11:22:27 | 2006-05-26 11:42:35 | 2006-05-26 11:45:55 | latin1_swedish_ci |     NULL |                |         |
+-----------------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
1 row in set (0.04 sec)

is before repair on another server (pretty identical data set, although some small additions have been made to the repaired table)

hope this helps

Cheers
[31 May 2006 11:44] Tonci Grgin
Paul it is as I thought. This is not a bug nor it needs fixing. Default values were just not enough for you anymore. ALTER TABLE <table_name> MAX_ROWS=1000000000 AVG_ROW_LENGTH=15000 statement is just what you  needed to do. I can only add that AVG_ROW_LENGTH is set bit too high, since it's actually 20x bytes.
More info in manual:
http://dev.mysql.com/doc/refman/4.1/en/table-size.html
http://dev.mysql.com/doc/refman/4.1/en/create-table.html
[31 May 2006 11:50] Paul O'Connor
Thanks for the info, though my concern was that no warnings were administered, and the table just corrupted. If I had known this in advance, I may have been able to avoid the situation, but without knowledge of this issue, it was a little difficult to track down.

At least I know now!

Cheers again.