Date Comparison Always Brings Back A Minimum Of 3 Rows

[intel352]

Okay, with the help of the SQLYog team, I've been able to setup several nice automated scripts for synchronizing my local MSSQL server with my remote MySQL server. (one-way sync, MSSQL to MySQL)

With SQLYog & some vbscript, I determine which rows need to be sent from each table, by finding the most recent *REMOTE* row, and use that value to pull up newer *LOCAL* rows, to be sent to the remote location.

This method is working great, every table is sending up any new data as it should, but the problem is that even when there are no new changes to send, there are still a few rows that are being sent, every time.

I.E.- when there are no changes:

Table1 sends 0 rows

Table2 sends 3 rows

Table3 sends 6 rows

Table4 sends 0 rows

And this situation occurs repeatedly. Theoretically, each time I run the jobfiles, the tables should be synced up completely, and 0 rows should be sent for each table thereafter. But this is not occurring

Is there any way to log the exact queries & values being sent/received?

I've tried the same queries on MS Access to the SQL server, but it returns 0 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