CODEWITHSUNDEEP
mysqlsql
RaGE
RaGE

Reputation: 8217

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

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 last updated date? Should I access to the database every time I need this value?

Upvotes: 220

Views: 385744

Answers (17)

Manohar Reddy Poreddy
Manohar Reddy Poreddy

Reputation: 27505

Same as others, but with some conditions i've used, to save time:

SELECT
  UPDATE_TIME,
  TABLE_SCHEMA,
  TABLE_NAME
FROM
  information_schema.tables
WHERE
  1 = 1
  AND UPDATE_TIME > '2021-11-09 00:00:00'
  AND TABLE_SCHEMA = 'db_name_here'
  AND TABLE_NAME not in ('table_name_here',)
ORDER BY
  UPDATE_TIME DESC,
  TABLE_SCHEMA,
  TABLE_NAME;

Upvotes: 0

TASC Solutions
TASC Solutions

Reputation: 75

I got this to work locally, but not on my shared host for my public website (rights issue I think).

SELECT last_update FROM mysql.innodb_table_stats WHERE table_name = 'yourTblName';

'2020-10-09 08:25:10'

MySQL 5.7.20-log on Win 8.1

Upvotes: 1

Francois Bourgeois
Francois Bourgeois

Reputation: 3690

For a list of recent table changes use this:

SELECT UPDATE_TIME, TABLE_SCHEMA, TABLE_NAME
FROM information_schema.tables
ORDER BY UPDATE_TIME DESC, TABLE_SCHEMA, TABLE_NAME

Upvotes: 19

justnajm
justnajm

Reputation: 4534

a) It will show you all tables and there last update dates

SHOW TABLE STATUS FROM db_name;

then, you can further ask for specific table:

SHOW TABLE STATUS FROM db_name like 'table_name';

b) As in above examples you cannot use sorting on 'Update_time' but using SELECT you can:

SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA='db_name' ORDER BY UPDATE_TIME DESC;

to further ask about particular table:

SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA='db_name' AND table_name='table_name' ORDER BY UPDATE_TIME DESC';

Upvotes: 1

saeed
saeed

Reputation: 1

i made a column by name : update-at in phpMyAdmin and got the current time from Date() method in my code (nodejs) . with every change in table this column hold the time of changes.

Upvotes: 0

Soul Reaver
Soul Reaver

Reputation: 2092

Although there is an accepted answer I don't feel that it is the right one. It is the simplest way to achieve what is needed, but even if already enabled in InnoDB (actually docs tell you that you still should get NULL ...), if you read MySQL docs, even in current version (8.0) using UPDATE_TIME is not the right option, because:

Timestamps are not persisted when the server is restarted or when the table is evicted from the InnoDB data dictionary cache.

If I understand correctly (can't verify it on a server right now), timestamp gets reset after server restart.

As for real (and, well, costly) solutions, you have Bill Karwin's solution with CURRENT_TIMESTAMP and I'd like to propose a different one, that is based on triggers (I'm using that one).

You start by creating a separate table (or maybe you have some other table that can be used for this purpose) which will work like a storage for global variables (here timestamps). You need to store two fields - table name (or whatever value you'd like to keep here as table id) and timestamp. After you have it, you should initialize it with this table id + starting date (NOW() is a good choice :) ).

Now, you move to tables you want to observe and add triggers AFTER INSERT/UPDATE/DELETE with this or similar procedure:

CREATE PROCEDURE `timestamp_update` ()
BEGIN
    UPDATE `SCHEMA_NAME`.`TIMESTAMPS_TABLE_NAME`
    SET `timestamp_column`=DATE_FORMAT(NOW(), '%Y-%m-%d %T')
    WHERE `table_name_column`='TABLE_NAME';
END

Upvotes: 6

user2654744
user2654744

Reputation: 524

The simplest thing would be to check the timestamp of the table files on the disk. For example, You can check under your data directory 

cd /var/lib/mysql/<mydatabase>
ls -lhtr *.ibd

This should give you the list of all tables with the table when it was last modified the oldest time, first.

Upvotes: 20

Bill Karwin
Bill Karwin

Reputation: 562881

I'm surprised no one has suggested tracking last update time per row:

mysql> CREATE TABLE foo (
  id INT PRIMARY KEY
  x INT,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
                     ON UPDATE CURRENT_TIMESTAMP,
  KEY (updated_at)
);

mysql> INSERT INTO foo VALUES (1, NOW() - INTERVAL 3 DAY), (2, NOW());

mysql> SELECT * FROM foo;
+----+------+---------------------+
| id | x    | updated_at          |
+----+------+---------------------+
|  1 | NULL | 2013-08-18 03:26:28 |
|  2 | NULL | 2013-08-21 03:26:28 |
+----+------+---------------------+

mysql> UPDATE foo SET x = 1234 WHERE id = 1;

This updates the timestamp even though we didn't mention it in the UPDATE.

mysql> SELECT * FROM foo;
+----+------+---------------------+
| id | x    | updated_at          |
+----+------+---------------------+
|  1 | 1235 | 2013-08-21 03:30:20 | <-- this row has been updated
|  2 | NULL | 2013-08-21 03:26:28 |
+----+------+---------------------+

Now you can query for the MAX():

mysql> SELECT MAX(updated_at) FROM foo;
+---------------------+
| MAX(updated_at)     |
+---------------------+
| 2013-08-21 03:30:20 |
+---------------------+

Admittedly, this requires more storage (4 bytes per row for TIMESTAMP).
But this works for InnoDB tables before 5.7.15 version of MySQL, which INFORMATION_SCHEMA.TABLES.UPDATE_TIME doesn't.

Upvotes: 65

John McLean
John McLean

Reputation: 41

OS level analysis:

Find where the DB is stored on disk:

grep datadir /etc/my.cnf
datadir=/var/lib/mysql

Check for most recent modifications

cd /var/lib/mysql/{db_name}
ls -lrt

Should work on all database types.

Upvotes: 3

Andr&#233;s Chand&#237;a
Andr&#233;s Chand&#237;a

Reputation: 1049

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 "<strong>1r tr.: </strong>".$row['UPDATE_TIME'];
    }
?>

Upvotes: -1

Radu M.
Radu M.

Reputation: 5748

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

Upvotes: 69

Jiju Thomas Mathew
Jiju Thomas Mathew

Reputation: 389

Not sure if this would be of any interest. Using mysqlproxy in between mysql and clients, and making use of a lua script to update a key value in memcached according to interesting table changes UPDATE,DELETE,INSERT was the solution which I did quite recently. If the wrapper supported hooks or triggers in php, this could have been eaiser. None of the wrappers as of now does this.

Upvotes: 0

Alnitak
Alnitak

Reputation: 340045

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.

Upvotes: 340

bartonlp
bartonlp

Reputation: 133

If you are running Linux you can use inotify to look at the table or the database directory. inotify is available from PHP, node.js, perl and I suspect most other languages. Of course you must have installed inotify or had your ISP install it. A lot of ISP will not.

Upvotes: 0

Steve Wood
Steve Wood

Reputation: 31

Just grab the file date modified from file system. In my language that is:

 tbl_updated = file.update_time(
        "C:\ProgramData\MySQL\MySQL Server 5.5\data\mydb\person.frm")

Output:

1/25/2013 06:04:10 AM

Upvotes: 0

Mikhail
Mikhail

Reputation: 1613

I would create a trigger that catches all updates/inserts/deletes and write timestamp in custom table, something like tablename | timestamp

Just because I don't like the idea to read internal system tables of db server directly

Upvotes: 6

Kieveli
Kieveli

Reputation: 11084

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.

Upvotes: -9

Related Questions