Query to find tables modified in the last hour

Question

I want to find out which tables have been modified in the last hour in a MySQL database. How can I do this?

Orson

Do your tables have a timestamp column when data was changed? – Walt

I think most of them, if not all, have a "created" field. I am not sure if that answers your question; the database is from a website which I did not develop but have to mantain, so I am a little lost. – Orson

can you describe the usecase? there might be a better solution to what you want to achieve – Riches

To clarify the bounty: the UPDATE_TIME feature / trick needs to work on an extant InnoDB database that we have read access to, not one that we are starting from scratch. Therefore triggers or simply adding an updated field are not feasible. – Sheng

@Sheng Are you using innodb_file_per_table ? – Sudorific

@Sheng Another question : What version of MySQL are you using ??? – Sudorific

See this related question for Rolando's past answer on the topic, and a plug for Percona server. – Uteutensil

Hi Rolando! The project where this is important is on MySQL 5.5, but I was hoping for a version-agnostic solution, such as your answer as linked by Ian. That may be a good solution. Also, it appears that MySQL 5.7 will have UPDATE_TIME enabled for InnoDB, but the value won't persist across resets of mysqld! – Sheng

@RolandoMySQLDBA: Can you please post an answer with a link to your post on dba.SE, as that does seem to be the best answer and I would like to accept it. Thanks! – Sheng

@Sheng I added my answer 12 minutes ago with a slight update. – Sudorific

Answer 1

MySQL 5.x can do this via the INFORMATION_SCHEMA database. This database contains information about tables, views, columns, etc.

SELECT * 
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE 
    DATE_SUB(NOW(), INTERVAL 1 HOUR) < `UPDATE_TIME`

Returns all tables that have been updated (UPDATE_TIME) in the last hour. You can also filter by database name (TABLE_SCHEMA column).

An example query:

SELECT 
    CONCAT(`TABLE_SCHEMA`, '.', `TABLE_NAME`) AS `Table`, 
    UPDATE_TIME AS `Updated`
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE
    DATE_SUB(NOW(), INTERVAL 3 DAY) < `UPDATE_TIME`
    AND `TABLE_SCHEMA` != 'INFORMATION_SCHEMA'
    AND `TABLE_TYPE` = 'BASE TABLE';

Raffin

Be aware that this approach only works for MyISAM tables, not InnoDB. – Sennight

For InnoDB I'm hard-pressed to find an alternative. Maybe triggers on insert and delete operations. – Raffin

As Ike Walker mentioned above, it is not working with InnoDB. The UPDATE_TIME column in information_schema.TABLES is never updated. Hence the query mentioned would fail. – Perverse

For mysql 5.7.15 the request is working for InnoDB tables just fine. – Tight

Yes, I confirm @Tight info, my MySQL server in 5.7.19 has both myISAM and InnoDB tables in different databases and it works fine for all. – Gurnard

I am looking for a Postgres-friendly way, this does not work. Please guide me to the correct Q&As. – Ortega

Answer 2

For each table you want to detect change, you need to have a column that holds the last change's timestamp.

For every insert or update in the table, you need to update that column with the current date and time.

Alternatively, you can set up a trigger which updates the column automatically on each insert or modify. That way you don't have to modify all of your query.

Once this works, to find out if rows from a table have been modified in the last hour, perform the query

select count(*) from mytable where datemod>subtime(now(),'1:0:0')

Repeat for every table you want to check.

Whippet

Answer 3

InnoDB still currently lacks a native mechanism to retreive this information. In the related feature request at MySQL, someone advises to set AFTER [all events] triggers on each table to be monitored. The trigger would issue a statement such as

INSERT INTO last_update VALUE ('current_table_name', NOW())
ON DUPLICATE KEY UPDATE update_time = NOW();

in a table like this:

CREATE TABLE last_update (
    table_name VARCHAR(64) PRIMARY KEY,
    update_time DATETIME
) ENGINE = MyISAM; -- no need for transactions here

Alternatively, if a slight inaccuracy in this data (in the range of one second) is acceptable, and if you have read access to the MySQL data files, you could switch to a setting where inndb_files_per_table = ON (recommended in any case) and check the last modification time of the underlying data files.

These files are found under /var/lib/mysql/[database_name]/*.ibd in most default installations.

Please note, if you decide to take this route, you need to recreate existing tables for the new setting to apply.

Hydraulics

You may want to use the TIMESTAMP datatype instead of DATETIME. Define it as " update_time TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP " ...and it will automatically be set when inserting data if omitted. – Toadfish

Answer 4

I have answered a question like this in the DBA StackExchange about 1.5 years ago: Fastest way to check if InnoDB table has changed.

Based on that old answer, I recommend the following

Flushing Writes to Disk

This is a one-time setup. You need to set innodb_max_dirty_pages_pct to 0.

First, add this to /etc/my.cnf

[mysqld]
innodb_max_dirty_pages_pct=0

Then, run this to avoid having to restart mysql:

mysql> SET GLOBAL innodb_max_dirty_pages_pct = 0;

Get Timestamp of InnoDB table's .ibd file

ls has the option to retrieve the UNIX timestamp in Seconds. For an InnoDB table mydb.mytable

$ cd /var/lib/mysql/mydb
$ ls -l --time-style="+%s" mytable.ibd | awk '{print $6}'

You can then compute UNIX_TIMESTAMP(NOW()) - (timestamp of the .ibd file) and see if it is 3600 or less.

Give it a Try !!!

Sudorific

Answer 5

SELECT *
FROM  information_schema.tables
WHERE UPDATE_TIME >= SYSDATE() - INTERVAL 1 DAY  && TABLE_TYPE != 'SYSTEM VIEW'

SELECT * 
FROM information_schema.tables
WHERE UPDATE_TIME >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) && TABLE_TYPE != 'SYSTEM VIEW'

Outstation