Bug #7941 documentation of SHOW TABLE STATUS; max_data_length

• Submitted: 16 Jan 2005 17:14
• Modified: 25 Feb 2005 18:20
• Reporter: Martin Friebe (Gold Quality Contributor) (OCA)
• Status: Closed
• Category: MySQL Server: Documentation
• Severity: S3 (Non-critical)
• Version: 4.1.9
• Assigned to: Paul DuBois
• CPU Architecture: Any

[16 Jan 2005 17:14] Martin Friebe

Description:
from the doumentation at http://dev.mysql.com/doc/mysql/en/SHOW_TABLE_STATUS.html

Max_data_length
    The maximum length of the data file. For fixed-row formats, this is the maximum number of rows in the table. For dynamic-row formats, this is the total number of data bytes that can be stored in the table, given the data pointer size used.

It states that fixed rows, should display the record number, instead of the byte length. The output I get suggest a different picture. (starting with a length of 5, to avoid conflicts with the minimum record set length of 4)

I use freebsd 4 and 5, but it should not matter, as long as a filesystem is used with bigfile support.

How to repeat:
create table len_v5 (a varchar(5)); insert into len_v5 values('12345');
create table len_v6 (a varchar(6)); insert into len_v6 values('123456');
create table len_5 (a char(5));  insert into len_5 values('12345');
create table len_6 (a char(6));  insert into len_6 values('123456');
create table len_7 (a char(7));  insert into len_7 values('1234567');

show table status from xxx like 'len%';
+--------+--------+---------+------------+------+----------------+-------------+-----------------+-
| Name   | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | .........
+--------+--------+---------+------------+------+----------------+-------------+-----------------+-
| len_5  | MyISAM |       9 | Fixed      |    1 |              6 |           6 |     25769803775 | 
| len_6  | MyISAM |       9 | Fixed      |    1 |              7 |           7 |     30064771071 | 
| len_7  | MyISAM |       9 | Fixed      |    1 |              8 |           8 |     34359738367 | 
| len_v5 | MyISAM |       9 | Dynamic    |    1 |             20 |          20 |      4294967295 | 
| len_v6 | MyISAM |       9 | Dynamic    |    1 |             20 |          20 |      4294967295 | 
+--------+--------+---------+------------+------+----------------+-------------+-----------------+-

# the max_data_length for the FIXED length tables is growing by 4gb for each byte in row_length, this indicates mysql shows the  byte length limitation

Suggested fix:
-

[16 Jan 2005 17:23] Martin Friebe

One more note:
avg_row_length for an empty table shows 0 (zero). I believe it should rather be NULL (for unknown), or for a fixed length table, it could also be the actual "will be" length.

[16 Jan 2005 17:33] Martin Friebe

the last note applies to MyIsam tables.
Heap tables do show the avg length for empty tables. So the behaviour is inconsistent between engines.

create table mem1 (a varchar(10)) ENGINE=HEAP; create table mem2 (a char(10)) ENGINE=HEAP;
create table isam1 (a varchar(10)) ENGINE=HEAP; create table isam2 (a char(10)) ENGINE=HEAP;

 show table status;
+------+--------+---------+------------+------+----------------+-------------+--
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length |  
+------+--------+---------+------------+------+----------------+-------------+--
| mem1 | HEAP   |       9 | Dynamic    |    0 |             11 |           0 |  
| mem2 | HEAP   |       9 | Fixed      |    0 |             11 |           0 |  
| isam1 | MyISAM |       9 | Dynamic    |    0 |              0 |           0 |  
| isam2 | MyISAM |       9 | Fixed      |    0 |              0 |           0 |  
+-------+--------+---------+------------+------+----------------+-------------+--

[25 Feb 2005 18:20] 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
product(s).

Additional info:

There are a couple of aspects to this report.

1) I've updated the SHOW TABLE status section
to indicate that Max_data_length is the max number
of bytes (without distinction between Fixed and
Dynamic row format).

2) The request for a change in Avg_row_length is
a request for a change in behavior. Martin, this can
be filed as a feature request, on the basis that the
current behavior is inconsistent as you note.

Thanks for the report.