Bug #2681 Ability to determine when DB was last modified (generic method)
Submitted: 9 Feb 2004 4:14 Modified: 13 May 2010 16:03
Reporter: Phil Sladen Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:4.1.0 OS:Linux (Linux 9.0)
Assigned to: Assigned Account
Triage: Needs Triage: D5 (Feature request)

[9 Feb 2004 4:14] Phil Sladen
Description:
My application potentially supports many discrete databases, only a few of which many be modified each day, and I would like to keep each one backed up separately. Currently, since I cannot determine which ones have been modified since the last backup run I have to back them all up each time.

I've queried the MySQL general email list to try to find out how you can determine when a database was last updated, but with no success. The best response I could get was to check the modification times on the database files themselves. However, this doesn't work for InnoDB tables since the data for all InnoDB tables seems to be kept in the same file(s)... so that checking their modification time(s) would only tell you that a database with InnoDB tables has been updated, and not which particular database.

Also, if I resorted to using 'show table status' on all of a database's tables this displays 'NULL' in the 'Update time' column for InnoDB tables.

Please add this enhancement. Thanks, Phil

How to repeat:
This is a feature request. Thanks.
[28 Nov 2005 9:55] Valerii Kravchuk
Thank you for a feature request. 

At least, keeping track of the last update time for each InnoDB table (as it is done for MyISAM) will create a useful base for answering the question. INFORMATION_SCHEMA.TABLES also have a column (UPDATE_TIME) just for that (it works so for MyISAM tables)
[19 Oct 2007 16:19] Kirk Holub
Are the plans add this feature?  If so in what release?

There has been no activity on this request for almost 2 years.
[3 Jun 2008 11:43] Lennart Schedin
This would be good feature to have. If it existed it could be used by a client application to determine a table should be re-read or not.
[25 Aug 2011 9:34] No No
I agree with Lennart Schedin. At the momemt it seems that teh only way to do so is to feed another table with a trigger. Would make it far cleaner if able to query last time databse was updated and then decide if you want to reload data or not in teh form...
[23 Jun 2013 6:53] Dotan Cohen
I don't see a feature on the MySQL issue tracker for 'voting' for a bug, so I am commenting in order to stress the importance of this RFE.
[23 Jun 2013 12:21] Vasil Dimov
Hi, non-persistent implementation of last update time has been implemented and will be released in 5.7.2. See http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-2.html and http://dev.mysql.com/doc/refman/5.7/en/tables-table.html
[23 Jun 2013 17:35] Dotan Cohen
Thank you, Vasil, this is great news, and honestly it will help me keep the company on MySQL when ideas are floating around regarding that 'other' Monty creation. I'll direct my questions regarding the implementation (persistentness) on the MySQL users mailing list.

Thank you!
[24 Jun 2013 9:05] Vasil Dimov
Hmm, this feature request has been opened in 2004!

For the persistence part - we need somewhere to store it and that is quite involved. Provided that a new data dictionary is coming, which will support such properties naturally, we decided to wait for the new data dictionary for the persistence part.

Thank you!