Bug #29203 archive tables have weird values in show table status
Submitted: 19 Jun 2007 9:34 Modified: 18 Dec 2009 14:54
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Archive storage engine Severity:S3 (Non-critical)
Version:5.0.44, 5.0, 5.1, next bzr OS:Any (MS Windows, Linux)
Assigned to: Sergey Vojtovich CPU Architecture:Any

[19 Jun 2007 9:34] Shane Bester
Description:
doing some simple inserts on an archive engine, I noticed the show table status is obviously wrong:

mysql> show table status\G
*************************** 1. row *********************
           Name: t1
         Engine: ARCHIVE
        Version: 10
     Row_format: Compressed
           Rows: 140586
 Avg_row_length: 4109
    Data_length: 9223372036854775807
Max_data_length: 577667874
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2007-06-19 10:16:44
    Update_time: 2007-06-19 11:21:06
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: max_rows=200000
        Comment:
1 row in set (0.05 sec)

Note Max_data_length, Data_length, Avg_row_length appear wrong.

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|   141563 |
+----------+
1 row in set (3 min 47.67 sec)

Rows isn't exact either.

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` longblob
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 MAX_ROWS=200000
1 row in set (0.05 sec)

How to repeat:
will upload proper testcase later.

I populated some large rows using insert into t1 set a=load_file(<some 54mb file>).

Then, populated up to just over 2gig using smaller inserts.

Suggested fix:
.
[19 Jun 2007 10:39] MySQL Verification Team
Hmm. Rows may have been fine afterall.  The topic of this bug report is in fact the huge value for Data_length
[28 Oct 2008 5:33] MySQL Verification Team
Seems to also be affecting 5.0.41 version.
[15 May 2009 5:05] Tetsuro Ikeda
Hi 

I'm a MySQL trainer in Japan, from partner company, Sumisho.
I found this problem in MySQL performance training, which has an exercise like following:

- mysql-5.1.34
- linux tarball x86_64 glibc23

mysql> alter table t1 engine = myisam;
Query OK, 263424 rows affected (0.98 sec)
Records: 263424  Duplicates: 0  Warnings: 0

mysql> show table status\G
*************************** 1. row ***************************
           Name: t1
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 263424
 Avg_row_length: 40                   <------- see here
    Data_length: 10536960
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2009-05-15 13:33:56
    Update_time: 2009-05-15 13:33:57
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> alter table t1 engine = innodb;
Query OK, 263424 rows affected (4.43 sec)
Records: 263424  Duplicates: 0  Warnings: 0

mysql> show table status\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 263649
 Avg_row_length: 65                   <------- see here
    Data_length: 17350656
Max_data_length: 0
   Index_length: 0
      Data_free: 11534336
 Auto_increment: NULL
    Create_time: 2009-05-15 13:37:28
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> alter table t1 engine = archive;
Query OK, 263424 rows affected (3.43 sec)
Records: 263424  Duplicates: 0  Warnings: 0

mysql> show table status\G
*************************** 1. row ***************************
           Name: t1
         Engine: ARCHIVE
        Version: 10
     Row_format: Compressed
           Rows: 263424
 Avg_row_length: 4198                   <------- see here
    Data_length: 5260187
Max_data_length: 1105853952
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2009-05-15 13:37:42
    Update_time: 2009-05-15 13:37:42
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

Average row length of MyISAM is 40, InnoDB is 67, but Archive is 4198.
[15 May 2009 5:08] Tetsuro Ikeda
1105853952 / 263424 = 4198

Archive engine seems to calcurate like following:

Avg_row_length = Max_data_length / Rows

This should be:

Avg_row_length = Data_length / Rows
[2 Sep 2009 8:36] Sveta Smirnova
Thank you for the report.

Verified as described.

Test case:

--source include/have_innodb.inc

CREATE TABLE `t1` (
  `a` longblob
) ENGINE=ARCHIVE;
--vertical_results
show table status like 't1';
select avg(length(a)) from t1;

insert into t1 values(repeat('foobar',100000));
show table status like 't1';
select avg(length(a)) from t1;

insert into t1 values(repeat('foobar',10000));
insert into t1 values(repeat('foobar',10000));
insert into t1 values(repeat('foobar',10000));
insert into t1 values(repeat('foobar',10000));

show table status like 't1';
select avg(length(a)) from t1;

alter table t1 engine=innodb;
show table status like 't1';
select avg(length(a)) from t1;

alter table t1 engine=myisam;
show table status like 't1';
select avg(length(a)) from t1;

After INSERT ARCHIVE shows:

5.0:

insert into t1 values(repeat('foobar',100000));
show table status like 't1';
Name    t1
Engine  ARCHIVE
Version 10
Row_format      Compressed
Rows    1
Avg_row_length  4109
Data_length     22
Max_data_length 4109
Index_length    0
Data_free       0
Auto_increment  NULL
Create_time     2009-09-02 11:34:26
Update_time     2009-09-02 11:34:26
Check_time      NULL
Collation       latin1_swedish_ci
Checksum        NULL
Create_options  
Comment 
select avg(length(a)) from t1;
avg(length(a))  600000.0000

Strange why Data_length is smaller than Avg_row_length

In 5.1 and next:

show table status like 't1';
Name    t1
Engine  ARCHIVE
Version 10
Row_format      Compressed
Rows    1
Avg_row_length  4109
Data_length     9554
Max_data_length 4109
Index_length    0
Data_free       0
Auto_increment  NULL
Create_time     2009-09-02 11:35:19
Update_time     2009-09-02 11:35:19
Check_time      NULL
Collation       latin1_swedish_ci
Checksum        NULL
Create_options  
Comment 
select avg(length(a)) from t1;
avg(length(a))  600000.0000

Data_length is greater than Max_data_length
[2 Sep 2009 8:38] Sveta Smirnova
Regarding to comment "[15 May 7:08] Tetsuro Ikeda"

Since version 5.1 MySQL behaves as described in this comment. But probably this should be in another bug report.
[8 Sep 2009 10:48] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82667

3108 Sergey Vojtovich	2009-09-08
      BUG#29203 - archive tables have weird values in show table status
      
      Archive engine returns wrong values for average record length
      and max data length.
      
      With this fix they're calculated as following:
      - max data length is 2 ^ 63 where large files are supported
        and INT_MAX32 where this is not supported;
      - average record length is data length / records in data file.
     @ mysql-test/r/archive.result
        A test case for BUG#29203.
     @ mysql-test/t/archive.test
        A test case for BUG#29203.
     @ storage/archive/ha_archive.cc
        Better estimation for average row length and maximal data
        file length.
[9 Sep 2009 10:31] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82795

3108 Sergey Vojtovich	2009-09-09
      BUG#29203 - archive tables have weird values in show table status
      
      Archive engine returns wrong values for average record length
      and max data length.
      
      With this fix they're calculated as following:
      - max data length is 2 ^ 63 where large files are supported
        and INT_MAX32 where this is not supported;
      - average record length is data length / records in data file.
     @ mysql-test/r/archive.result
        A test case for BUG#29203.
     @ mysql-test/t/archive.test
        A test case for BUG#29203.
     @ storage/archive/ha_archive.cc
        Better estimation for average row length and maximal data
        file length.
[14 Sep 2009 16:03] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (version source revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (merge vers: 5.4.4-alpha) (pib:11)
[22 Sep 2009 9:56] Tony Bedford
An entry was added to the 5.4.4 changelog:

When using the Archive storage engine, SHOW TABLE STATUS displayed incorrect information for Max_data_length, Data_length and Avg_row_length.
[2 Oct 2009 0:13] Paul Dubois
Moved 5.4 changelog entry from 5.4.4 to 5.4.3.
[6 Oct 2009 9:00] Bugs System
Pushed into 5.1.40 (revid:joro@sun.com-20091006073316-lea2cpijh9r6on7c) (version source revid:ingo.struewing@sun.com-20090916070128-6053el2ucp5z7pyn) (merge vers: 5.1.39) (pib:11)
[18 Dec 2009 10:27] Bugs System
Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)
[18 Dec 2009 10:43] Bugs System
Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)
[18 Dec 2009 10:59] Bugs System
Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)
[18 Dec 2009 11:13] Bugs System
Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)
[18 Dec 2009 14:54] Tony Bedford
Changelog entry updated to include additional versions:
5.1.40
5.1.41-ndb-7.1.0
5.1.41-ndb-6.2.19
5.1.41-ndb-6.3.31
5.1.41-ndb-7.0.11