Page 1 of 1

Database Recovery

PostPosted: Wed Dec 05, 2018 1:09 pm
by ridgedale
I have been passed some archive files for race results one of which appears to be corrupted.

I've tried the steps provided in the following tutorial: and get the following errors when the file is unzipped using 7-Zip:
Unexpected end of data
Data error: database\data

It looks like the data file has been damaged at some point and contains little of the data it should have.
However, the backup file contained in the database folder appears to be intact.

My question is whether or not it is possible to recover database using the backup file.

Any assistance would be greatly appreciated.

Re: Database Recovery

PostPosted: Wed Dec 05, 2018 3:51 pm
by Sliderule
Please post your database file ( *.odb ) to this forum, OR, a link from a web site where I can download it, and, I will attempt to repair your file.


Re: Database Recovery

PostPosted: Fri Dec 07, 2018 2:30 pm
by ridgedale
Hi Sliderule,
Thank you for your reply and offer. However owing to the personal data contained in the database the file cannot be shared.
Are you able to share the procedure you intended to use to attempt the repair? Or would you have been attempting the repair with a commercial tool?

Re: Database Recovery

PostPosted: Fri Dec 07, 2018 3:53 pm
by Sliderule
ridgedale wrote:Hi Sliderule,
Thank you for your reply and offer. However owing to the personal data contained in the database the file cannot be shared.
Are you able to share the procedure you intended to use to attempt the repair? Or would you have been attempting the repair with a commercial tool?

No, not a commercial tool.

Since OpenOffice / LibreOffice Base is not a database, but rather a front-end to various database back-ends, and, you have not indicated your database back-end, and, you have elected to not allow me to see the corrupt *.odb file, I cannot help.

Please read, for whatever database back-end you are using, your database documentation.

While I have been able to repair over 75 corrupt Base files ( *.odb ) not all could be recovered, depending on which pieces inside the ZIP file are available ( not corrupt ) . See forum post:



Re: Database Recovery

PostPosted: Wed Dec 12, 2018 8:24 pm
by ridgedale
Hi Sliderule,
Apologies for the delay in responding.
Since OpenOffice / LibreOffice Base is not a database, but rather a front-end to various database back-ends ...

Please excuse my ignorance. I was not aware of that.
The .odb file is a straight desktop OpenOffice created Base file containing tables with output generated using a macro file and SQL queries. There is no third party database backend. I have working .odb files for previous and subsequent years. What I am not certain of is whether or not the macros file was updated other than for the standard season date changes, but that is not necessarily an issue. The entry and results data would definitely not be the same and is the important data that needs to be recovered, if possible. The extracted .zip file using the latest version of 7-Zip contains the following files/folders only:
        current.xml <-- 0b Empty file.
      floater <-- Empty folder.
        Bitmaps <-- Empty folder.
      menubar <-- Empty folder.
      popupmenu <-- Empty folder.
      progressbar <-- Empty folder.
      statusbar <-- Empty folder.
      toolbar <-- Empty folder.
      toolpanel <-- Empty folder.
    content.xml <-- 17kb readable and appears complete.
      backup <-- This file looks complete at 36.9Mb.
      data <-- This file is definitely damaged and incomplete at only 152kb - expected size in terms of Mbs likely exceeding 37Mb.
      properties <-- 458b readable and appears complete.
      script <-- 10kb readable and appears complete.
    META-INF <-- Empty folder.
    mimetype <-- 39b Content single line = application/vnd.oasis.opendocument.base.
Hope that all makes sense.
Is there any possible way to use the database > backup file to attempt to recover any data? Is it possible to run an OpenOffice, Base or Windows command to restore the data from the unzipped database > backup file to a copy of the previous year's file used as a donor file?
Any thoughts appreciated and thanks for your patience.

Re: Database Recovery

PostPosted: Wed Dec 12, 2018 8:30 pm
by RoryOF
I'm not a database expert so take my advice in that light. First of all, make a copy of your file at operating system level and work only on that copy, leaving you an original of the damaged file. Extract the database backup file, rename it to the same name as the database data file, and insert it into the OO archive, in the data position. Then try to open that file.

Re: Database Recovery

PostPosted: Wed Dec 12, 2018 8:46 pm
by Villeroy
Each HSQLDB database consists of between 2 to 5 files, all named the same but with different extensions, located in the same directory. For example, the database named "test" consists of the following files:




