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:
None 
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:06] Roland Bouman
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.
[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.