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:
None 
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

[24 Feb 2004 20:34] Jeffrey Jones
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.
[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.