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

Question

In the footer of my page, I would like to add something like "last updated the xx/xx/200x"; with this date being the last time a certain mySQL table has been updated. What is the best way to do that ? Is there a function to retrieve the date ? Should I make an access to the database everytime my footer is displayed ?

Answer 1

Cache the query in a global variable when it is not available.

Create a webpage to force the cache to be reloaded when you update it.

Add a call to the reloading page into your deployment scripts.

Answer 2

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 the PHP equivalent of the utimes() function to change the file timestamp.

On page display:

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

Answer 3

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

TABLE_SCHEMA not TABLE_SCHEME, there is a typo in the top post!

Answer 4

very useful information given at one place by Alnitak

Answer 5

I don't have information_schema database, using mysql version 4.1.16, so in this case you can query this:

show table status from your_database like '%your_table%';.

It will return these columns:

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

As you can see there is a column called: "Update_time" that shows you the last update time for that table.

Hope this will help someone when looking for a solution :)

Answer 6

This is what I did, I hope it helps.

<?php

mysql_connect("localhost", "USER", "PASSWORD") or die(mysql_error()); mysql_select_db("information_schema") or die(mysql_error()); $query1 = "SELECT UPDATE_TIME FROM TABLES WHERE TABLE_SCHEMA LIKE 'DataBaseName' AND TABLE_NAME LIKE 'TableName'"; $result1 = mysql_query($query1) or die(mysql_error()); while($row = mysql_fetch_array($result1)){ echo "1r tr.: ". $row['UPDATE_TIME'] ; } ?>

Answer 7

won't work with innodb engine