Bug #61927 DATA_LENGTH doesn't show anything useful for compressed tables

Submitted: 20 Jul 2011 12:11 Modified: 25 Jul 2011 14:46
Reporter: Domas Mituzas Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB Plugin storage engine Severity:S3 (Non-critical)
Version:5.5-bzr, 5.1-bzr OS:Any
Assigned to: CPU Architecture:Any

[20 Jul 2011 12:11] Domas Mituzas
Description:
Both "SHOW TABLE STATUS" and INFORMATION_SCHEMA.TABLES does not report table sizes correctly for compressed tables, e.g.:

mysql> select table_name,create_options, data_length  from information_schema.tables where table_name like '%compr%';
+--------------+----------------------------------------+-------------+
| table_name   | create_options                         | data_length |
+--------------+----------------------------------------+-------------+
| compressed   | row_format=COMPRESSED KEY_BLOCK_SIZE=4 |    88883200 |
| uncompressed |                                        |    39403520 |
+--------------+----------------------------------------+-------------+
2 rows in set (0.00 sec)

mysql> show table status like '%compr%';
+--------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------------------------------+---------+
| Name         | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options                         | Comment |
+--------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------------------------------+---------+
| compressed   | InnoDB |      10 | Compressed |  998529 |             89 |    88883200 |               0 |            0 |   1835008 |           NULL | 2011-07-20 14:52:45 | NULL        | NULL       | latin1_swedish_ci |     NULL | row_format=COMPRESSED KEY_BLOCK_SIZE=4 |         |
| uncompressed | InnoDB |      10 | Compact    | 1000392 |             39 |    39403520 |               0 |            0 |   7340032 |           NULL | 2011-07-20 14:52:17 | NULL        | NULL       | latin1_swedish_ci |     NULL |                                        |         |
+--------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------------------------------+---------+

Whereas on disk:

# du -sh  *compr*ibd
 24M	compressed.ibd
 48M	uncompressed.ibd

How to repeat:
create a table with data that compresses ok'ish, check space on disk vs space reported, yay, broken. 

Suggested fix:
show correct data
[20 Jul 2011 13:57] Shane Bester
Is an already reported internal bug. Will link the two.
BUG 12770537 - I_S.TABLES.DATA_LENGTH DOES NOT SHOW ON-DISK SIZE FOR COMPRESSED INNODB
[20 Jul 2011 14:07] Shane Bester
we also have: http://bugs.mysql.com/bug.php?id=59659
[25 Jul 2011 14:46] Domas Mituzas
I was probably wrong, it does show something useful, you just have to divide it by (16/keyblocksize) ;-)

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.052 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.