| Bug #11826 | SHOW TABLE STATUS: CHECK TIME not updated for InnoDB tables | ||
|---|---|---|---|
| Submitted: | 8 Jul 2005 23:06 | Modified: | 19 Aug 2005 1:06 |
| Reporter: | Roland Bouman | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 5.0.7 | OS: | Windows (Win XP Professional SP2) |
| Assigned to: | Paul DuBois | CPU Architecture: | Any |
[8 Jul 2005 23:44]
MySQL Verification Team
In my opinion here is a documentation issue clarifying the reason why
these variables can't be updated for InnoDB storage engine:
mysql> show table status like 't_%'\G
*************************** 1. row ***************************
Name: t_innodb
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2005-07-08 20:27:36
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 4096 kB
*************************** 2. row ***************************
Name: t_myisam
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 1970324836974591
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2005-07-08 20:27:27
Update_time: 2005-07-08 20:27:27
Check_time: 2005-07-08 20:29:02
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
2 rows in set (0.03 sec)
[9 Jul 2005 0:34]
Roland Bouman
Oh, I can't believe I typed CREATE TIME in the synopsis field instead of CHECK TIME, stupid me. Anyway, will this documentation include info include info on how to find out what the last check time actually was for InnoDB tables?
[11 Jul 2005 11:23]
Heikki Tuuri
Modifying the synopsis, as the bug is about CHECK TABLE time. This is a documentation issue. Or in 5.1, we could put the time to the InnoDB system tables or the .frm file, or in the information schema. Regards, Heikki
[19 Aug 2005 1:06]
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 product(s). Additional info: I'll update the manual to indicate that this field is NULL for engines that don't update it.
Description: The last date a CHECK TABLE command was run on a table backed by the InnoDB storage engine is not displayed in the Check_time column of the output of the SHOW TABLE STATUS command. The corresponding column CHECK_TIME in the information_schema.TABLES table is also not updated in this case. A similar SHOW TABLE STATUS command executed for a table backed by the MyISAM engine does have the expected effect. In this case, the last date the CHECK TABLE command was run is displayed in the output of the SHOW TABLE STATUS command; also, the corresponding column CHECK_TIME in the information schema.TABLES column is updated accordingly. As the CHECK TABLE command is supported only for tables backed by either one of these storage engines, it was expected that the result regarding both the output of the SHOW TABLE STATUS command as well as the CHECK_TIME column value of the information_schema.TABLES table would be similar, but it is not. How to repeat: mysql> use test; Database changed mysql> create table t_myisam(c int) engine = myisam; Query OK, 0 rows affected (0.06 sec) mysql> create table t_innodb(c int) engine = innodb; Query OK, 0 rows affected (0.09 sec) mysql> check table t_myisam; +---------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------+-------+----------+----------+ | test.t_myisam | check | status | OK | +---------------+-------+----------+----------+ 1 row in set (0.00 sec) mysql> check table t_innodb; +---------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------+-------+----------+----------+ | test.t_innodb | check | status | OK | +---------------+-------+----------+----------+ 1 row in set (0.00 sec) mysql> show table status like 't_%' mysql> show table status like 't_%' -> ; +----------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+------------------------+ | 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 | +----------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+------------------------+ | t_innodb | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2005-07-09 00:46:05 | NULL | NULL | latin1_swedish_ci | NULL | | InnoDB free: 141312 kB | | t_myisam | MyISAM | 10 | Fixed | 0 | 0 | 0 | 1970324836974591 | 1024 | 0 | NULL | 2005-07-09 00:45:52 | 2005-07-09 00:45:52 | 2005-07-09 00:46:36 | latin1_swedish_ci | NULL | | | +----------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+------------------------+ 2 rows in set (0.00 sec) Suggested fix: The SHOW TABLE STATUS command should display the last date the CHECK TABLE command was run for a table backed by the InnoDB engine in the Check_time column. The CHECK_TIME column of the information_schema.TABLES table should be updated accordingly not only for MyISAM but also for InnoDB backed tables.