MySQL Bugs: #32440: InnoDB free space info does not appear in SHOW TABLE STATUS or I

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

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:

Vasil,

please look at this.

Heikki

Is there maybe an other way to retieve this info.

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.

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.

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

Thank you, Sergei! I have created a patch that puts the free space in kilobytes in INFORMATION_SCHEMA.TABLES.DATA_FREE.

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

Patch that adds the free space to DATA_FREE in kilobytes

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

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;

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.

Bug #34009 was marked as duplicate of this one.

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

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.

Is it supposed to be in kilobytes? Every other field for all engines are in bytes, not kilobytes. This may cause lots of confusion.

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?

I changed this to show bytes instead of kilobytes in INFORMATION_SCHEMA.TABLES.DATA_FREE.

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

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

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

Pushed into 5.1.25-rc

The changeset for Bug#32440 also includes a fix for Bug#36278, although the changeset comment does not mention it.

Setting report to Patch queued pending re-push of this patch.

Pushed into 6.0.6-alpha

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.

Merged into 6.0.6-alpha, according to Tim. But the patch has not been pushed into 5.1 yet.

Closing. Problem is covered by changelog for Bug#36278.

This change introduced the extra locking and speed decrease on some platforms reported in bug #38185, work there is develping an improved version.

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

@Robin,

I've opened Bug#38964 to track the problem you just reported.

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.

The InnoDB free space information is in the Data_free column as of MySQL 5.1.24/6.0.5.

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)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

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)

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)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

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)

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)

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)