Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 10 of 10 total |
oops--replicated temp tables! |
Thu, Aug 7 2008 9:02 PM | Permanent Link |
"David Cornelius" | 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 |
Fri, Aug 8 2008 2:48 AM | Permanent Link |
"David Cornelius" | > 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 |
Fri, Aug 8 2008 10:23 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.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 |
Fri, Aug 8 2008 10:57 AM | Permanent Link |
"David Cornelius" | > 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 |
Fri, Aug 8 2008 11:54 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.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 |
Fri, Aug 8 2008 12:59 PM | Permanent Link |
"David Cornelius" | > "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 |
Fri, Aug 8 2008 1:02 PM | Permanent Link |
"David Cornelius" | 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 |
Fri, Aug 8 2008 1:17 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.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 |
Fri, Aug 8 2008 1:34 PM | Permanent Link |
"David Cornelius" | > 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 |
Fri, Aug 8 2008 1:42 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.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 |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |