I was very happy today to get a customer working live from local databases,
replicating across the internet to a server.  However, the hourly
synchronization job stopped half-way through the day.  So I ran the script
manually to see what might be going on (I'll have to add in some error
handling and logging).

It turns out that I gleefully replicated the entire database instead of
listing specific tables.  But I thought I wanted to replicate everything.
Well, everything except temp tables--oops!  I forgot about those.  Usually,
they're empty because, well, they're just temporary holding tanks.

So now I have a problem:  Four people have been using this program and and
hitting the Synchronize button several times today, hoping to see each
other's data.  There are several update packets waiting for loading on the
server, but attempting to load them chokes with the following error:

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 TempDetails with the primary key values of 46))

(The statement at line 35 is EXECUTE IMMEDIATE 'LOAD UPDATES FOR DATABASE
"DCI_Master" FROM ...')


Crud.  Should I UNPUBLISH each client's local database, then re-publish with
all tables except the temp tables?  Probably, but I can't go back in time to
do that and there are records that need to be loaded.  Can I do something
like create records in the server's copy of the temp tables so that the
replication works (I already made one futile attempt)?

--
David Cornelius
CorneliusConcepts.com

> So now I have a problem:  Four people have been using this program
> and and hitting the Synchronize button several times today, hoping to
> see each other's data.  There are several update packets waiting for
> loading on the server, but attempting to load them chokes with the
> following error:
>
> 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 TempDetails with the primary key values of
> 46))

It's not just the "TempDetails" table.  After putting in some error
trapping, it went on to load other pending updates and received a
similar error in another table:

No row found in the table POHeaders with the primary key values of
30000001.

I used a similar strategy that was employed in the Replication Demo on
the Elevate Soft home page: after the remote databases get restored
from the master, a script runs to restart their autoinc values to be a
unique starting sequence from all other staff members to avoid primary
key violation when replicated back on the server.  I did NOT do it on
the TempDetails table, but DID on the POHeaders table.

So the TempDetails has low, possibly colliding ID numbers, but should
always be empty (records are always deleted after being used).

The error message above makes me wonder about the following scenario:
If a user on a remote database creates a record, then deletes it, how
does the replication work when updates are loaded on the server?  Does
it try to apply all changes in the same order they were originally
executed?  For example would it 1) INSERT a new record, then 2) DELETE
the record?  Or does it only look at the last change--DELETE, which in
the case of a newly created record being immediately deleted, wouldn't
exist at all at the server?

This temporary table I'm using is certainly a case for this and I admit
I should not have published it.  But the POHeaders table must be
published.  And the users of this application sometimes do strange
things--like delete records they just created, possibly because they
realize they entered a wrong PO or they entered too much wrong
information and (to them) it's just easier to start over.  Perhaps I
should never actually delete records, but just deactivate and hide them?


Ugh.  What a mess.  How do I clean this up?

--
David Cornelius
CorneliusConcepts.com

David,

<< The error message above makes me wonder about the following scenario: If
a user on a remote database creates a record, then deletes it, how does the
replication work when updates are loaded on the server?  Does it try to
apply all changes in the same order they were originally executed?  For
example would it 1) INSERT a new record, then 2) DELETE the record? >>

Yes.  It has to do it this way because it has no idea what other operations
may have taken place in-between the insert and delete.

<< This temporary table I'm using is certainly a case for this and I admit I
should not have published it.  But the POHeaders table must be published.
And the users of this application sometimes do strange things--like delete
records they just created, possibly because they realize they entered a
wrong PO or they entered too much wrong
information and (to them) it's just easier to start over.  Perhaps I should
never actually delete records, but just deactivate and hide them?

Ugh.  What a mess.  How do I clean this up? >>

Unfortunately you're going to probably have to do some manual updates to get
things back where they should be.  After a SAVE UPDATES, the published
tables will not having updates in their logs, so they'll be clean.  But the
satellites that are synchronizing will have to be brought up to date
manually if you can't load the outstanding update files due to the issues
with primary key constraint violations.

--
Tim Young
Elevate Software
www.elevatesoft.com

> Unfortunately you're going to probably have to do some manual updates to
> get things back where they should be.  After a SAVE UPDATES, the published
> tables will not having updates in their logs, so they'll be clean.  But
> the satellites that are synchronizing will have to be brought up to date
> manually if you can't load the outstanding update files due to the issues
> with primary key constraint violations.


OK, so there's very few records this affects so far--and I'll have to put
some advanced features in the application to allow them to fix this.

My big worry now is that why did these tables get messed up in the first
place?  I can understand the TempDetails because there was no RESTART on the
autoinc values, so there's bound to be clashes.  But not on the POHeaders
table.

--
David Cornelius
CorneliusConcepts.com

David,

<< My big worry now is that why did these tables get messed up in the first
place?  I can understand the TempDetails because there was no RESTART on the
autoinc values, so there's bound to be clashes.  But not on the POHeaders
table. >>

Your error message was this:

"No row found in the table POHeaders with the primary key values of
30000001."

Which indicates that one of the users/systems updated or deleted a row with
in the POHeaders table with a primary key as indicated.  When this update
was propogated to the other systems, there was an error due to the fact that
no row existed in the POHeaders table with that primary key value.  This is
usually down to the updates being loaded incorrectly, either in the wrong
order or something similar.

--
Tim Young
Elevate Software
www.elevatesoft.com

