Bug #32440 InnoDB free space info does not appear in SHOW TABLE STATUS or I_S
Submitted: 16 Nov 2007 16:23 Modified: 20 Jun 2010 0:51
Reporter: David Shrewsbury Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.1.22, 6.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: fixed in pre-v6, qc, v6

[16 Nov 2007 16:23] David Shrewsbury
Description:
The Comment field from SHOW TABLE STATUS, and the TABLE_COMMENT column from INFORMATION_SCHEMA.TABLES, does not show the amount of free space in InnoDB tablespaces.

How to repeat:
DROP TABLE IF EXISTS `test`.`t1`;
CREATE TABLE `test`.`t1` ( id INT ) ENGINE=InnoDB;
INSERT INTO `test`.`t1` VALUES (1),(2),(3);
SHOW TABLE STATUS FROM `test` LIKE 't1'\G
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'\G

Output:

*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 3
 Avg_row_length: 5461
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2007-11-16 11:21:15
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 1. row ***************************
 TABLE_SCHEMA: test
   TABLE_NAME: t1
   TABLE_TYPE: BASE TABLE
       ENGINE: InnoDB
TABLE_COMMENT:
[20 Nov 2007 16:18] Heikki Tuuri
Vasil,

please look at this.

Heikki
[27 Nov 2007 14:17] Bart Huls
Is there maybe an other way to retieve this info.
[4 Dec 2007 14:47] Vasil Dimov
This is because ha_innobase::update_table_comment() is not called by MySQL when SHOW TABLE STATUS is invoked, actually this method is not called anywhere in the code:

% grep -rI update_table_comment .
./sql/ha_partition.h:    update_table_comment is used in SHOW TABLE commands to provide a
./sql/ha_partition.h:  virtual char *update_table_comment(const char *comment);
./sql/ha_ndbcluster.cc:ha_ndbcluster::update_table_comment(
./sql/ha_ndbcluster.cc:    sql_print_error("ha_ndbcluster::update_table_comment: "
./sql/ha_ndbcluster.h:  char *update_table_comment(const char * comment);
./sql/handler.h:  virtual char *update_table_comment(const char * comment)
./sql/ha_partition.cc:    update_table_comment()
./sql/ha_partition.cc:char *ha_partition::update_table_comment(const char *comment)
./storage/innobase/handler/ha_innodb.cc:ha_innobase::update_table_comment(
./storage/innobase/handler/ha_innodb.h: char* update_table_comment(const char* comment);
%

Its invocation was deleted from sql/sql_show.cc recently!

Take a look at this page:

http://mysql.bkbits.net:8080/mysql-5.1/sql/sql_show.cc?PAGE=related

the invocation is present in 1.2541.12.1:
http://mysql.bkbits.net:8080/mysql-5.1/sql/sql_show.cc?PAGE=anno&REV=46f23554302XhvvI79JJ7...

but not present in the next 1.2541.19.2:
http://mysql.bkbits.net:8080/mysql-5.1/sql/sql_show.cc?PAGE=anno&REV=46f238ebLinrkV-GjeKFJ...

however the deletion does not show up in the diff for 1.2541.19.2, maybe I am misusing the BK web interface somehow.
[5 Dec 2007 13:57] Sergei Golubchik
It was removed while working on WL#3732 (Information schema optimization), as a bugfix for bug#11379.

Modifying table's comment is a wrong approach anyway, the comment is specified by the user when a table is created.

You can provide this information, for example, in the DATA_FREE column of the information_schema.tables. To do it - store the number in the handler::stats.delete_length on the handler::info(HA_STATUS_VARIABLE) call.
[7 Dec 2007 13:38] Heikki Tuuri
Vasil,

please find out how InnoDB can provide this vital information. I think we need to have this restored in 5.1.xx.

Regards,

Heikki
[11 Dec 2007 10:24] Vasil Dimov
Thank you, Sergei! I have created a patch that puts the free space in kilobytes in INFORMATION_SCHEMA.TABLES.DATA_FREE.
[11 Dec 2007 10:28] Vasil Dimov
Many users will be surprised that the information disappeared from table_comment.

It needs to be properly documented that it is now available in data_free (once this gets committed).

It is also important to explain that the value is in kilobytes (not bytes or something).
[11 Dec 2007 14:07] Vasil Dimov
Patch that adds the free space to DATA_FREE in kilobytes

Attachment: data_free-5.1.diff (application/octet-stream, text), 738 bytes.

[11 Jan 2008 19:27] Timothy Smith
Test case:

create table t1 (id int) comment='this is a comment' engine=innodb;
select table_comment, data_free > 0 as data_free_is_set from information_schema.tables where table_schema='test';
drop table t1;
[24 Jan 2008 8:53] Timothy Smith
Patch queued to 5.1-build.  NOT yet in 6.0 (5.1-snapshot is null merged into 6.0).  Please return to "Patch approved" after documenting until 6.0 snapshot is available.
[25 Jan 2008 12:01] Sveta Smirnova
Bug #34009 was marked as duplicate of this one.
[25 Feb 2008 16:00] Bugs System
Pushed into 5.1.24-rc
[25 Feb 2008 16:06] Bugs System
Pushed into 6.0.5-alpha
[31 Mar 2008 16:26] Jon Stephens
Documented in the 5.1.23-ndb-6.3.11, 5.1.24, and 6.0.5 changelogs as follows:

        InnoDB free space information is now shown in the Data_free column of
        SHOW TABLE STATUS and and the DATA_FREE column of the
        INFORMATION_SCHEMA.TABLES table.
[23 Apr 2008 9:15] Domas Mituzas
Is it supposed to be in kilobytes? Every other field for all engines are in bytes, not kilobytes. This may cause lots of confusion.
[23 Apr 2008 9:41] Vasil Dimov
Domas, you are right. I put it kilobytes because it was in kilobytes originally in the table comment, so I just moved it.

Isn't it too late to switch to bytes?

Heikki, should I change it?
[23 Apr 2008 15:49] Vasil Dimov
I changed this to show bytes instead of kilobytes in INFORMATION_SCHEMA.TABLES.DATA_FREE.
[10 May 2008 17:56] 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/46593

ChangeSet@1.2622, 2008-05-10 11:55:53-06:00, tsmith@ramayana.hindu.god +7 -0
  Apply InnoDB snapshot innodb-5.1-ss2438.
  
  Addresses the following bugs:
  
    Change the fix for Bug#32440 to show bytes instead of kilobytes in
    INFORMATION_SCHEMA.TABLES.DATA_FREE.
  
    branches/5.1: Fix bug#29507 TRUNCATE shows to many rows effected
    In InnoDB, the row count is only a rough estimate used by SQL
    optimization. InnoDB is now return row count 0 for TRUNCATE operation.
  
    branches/5.1: Fix bug#35537 - Innodb doesn't increment handler_update
    and handler_delete
    Add the calls to ha_statistic_increment() in ha_innobase::delete_row()
    and ha_innobase::update_row().
  
    Fix Bug#36169 create innodb compressed table with too large row size crashed
    Sometimes it is possible that
    row_drop_table_for_mysql(index->table_name, trx, FALSE); is invoked in
    row_create_index_for_mysql() when the index object is freed so copy the
    table name to a safe place beforehand and use the copy.
  
    Fix Bug#36434 ha_innodb.so is installed in the wrong directory
    Change pkglib_LTLIBRARIES with pkgplugin_LTLIBRARIES which has been
    forgotten in this commit: http://lists.mysql.com/commits/40206
[14 May 2008 4:25] 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/46680

ChangeSet@1.2633, 2008-05-14 06:24:20+02:00, tnurnberg@noir.wlan.koehntopp.de +14 -0
  revert the following CS on grounds of "right patch, wrong time":
  
  Apply InnoDB snapshot innodb-5.1-ss2438.
  
  Addresses the following bugs:
  
    Change the fix for Bug#32440 to show bytes instead of kilobytes in
    INFORMATION_SCHEMA.TABLES.DATA_FREE.
  
    branches/5.1: Fix bug#29507 TRUNCATE shows to many rows effected
    In InnoDB, the row count is only a rough estimate used by SQL
    optimization. InnoDB is now return row count 0 for TRUNCATE operation.
  
    branches/5.1: Fix bug#35537 - Innodb doesn't increment handler_update
    and handler_delete
    Add the calls to ha_statistic_increment() in ha_innobase::delete_row()
    and ha_innobase::update_row().
  
    Fix Bug#36169 create innodb compressed table with too large row size crash
    Sometimes it is possible that
    row_drop_table_for_mysql(index->table_name, trx, FALSE); is invoked in
    row_create_index_for_mysql() when the index object is freed so copy the
    table name to a safe place beforehand and use the copy.
  
    Fix Bug#36434 ha_innodb.so is installed in the wrong directory
    Change pkglib_LTLIBRARIES with pkgplugin_LTLIBRARIES which has been
    forgotten in this commit: http://lists.mysql.com/commits/40206
[14 May 2008 8:11] 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/46684

ChangeSet@1.2633, 2008-05-14 10:10:26+02:00, tnurnberg@noir.wlan.koehntopp.de +15 -0
  revert the following CS on grounds of "right patch, wrong time":
  
  Apply InnoDB snapshot innodb-5.1-ss2438.
  
  Addresses the following bugs:
  
    Change the fix for Bug#32440 to show bytes instead of kilobytes in
    INFORMATION_SCHEMA.TABLES.DATA_FREE.
  
    branches/5.1: Fix bug#29507 TRUNCATE shows to many rows effected
    In InnoDB, the row count is only a rough estimate used by SQL
    optimization. InnoDB is now return row count 0 for TRUNCATE operation.
  
    branches/5.1: Fix bug#35537 - Innodb doesn't increment handler_update
    and handler_delete
    Add the calls to ha_statistic_increment() in ha_innobase::delete_row()
    and ha_innobase::update_row().
  
    Fix Bug#36169 create innodb compressed table with too large row size crash
    Sometimes it is possible that
    row_drop_table_for_mysql(index->table_name, trx, FALSE); is invoked in
    row_create_index_for_mysql() when the index object is freed so copy the
    table name to a safe place beforehand and use the copy.
  
    Fix Bug#36434 ha_innodb.so is installed in the wrong directory
    Change pkglib_LTLIBRARIES with pkgplugin_LTLIBRARIES which has been
    forgotten in this commit: http://lists.mysql.com/commits/40206
[14 May 2008 15:21] Bugs System
Pushed into 5.1.25-rc
[19 May 2008 20:02] Paul DuBois
The changeset for Bug#32440 also includes a fix for Bug#36278, although the changeset comment does not mention it.
[19 May 2008 20:18] Paul DuBois
Setting report to Patch queued pending re-push of this patch.
[22 May 2008 9:51] Bugs System
Pushed into 6.0.6-alpha
[27 May 2008 18:39] Paul DuBois
The push into 6.0.x was a null merge that changed nothing. Resetting report to Patch Approved pending further push of patch into 6.0.x.

The "parent" report for this set of bugs is Bug#32440.
[24 Jun 2008 21:32] Calvin Sun
Merged into 6.0.6-alpha, according to Tim. But the patch has not been pushed into 5.1 yet.
[15 Jul 2008 20:03] Paul DuBois
Closing. Problem is covered by changelog for Bug#36278.
[24 Jul 2008 3:14] James Day
This change introduced the extra locking and speed decrease on some platforms reported in bug #38185, work there is develping an improved version.
[28 Jul 2008 11:36] Robin Cui
mysql> select table_name,data_free,CREATE_OPTIONS from tables where TABLE_SCHEMA='test';
+-------------------+-----------+----------------+
| table_name        | data_free | CREATE_OPTIONS |
+-------------------+-----------+----------------+
| a                 |   9412608 |                | 
| log_data          |  18825216 | partitioned    | 
| logdump_fight     |  56475648 | partitioned    | 
| logdump_fight1    |   9412608 | partitioned    | 
| new_logdump_fight |   9412608 |                | 
| old_logdump_fight |   9412608 |                | 
+-------------------+-----------+----------------+
6 rows in set (0.01 sec)

when a table was partitioned, the datafree is showed true free space * partition count。

Versin is Mysql 5.1.26
[22 Aug 2008 15:32] Timothy Smith
@Robin,

I've opened Bug#38964 to track the problem you just reported.
[6 Mar 2009 17:35] 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 products.
[6 Mar 2009 17:37] Paul DuBois
The InnoDB free space information is in the Data_free column as of MySQL 5.1.24/6.0.5.
[5 May 2010 15:22] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 3:05] Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 5:46] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:16] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:43] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 22:58] Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 11:46] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:24] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:11] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)