| Bug #32440 | InnoDB free space info does not appear in SHOW TABLE STATUS or I_S | ||
|---|---|---|---|
| Submitted: | 16 Nov 2007 17:23 | Modified: | 15 Jul 2008 22:03 |
| Reporter: | David Shrewsbury | ||
| Status: | Closed | ||
| Category: | Server: InnoDB | Severity: | S3 (Non-critical) |
| Version: | 5.1.22, 6.0 | OS: | Any |
| Assigned to: | Timothy Smith | Target Version: | |
| Tags: | v6, qc, fixed in pre-v6 | ||
| Triage: | D3 (Medium) | ||
[20 Nov 2007 17:18]
Heikki Tuuri
Vasil, please look at this. Heikki
[27 Nov 2007 15:17]
Bart Huls
Is there maybe an other way to retieve this info.
[4 Dec 2007 15: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 14: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 14: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 11: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 11: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 15: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 20: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 9: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 13:01]
Sveta Smirnova
Bug #34009 was marked as duplicate of this one.
[25 Feb 2008 17:00]
Bugs System
Pushed into 5.1.24-rc
[25 Feb 2008 17:06]
Bugs System
Pushed into 6.0.5-alpha
[31 Mar 2008 18: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 11: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 11: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 17:49]
Vasil Dimov
I changed this to show bytes instead of kilobytes in INFORMATION_SCHEMA.TABLES.DATA_FREE.
[10 May 2008 19: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 6: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 10: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 17:21]
Bugs System
Pushed into 5.1.25-rc
[19 May 2008 22: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 22:18]
Paul DuBois
Setting report to Patch queued pending re-push of this patch.
[22 May 2008 11:51]
Bugs System
Pushed into 6.0.6-alpha
[27 May 2008 20: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 23: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 22:03]
Paul DuBois
Closing. Problem is covered by changelog for Bug#36278.
[24 Jul 2008 5: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 13: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 17:32]
Timothy Smith
@Robin, I've opened Bug#38964 to track the problem you just reported.
[6 Mar 2009 18: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 18:37]
Paul DuBois
The InnoDB free space information is in the Data_free column as of MySQL 5.1.24/6.0.5.
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: