Geek Answers Handbook

How to determine when the last InnoDB table was modified?

In the past, I was successful in storing (strongly) processed database query results in memcached, using recent updates to base tables (s) as part of the cache key. For MyISAM tables, this last modified time is available in SHOW TABLE STATUS . Unfortunately, this is usually NULL for InnoDB tables.

In MySQL 4.1, the ctime for InnoDB in the SHOW TABLE STATUS usually the actual last update time, but this is not like MySQL 5.1.

There is a DATETIME field in the table, but it is displayed only when the row has been changed - it cannot show the time to delete a row that no longer exists! So, I really can't use MAX(update_time) .

Here is a very difficult part. I have a number of lines that I read. Can I determine the state of the table, which does not depend on when the changes were actually applied?

My conclusion after some time working on this is that there will be no way to get this information as cheaply as possible. I'm probably going to cache the data until I expect the table to change (updated once a day), and let the query cache help where possible.

+7
mysql innodb
source share
3 answers

This is MySQL Error 14374 , 15438 , and the underlying error is InnoDB 2681 .

I have two suggestions (other than the MySQL fix).

  • If you use one table for each file ( innodb_file_per_table ), put the main file. To do this, you can write a MySQL function / extension. This may be slightly lagging due to database caching.
  • You can use after updating, deleting and inserting triggers to save your own metadata table with the latest update time for each table that you are worried about.

I would suggest the second option, since it is much more portable and does not depend on implementation details (for example, innodb_file_per_table ).

+8
source share

If you are not very interested when the database was changed, but you want to find out if one database table has changed, you should look in MySQL CHECKSUM TABLE

Hope this helps.

+11
source share

I would suggest adding another column to the table and let MySQL keep track of when the table was last modified, something like this:

 ADD COLUMN `last_update` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
-one
source share

More articles:


All Articles