I have some issues with published updates. While another applicaitons writes or updates some records on a published table while I do a Save Database Updates (from EDB Manager), I get missing records on the next update. Somehow the records added or edited on that update process seems to get lost. After the last update the record count of the server table is different to the local version and a new Save Updates doesn't save anything.

What is the correct way to do an Save Database Updates so not records get lost? What can I do to make the two databases to be in sync again?

FYI: I'm just in testing mode and have running an applicaiton aside of the EDB Manager. The Save Database Updates is manually started from inside the EDB Manager.

Rolf

If:

1. All databases and all tables in each DB are published.
2. All "SAVE UPDATES" statements do not limit the tables to save.
3. All databases are using EDB 2.22 or above (there were minor issues in earlier versions)
4. You are sure to call "RESET PUBLISHED TABLES" when you RESTORE a backup file to create a new DB instance to be part of a replication system.

then once you start adding/editing data, updates should copy all data to all instances.

You do have to be careful how you apply updates: They must always be Loaded in date-order.
There can be issues if calculated fields or triggers cause errors during the "load", as a part of the update file may run but fail to apply/save to the database.

It can be quite hard to ensure that all instances are really identical when you start the whole replication process. If they are not you are in trouble.

To make this easier I tend to:

1. Create the DB I am happy with.
2. UNPUBLISH it (to ensure there is no lingering update data in it)
3. BACKUP.
4. Move to the new machine, RESTORE (with "RESET PUBLISHED")
5. PUBLISH.
6. Back on the old machine PUBLISH.

All my instances are Client Server. I am unclear whether non-CS access might be an issue.

Have a look over these steps and see whether anything is helpful.

I use 2.23b1. There is only one table published yet and only on the client PC. This database is accessed in filemode, no C/S. EDB Manager and my client bulk update application is using filemode.

The problem is for 100% sure on the client side, as I can't save any updates, as it thinks there are no changes and the update file seems to be empty. I'm unable to generate a update file for the missing records.

I do manually update the server wih the manually generated updatefile and I'm 100% sure I do it the right way. After every Save Update I copy the update file to the server and load the updates there. Everytime I do check it manually to see if everything was fine.

As long my Bulk application is not running at the time the save updates is done, everything is fine. But as soon my applications updates the table while a Save Updates is in progress, I loos records. Something must be wrong here, as I believe, the Save Updates command doesn't lock other application fom writing to the published table and deletes the published update table (EDBPbl), were new records were added before the save was finished.


Also there are some other irritating things going on in EDB Manager. For some reason I must close the database to do a Save Updates as else it sometimes doesn't realize that there were updates and it doesn't generate an update file at all. After closing the database, it seems to see the changes and creates an update file.

I don't know if this is a missbeahviour of the EDB Manager or of the engine self. Should it be possible to save update with the open database?

Rolf,

<< I have some issues with published updates. While another applicaitons writes or updates some records on a published table while I do a Save Database Updates (from EDB Manager), I get missing records on the next update. Somehow the records added or edited on that update process seems to get lost. After the last update the record count of the server table is different to the local version and a new Save Updates doesn't save anything. >>

You're not doing something correctly.  I just confirmed that this works correctly (2.23 B1) by:

1) Pausing the SAVE UPDATES operation on a database in the EDB Manager
2) Opening up another EDB Manager instance, and then trying to update a row in one of the published tables.
3) Un-pausing the SAVE UPDATES operation.
4) Executing another SAVE UPDATES to create a new updates file.

I then used the CREATE TEMPORARY TABLE..FROM UPDATES statement to examine both of the updates files created, and they are both correct.

The SAVE UPDATES process obtains a transaction lock on all of the published tables before executing.  This ensures that none of the published tables allow a write to occur while the updates are being saved.  If this didn't work properly, then transactions and a lot of other foundational aspects of ElevateDB wouldn't work properly.

Tim Young
Elevate Software
www.elevatesoft.com

Rolf,

<< Also there are some other irritating things going on in EDB Manager. For some reason I must close the database to do a Save Updates as else it sometimes doesn't realize that there were updates and it doesn't generate an update file at all. After closing the database, it seems to see the changes and creates an update file. >>

Again, not correct.  You're mistaken about this, and must be doing something differently than what you're describing.

<< I don't know if this is a missbeahviour of the EDB Manager or of the engine self. Should it be possible to save update with the open database? >>

Neither, and yes, absolutely.

Tim Young
Elevate Software
www.elevatesoft.com

Tim,

I understand that you don't want belive me, but I can reproduce it and at the moment I have running my application and can see in the Windows Eplorer, that the VideoList.EDBPbl is growing, but if I use the Save Database Update function in EDBM it doesn't generate an update file at all, even if I selected to save an empty file. So I begin to believe that the Save Update file is getting some type of error and just eats that error and returns with no error. Maybe a locking error as my applications is inserting and updating records nonstop. It my fail as my application has a lock on an edit process.

My Application is right simple as it just scans a webserver and stores all Links on into the database (EDBTable.AppenRecord). If the link is already in the table, it will update that record with some changed data (EDBTable.Edit + Post). Thats all.

After stopping my website scanning application, the save update does still not save an update file. No idea why, as now no insert/edits are running anymore. After closing the database and reopen it in EDBM, I was able to save the updates, but it missed some inserted records and the two databases are again out of sync.

So again what I did is exactly this:
After the error and my server database was not anymore in sync with my local database (missing records) I did this:
1. Saved a pending Update File to be sure the published file (EDBPbl) is empty.
2. Backuped the local database and sent it onto the remote server store.
3. On the server I did restore the backup with the catalog and reseting the Publised Update Table.
3. Than I unpublished the published table, as it should not be published on the server.

