Date Comparison Always Brings Back A Minimum Of 3 Rows

[peterlaursen]

You can enable logging on the server side.

insert

log = path_tomylog.log

in the [mysqld] section of the my.ini file, restart and the MySQL 'general log' will log everything.

Some ideas why this could happen:

1)

Are you sue that you do not use te > operator weher it should be >= or vice versa in your algorithm? TIMESTAMPs sare not very accurate in MySQL (do not know about MS SQL). But consider i there are records with identical TIMESTAMPs that cause this?!

2)

if you convert a TIMESTAMP to an integer, then note that an 'ordinary' (2 byte) integer is not long enough. You will need a 4-byte inger if not data shall be truncated in one end.

[intel352]

I'll try the server-side logging, thanks.

Regarding the timestamps, I've had no issue using this method with source->target syncs, other than these exact same rows constantly being sent. The timestamps are all managed locally (when a row is updated, the timestamp is modified), so the exact same timestamps are sent to the MySQL server, and are not modified automatically by MySQL.

on the MySQL server, I execute a MAX(fieldname) query to grab the latest timestamp, that is lesser-than-or-equal-to NOW(). I then use that value to query the SQL server, pulling all rows that are greater-than-or-equal-to DATE-VALUE. So all values are sent to MySQL that have a timestamp greater than the MySQL MAX date value, and primary ids are used to prevent data duplication.

I'm not actively converting the TIMESTAMP values to an integer, to my knowledge, so I don't think that's a factor here.

Again, this method works for some tables perfectly, resulting in 0 rows being sent when there are no new changes… It's very strange.

I'll post once I find out what the Logs have to reveal.

[Author]

Posts