Odbc -> Mysql, Deleting Old Rows From Destination

[peterlaursen]

Quote:

The source ODBC table often has rows removed, which must be removed from the destintation MYSQL table.

yes! we are aware of this. We also have discussed if we can do something about this. But priorities are so that we will only look into this after the MySQL Users Conference (end of April 2007).

[terbonium]

peterlaursen wrote on Dec 12 2006, 12:07 AM:

yes! we are aware of this. We also have discussed if we can do something about this. But priorities are so that we will only look into this after the MySQL Users Conference (end of April 2007).

okay.. well in the mean time is there a fix? is there a way to run a post or pre sql command after the sync is complete? actually, never mind, maybe I'll just do a ODBC->MySQL copy up to the server, then just right my own little script to sync the two tables within mysql. OR.. WAIT, how about this.. could I run one job to copy from the ODBC source to a temporary mysql table, then use the MySQL<->MySQL sync functions to merge the changes between the temp table and the “live” mysql table..

[terbonium]

peterlaursen wrote on Dec 12 2006, 12:07 AM:

yes! we are aware of this. We also have discussed if we can do something about this. But priorities are so that we will only look into this after the MySQL Users Conference (end of April 2007).

IT WORKS!

Created a local mySQL database on the server which hosts the ODBC data source.

The sync process runs from a bat file as follows:

1. Delete all the rows in the local mySQL table

2. Copy the ODBC source table to the local mySQL table

3. Sync the local mySQL table with the mySQL table on the internet

DONE!

It all happens in less that 10 seconds. The local ODBC copy is very fast. The mySQL->mySQL sync is also very fast, and it is able to catch deleted rows from the local source mysql table as well as any changes or additions.

[Author]

Posts