| Bug #55202 | Creating a MyISAM table with MAX_ROWS always limits MAX_ROWS to 4294967295 | ||
|---|---|---|---|
| Submitted: | 13 Jul 2010 5:17 | Modified: | 13 Jul 2010 18:29 |
| Reporter: | Gideon Greenspan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S2 (Serious) |
| Version: | 5.1.48 | OS: | Linux |
| Assigned to: | Paul DuBois | CPU Architecture: | Any |
| Tags: | MAX_ROWS, myisam | ||
[13 Jul 2010 5:23]
Valeriy Kravchuk
This is easy to verify on any MySQL server version. But I think that this formal(!) limit for the MAX_ROWS value should just be described in http://dev.mysql.com/doc/refman/5.1/en/create-table.html.
[13 Jul 2010 18:29]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.
Description: If you create a MyISAM table with an explicit MAX_ROWS setting that is over 4294967295, the table is created with MAX_ROWS=4294967295, and it is also not possible to add more than that many rows. If however you leave out the MAX_ROWS setting, then the maximum number of rows is determined by the global variable myisam_data_pointer_size (in a MyISAM table with fixed-width rows.) I would have expected that you could provide a MAX_ROWS value which is higher than 2^32, and the table pointer size would be set accordingly. How to repeat: CREATE TABLE big (col int) MAX_ROWS=1099511627775; SHOW TABLE STATUS LIKE 'big'\G Outputs: *************************** 1. row *************************** Name: big Engine: MyISAM Version: 10 Row_format: Fixed Rows: 0 Avg_row_length: 0 Data_length: 0 Max_data_length: 21474836479 [snip] Create_options: max_rows=4294967295 Similar story with: SHOW CREATE TABLE big\G Suggested fix: The workaround is not to provide the MAX_ROWS parameter, but instead to use something like the code below to set the table pointer size before creation. SET GLOBAL myisam_data_pointer_size=5; In a MyISAM table with fixed width rows, the maximum number of rows will then be equal to (2^(8* myisam_data_pointer_size))-1.