CODEWITHSUNDEEP
mysql
averageman
averageman

Reputation: 923

Detecting database change

I have a database intensive application that needs to run every couple hours. Is there a way to detect whether a given table has changed since the last time this application ran?

Upvotes: 4

Views: 13478

Answers (6)

dev.meghraj
dev.meghraj

Reputation: 9110

The most efficient way to detect changes is this.

CHECKSUM TABLE tableName

Upvotes: 17

user3833268
user3833268

Reputation: 1

Have used CHECKSUM TABLE tablename and that works just splendid.

Am calling it from an AJAX request to check for table updates. If changes are found a screen refresh is performed.

For database "myMVC" and table "detail" it returns one row with fields "table" and "Checksum" set to "mymvc.detail" and "521719307" respectively.

Upvotes: -1

Jonathan Hall
Jonathan Hall

Reputation: 79536

A common way to detect changes to a table between runs is with a query like this:

SELECT COUNT(*),MAX(t) FROM table;

But for this to work, a few assumptions must be true about your table:

  1. The t column has a default value of NOW()
  2. There is a trigger that runs on UPDATE and always sets the t column to NOW().

Any normal changes made to the table will then cause the output of the above query to change:

There are a few race conditions that can make this sort of check not work in some instances.

Upvotes: 0

georgepsarakis
georgepsarakis

Reputation: 1957

A couple of questions:

  1. Which OS are you working on?
  2. Which storage engine are you using?

The command [http://dev.mysql.com/doc/refman/5.5/en/show-table-status.html](SHOW TABLE STATUS) can display some info depending on storage engine though.

It also depends on how large is the interval between runs of your intensive operation.

The most precise way I believe is with the use of triggers (AFTER INSERT/UPDATE) as @Neuticle mentioned, and just store the CURRENT_TIMESTAMP next to the table name.

CREATE TABLE table_versions(
  table_name VARCHAR(50) NOT NULL PRIMARY KEY, 
  version TIMESTAMP NOT NULL
);

CREATE TRIGGER table_1_version_insert AFTER INSERT
ON table_1
FOR EACH ROW
BEGIN
  REPLACE INTO table_versions VALUES('table_1', CURRENT_TIMESTAMP);
END

Upvotes: 1

Pete B.
Pete B.

Reputation: 3276

Use timestamp. Depending upon your needs you can set it to update on new rows, or just changes to existing rows. Go here to see a reference:

http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html

Upvotes: 0

Adam
Adam

Reputation: 802

Could you set a trigger on the tables you want to track to add to a log table on insert? If that would work you only have to read the log tables on each run.

Upvotes: 0

Related Questions