Please help - missing records in Base

English
#1

I am using Version 6.3.5.2 6.4.6.2 on an iMac with OS 10.15.6. I use Base to store my work activities. I have been using this system for over a year with no problem but all of the information I entered yesterday has vanished. As far as I’m aware this has never happened to me before. I use these records for invoicing so it’s a bit of a nightmare.

I have various linked tables (one that holds the overall record for the day, one with the individual tasks I’ve completed, another for tasks to do and so on). I add to these tables using a form which has information for the day on the main form plus two separate subform tables (Done and To Do).

I was adding records throughout the day and often click back and forward between days to get calculated fields to update and to make sure everything I’ve entered has saved. Nothing appeared to disappear during the day. It all looked to be working fine as usual. At the end of the day I closed the form, clicked save on the database and closed it. This morning, when I looked, none of the records I created yesterday are in any of the tables.

The database is stored on Dropbox so I looked at the version history. There was only one version from yesterday, which was 18:35 (that was when I clicked Save). The records normally save regardless of whether I actually click Save on the database. - No longer relevant since database is now stored locally.

I restored the version from yesterday and that was identical to the one I opened this morning, with all yesterday’s records missing.

Is there any way to retrieve them or at least to prevent this happening again in the future?

Edit: This has happened again three weeks after following the advice to move my database to a local drive. The records have vanished from the main copy of my database and from the backup. This is not a dropbox problem since I no longer use dropbox for this database. I would like to know exactly where the records are stored since I suspect it may not be within the .odb file.

#2

Hello,

You have not stated what database you are using. HSQLDB embedded can be problematic. It would also help to know the process (steps for Base usage and storage back) you use with Dropbox and Base as this can be an issue also.

#3

Yes I’m using HSQLDB embedded. I’m sorry I don’t understand the second part of your question - I’m an amateur sorry.

#4

Your .odb resides on Dropbox. You sign in to Dropbox. The file (from memory) is accessible on your system in a synced folder. What do you do to access the Base file? Open and use from the synced folder? Move it to another, process & move back when done. Simply looking for steps you take to process.

Both using HSQLDB embedded and Dropbox can cause the problem you are having and continued use need at least some procedure if not an actual change.

#5

Thank you for taking the time to help me.

I have a Dropbox folder on my computer so it saves to there and then that syncs with Dropbox. Is that what you mean? I don’t move the file at all. I just open LibreOffice and click on the database in the recent files window to open it. At the end, I click Save and close it. I don’t usually go to the Dropbox website - I just did today to look at the version history.

I’ve just been reading about the alternative to HSQLDB as I didn’t know anything about that. I keep finding contradictory posts that either say Firebird is better or HSQLDB is better. Are they the only two choices? Should I change to Firebird?

#6

Sorry. One more thing. Should I move the file out of Dropbox? Is OneDrive better or should I keep it on my local computer? Once I can go back into the office, I’d like to be able to access it from home and work, but is that a bad idea?

I also have iCloud and Box if either of those are better.

#7

I have just had exactly the same problem again. My database is now stored on my local desktop and I save a back up at the end of every day. Last night it all looked fine. I’d filled in my activities during the day then saved and closed the database around 6pm. I also saved a backup copy. This morning all of my records from yesterday have vanished. They are also missing from the backup. I am still very confused about where the records are actually stored on my computer and how I can check whether they have saved correctly. Please help. This is not a dropbox problem since I no longer use dropbox for this database.

#8

Hello,

Cannot come up with a reason (other than incorrect processing) that would cause only one days information to be lost. Lost in backup seems because never stored in original to begin with.

May be best to file a report → Bugzilla

#9

