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?

[RussellReal]

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

 

seems to only need 1 hidden update :)I doubt it would be any less effective than if it updated it the way it should on windows.. the only difference is it will make the script you're writing less portable.. but thats not a huge problem.

[kickstart]

Hi

 

I am doing this to create a cached php object. Once created the cached object is pretty fast and efficient, but don't want to create thousands of times on an update script without using the object.

 

The processing that updates the table does a massive number of inserts and updates, hence extra updates on a trigger might well make a fair impact on that (php on Windows includes db times in the limit of run times, but not sure if a triggered update would be included in this).

 

All the best

 

Keith

[shlumph]

I'm not sure how your code is currently set up, or if I'm fully understanding what you're trying to do, but it might be worth the extra effort to set up a unit of work pattern in there:

 

http://css.dzone.com/books/practical-php-patterns-unit

 

Basically, after operating on multiple objects multiple times in the same request, you will only persist the "dirty" objects, the ones that have changed. Sometimes, you'll change the object and it'll end up being the same as it was - so it's not needed to be persisted.

 

Hope this is on the right track of what you're looking for.

[mikosiko]

.... But a trigger means a load of extra hidden updates.

 

Keith

 

and?.... what is the problem with that Keith?.... I've DB's with a lot.. and I mean ... a lot.... of triggers/stored procedures and never I have had any problem with that... works like a charm for me... but again that is JMHO.

 

Miko

[kickstart]

I'm not sure how your code is currently set up, or if I'm fully understanding what you're trying to do, but it might be worth the extra effort to set up a unit of work pattern in there:

 

This is an extract from a massive number of Access databases, ~1200 of them (I take no responsibility for that bit!). With a regular extract taken of the latest info per month (where there is data for that month) to a MySQL database. The data is then extracted to provide a month by month report (cross joined with a table of months) and also a couple of different very high level reports. The data extracted is for the last 18 months of so, but data going back months can still be updated (around half a million records inserted / updated on one full extract).

 

As such the object will only change occasionally (but when it does there are a very large number of inserts / updates), while the data is used many times (ie, paging, different people using it, etc). When it does change it will change a fair amount but not consistently in any area.

 

and?.... what is the problem with that Keith?.... I've DB's with a lot.. and I mean ... a lot.... of triggers/stored procedures and never I have had any problem with that... works like a charm for me... but again that is JMHO.

 

Will have a play, but it is running under windows and I am already pushing the timing a bit on the extracts. Having half a million extra background inserts / updates does seem a bit wasteful.

 

All the best

 

Keith