4. In the still open EDBM I deleted the updatefiles.
5. Now I started my application.
6. After some time with still running my application, I selected my database in EDBM and selected the context menu "Save Database Updates". This did not work as described above. (no error, but also no update file)
7. Now I stopped my application and tried again to save the update file, but it was still not saved.
8. So I closed the session completly in the EDBM and reopened than my database again.
9. Again I did a "Save Database Updates" command. This time an updatefile was generated.

10. Now I copied the Update File to the Remote Store.
11. On the server database I did a "Load Database Updates" and it imported the update without an error.
12. Now I checked the record count of my local client table to the just updated one of the server and there are now again some records missing. I belieave also, that some update would be missing.

I strongly believe that the failed Save Update call (because the locking issue or whatever) did something wrong with the EDBPbl file in this case. I have noticed, that just after the save update call  the file size in the Windows Explorer was jumping down to 0 bytes, while it was still be updated by my application. After a refresh of the Explorer the bytes jumped up again.

This project is some type of testing for me to see if and how good EDB will fit in my upcoming large project, where I want to use published tables on the client, which than should be updated on the server with an update file. At the moment I see no way to use this, as long this isssue is not resolved and we didn't find out what is causing this behaviour. So please help me.

Some additional Infos:

The correct client table record count is now 356'067, while the server tables has now only 356'061.

I did an ASCII export of the two tables and compared them. They have now many, many differences. Exactly 26'370 different lines.

I did also create a temp table from the updatefile and this shows me only 19'059 records. So there seems to be a serious problem with this updatefile and it looks as the failed Save Update did somehow corrupt it.

Rolf,

First of all - I *am* helping you.  I tried what you're suggesting here, and I can't replicate it.  So, unless you can send me a project or come up with an exact scenario whereby *I* can make this happen, then I have to assume that you're doing something wrong.

<< I understand that you don't want belive me, >>

No, not true.  Belief has nothing to do with this.  I need to actually *watch* it occurring in order to do something about it, and that hasn't happened.

<< but I can reproduce it and at the moment I have running my application and can see in the Windows Eplorer, that the VideoList.EDBPbl is growing, but if I use the Save Database Update function in EDBM it doesn't generate an update file at all, even if I selected to save an empty file. So I begin to believe that the Save Update file is getting some type of error and just eats that error and returns with no error. >>

EDB does not "eat" exceptions, and errors are *always* logged to the LogEvents system information table:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=LogEvents_Table

<< Maybe a locking error as my applications is inserting and updating records nonstop. It my fail as my application has a lock on an edit process. >>

The SAVE UPDATES functionality does not run into issues with row locks - it doesn't care about them.  The only thing that it cares about is getting a transaction lock on the tables involved with the SAVE UPDATES operation.  That prevents any writes to the table and gives a clean delineation of the SAVE UPDATES operation, just as with a transaction.

<< My Application is right simple as it just scans a webserver and stores all Links on into the database (EDBTable.AppenRecord). If the link is already in the table, it will update that record with some changed data (EDBTable.Edit + Post). Thats all. >>

Good, so it should be fairly easy to break down into a sample application that you can email me.

Tim Young
Elevate Software
www.elevatesoft.com

Rolf,

Are you using the EDB Server here, or are you using direct local sessions ?  If the latter, are you ensuring that all local sessions are using the same configuration file path ?  If they aren't, then they won't see each other's transaction locks and you'll see issues like what you're seeing.  Likewise if you have one session with LargeFileSupport = True and another with LargeFileSupport = False.

Tim Young
Elevate Software
www.elevatesoft.com

Thanks Tim,

1. No use of a EDB Server localy, only on the server for sending the Update files. My Session in EDBM and my local application are running in local mode.

2. Yes both EDBM and my application are using the same config path. You mean the TEDBSession.LocalConfigPath property right? The EDBM Session is set to the same path. (C:\Delphi\Projects\XUPNP-VideoList). If I manually start a transaction in EDBM, my application stops until I commit or rollback. So this should be OK.

3. Both my application and EDBM are set to use LargeFileSupport. EDBM -> "Enable Large File Support" is checked. My application have TEDBSession.LocalLargeFileSupport = True.


My further investigations brought me to the actual conclusion, that it seems to be a problem of the EDB Manager and not the engine itself, but I'm not 100% sure yet. I did make a small simple application which just does a SAVE UPDATES call. For some reason this application works perfectly and I was not yet able to reproduce the same error as in EDBM. Everytime I used my Save Update Application the update files were fine and correct saved, which was not the case in EDBM.

I have no idea yet what is different in EDBM which leads to this issue. I have 2 sessions in EDBM, one is the local one and the other is the C/S one to the server database. In both sessions the database has the same name. Can this be an issue?

I believe the Manager gets sometimes some irritations of the two sessions and I have the fealing, that it doesnt correctly change all the configuration on switching between this sessions. The most time I have both of them active and sometimes it complaning on any SQL, as if it is not on my actual Session. In this case I must manuallay select the other Sessions and back to the one I want work with. I have not yet found out when this happens, but it can be related to my problem.

And as I did write already, EDBM doesn't have this issue all the time. If I close the Session or database, everything goes back to normal and the save updates does again work until the issues raises the next time. What you evenutally should know too is, that EDBM is running 24/7 normaly and my PC goes into standby at the evening and gets waked up the next day. Eventually EDBM has an issue with the standby/wakup of the PC, but I doubt it.

Hope this helps you further. If not I will try to make a new Database with the same structure and a simple demo application.