| Bug #2948 | Unpacked table has decreased max data length | ||
|---|---|---|---|
| Submitted: | 24 Feb 2004 20:34 | Modified: | 25 Feb 2004 2:42 |
| Reporter: | Jeffrey Jones | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | Ver 12.22 Distrib 4.0.16 | OS: | Linux (redhat linux) |
| Assigned to: | Sergei Golubchik | CPU Architecture: | Any |
[25 Feb 2004 2:42]
Sergei Golubchik
most probably yes. As myisampack tries to make a table as small as possible, it also tries to make an offset to a row as small as possible (as it is used in the table file for various purposes). Thus if you have max_data_length=4294967295 (that is 4-byte offset) but actual datafile length is smaller, myisampack may conclude that, e.g. 3 bytes for an offset is enough, and reduce max_data_length to save a byte on each offset.
Description: We load data, and pack the table to conserve on space. When new data is to be loaded, we unpack the appropriate table and load the data. The load is actually performed through an insert into SQL statement. We were receiving the error ERROR 1114 at line 2: The table 't_f_activity_17_200401' is full Below is a show table status, unpack and another show table status output. Note that the max_data_length of the packed table is 4294967295, but when unpacked, the max_data_length changes to 2490367. Name: t_f_activity_17_200401 Type: MyISAM Row_format: Compressed Rows: 46964 Avg_row_length: 14 Data_length: 665400 Max_data_length: 4294967295 Index_length: 2359296 Data_free: 0 Auto_increment: NULL Create_time: 2004-02-24 10:58:41 Update_time: 2004-02-24 10:58:42 Check_time: 2004-02-24 10:58:43 Create_options: max_rows=200000000 avg_row_length=50 Comment: Tue Feb 24 10:59:01 CST 2004 Unpacking data for t_f_activity_17_200401 *************************** 1. row *************************** Name: t_f_activity_17_200401 Type: MyISAM Row_format: Fixed Rows: 46964 Avg_row_length: 38 Data_length: 1784632 Max_data_length: 2490367 Index_length: 2013184 Data_free: 0 Auto_increment: NULL Create_time: 2004-02-24 10:58:41 Update_time: 2004-02-24 10:59:01 Check_time: 2004-02-24 10:59:01 Create_options: max_rows=200000000 avg_row_length=50 Comment: How to repeat: Create a table load data pack it unpack it Suggested fix: The way that I fixed this was before loading more data I execute an ALTER TABLE <table> MAX_ROWS=200000000 AVG_ROW_LENGTH=50; command. I am just trying to understand if this is the correct operation of the unpack.