MySQL Bugs: #2681: Ability to determine when DB was last modified (generic method)

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	CPU Architecture:	Any

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.

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)

Are the plans add this feature?  If so in what release?

There has been no activity on this request for almost 2 years.

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.

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...

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.

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

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!

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!