By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,190 Members | 1,503 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,190 IT Pros & Developers. It's quick & easy.

**Checking For Modified Rows**

P: n/a
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
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.