0

I’m trying to detect if a table has been updated within the last 5 minutes, and if so, make a copy of it. I can create queries for both parts, however I’m struggling to find an appropriate way to combine them.

Part 1 - Detecting updates:

SELECT *
FROM `INFORMATION_SCHEMA`.`TABLES`
   WHERE 
    DATE_SUB(NOW(), INTERVAL 5 HOUR_MINUTE) < `UPDATE_TIME`
    AND TABLE_NAME = 'admin'

Part 2 - Making a copy:

CREATE TABLE admin_copy LIKE admin
INSERT admin_copy
SELECT * FROM admin

Any assistance would be appreciated.

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
mojobullfrog
  • 189
  • 3
  • 11
  • How will that query detect updates? If you are thinnking of using `UPDATE_TIME` that column does not get updated every time a table is amended – RiggsFolly Nov 09 '21 at 11:21
  • 2
    ___From the manual___ Update_time When the data file was last updated. For some storage engines, this value is NULL. For example, InnoDB stores multiple tables in its system tablespace and the data file timestamp does not apply. Even with file-per-table mode with each InnoDB table in a separate .ibd file, change buffering can delay the write to the data file, so the file modification time is different from the time of the last insert, update, or delete. For MyISAM, the data file timestamp is used; however, on Windows the timestamp is not updated by updates, so the value is inaccurate. – RiggsFolly Nov 09 '21 at 11:25
  • 1
    To get innodb tables update time check: https://stackoverflow.com/questions/2785429/how-can-i-determine-when-an-innodb-table-was-last-changed – Ergest Basha Nov 09 '21 at 11:28
  • Interesting. I was using update_time successfully (or so I thought) when testing, despite the table's storage engine being InnoDB. – mojobullfrog Nov 09 '21 at 11:32

2 Answers2

1

You can create a second table to log changes to the table and use triggers to update the time of the last change, as seen here.

Halley Oliveira
  • 219
  • 2
  • 3
  • Thanks. This is helpful for obtaining the correct update time, however my main problem is writing the correct query - if I detect a table has been updated in the previous 5 minutes, only then should a copy of this table be created. Otherwise do nothing. – mojobullfrog Nov 09 '21 at 21:50
0

See if this works

CREATE TABLE admin_copy LIKE admin; 
INSERT admin_copy SELECT * FROM admin WHERE `UPDATE_TIME` BETWEEN (DATE_SUB(NOW(),INTERVAL 5 MINUTE)) AND NOW();
Punith B M
  • 39
  • 1
  • 12