With high hopes, I gave my customer the go-ahead to switch to their new
program today.  I thought I had tested everything thoroughly enough--I
had encountered some problems at one point, but cleared out old data
and everything seemed to running fine.

Alas, synchronization failed and the server crashed.  After lots of
searching, testing, and server restarts, I was finally able to pin it
down to a LOAD UPDATES statement on the server.

There are four remote clients, all synchronizing with a server
database.  The remote clients don't synchronize at the same time.
Further, a record created by one client can be deleted while being
updated by another (although this is very rare).  Of course, the
strangest and rarest things happened today--I guess to stress-test the
new build!

Even though I used the "IGNORE MISSING UPDATES" clause on the LOAD
UPDATES statement, I still got the following message:

"ElevateDB Error #1305 An error occurred with the statement at line 35
and column 25 (Error loading the updates for the database DCI_Master
(No row found in the table POHeaders with the primary key values of
60000108))"

Granted, I should put all updates in one directory and load them in
order they were created instead of going through each remote store and
loading all updates for that one client before moving on.  That way ALL
the updates would be loaded in the order they were created instead of
just within each client's store.  But, this new clause of the LOAD
UPDATES statement was supposed to prevent this problem.

Yes, EDB 2.01 b5 is used in the application and on the server.

--
David Cornelius
CorneliusConcepts.com

> Alas, synchronization failed and the server crashed.  After lots of
> searching, testing, and server restarts, I was finally able to pin it
> down to a LOAD UPDATES statement on the server.

I should clarify one thing.  The load updates crashed because I had
deleted a table on the server that the LOAD UPDATES was trying update
from a client's data where the table still existed.  The missed update
is not what crashed the server.

But it would be nice to have it simply raise an error in such a case
rather than crash the server!

--
David Cornelius
CorneliusConcepts.com

OK, I've got everything working by restructing the way I load updates.
Instead of loading all updates for one remote client, then moving to
the next remote client, I now move all updates from all client stores
to one main store on the server, then load them all chronologically.

The IGNORE MISSING UPDATES clause isn't even needed--as long as
everything is lined up correctly.

And I created the two tables that the old client databases had, so the
server won't crash anymore.

There is still a gnawing worry that things could blow up at any moment,
but we'll try again tomorrow with the customer.

--
David Cornelius
CorneliusConcepts.com

David,

<< Alas, synchronization failed and the server crashed.  After lots of
searching, testing, and server restarts, I was finally able to pin it down
to a LOAD UPDATES statement on the server. >>

What do you mean by "crashed" ?  Do you mean that the ElevateDB Server went
down ?

<< Even though I used the "IGNORE MISSING UPDATES" clause on the LOAD
UPDATES statement, I still got the following message: >>

See my email response.  This is a bug that has been fixed for 2.02.

--
Tim Young
Elevate Software
www.elevatesoft.com

> << Alas, synchronization failed and the server crashed.  After lots
> of searching, testing, and server restarts, I was finally able to pin
> it down to a LOAD UPDATES statement on the server. >>
>
> What do you mean by "crashed" ?  Do you mean that the ElevateDB
> Server went down ?

I had to go into the Windows Services list and restart the EDB service.
Yes, the EDB server went down.

--
David Cornelius
CorneliusConcepts.com

David,

<< I had to go into the Windows Services list and restart the EDB service.
Yes, the EDB server went down. >>

Just to keep everyone up to date on this, per my email:

Okay, here's the issue with the "lockup":

You've got a trigger defined for the OrderAckHeaders table, and it is
executing a SUM query against the OrderAckDetails with this query:

SELECT SUM(Price * Qty) FROM OrderAckDetails WHERE DocNum = ?

There are 44,000+ rows in the OrderAckDetails table, which, by itself, is
not an issue.   The real issue is that the one index available for the
DocNum column in the OrderAckDetails table is defined with the collation:

COLLATE ANSI_CI

Which means that the query can't use it, and has to execute a table scan for
the query.  So, for every insert or update coming in from the updates file
that you're loading, a table scan of 44,000+ rows has to take place.  In the
case of the update file that you were trying to load, there was over a MB of
updates for the OrderAckHeaders table, which could mean quite a few table
scans.

Changing the query to this:

SELECT SUM(Price * Qty) FROM OrderAckDetails WHERE DocNum COLLATE ANSI_CI =
?

in both triggers for the OrderAckHeaders table will fix the problem for that
table.  However, I notice that you do similar types of queries for the
InvoiceHeaders table, etc., so you'll need to fix them also.

Tim Young
Elevate Software
www.elevatesoft.com

> Okay, here's the issue with the "lockup":
>
> ...
>
> Changing the query to this:
>
> SELECT SUM(Price * Qty) FROM OrderAckDetails WHERE DocNum COLLATE
> ANSI_CI = ?
>
> in both triggers for the OrderAckHeaders table will fix the problem
> for that table.  However, I notice that you do similar types of
> queries for the InvoiceHeaders table, etc., so you'll need to fix
> them also.

Wow--I hadn't thought about the fact that it was locking up because it
was busy processing a slow query.  This isn't the first time a trigger
which I had forgotten about has tripped me up.

Thank you SO MUCH, Tim, for looking into this and explaining what I
need to do.  I will learn all about collation and rework some triggers
and other scripts this weekend.

--
David Cornelius
CorneliusConcepts.com

David,

<< Wow--I hadn't thought about the fact that it was locking up because it
was busy processing a slow query.  This isn't the first time a trigger which
I had forgotten about has tripped me up.

Thank you SO MUCH, Tim, for looking into this and explaining what I need to
do.  I will learn all about collation and rework some triggers and other
scripts this weekend. >>

No problem.  Let me know if you see any other issues.  BTW, sometimes it is
easier to just simply define the column with a collation of ANSI_CI in the
cases where you always want comparisons to be case-insensitive.  By doing
this, all indexes automatically pick up the collation, and you don't need to
use the literal COLLATE clause in your WHERE clause conditions.  See here
for more information:

http://www.elevatesoft.com/articles?action=view&articleno=2

--
Tim Young
Elevate Software
www.elevatesoft.com

> No problem.  Let me know if you see any other issues.  BTW, sometimes
> it is easier to just simply define the column with a collation of
> ANSI_CI in the cases where you always want comparisons to be
> case-insensitive.  By doing this, all indexes automatically pick up
> the collation, and you don't need to use the literal COLLATE clause
> in your WHERE clause conditions.  See here for more information:
>
> http://www.elevatesoft.com/articles?action=view&articleno=2

Thanks for that--I definitely don't want to have to clutter up the
WHERE clauses.  I'll let you know how my testing goes this weekend.

--
David Cornelius
CorneliusConcepts.com

It's working!  IT'S WORKING!

I'm so happy--and my customer is too!

I decided to modify the WHERE clause in the triggers after all.  But
did some testing and reading of the manual (what a concept!) and
learned a lot about collation and constraints.  In fact, I put a short
"knowledgebase posting" on this subject in the SQL group to help this
stick in my head.

Tim, thanks again for your awesome extra-mile help on this!



--
David Cornelius
CorneliusConcepts.com