[Solved] Editing Base on USB stick corrupts file

Discuss the database features
Post Reply
tomlock2
Posts: 9
Joined: Thu Dec 06, 2018 4:17 am

[Solved] Editing Base on USB stick corrupts file

Post by tomlock2 »

I create an oobase file on my c: drive and work with it and save it. Works fine. Then I copy the oobase file to my flash drive for backup. If I then open the oobase from the flash drive and make some change to it (as minor as just viewing a table for example) and close the oobase file, it gets corrupted == I cannot ever open it again. Trying opens up a FILTER Selection window. The copy of the oobase file on the flash drive appears to be corrupted. If I then copy that oobase file from my flash drive back to my C: drive in a different location than the original c:drive copy and try to open that oobase file I get the same message as I did when I tried to open it on the flash drive --it comes up with the Filter Selection menu!

Basically the moral appears to be DO NOT EVER OPEN ANY oobase file stored on a flash drive, you will likely destroy it. SO if you use a flash drive for backing up an oobase file DO NOT ever open it on the flash drive. You can copy it back to your c: drive without a problem as LONG AS YOU NEVER HAVE OPENED IT while it was stored on the flash drive.

I find this to big such a big issue (ACTUALLY I CALL IT A MAJOR MAJOR BUG) that I cannot safely use oobase! I have used other database software for years and have never encountered such an issue.

Please comment.
Last edited by RoryOF on Thu Jan 17, 2019 7:14 pm, edited 2 times in total.
Reason: Added green tick [RoryOF, Moderator]
Open Office 4.1.5
OS Windows 10
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Using a USB stick with OpenOffice.org and its Derivative

Post by RoryOF »

I would suggest trying using a USB hard drive, rather than a USB stick. You should be certain that you have observed the operating system's USB removal protocols; too often people just whip out the USB stick, which may prevent the file or directory entry for it being updated correctly.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Using a USB stick with OpenOffice.org and its Derivative

Post by Villeroy »

I can't reproduce this with Linux. Nevertheless you must not open a Base file with an embedded database like this. To be more precise, you must not use an embedded database for anything serious beyond demo, teaching, less important stuff.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
tomlock2
Posts: 9
Joined: Thu Dec 06, 2018 4:17 am

Re: Using a USB stick with OpenOffice.org and its Derivative

Post by tomlock2 »

I guess I do not understand your response about 'You should not use an emm....."

Are you saying that if you open a oobase on a flash drive which you are using for backup to your c: drive working copy of that database destroying the backup copy is "operating as designed"?
Open Office 4.1.5
OS Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Using a USB stick with OpenOffice.org and its Derivative

Post by Villeroy »

Statement 0 (on topic): Don't load and edit any files from USB sticks.

Statement 1 (ignoring the topic): I can plug an USB stick to my Linux system, copy a Base file with an embedded HSQL to the stick, open that copy, edit forms, queries and table data close everything and unmount the stick without problems.

Statement 2: Using database documents with embedded HSQL is not recommended except for trivial, less important tasks. This whole embedded thing is flawed because it "installs" the embedded database to a temporary directory when you load any object of it and the temporary database is repackaged back into the odb document when you finish editing. This may happen when you close the document or not. It may take more seconds of time than you allow the process to finish. Killing the connection to the USB stick while the database document is rewritten kills the database document for shure. I often notice that a database connection is still alive although the database document is closed. This may be due to connections of other documents to the database. It may be due to connection pooling. Even when you close the last office window, the quick starter may keep the office process anyway. Killing the USB storage with the process keeping the database connection still running may destroy the document because the database may not be written back into the containing document. This has nothing to do with HSQL as a database product. It is the way how the embedding is implemented and how naive users tend to handle this delicate construction.

My productive databases are HSQL databases because HSQL is small, simple, good enough for me and perfectly platform independent. But they are not embedded in the connecting Base documents. Whatever happens to the office or to the connection, the database should not lose more than the currently edited record. One of my HSQLDBs is running on a Windows server as a stand-alone process serving multiple computers on a local network.
I use some other databases without a running server connecting to a set of local database files just like the embedded HSQLDB does after extraction of its database. Just for you I copy one of those documents to a stick, open all its embedded forms, start editing data and pull off the stick. The database keeps on working since neither the database document nor form documents are modified and the actual database (tables, views, relations) is on my hard disk. Now I edit and save a form document in design mode and try to save the containing database document which fails as a matter of course since the storage is unavaillable. But nothing crashes. I can still access the database on disk and when I close the database document without saving, nothing tries to remerge the external database with its container. The connection is closed instantly. The stick took no harm.

