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

User · Answer

I m surprised no one has suggested tracking last update time per row   mysql gt  CREATE TABLE foo     id INT PRIMARY KEY   x INT    updated at TIMESTAMP DEFAULT CURRENT TIMESTAMP                       ON UPDATE CURRENT TIMESTAMP    KEY  updated at      mysql gt  INSERT INTO foo VALUES  1  NOW   - INTERVAL 3 DAY    2  NOW      mysql gt  SELECT   FROM foo   ---- ------ ---------------------    id   x      updated at             ---- ------ ---------------------     1   NULL   2013-08-18 03 26 28      2   NULL   2013-08-21 03 26 28    ---- ------ ---------------------   mysql gt  UPDATE foo SET x   1234 WHERE id   1    This updates the timestamp even though we didn t mention it in the UPDATE   mysql gt  SELECT   FROM foo   ---- ------ ---------------------    id   x      updated at             ---- ------ ---------------------     1   1235   2013-08-21 03 30 20    lt -- this row has been updated    2   NULL   2013-08-21 03 26 28    ---- ------ ---------------------    Now you can query for the MAX     mysql gt  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

User · Answer

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

User · Answer

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

User · Answer

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  lt mydatabase gt  ls -lhtr   ibd   This should give you the list of all tables with the table when it was last modified the oldest time  first

User · Answer

This is what I did  I hope it helps    lt  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   lt strong gt 1r tr    lt  strong gt    row  UPDATE TIME            gt

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

This is what I did  I hope it helps    lt  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   lt strong gt 1r tr    lt  strong gt    row  UPDATE TIME            gt

User · Answer

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  lt mydatabase gt  ls -lhtr   ibd   This should give you the list of all tables with the table when it was last modified the oldest time  first

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I m surprised no one has suggested tracking last update time per row   mysql gt  CREATE TABLE foo     id INT PRIMARY KEY   x INT    updated at TIMESTAMP DEFAULT CURRENT TIMESTAMP                       ON UPDATE CURRENT TIMESTAMP    KEY  updated at      mysql gt  INSERT INTO foo VALUES  1  NOW   - INTERVAL 3 DAY    2  NOW      mysql gt  SELECT   FROM foo   ---- ------ ---------------------    id   x      updated at             ---- ------ ---------------------     1   NULL   2013-08-18 03 26 28      2   NULL   2013-08-21 03 26 28    ---- ------ ---------------------   mysql gt  UPDATE foo SET x   1234 WHERE id   1    This updates the timestamp even though we didn t mention it in the UPDATE   mysql gt  SELECT   FROM foo   ---- ------ ---------------------    id   x      updated at             ---- ------ ---------------------     1   1235   2013-08-21 03 30 20    lt -- this row has been updated    2   NULL   2013-08-21 03 26 28    ---- ------ ---------------------    Now you can query for the MAX     mysql gt  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

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

Examples related to mysql

Examples related to sql