The properties files contains general settings about the database. The script file contains the definition of tables and other database objects, plus the data for non-cached tables. The log file contains recent changes to the database. The data file contains the data for cached tables and the backup file is a zipped backup of the last known consistent state of the data file. All these files are essential and should never be deleted. If the database has no cached tables, the and test.backup files will not be present. In addition to those files, HSQLDB database may link to any formatted text files, such as CSV lists, anywhere on the disk.

An embedded HSQLDB has no file name prefix. The files data, properties, backup, script, log belong to the containing Base document.

Re: Database Recovery

PostPosted: Thu Dec 13, 2018 1:45 am
by Sliderule

If I cannot have / see / manipulate the database file ( *.odb ) you have, I cannot help you repair the file, nor give you additional information on how to attempt to repair the file, since, there are a multitude of possibilities that might be required.

As I originally posted, post the file here, or, if you want to send me a private PM ( Personal Message ) with your email address, I will let you how you can get the file to me so no one else can see it.

Otherwise, I have no advice to you, except to say, read the HSQL documentation.


Re: Database Recovery

PostPosted: Mon Dec 17, 2018 12:21 pm
by ridgedale
Thanks for your replies RoryOF, Villeroy and Sliderule.

@RoryOF: Your suggestion did not allow the .odb file to be opened. I tried multiple ways using both the backup, properties and script files from the original backup and copying and renaming the backup file as data, and re-zipping everything. I also tried using copies of the previous and following seasons' working files as donors and replacing the backup file and data file as suggested. None of those routes worked. Having checked the contents of the backup file I realise the data must either be encoded in some way or itself corrupted as there is no recognisable data content within the file, whereas there is in the damaged/incomplete data file.

@Villeroy: I had already read the information you provided and understand that the files in the standalone .odb do not include the prefix.

@Sliderule: I have already pointed out that the data cannot be shared on account of the sensitive data the file contains owing to the GDPR. I also understand that you cannot provide any assistance on that basis.

Having searched through the Hsqldb User Guide as suggested I found the following, albeit there is no log file:


The current file is corrupt, but with the old (from the test.backup file and test.script) and the current test.log, the database is made up-to-date. The database engine takes these steps:

Procedure C.3. Database Repair

