MySQL Bugs: #14374: Update_time is NULL for InnoDB tables

Bug #14374	Update_time is NULL for InnoDB tables
Submitted:	27 Oct 2005 6:12	Modified:	15 Sep 2014 5:21
Reporter:	Janak Ramakrishnan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.0.16-BK, 5.0.15	OS:	Linux (Linux, Windows)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
InnoDB tables that I create always have an Update_time of NULL when I do
SHOW TABLE STATUS
MyISAM tables have the correct Update_time value
The output of the show table status is below.  The SQL commands leading up to it are in the "How to repeat" section.
Note that the Windows filesystem does show the correct update time for the table's file.

(sorry for the messy layout -- it will align in a fixed-point font with no linewraps)
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+----------------------+
| 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              |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+----------------------+
| test | InnoDB |      10 | Compact    |    0 |              0 |       16384 |            0    |            0 |         0 |           NULL | 2005-10-26 22:55:30 | NULL        | NULL       | latin1_swedish_ci |     NULL |                | InnoDB free: 4096 kB |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+----------------------+

How to repeat:
create database testing;
use testing;
create table test (id int) engine = innodb;
show table status from testing;

Thank you for a problem report. It is easily repetable just as you described, also on Linux with 5.0.16-BK (ChangeSet@1.1932.338.1, 2005-10-25 19:04:31+02:00, pgalbraith@mysql.com):

mysql> create database testing;
Query OK, 1 row affected (0,00 sec)

mysql> use testing;
Database changed
mysql> create table test1 (id int) engine = innodb;
Query OK, 0 rows affected (0,03 sec)

mysql> create table test2 (id int) engine = MyISAM;
Query OK, 0 rows affected (0,01 sec)

mysql> show table status from testing;
+-------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+-----------------------+
| 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               |
+-------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+-----------------------+
| test1 | InnoDB |      10 | Compact    |    0 |              0 |       16384 |
               0 |            0 |         0 |           NULL | 2005-10-27 12:44:37 | NULL                | NULL       | latin1_swedish_ci |     NULL |      | InnoDB free: 11264 kB |
| test2 | MyISAM |      10 | Fixed      |    0 |              0 |           0 |
1970324836974591 |         1024 |         0 |           NULL | 2005-10-27 12:44:46 | 2005-10-27 12:44:46 | NULL       | latin1_swedish_ci |     NULL |      |           |
+-------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+-----------------------+
2 rows in set (0,01 sec)

So, it is either a bug, or a feature that have to be properly documented in the manual (http://dev.mysql.com/doc/refman/5.0/en/show-table-status.html)

This is a feature request. InnoDB currently does not write a timestamp to any file about the latest INSERT, DELETE, UPDATE of a table. The information is not available unless we let InnoDB to keep such a timestamp.

Hmm... a very simple solution would be to keep the timestamp in main memory, in the data dictionary cache. But after a mysqld startup, Update_time would be null.

Another option is to store the timestamp to the .frm file.

Yet another option is to write the timestamp to the root page of the clustered index before doing the update. But then we must get an X-latch to the root page.

Regards,

Heikki

Please excuse my ignorance, but is it not possible to get a timestamp from the OS?  I thought that's what MyISAM did.

I also want to get this information! Is it possible to get UPDATE_TIME in anotehr way for InnoDB tables? I understand that MyISAM tables works 30% faster then InnoDB, but in big systems I think noone professional uses myisam to store business data (referential integrity is the base of dms). 
Also it verry usefull fo client application to determine when was modified InnoDB table. For MyIsam it is simply..but in InnoDB there are foreign keys..cascade options and so on...
This will be verry nice feature!

So it is 2009 now, MySQL 5.1.36 and it's still not here... Just a little bit nagging :P
It would save me quite a lot of work and time if this information was easily available.

Is there any alternative way to get the last update date of a table? This is an essential feature of a db. I am surprised that this bug has been standing for almost 4 years, which is a long time in the internet age.

I totally agree with the previous speakers! This is a feature every professional database should have!
I would appreciate a prompt implementation.

Thanks in advance
Sebastian Gentil

I aggree willa ll he above...

in 2012 too

Any progress with this?

Looks like users want this but developers don't?

Is this feature that hard to add?

WTF? How is it possible that this is still not implemented, when it is practically not possible to achieve similar behavior? Seriously we got just 2 options:

1. Heavily changing our database (adding UPDATE,INSERT,DELETE triggers and storing update times in different table)

2. Using system file modification time for each table, which is pretty inaccurate for InnoDB because of all the foreign keys, ON UPDATE and so on. And i'm not even talking about when changes happen in memory and are not written right away...

tl;dr : It would be pretty nice to know if this will be implemented EVER. Just so we know if there is hope.

See also bug #69673

Bug #69689 marked as duplicate of this one.

Bug#64914 was marked as a duplicate of this.

Posted by developer:
 
WL#6658 implemented update_time for InnoDB tables in MySQL 5.7.2.
Note that it is not persistent across server restarts.
WL#6917 was filed for making update_time persistent.