469,608 Members | 2,134 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,608 developers. It's quick & easy.

**Checking For Modified Rows**

Hi.

I need to replicate data (approx. 10,000 records) from a mySQL database into
a different (non mySQl) database automatically on a regular basis. I have no
control over the mySQL server (apart from allowing ODBC access) so I cannot
modify any tables or enable replication etc...

My initial thoughts on how to do this are:

1. Execute a query similar to the following to retrieve a list of rows and a
unique checksum:
SELECT id, md5(id + "_" + name + "_" + path + "_" + comments) from mytable
2. Lookup each id in my local database
a) If it doesn't exist add the id and checksum to my local database and add
the record into my actions database as a record to replicate.

b) If it exists and the checksums are different update my local database and
add the record into my actions database as a record to replicate.

REPEAT step 2 for all records

At this point the actions database would contain a list of all records which
have been inserted or modified. The last step would be to determine which
records have been deleted. My thoughts where to mark each record in my local
database when a match occurred. Any records in my local database which were
not marked as matched would be added to the actions database as records to
delete and then deleted from my local database.
Any thoughts / feedback on this would be appreciated.
Cheers.
Kev.

E-MAIL DISCLAIMER: The information in this email is confidential and may be legally privileged. It is intended solely for the addressee. Access to this email by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful. All information provided, including but not limited to, quotations, system specifications and suggestions concerning hardware/software (and services) configurations are strictly subject to our standard terms and conditions of business, copies of which are available on request.

__________________________________________________ ______________________
This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
__________________________________________________ ______________________

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw

Jul 19 '05 #1
0 1346

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Kevin Gale | last post: by
reply views Thread by guiromero | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.