| 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 22:03 |
| Reporter: | David Shrewsbury | ||
| Status: | Closed | ||
| Category: | Server: InnoDB | Severity: | S3 (Non-critical) |
| Version: | 5.1.22, 6.0 | OS: | Any |
| Assigned to: | Tim Smith | Target Version: | 5.1+ |
| Tags: | fixed in pre-v6, qc, 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
fyrsDEyXQ
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
jTrFntcrA
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 20:27]
Tim 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 9:53]
Tim 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 13:01]
Sveta Smirnova
Bug #34009 was marked as duplicate of this one.
[25 Feb 17:00]
Bugs System
Pushed into 5.1.24-rc
[25 Feb 17:06]
Bugs System
Pushed into 6.0.5-alpha
[31 Mar 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 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 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 17:49]
Vasil Dimov
I changed this to show bytes instead of kilobytes in INFORMATION_SCHEMA.TABLES.DATA_FREE.
[10 May 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 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 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 17:21]
Bugs System
Pushed into 5.1.25-rc
[19 May 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 22:18]
Paul DuBois
Setting report to Patch queued pending re-push of this patch.
[22 May 11:51]
Bugs System
Pushed into 6.0.6-alpha
[27 May 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 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 22:03]
Paul DuBois
Closing. Problem is covered by changelog for Bug#36278.
[24 Jul 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.
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: