You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@ofbiz.apache.org by Jacques Le Roux <ja...@les7arts.com> on 2010/04/06 12:31:49 UTC

Re: ofbiz entity sync.

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 .

Jacques

  ----- Original Message ----- 
  From: Deyan Tsvetanov 
  To: Jacques Le Roux 
  Cc: Hans Bakker ; marc@emforium.com 
  Sent: Tuesday, April 06, 2010 9:42 AM
  Subject: Re: ofbiz entity sync.

  Hi Sirs, 

  I'm sorry, i got lost for some time :)
  Things happen :)

  There was a lot of discussion on the topic, but the summary: 

  there are some major issues that CAN be fixed in the current implementation: error recovery - due to interrupted connection between the two servers, sync speed - RMI can be removed , etc. 

  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. 

  For a customer I've implemented a transaction log based on triggers and stored procedures. The transaction log, triggers and the stored procedures however I implemented only postgresql as that was the customer's database. It's easy to implement ms sql or oracle version though. It works perfectly, much much much faster than RMI, recovers if the sync process is interrupted , etc. My goal was to implement this mechanism using entity engine level triggers and eventually commit it, but unfortunately still pretty busy with other things so we don't have resources that can be dedicated to work on that task at the moment, we're trying to work out the world financial crisis :)

  So if you find what i say reasonable you could go ahead with the triggers and SPs. For that you need database that supports triggers - so mysql won't work :)

  That was just the first part. 

  The second part is to identify all the tables that you need to synchronize. Some of them will be only pulled, some of them  pushed only and some of them synced in both directions. Next you need to test, reset the database and  test again and again until you identify the correct list of the tables so your sync process doesn't end up with FK  insert / update errors. That is pretty easy but time consuming task - it takes few days to complete :)

  So that's all I can say for now, without getting your bored with details :)
  If you have other questions - go ahead :)

  Cheers, 
  Deyan

  -----Original Message-----
  From: Jacques Le Roux <ja...@les7arts.com>
  Reply-to: "Jacques Le Roux" <ja...@les7arts.com>
  To: Hans Bakker <h....@antwebsystems.com>, deyan.tsvetanov@ittconsult.com, marc@emforium.com
  Subject: Re: ofbiz entity sync.
  Date: Sat, 3 Apr 2010 10:04:29 +0200

Hi Hans,

I put a comment with an abstract of the situation. Please let me know what you think to get the ball rolling

Thanks

Jacques

From: "Hans Bakker" <h....@antwebsystems.com>
> Hi Gentleman,
> 
> may i ask if there was any progress on this issue?
> 
> https://issues.apache.org/jira/browse/OFBIZ-3333
> 
> I added the following comment:
> 
> We have a customer using entity sync and experiencing the problems in
> this issue.
> 
> We are interested working with other parties on this subject. If there
> is already substantial work done we are interested to compensate
> financially for it.
> 
> Please let me know if there is any progress here.
> 
> regards,
> Hans
> 
> -- 
> http://www.antwebsystems.com : 
> Quality OFBiz support for competitive rates....
> 
>

Re: ofbiz entity sync.

Posted by Adrian Crum <ad...@hlmksw.com>.

Deyan Tsvetanov wrote:
> Here is an example: 
> On server A ( the one that pushes data ) we insert the following records
> with the following sequence and timestamps: 
> 
> PARTY : party_id: 1000 , created_timestamp_tx : 6-APR -2010
> 20:00:00.010    
> PARTY_GROUP : party_group_id : 1000 ( FK to PARTY.party_id ) ,
> created_timestamp_tx: 6-APR-2010 20:00:00:013  - three  milliseconds
> later. 
> 
> That would the the database contents without issues. 
> Now imagine we do a NTP time synchronization between the two inserts.
> The NTP sync "corrects" the system clock 5 ms back. The database
> contents now would be: 
> 
> PARTY : party_id: 1000 , created_timestamp_tx : 6-APR -2010
> 20:00:00.010    
> PARTY_GROUP : party_group_id : 1000 ( FK to PARTY.party_id ) ,
> created_timestamp_tx: 6-APR-2010 20:00:00:008  - earlier than the PARTY
> record. 
> 
> When the server queries the records to be sent for synchronization the
> result will be: 
> 
> PARTY_GROUP : party_group_id : 1000 ( FK to PARTY.party_id ) ,
> created_timestamp_tx: 6-APR-2010 20:00:00:008 
> PARTY : party_id: 1000 , created_timestamp_tx : 6-APR -2010
> 20:00:00.010    
> 
> The server that accepts the push sync will try to insert first the
> PARTY_GROUP record and will get a FK constraint error - the PARTY record
> is missing. 

Okay, that's a different problem than the one I was imagining.

Re: JUNK->Re: ofbiz entity sync.

Posted by Deyan Tsvetanov <de...@ittconsult.com>.

Here is an example: 
On server A ( the one that pushes data ) we insert the following records
with the following sequence and timestamps: 

PARTY : party_id: 1000 , created_timestamp_tx : 6-APR -2010
20:00:00.010    
PARTY_GROUP : party_group_id : 1000 ( FK to PARTY.party_id ) ,
created_timestamp_tx: 6-APR-2010 20:00:00:013  - three  milliseconds
later. 

That would the the database contents without issues. 
Now imagine we do a NTP time synchronization between the two inserts.
The NTP sync "corrects" the system clock 5 ms back. The database
contents now would be: 

PARTY : party_id: 1000 , created_timestamp_tx : 6-APR -2010
20:00:00.010    
PARTY_GROUP : party_group_id : 1000 ( FK to PARTY.party_id ) ,
created_timestamp_tx: 6-APR-2010 20:00:00:008  - earlier than the PARTY
record. 

When the server queries the records to be sent for synchronization the
result will be: 

PARTY_GROUP : party_group_id : 1000 ( FK to PARTY.party_id ) ,
created_timestamp_tx: 6-APR-2010 20:00:00:008 
PARTY : party_id: 1000 , created_timestamp_tx : 6-APR -2010
20:00:00.010    

The server that accepts the push sync will try to insert first the
PARTY_GROUP record and will get a FK constraint error - the PARTY record
is missing. 

And that is just an easy example - with parties and party groups. The
examples with invoices, orders, items and last but not least:
acctg_trans and acctg_trans_entry records are much more complex to
recover from. 

So that is my concern actually. I experienced it, also it's pretty easy
to verify :)

-- deyan

-----Original Message-----
From: Adrian Crum <ad...@hlmksw.com>
Reply-to: user@ofbiz.apache.org
To: user@ofbiz.apache.org
Subject: Re: JUNK->Re: ofbiz entity sync.
Date: Tue, 06 Apr 2010 10:48:28 -0700


Deyan Tsvetanov wrote:
> 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 ? :)
> 
> Jacques, off the topic - is there an option to call native stored
> procedure in the database from entity engine ? It comes very handy
> sometimes ;) 
> 
>> 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 :)

I might be wrong, but the only issue I see with servers time drifting is 
when to trigger an update. I'm not an expert on entity sync, but from 
what I understand, timestamps are compared to see if server B's data 
changed since the last sync. So, you query server B for its current 
time, calculate an offset from your time, and adjust the query's timestamp.

-Adrian

Re: JUNK->Re: ofbiz entity sync.

Posted by Adrian Crum <ad...@hlmksw.com>.

Deyan Tsvetanov wrote:
> 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 ? :)
> 
> Jacques, off the topic - is there an option to call native stored
> procedure in the database from entity engine ? It comes very handy
> sometimes ;) 
> 
>> 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 :)

I might be wrong, but the only issue I see with servers time drifting is 
when to trigger an update. I'm not an expert on entity sync, but from 
what I understand, timestamps are compared to see if server B's data 
changed since the last sync. So, you query server B for its current 
time, calculate an offset from your time, and adjust the query's timestamp.

-Adrian

Re: JUNK->Re: ofbiz entity sync.

Posted by David E Jones <de...@me.com>.

On Apr 7, 2010, at 2:04 AM, Deyan Tsvetanov wrote:

> 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?
> 
> [Deyan] dealing with timestamps is difficult. 
> 
> In the end of the day the question is: why do we need created_timestamp_tx and last_update_tx for all entities / db tables ?
> Answer: we need them in order to generate a list of entities ( database records ) to be pushed / pulled and that list must be in the correct order 
> so we avoid FK or PK constraint issues. 

