Find new rows in database

Question

I am looking for a way to know which rows in a database (mysql) are new (or updated) in order to fetch just those from the server and store locally in an application (client).

My initial thought was to add a timestamp to each row and have my application remember the time it last got an update, however I am worried of the server clock changing backwards (e.g. when going from summer to winter time) or a client updating while a transaction is in progress on the server -> example

Apart from time stamps (quite obvious and apparently common idea), is there a recommended best practice for these kinds of things?

Answer 1

Many databases use a timestamp for concurrency checking, so that would be the natural (and correct IMO) way to do it. Especially if there is already a timestamp field in use for concurrency checking.

During DST changes, you need a way to invalidate your records cache on the client, so that it will reload all of the records after the time change.

Or, as Daniel points out, just use UTC for the timestamp, and then you don't have to worry about time zone changes at all.

Answer 2

Other ideas:

• for data that can only be inserted, an increasing id (e.g. generated by a sequence or similar concept) is sufficient - all you'd have to remember is the last id you copied
• this can be extended by a column in each table that gets updated from a sequence whenever it is inserted or updated (set by a trigger)
• instead of a column for each table it may be easier to have a central log table that collects the similar information (tricky to identify the releavant rows if you have composite keys)

Unless you have a good reason to work on such a scheme, I'd be very careful. This type of synchronisation may run into a number of tricky problems that may be harder to solve than they seem to be.

Answer 3

Instead of a timestamp, put an autoincrement column in your table, and store the currently-largest value of that column. When you need to update, do your select on auto_column>my_largest_value.

Answer 4

Take a look at the Timestamp column type -- it automatically updates whenever you create or alter a record.

Time zone should not be an issue depending on how you query against it. It stores the value as UTC which has no daylight savings time.

Answer 5

Since there is no native Change Data Capture in MySQL, you can implement INSERT and UPDATE and DELETE triggers to capture what you need in an audit table and pull the data from there. Sort of a poor-man's replication technique.