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