1

Is it possible to get timestamp of last change to database?

The date should reflect any change - create, update, delete - and any table - posts, comments, post_meta, attachments, etc. I've found some related questions, but all of them target only specific tables, such as Show last time WordPress site was updated / modified,

My background: I'm letting my client to fill in their content to the site on my staging environment, while I develop locally. Once in a while I need to synchronize the versions. To see the time of the last modification would ease my worrying, that I accidentally discard their work. :)

Thanks in advance!

Improve this question
2
  • Another alternative: hook your local installation to the same database the client is using. That way whatever changes they make, they appear on what you're developing - but you can also touch any files you need to without breaking the staging site as they work on it.
    – WebElaine
    Jun 6, 2017 at 14:16
  • That's not a bad idea! Not sure if it's possible with my Vagrant powered setup: roots.io/trellis Will definitely think about it though!
    – Petr Cibulka
    Jun 6, 2017 at 14:32

1 Answer 1

Reset to default
2

There's an UPDATE_TIME column in the TABLES table in the information_schema database.

So you can try to get the max from that column with e.g.:

SELECT MAX(`UPDATE_TIME`) 
FROM information_schema.TABLES
WHERE  TABLE_SCHEMA = 'dbname'

where we replace 'dbname' with the corresponding database name.

More related info here on SO.

From the MySQL 5.7 docs:

UPDATE_TIME displays a timestamp value for the last UPDATE, INSERT, or DELETE performed on InnoDB tables that are not partitioned. For MVCC, the timestamp value reflects the COMMIT time, which is considered the last update time. Timestamps are not persisted when the server is restarted or when the table is evicted from the InnoDB data dictionary cache.

The UPDATE_TIME column also shows this information for partitioned InnoDB tables.

There seems to have been a bug, that UPDATE_TIME wasn't updated for InnoDB tables in MySQL <= 5.6, but should be fixed for 5.7+

Improve this answer
7
  • That seems to be exactly what I need. $wpdb->get_results("SELECT UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = 'dbname'"); gives me empty result though, do I do someting wrong?
    – Petr Cibulka
    Jun 6, 2017 at 14:41
  • Did you replace 'dbname' with the actual db name.?
    – birgire
    Jun 6, 2017 at 14:46
  • Strangely when I test this on my MariaDB test db, I get only non-null UPDATE_TIME values for my MyISAM tables and only null values for my InnoDB tables
    – birgire
    Jun 6, 2017 at 14:56
  • I got an error when I tried that, but now I see it was due to missing quotes. Now I'm getting an array of empty objects. Array ( [0] => stdClass Object ( [UPDATE_TIME] => ) ) // 12 in total
    – Petr Cibulka
    Jun 6, 2017 at 15:12
  • 1
    This is the version string - 5.5.5-10.0.29-MariaDB-0ubuntu0.16.04.1, so MariaDB and MySQL 5.5.5 I believe. That would answer my question I believe, thank you! I will try to update my provisioning setup and let us know, if that fixed my problem. Thanks!
    – Petr Cibulka
    Jun 6, 2017 at 16:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.