Topic: Determining if a table has been updated

[kickstart]

Hi

I have googled around but can't find anything beyond using the table checksum.

What I want to do is store an object based on fairly static table data. This way it can just be serialized to store it as 99% of the time the table data will not have changed, and when brought back it can just check if it needs to update itself.

Is there a way to determine if a table has changed (ie, any records added, updated, deleted or inserted) since it was last read?

Table checksum might be useable, although it is theoretically possible that an update could result in no change to the checksum.

Checking all the rows would defeat the object of the exercise.

[mikosiko]

column Update_time in "show table status" maybe?

[kickstart]

Hi

Thanks. Unfortunately I am on a Windows system and the update time is given as NULL .

All the best

Keith

[mikosiko]

if you are using MyIsam storage engine and you have access to INFORMATION_SCHEMA.tables table you can get the last update from there... doesn't work for Innodb storage.

[PFMaBiSmAd]

I would use triggers (one each for INSERT, DELETE, and UPDATE queries) to write the datetime to a Semaphore table that you could query to find out if the table in question has been changed.

[kickstart]

Hi

mikosiko - nice idea but probably best to avoid anything that is db engine specific.

I would use triggers (one each for INSERT, DELETE, and UPDATE queries) to write the datetime to a Semaphore table that you could query to find out if the table in question has been changed.

That idea appeals to me the best so far. Minor hit on update queries, but for the situation I envisage with fairly static data this shouldn't be a major problem.

Thank you

All the best

Keith

[fenway]

Yup -- I stumbled upon this issue a while ago -- SHOW TABLE STATUS only works for MyISAM, and even then, only on Linux.  Crazy.

Right now, since I'm using a DB wrapper anyway, I just automatically update a system table (with one record per table) whenever my app issues any update/insert/delete statements.

Triggers is a technically a better way to handle this, but because you have to have 3 on each table, I just find it really annoying to maintain.

[kickstart]

Hi

What concerns me about using some kind of manual way to record the updates (such as a wrapper) is it won't take account of manual changes such as someone sorting out a problem through phpmyadmin.

I am still a bit tempted to use the check sum.

Thankyou.

All the best

Keith

[fenway]

Table checksum might be useable, although it is theoretically possible that an update could result in no change to the checksum.

Yes, but incredibly unlikely.

[RussellReal]

I was also gonna suggest triggers, I think thats 2 thumbs up for triggers

[kickstart]

Yes, but incredibly unlikely.

Very true and not critical if it does happen hence thinking of it. Just that it seems million to one chances occur 9 times out of 10.

All the best

Keith

[fenway]

I mean, the CHECKSUM algorithm is really just millions of CRC32() calls, and with a large enough table, it's unlikely to collide in hash generation.

[kickstart]

Hi

I know, just that it seems wrong. The chances of an issue are small but an easily returned date of last update would mean effectively zero chance. But a trigger means a load of extra hidden updates.

All the best

Keith

[fenway]

It's really annoying that the update_time isn't maintained on windows.

MyISAM uses file's timestamp  for Update_time that you see in SHOW TABLE STATUS. Windows
does not update timestamp of the open file (according to my experiments) even if you do
fflush().

So really, they're just using the same code on both platforms, instead of doing it properly.  Seems like an easy patch to commit.

[shlumph]

Have you considered caching the result set, and then purging the cache on inserts/deletes/updates?