Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 http://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.

share|improve this question

1 Answer 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 !!!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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