Restore the old file from the backup (uncompress the test.backup and overwrite

Execute all commands in the test.script file.

Execute all commands in the test.log file. If due to corruption, an exception is thrown, the rest of the lines of command in the test.log file are ignored.

Close the database correctly (including a backup).

I presume that cannot be done from within OpenOffice and requires setting a SQL server to execute the commands in the script file from the command line. Is that presumption correct?

Re: Database Recovery

PostPosted: Mon Dec 17, 2018 2:08 pm
by Villeroy
This is how I would try to do it using my own tool.
Copy your database to a dedicated directory.
Install the Python script from viewtopic.php?f=21&t=86071 and run it against the new, empty db without adding a new hsqldb driver. It will create a subdirectory "database" with all the database files. The database document will be connected to that external database using the hsql driver of your office suite.
Now you can do all the operations in the database directory and you can use Base menu: Tools>SQL... as a command line interface.
If you see all the tables and relations, the operation was successfull.
For maintainance run SHUTDOWN COMPACT; and close the office suite for now.
Now it should be possible to "transplant" the repaired database back into the old Base document with your queries, forms and reports but I would never do that for the reasons you already know yet.
The Python macro has a helper routine which allows you to remove the embedded database. You can do this manually as well when the database is closed.
Upgrade to HSQL2 if you want:
Make a backup copy of the database folder.
Download the HSQL2 package from (just another zip file), create a subdirectory "driver" in your database folder, copy or link lib/hsqldb.jar into this driver folder.
Run my Python macro and choose to connect with the existing external database again. It will include the existing driver/hsqldb.jar
Test your database. In some cases there may be compatibility issues which are resolvable.

Re: Database Recovery

PostPosted: Mon Dec 17, 2018 2:28 pm
by Villeroy
Another tool of mine is FreeHSQL which can not extract embedded databases. But if you have an extracted HSQL and a hsqldb.jar somewhere in your local file hierarchy, you can connect any database with any set of databsase files and a driver hslqdb.jar using a dialog.
If you extract the damaged embedded database manually, you have to rename each file with a prefix ( script -> MyDB.script ) before you can connect to this set of files.
You find the HSQL 1.8 driver of your office suite in subdirectory program/classes/hsqldb.jar of the installation directory (C:\Program Files (x86)\OpenOffice4\program\classes\hsqldb.jar or similar)

Re: Database Recovery

PostPosted: Mon Dec 17, 2018 3:16 pm
by ridgedale
Hi Villeroy,
Thanks for the instructions. Much appreciated.
All I really need to recover is the contents of the tables as everything else can be simply recovered by using an emptied copy of the previous season's or following season's working .odb file and updating the macro as required.
Would it not be possible to use an emptied copy (all data removed from tables) of the previous season's .odb file and then run the Base menu: Tools>SQL... as a command line interface to retrieve the data from the original backup file (of the corrupt/damaged .odb file)? Or is it a requirement for any recovery to start from a new blank .odb file devoid of any structure?
I would connect your old database document containing the damaged database to the new external database ...

I'm not sure I understand how the original database document should be connected. Is that done via a SQL command?

Re: Database Recovery

PostPosted: Mon Dec 17, 2018 4:09 pm
by Villeroy
Sorry, I shortened the above procedure. It is not necessary to start with a blank, new database. Just use a copy of the damaged one you have, let the Python macro extract the damaged db from that one. The macro will change the document setup to connect with the extracted one. Of course, this new setup will fail. Try fixing the extracted db according to the HSQL documentation. If the tables and relations are back, the queries, forms and reports should work as well and you have the latest possible set of data.

Of course you can use any working backup database embedded in a Base document. Just shut down the whole office suite, put the embedded HSQL files into the database directory and add the same prefix to all the file names, for instance "DBName."
properties -->
script --> DBName.script
data -->
backup --> DBName.backup

Now you can use the Python macro to connect the currently loaded document DBName.odb with the files database/DBName or you can use the FreeHSQL macro (with a dialog) to connect the currently loaded document with the database files regardless of the naming (that tool can connect Some.odb with Other.script and etc).
It should be possible to replace the damaged embedded HSQL with the embedded HSQLDB of a backup document so you can use the latest version of queries forms and reports with an older version of the database. I have never done this and I would not do it because embedded databases are unsafe. Just remove the damaged embedded database folder from the document A, extract the working embedded database from backup document B and add the extracted B database to document A using your preferred zip tool while the whole office suite is shut down (just to be sure that nothing is connected to either file). It should work just like that but trying is up to you.

Then you may face 2 problems.
1) Data that were stored between the 2 versions are not included in the old database.
2) The newer queries, forms and reports may not match with the old version of your database. You may get errors about unknown tables and columns. This is certainly not a problem if tables and columns of the backup version are the same as the latest version.
Embedded databases should be avoided. All my productive HSQL databases are external ones. Some of them are running since 9 years by now.
All the helper tools do not set up a HSQL server for you. They connect a set of files with a Base document in the same way as the embedded HSQLDB but without constantly extracting and zipping the database. The files remain extracted when you close the Base document and when the office suite crashes for any reason, the database files are not in danger.
A Base document that extracts its own embedded HSQLDB before connecting it to shows "embedded HSQL" in the status bar.
A Base document connected to a set of database files shows an URL like this in the status bar: jdbc:hsqldb:file:///path/DB_Name where "DB_Name" is the common name prefix of the script, properties, data files.
A Base document connected to a HSQL server for simultanious multi-user access shows an URL like this in the status bar: jdbc:hsqldb:hsql:server_name/DB_Name or with an IP number instead of the server_name. The DB_Name may be the common prefix of the database files or some other name declared in a configuration file on the server machine.

Re: Database Recovery

PostPosted: Mon Dec 17, 2018 4:24 pm
by Villeroy
And theoretically it should be possible to transplant an external HSQL 1.8 back into a Base document as long as it was not upgraded to HSQL2. Remove all embedded database files, remove the file name prefix from the external database files and zip them into the database document.
If the status bar does not indicate "embedded HSQLDB" you can edit content.xml and replace the connection URL with "sdbc:embedded:hsqldb" or you can run "Revert_to_Embedded" of my Python module which does the same.

Re: Database Recovery

PostPosted: Mon Oct 28, 2019 4:33 pm
by arfgh
is it possible to disable the 'backup' file creation into the .odb database file ?

Re: Database Recovery

PostPosted: Mon Oct 28, 2019 4:42 pm
by Villeroy
The embedded "backup" is the database you used to use before converting it to an external one. It is no longer touched when you continue working with the external database. Remove it if you are sure that the external one works for you.