From: "Deyan Tsvetanov" <[email protected]>
Hi Adrian,
yes , my current approach is database specific. It was implemented under
pressure when I found out that ofbiz sync does not work, at least in my
setup :)
So my suggestion and idea is to implement database independent solution,
I would do it but may be in few months.
The approach suggests that we implement a triggering function in entity
engine which would allow us to do event based handling on insert /
update / delete events. So in general we say: onInsert for a specific
entity execute some java method.
Entity Engine would either do that event handling completely in it's
java code or would generate and create real database triggers, if the db
engine supports it. I don't know if there is such a feature already, I
did a very quick search in the docs and found nothing. May be
Jacques could help here ? :)
There are no such features in OFBiz.
Note that you said that MySql has no triggers but since 5.0.2 there are triggers
http://dev.mysql.com/doc/refman/5.1/en/trigger-syntax.html
Actually, most modern DBMS have triggers, the problems with them are syntax differences
http://en.wikipedia.org/wiki/Database_trigger.
We could try to use common denominator (if it exists, I did not check), but some are still using older DBMS without any such
capabilities.
Jacques, off the topic - is there an option to call native stored
procedure in the database from entity engine ? It comes very handy
sometimes ;)
No this does not exist for the same reason
http://en.wikipedia.org/wiki/Stored_procedure
Jacques
PS: I saw your answer to Adrian and your example. Yes this seems to be a real problem, I can't find a good solution so far :/ If we
could avoid to have NTP sync the machine during transactions it should not be a problem. I found
http://commons.apache.org/net/api-release/org/apache/commons/net/ntp/TimeInfo.html But there are maybe other problems you crossed
during your experiment?
As an alternative, you could query the other servers for their
current time
and generate a set of offsets to adjust the timing.
Deyan: Yes, but the clock of the main server also could run faster or
slower and in few weeks or months would eventually get synced by NTP.
That would also cause issues. Another problem is that you insert
entities with local timestamp and synchronize them using a remote one
for comparison. So you need to calculate time diff, too much logic i
think :)
For the transaction log method I couldn't think of cons. I'll send you
some example stored procedures and description, that would illustrate
the approach I use best i believe :) You might find something, i'll be
happy to discuss it :)
The goal here is to create a very reliable mechanism, that runs fast,
does not make much traffic, does not require lot of cpu and resources -
like java heap memory, recovers from interruptions reliably ( at least
logically reliably ), configures easily, troubleshoots easily ( ! ) ,
implements easily ( if possible :) and works on all databases, If
possible :)
I'll send the stuff tomorrow so we know what are we discussing :)
Cheers,
Deyan
-----Original Message-----
From: Adrian Crum <[email protected]>
Reply-to: [email protected]
To: [email protected]
Subject: Re: JUNK->Re: ofbiz entity sync.
Date: Tue, 06 Apr 2010 07:27:50 -0700
Jacques Le Roux wrote:
Hi Deyan,
Thanks for your clear explanation and suggestion. As I'm busy with another stuff, I have quickly put your comment as a quote in
OFBIZ-3333 .
There is however a major issue that CAN NOT be fixed in the current implementation: the list and sequence of entities to be
synchronized gets created by entities' timestamp - date_created_tx and last_update_tx. It works as long as the clocks of all the
syncing parties are in sync. You can easily achieve this by using NTP for example - reliable enough. But if the clock of one of
the parties gets un-synced for just few minutes, and during those few minutes records get inserted or updated than you are in
trouble. Syncing the clock back won't help you because you won't be able to sync the broken records due to foreign key constraint
issues. Examples I could give but I guess you could think of such by yourselves :)
So IMHO the best approach for synchronization is not the timestamp but the TRANSACTION LOG. This approach is used in all major
databases - m$ $ql, oracle.
The problem with that approach is that it is database-specific. As an
alternative, you could query the other servers for their current time
and generate a set of offsets to adjust the timing.
-Adrian
