Sync databases

[Eddy Respondek]

This may be a little off topic but maybe someone has done something similar before.

Basically I have a separate Wordpress site (php/mysql) which I've been extending significantly and I've setup another server on the same network for graph db testing (neo4j/tinkerpop/python-bulbs). I'm confident with my graph setup now and would like to attempt to get something small into development so I can monitor the results. I want to do a simple "like" relationship between users and articles.

That means I need to keep an identical index of user ids and article ids in the graph db. I know how to update the id's when a new user or article is created, deleted, etc. What I don't know is the correct way to ensure data integrity in case something goes wrong like the graph db server crashes, etc.

Does anyone have any thoughts on the best way to do this?

[Marko Rodriguez]

Hey Eddy,

Someone might be able to help you here, but the guy who will give you the two page rattle on such matters is Michael Hunger on the Neo4j users list. I've read him talking about similar things --- "cross db transactions"-style stuff.

You might want to post your thoughts to that list.

Marko.

http://markorodriguez.com

[James Thornton]

Hi Eddy -

You should definitely post this question to the Neo4j list as well because I would be interested in Michael's ideas on this.

One approach would be to use a message-passing library like ZeroMQ (http://www.zeromq.org/) to set up a communication channel between PHP and Python. This will allow you to write to both MySQL and Neo4j when you create a new user.

ZeroMQ is stupid fast. You can send millions of requests per second (http://www.zeromq.org/results:10gbe-tests-v031), and it supports pub/sub and muticast so you can write to multiple devices/programs at once (http://zguide.zeromq.org/page:all).  

Here are the PHP and Python bindings:

* http://www.zeromq.org/bindings:php

* http://www.zeromq.org/bindings:python

JSON is probably the easiest way to serialize data, or you could use a binary serialization library like MessagePack. 

I'm working on creating a batch loader for Bulbs that uses ZeroMQ to send requests to a Java/Jython server running the Neo4jBatchGraph implementation Marko just added (https://groups.google.com/d/topic/gremlin-users/muuylAEZKrQ/discussionZeroMQ) -- I'll post an example soon.

- James