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]
Shane Bester
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
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.049 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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: .