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: | |
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
[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.