OK, that was not realistic. So I copied both, the database files and the document, to the stick, opened forms, start editing, pull the stick, close everything cleanly and after I remount the stick, everything works well as if nothing ever happened. I know that this could be a method to kill the entire file system on the stick but it is not that likely because I only edit small portions of an external database. The program, even if it crashes, does not write megabytes of database data into a zip file (office documents are zip files).
Last edited by Villeroy on Sat Jan 05, 2019 3:26 pm, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Editing Base on USB stick corrupts file

Post by UnklDonald418 »

I also tried to duplicate your problem. I copied a sample embedded database to a USB stick on a Windows 10 machine.
I added a new table with about 100 records to the database and removed the stick without closing the database or going through the Windows eject process for the USB device (either of those actions can cause problems)
When I reinstalled the stick I could see the .lck file left over from not closing the database, but the database still opened without any ill effects.
Have you tried a different USB stick?

You should be aware of the potential issues when using an Embedded database (look in the lower left corner of your main database window to see the type of connection being used).
When using an Embedded Base database everything is stored in the .odb file, tables, queries, forms, reports and a very old database engine. An .odb file is actually a zip archive so if anything goes wrong with the zip process the result is a corrupt and often unrecoverable file.

There is a way to minimize the problems by using a split database (JDBC connection) where the tables are stored outside the archive in separate unzipped files, and as a bonus you get a more recent database engine.
[Wizard] Create a new 'split' HSQL 2.x database
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
tomlock2
Posts: 9
Joined: Thu Dec 06, 2018 4:17 am

Re: Editing Base on USB stick corrupts file

Post by tomlock2 »

I believe the problem has something to do with the database file's properties when it is stored on the FLASH drive. The problem does not occur when I store the database on a SD card or anywhere on my Laptop hard drive. When I make any change to the database file while it is stored on the Flash drive and save it I am unable to ever open the database again. It is corrupted.
Open Office 4.1.5
OS Windows 10
tomlock2
Posts: 9
Joined: Thu Dec 06, 2018 4:17 am

Re: Editing Base on USB stick corrupts file

Post by tomlock2 »

My latest thought is that the problem I am having has to do with the fact that the USB (Flash) drive is formatted NTFS. I do not have the same problems with 3 other Flash drives which I have that are formatted FAT. Comments anyone?
Open Office 4.1.5
OS Windows 10
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Editing Base on USB stick corrupts file

Post by RoryOF »

By copying everything off the USB stick, reformatting it to Fat or Fat32, and putting everything back, you can then ascertain if OO can save to it correctly. If not, it is a peculiarity of that USB device.
 Edit: Have you run chkdsk on the USB stick? 
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Editing Base on USB stick corrupts file

Post by UnklDonald418 »

A Base .odb file is actually a zip archive (you can look inside with a zip tool). The NTFS supports file and folder compression, so there may be a conflict between the two compression schemes.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
tomlock2
Posts: 9
Joined: Thu Dec 06, 2018 4:17 am

Re: Editing Base on USB stick corrupts file

Post by tomlock2 »

I can edit the ooBase file stored on a USB stick successfully using my laptop IF THE STICK IS INSERTED IN MY LAPTOP. If, however I insert the stick in my router and attempt to update to ooBase file and resave it, it gets corrupted. Can anyone explain why this occurs and what, if anything, I can do about it?
Open Office 4.1.5
OS Windows 10
tomlock2
Posts: 9
Joined: Thu Dec 06, 2018 4:17 am

Re: [Solved]Editing Base on USB stick corrupts file

Post by tomlock2 »

The problem all along was the FORMATTING of the USB drive. I believe it had some funky formatting on it when purchased and was used without reformatting. After formatting NTSC as a locale drive on a Windows 10 laptop all of the problems went away. One of the symptoms that the bad drive had was that you could not control file properties or security. I believe as a result when updating a ooBase file stored on it the ooBase file would get corrupted due to write permissions.
Open Office 4.1.5
OS Windows 10
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [Solved]Editing Base on USB stick corrupts file

Post by RoryOF »

Perhaps some reserved partition containing a free system backup utility or similar?
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Post Reply