Live Synchronization of a Mysql Database and a Neo4j Database ?

[Christophe L]

Hello,

We would like to keep synchronize in live a MySql database (in PHP)
and a Neo4j database, but using the Neo4j REST API (using Neo4jPHP).

In the SQL database, the node id of the node matching the SQL data is
stored.
In the Neo4j database, the database id of the SQL record matching the
node is stored.

So, we can see two possible scenarios:
1)

try
a) start db transaction
b) insert the data in SQL and retrieve the id for the inserted
record
c) insert the node in Neo4j through REST ( with the SQL id as
attribute) and retrieve the node id for the inserted node
d) update the SQL record with the node id
e) commit db transaction
catch
f) rollback db transaction if started
g) remove Neo4j node through REST if inserted

2)

try
a) insert the node in Neo4j through REST and retrieve the node id
for the inserted node
b) start db transaction
c) insert the data in SQL ( with the node id as column parameter)
and retrieve the id for the inserted record
d) update the node in Neo4j through REST with the SQL id
e) commit db transaction
catch
f) rollback db transaction if started
g) remove Neo4j node through REST if inserted

For the moment, we choose scenario 1, because we think it is faster to
do an insert + an update to a DB rather than 2 REST calls but do you
see better ways to achieve this synchronization ? a more safer / more
transactional way to do it ?

Thanks in advance for your answers

Best Regards,
Christophe

[Christophe L]

Hello,

Sorry to bother you once again with this question, but we would really
appreciate your opinion on this subject.
I will simplify the question:
What is the best way to keep synchronized a SQL database with a Neo4J
database using REST API according to you please ?
Since there is no transaction concept on the REST API, could you tell
us what is the best approach for fullfing this requirement please ?

Thanks in advance for your answers.

Best regards,
Christophe

[Peter Neubauer]

Mmh,
it is true that there is no transaction concept with REST. I could
imagine a scenario where you have a Neo4j Server and an Embedded Java
instance in a HA cluster.

Putting the Embedded Neo4j and the SQL RDBMS into one distributed
transaction would get you very close to a synched updating of both
datasources. That way, you can synch via real transactions, and have
the Neo4j Server instance(s) server you REST API.

Otherwise, I know many users are using message queues like Redis,
RabbitMQ and others to propagate mutating events to both the RDBMS and
Neo4j, but that is of course not transactional.

HTH

Cheers,

/peter neubauer

G:  neubauer.peter
S:  peter.neubauer
P:  +46 704 106975
L:   http://www.linkedin.com/in/neubauer
T:   @peterneubauer

Neo4j                                - Graphs rule.
Program or be programmed - Computer Literacy for kids.
http://foocafe.org/#CoderDojo

[Christophe L]

Thanks for your answer !

About the distributed transaction, since our application is in PHP,
that would mean moving the data persistence layer ( or at least part
of it) to java, and the project leader would prefer not to mix java
and php.
About the messages queues, I thought about it ( we are working with
Opscode Chef which is using message queue for indexing of data in
solr), but this would mean adding another component to the
application, and the hosting would prefer to avoid that.

So both are good proposals, but can't be done in our project for non-
technical reasons :(

If you have any other ideas, you are most welcome :)

Or is there a planned feature that could help us so we can plan to
have transactional behavior in the future ?

Thank you very much.

Best regards,
Christophe

On 4 avr, 00:26, Peter Neubauer <peter.neuba...@neotechnology.com>
wrote:

> Mmh,
> it is true that there is no transaction concept with REST. I could
> imagine a scenario where you have a Neo4j Server and an Embedded Java
> instance in a HA cluster.
>
> Putting the Embedded Neo4j and the SQL RDBMS into one distributed
> transaction would get you very close to a synched updating of both
> datasources. That way, you can synch via real transactions, and have
> the Neo4j Server instance(s) server you REST API.
>
> Otherwise, I know many users are using message queues like Redis,
> RabbitMQ and others to propagate mutating events to both the RDBMS and
> Neo4j, but that is of course not transactional.
>
> HTH
>
> Cheers,
>
> /peter neubauer
>
> G:  neubauer.peter
> S:  peter.neubauer
> P:  +46 704 106975
> L:   http://www.linkedin.com/in/neubauer
> T:   @peterneubauer
>
> Neo4j                                - Graphs rule.

> Program or be programmed - Computer Literacy for kids.http://foocafe.org/#CoderDojo

[simo]

Hi Christophe,

I'm really not a Neo4j expert, but you may consider batch operations via the REST API.

http://docs.neo4j.org/chunked/1.7.M02/rest-api-batch-ops.html

We're currently using them to fill up our neo4j db.

Simona