> "No row found in the table POHeaders with the primary key values of
> 30000001."
>
> Which indicates that one of the users/systems updated or deleted a row
> with in the POHeaders table with a primary key as indicated.  When this
> update was propogated to the other systems, there was an error due to the
> fact that no row existed in the POHeaders table with that primary key
> value.  This is usually down to the updates being loaded incorrectly,
> either in the wrong order or something similar.


The original POHeaders table on the server had normal auto-inc values that
originally started from 1.  The next autoinc value is currently 3449.  I
carefully studied and closely followed your replication demo on the web site
and did the following steps when setting things up:

1) setup a "staff" table with an AutoIncStart field:
"AutoIncStart" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT
BY 10000000) NOT NULL

2) added a trigger to automatically create numbered in/out stores for each
staff member:
TRIGGER "CreateStores"
BEGIN
 DECLARE StaffSuffix VARCHAR DEFAULT CAST(NEWROW.ID AS VARCHAR);

 EXECUTE IMMEDIATE 'CREATE STORE "Staff' + StaffSuffix + 'in" ' +
    ' AS LOCAL PATH ''D:\DB\ElevateDB\stores\DCI_Staff' + StaffSuffix +
'in''';

 EXECUTE IMMEDIATE 'CREATE STORE "Staff' + StaffSuffix + 'out" ' +
    ' AS LOCAL PATH ''D:\DB\ElevateDB\stores\DCI_Staff' + StaffSuffix +
'out''';
END

3) added the staff members (total of 4)

4) backed up the master database

5) copied the master database to each of the staff member's "out" store

6) published the master database

Then the setup program that each staff member downloaded, allowed them to
choose which staff member they are from the server list (and marked the
chosen person as selected so it could not be accidentally selected by
someone else), and restored the database from the backup.  Then it created a
local in-store and out-store hooking up to the remote stores for that staff
id.  Finally, it altered all the tables (except TempDetails) with a
statement like this:
 EXECUTE IMMEDIATE 'ALTER TABLE POHeaders ALTER COLUMN ID RESTART WITH ' +
StaffStartStr;
where StaffStartStr was defined earlier in the script as
 SET StaffStartStr = CAST(StaffStartID AS VARCHAR)  ... and of course
StaffStartID was each person's unique AutoIncStart value.

So I should only be getting uniquely numbered POHeader records.


The other part of your question wondered about the order in which updates
are applied.  I'm using the following code from the scheduled job on the
server to load the updates:

 PREPARE StaffStmt FROM 'SELECT * FROM Staff';
 OPEN StaffCursor;
 FETCH FIRST FROM StaffCursor (ID) INTO ID;

 USE Configuration;
 WHILE NOT EOF(StaffCursor) DO
   SET StaffSuffix = CAST(ID AS VARCHAR);
   EXECUTE IMMEDIATE 'SET UPDATES STORE TO "Staff' + StaffSuffix + 'in"';
   PREPARE UpdateStmt FROM 'SELECT * FROM Updates ORDER BY CreatedOn';

   OPEN UpdateCursor;
   FETCH FIRST FROM UpdateCursor (Name) INTO UpdateFile;
   WHILE NOT EOF(UpdateCursor) DO
     EXECUTE IMMEDIATE 'LOAD UPDATES FOR DATABASE "DCI_Master"
                       FROM "' + UpdateFile + '" IN STORE
                       "Staff' + StaffSuffix + 'in"';
      ...

So they should be applied in the order they were created.


I'm sure there's room for error here somewhere, but it all makes sense.
Then again, I've looked at this so many times, I may be missing something
obvious.

--
David Cornelius
CorneliusConcepts.com

I just had a thought...

Let's say there are two updates pending from one staff member, Update1 and
Update2.

If Update1 contains (in this order):
  INSERT INTO TempDetails ...
  INSERT INTO POHeaders {new record #30000006}
and Update2 contains this statement:
  UPDATE POHeaders {stuff} WHERE ID = #30000006

and if Update1 broke because of a problem with TempDetails, then does the
POHeaders statement in Update1 get skipped?  That would explain why the
update statement in Update2 causes an error.

--
David Cornelius
CorneliusConcepts.com

David,

<< Let's say there are two updates pending from one staff member, Update1
and Update2.

If Update1 contains (in this order):
  INSERT INTO TempDetails ...
  INSERT INTO POHeaders {new record #30000006}
and Update2 contains this statement:
  UPDATE POHeaders {stuff} WHERE ID = #30000006

and if Update1 broke because of a problem with TempDetails, then does the
POHeaders statement in Update1 get skipped? >>

Yes, the whole thing is rolled back and no updates are applied.

<< That would explain why the update statement in Update2 causes an error.
>>

Yes, but that would require you trying to load the second update even though
the first update (in order) failed due to an exception.  Is this possible
with your setup ?

--
Tim Young
Elevate Software
www.elevatesoft.com

> Yes, the whole thing is rolled back and no updates are applied.

That explains it then!

> Yes, but that would require you trying to load the second update even
> though the first update (in order) failed due to an exception.  Is this
> possible with your setup ?

At first, no, it broke out of the script.  So I put an EXCEPTION block in to
hopefully load the other updates and found the additional errors.  It looks
like my exception handling should re-raise the exception so that it won't
try to load additional updates.


It's all making sense now...  Now, I've got to clean it up!  Argh!

--
David Cornelius
CorneliusConcepts.com

David,

<< At first, no, it broke out of the script.  So I put an EXCEPTION block in
to hopefully load the other updates and found the additional errors.  It
looks like my exception handling should re-raise the exception so that it
won't try to load additional updates. >>

Yep, that will do the trick.

--
Tim Young
Elevate Software
www.elevatesoft.com