How to Tell When a MySQL Table Was Last Updated

How can I tell when a MySQL table was last updated?

In later versions of MySQL you can use the information_schema database to tell you when another table was updated:

SELECT UPDATE_TIME
FROM   information_schema.tables
WHERE  TABLE_SCHEMA = 'dbname'
   AND TABLE_NAME = 'tabname'

This does of course mean opening a connection to the database.

---

An alternative option would be to "touch" a particular file whenever the MySQL table is updated:

On database updates:

• Open your timestamp file in O_RDRW mode
• close it again

or alternatively

• use touch(), the PHP equivalent of the utimes() function, to change the file timestamp.

On page display:

• use stat() to read back the file modification time.

How to get the last modified date of an mysql table

As you can read in mysql documentation for The INFORMATION_SCHEMA TABLES Table,
you can get the update_time (and other information) with a simple query:

SELECT update_time 
FROM information_schema.tables 
WHERE table_name = 'table_name';

how to find mysql database last updated date

If timestamp is not what you can do, which would be ideal, then you can try a workaround.

Codeigniter has a last_query function, which pretty much returns the last executed query. If you find that function and either extend it, or find and actually alter the method to store ONLY update functions (or some other logic for storing it, maybe another method called from inside of it), and when you call it, you get the query executed. From there, it is string manipulation.

How can I determine when an InnoDB table was last changed?

This is MySQL bug 14374, 15438, and underlying InnoDB bug 2681.

I have two suggestions (other than patching MySQL).

1. If you're using one table per file (innodb_file_per_table), stat the underlying file. You could write a MySQL function/extension to do this. This may lag slightly, due to database caching.
2. You can use after update, delete, and insert triggers to keep your own metadata table with the last update times for each table you're concerned with.

I'd personally suggest the second, as its much more portable and doesn't depend on implementation details (such as innodb_file_per_table).

Getting the date/time of the last change to a MySQL database

SELECT update_time
FROM   information_schema.tables
WHERE  table_schema = 'dbName'
       AND table_name = 'tableName'

---

Related Topics