0

I have to create a xls datafeed for a website and I would like to know which tables are getting affected when I do a manual entry from CMS.

If i have installed fresh database and I'm doing first entry in it using CMS: I would like to know which tables got updated/appended in last 1 min in that DB.

It is somewhat similar to this question https://stackoverflow.com/questions/307438/how-can-i-tell-when-a-mysql-table-was-last-updated

But in my case I dont know which tables to check. I can check each and every table in the db using the solution posted in the question but I have a gut feeling that there is a better solution for this.

Community
  • 1
Kuldeep Daftary
  • 101
  • 1
  • 1
  • 2

1 Answers1

1

There are two distinct answers I have to give by Storage Engine

MyISAM

For all MyISAM tables, checking the last time a table was updated is quick and dirty

SET @IntervalMinutes = 2;
SET @IntervalSeconds = @IntervalMinutes * 60;
SET @DBtoCheck = DATABASE();
SELECT table_schema,table_name,update_time
FROM information_schema.tables WHERE engine='MyISAM'
AND table_schema = @DBtoCheck
AND update_date > (NOW() - INTERVAL @IntervalSeconds SECOND)
ORDER BY update_time DESC A;

InnoDB

I have some bad news: you cannot trust the INFORMATION_SCHEMA database because

  • Transaction Behavior of InnoDB
  • Location of the Data and Index Pages
  • Heavy duty locking of the INFORMATION_SCHEMA when reading about an InnoDB table

In order to know the last update by some timestamp, you must have innodb_file_per_table enabled. If not, all the data and index pages will sit inside ibdata1 (system tablespace). It would be anybody's guess when a table experienced an update.

You should first cleanup the InnoDB Infrastructure.

Then, the easiest way to get the timestamp is to ask the OS for it

EXAMPLE

Given this scenario

  • datadir is /var/lib/mysql
  • you want timetamp of mydb.mytable

Simply run this in the OS

TS_TAB=`ls -l --time-style="+%s" /var/lib/mysql/mydb/mytable.ibd | awk '{print $6}'`
TS_NOW=`date +%s`
(( TS_DIFF = TS_NOW - TS_TAB ))

TS_DIFF will be the number of seconds elapsed since the last time a write was posted to that table. That write can be anything (INSERT, UPDATE, DELETE, DDL).

That's as fast as I can get it for InnoDB

Give it a Try !!!

Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536