Hello Baxter: HSQLDB ‘embedded’ setup stores your database information in a single ‘self-contained’ .odb file which (I believe) is actually a ZIP file, so the date stamp of the file will show the last time your file was modified. As you are using Mac OS, you should be able to examine the file modification date of your problematic .odb file (highlight your .odb file and then press command-I (for info). The modification date should show when the file was last changed. If it does not, it means the SAVE has never been performed! For some reason, I suspect your file is not SAVED as you intend. (Actually, I don’t use HSQLDB ‘embedded’ Base files, so I can’t quite recall now if that setup’s ‘saves’ are done them as auto-saves or if the user has to do them explicitly?). Is it possible that you think you are saving the database, but in fact you are not! Anyway, I seem to recall HSQLDB ‘embedded’ Base files are known to have problems with updating the ZIP file in a timely fashion.

#10

Thanks. What confuses me is that I can successfully save the database without clicking Save in Base. The records seem to save automatically (as with MS Access) and I can close the file and come back later and they’re still there. When I do this though, the .odb file’s ‘last modified’ date does not update. The modified date can be several days before the last time I successfully stored data in the database. Clicking Save in base doesn’t seem to have any effect on whether the records are saved - I think it is saving the database design rather than the contents. That’s what makes me wonder whether the records are actually stored somewhere else. If I could find out where the record data is actually stored on my computer, I’d be able to check that the ‘last modified’ date has updated correctly before I quit the program and lose my data.

#11

The data (as you earlier stated to be using HSQLDB embedded) is stored int the ‘database’ folder within the Base .odb file. For embedded databases, the Base file typically contains everything. An exception would be if you used external linking for items such as images.

HSQLDB does not require a Save to save data - committed on each transaction. Not so with Firebird. Other changes such as a form change will require a save.

Even with a single record changed, the date of the file should change. If not, try resetting the user profile (LibreOffice user profile) and if further problems file a report → Bugzilla.

#18

I have tried using Dropbox to access Base files from different locations (like you) and it seems to work OK with the following method (as suggested by Ratslinger in his answer):

  1. I use the Firebird ‘embedded’ database ‘engine’ (you would need to convert your HSQL ‘embedded’ Base file and NOTE, there can be conversion issues, so please check your converted data integrity and keep a backup of the old Base-HSQL file - these’s good info on this forum to help).

  2. I move the Base-Firebird file from my Dropbox sync folder to another separate external folder, work on it, save it and then move it back to the Dropbox sync folder.

Note: with a Base-FB-embedded file, the database must be explicitly saved before file closure (unlike a Base HSQL-embedded file which auto-saves). I am using LO Base v.6.4.5.2 on Macs.

UPDATE

Looks like HSQLDB v1.8 makes SAVES to memory and not to DISK until file-close, and this is known to cause data-loss in some circumstances. See these posts:

https://forum-test.openoffice.org/en/forum/viewtopic.php?f=83&t=17567

The fact that your odb file’s ‘last modified’ date does not update on occasion is consistent with the ‘failure-to-write-to-disk’ hypothesis.

It might be time to migrate your Base file to Firebird ‘Embedded’ or the HSQLDB v2.x ‘split’ database setup.

#19

Thank you. That’s very helpful.

#12

or at least to prevent this happening again in the future?

Yes!

Preventing data disaster – LibreOffice 6-4

Good luck!

#13

Thanks very much for that. I’ve read through all of that page but I’m still very confused. I already had the “Save AutoRecovery information every 10 mins” box checked. I have now checked the “create a backup copy” box as well. My thinking in using Dropbox is that it also creates backups by keeping the version history as the file changes. I also backup the database to another cloud storage drive every few days. I think my problem yesterday seems to be that the records weren’t present in the saved version even though they were there when I clicked save. I don’t think backup options would help with that would they since the backup version would be identical to the saved version?

#14

I think the main reason I’m confused is because I don’t understand where or how the data is stored or what the correct procedure is for saving. Most days I just shut the program without clicking save and the stuff is still there the next day. I’ve noticed that the “last modified” date of the .odb file can sometimes be several days prior to my most recent record. The help guide says that the database saves automatically every time a completed record is saved. So how did I manage to lose so many records at once? Where did they go?

#15

I’ve just had another look at the Dropbox version history and today there is a new version every couple of minutes, whereas yesterday there’s just the one at the end of the day.

#16

Hello,

Personally do not use cloud storage solutions. Have tested a number of them in the past including Dropbox to answer questions and resolve similar situations.

HSQLDB embedded is very old and has been known to have problems, in certain situations, with losing data (most notably when embedded images or large amounts of data are involved). Firebird embedded is better but has not been fully incorporated into Base. Most problems have alternative solutions but it is best for experienced users. You can also use many other databases but are not as portable as the embedded.

Dealing with problems using LO Base with cloud services usually are procedural. It does not seem to matter which service is used. Have found it best to copy the Base file to another non synced folder, use it from there & when done place back into synced folder for storage on your service. There are a number of files in a Base .odb file and this may be cause in syncing when certain changes are made.

#17

Thanks so much for all your help. I will move my database file to a non-synced folder and just save a copy to Dropbox at the end of each day. I won’t switch to Firebird at this time as I’m not an experienced user but I’ll consider it if I have the same problem in the future.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.