Actually this is NOT the purpose of the timestamps. The EntitySync stuff does use these to recreate the order as much as possible, but there is no way to solve problems with FKs when you have circular references or something is changed multiple times between syncs. If you're relying on this to solve FK problems you're going to have issues, guaranteed. The dummy-fks feature is what is meant to solve the FK problem.

-David

Re: JUNK->Re: ofbiz entity sync.

Posted by Deyan Tsvetanov <de...@ittconsult.com>.

-----Original Message-----
From: Jacques Le Roux <ja...@les7arts.com>
Reply-to: "Jacques Le Roux" <ja...@les7arts.com>
To: user@ofbiz.apache.org
Subject: Re: JUNK->Re: ofbiz entity sync.
Date: Tue, 6 Apr 2010 21:30:24 +0200

From: "Deyan Tsvetanov" <de...@ittconsult.com>
> 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.

[Deyan] Well yes, the triggers are database specific, but isn't what entity engine is about to provide: unified database
access layer ? Triggers are also an almost universal database feature. They could be very useful, especially for 
extra security in ofbiz, synchronization, integration with 3rd party systems or home grown software like most of companies have. 
Most of the databases have it, for mysql we have to increase the minimum required version to 5. It's not a pain. 
At some point we might have the question: so mysql does not support things that all others do support. What do we do: do we implement the missing 
features in entity engine's java code ? How much effort will it take ? Why do we have to implement stuff that is already provided by many other products 
for free and in a good quality ?

In the end of the day the goal is to provide lot of good features, erp, enterprise class features for a low cost to users, customers, companies.
Providing both options - open source and commercial database engines support is required and very good, keeps TCO down. But if some database isn't 
improving as fast as needed than it shouldn't not be a problem to drop the support for it. Mysql is targeted to different types of customers and users who
have different requirements than ours - like web developers, php, jsp, perl, whatever. 

So I say for now we implement the entity events / triggers in java, for the purpose of ofbiz synchronization it will be enough. We don't need 
native db triggers yet. 

> 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

[Deyan]
Why do we need native stored procedures ?

OFBiz database is complex. Its more flexible and more complex than the database of a typical web site. Customers want reports. For reports 
we use tools like BIRT, Pentaho, etc. For reports calculations and functions are needed, often not supported by entity engine. SUM and GROUP_BY
sometimes are not enough.For reports the grouping and calculations need to happen in the database engine as much as possible. If you let the BI tools to 
 iterate result sets in a JVM then you're in trouble with "memory issues" and limits :)
SPs are also convenient when doing complex database queries from a complex search GUI screen. 

So here is where it comes in hand to be able to call native DB stored procedures.
Entity engine would have to generate db specific code, either "call sp_select_average_sales()"  or "SELECT * FROM sp_select_average_sales()".
It's not hard, it's the purpose of entity engine actually. 
And then code the stored procedures for all engines you like to support.

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?

[Deyan] dealing with timestamps is difficult. 

In the end of the day the question is: why do we need created_timestamp_tx and last_update_tx for all entities / db tables ?
Answer: we need them in order to generate a list of entities ( database records ) to be pushed / pulled and that list must be in the correct order 
so we avoid FK or PK constraint issues. 

Well there is another way to generate that list without dealing with timestamps and time offsets at all. 
We can generate that list on the fly - in real time - using triggers which upon execution create an entry in a transaction log. This approach 
would guarantee that if a PARTY is created before a PARTY_GROUP in the real life - the same sequence of actions will be appended to the transaction log. 
I'll send you some examples a bit later today. 

-- deyan

>> 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 <ad...@hlmksw.com>
> Reply-to: user@ofbiz.apache.org
> To: user@ofbiz.apache.org
> 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
>
>
>

Re: JUNK->Re: ofbiz entity sync.

Posted by Jacques Le Roux <ja...@les7arts.com>.

From: "Deyan Tsvetanov" <de...@ittconsult.com>
> 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 <ad...@hlmksw.com>
> Reply-to: user@ofbiz.apache.org
> To: user@ofbiz.apache.org
> 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
>
>
>

Re: JUNK->Re: ofbiz entity sync.

Posted by Deyan Tsvetanov <de...@ittconsult.com>.

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 ? :)

Jacques, off the topic - is there an option to call native stored
procedure in the database from entity engine ? It comes very handy
sometimes ;) 

> 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 <ad...@hlmksw.com>
Reply-to: user@ofbiz.apache.org
To: user@ofbiz.apache.org
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