[Solved] Recover a split database

Discuss the database features
Post Reply
Shrimpette
Posts: 63
Joined: Sat Jun 30, 2012 2:07 pm

[Solved] Recover a split database

Post by Shrimpette »

Hello

I have had an issue with my databases. I currently run 2 for our business and am in the process of "testing" a third. All 3 are split databases (I had an issue before and someone mentioned that I should not have an issue recovering the tables because of this.)

This morning I opened all 3 databases and have an error message from one, no tables appear in the second and the new database appears to be fine. All 3 databases should have tables with over 3200 rows of data. When I opened the first database with the error, the "save" icon was highlighted as if I had made changes so I haven't done anything (haven't closed it or pressed save).

The error message is
error in script file line: 48 org.hsqldb.HsqlException: java.io.EOFException in statement [SET TABLE PUBLIC."Engineer List" INDEX '7 0 7'
I don't have a table called Engineer List, so am not sure why this has happened. I did have one but deleted it a while ago. I am not sure if this helps, but when I went into the database folder, there are several files with "Conflict" in the title (e.g. Jobs (Conflict - 2014-01-26 12-36-40)(Conflict - 2014-01-26 12-36-42.script) So there are 3 x .properties files, 3 x .data files and 3x .script files. Is this something to do with my Cloud backup?

Can someone help me recover them? What do I need to do? I need to recover this data to run this month's invoices... HELP!!

Thanks in advance if you can provide any insight or advice.
x
Last edited by Shrimpette on Mon Jan 27, 2014 9:30 pm, edited 1 time in total.
OpenOffice Apache 4.0 on Windows 7
Shrimpette
Posts: 63
Joined: Sat Jun 30, 2012 2:07 pm

Re: Recover a split database

Post by Shrimpette »

Can I copy the entire database folder, delete the conflict files so there is only one copy of each (.backup, .script, .properties and .lobs) and see if this would work?
OpenOffice Apache 4.0 on Windows 7
Shrimpette
Posts: 63
Joined: Sat Jun 30, 2012 2:07 pm

Re: Recover a split database

Post by Shrimpette »

FYI, this is a screenshot of the database folder
Capture.JPG
OpenOffice Apache 4.0 on Windows 7
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Recover a split database

Post by RoryOF »

I would suggest not deleting anything pending getting firm advice from a database familiar Forum volunteer. When did you last restart your computer?
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Greengiant224
Posts: 283
Joined: Wed Jun 09, 2010 3:50 pm
Location: All Over The World

Re: Recover a split database

Post by Greengiant224 »

Shrimpette:
Can I copy the entire database folder, delete the conflict files so there is only one copy of each (.backup, .script, .properties and .lobs) and see if this would work?
You could try this?. Make a copy of the complete db folder on your desktop and see if it works.

Have you any other backups of the db on your pc or a usb drive etc? If so, try looking in there to compare files and file sizes.
I don't use cloud backup so I can not offer any help there.
Perhaps the synchronization between your pc and cloud has gone 'haywire' causing your corruption.
Can you upload a copy of the .script file etc for someone to take a look at it?

Kind regards

Greengiant224

Win 7, Portable AOO 4.1.14, LibreOffice 6.2, 7.4.5 & 7.6.1 (Java 1.7.81 & 1.8.0_281) utilising HSQL 1.8.10 & 2.4.*, MySQL, PostgreSQL, SQLite
+ Blood, Sweat and Tears (Application, Determination and Perseverance)
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Recover a split database

Post by RoryOF »

I'm thinking possible lost clusters on the disk. I know that one can run chkdsk and tell it not to make any corrections (just to find out if there are lost clusters), but it is so long since I've done this on a Windows system I'd hesitate to say how,
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Shrimpette
Posts: 63
Joined: Sat Jun 30, 2012 2:07 pm

Re: Recover a split database

Post by Shrimpette »

Thank you Greengiant224 and RoryOF. I am sitting here quietly panicking....

I restarted my laptop several times last night (I was doing Windows Updates) so I am now kicking myself.... The last time I opened/closed the database was on Friday.

Don't worry, I am not going to delete anything permanently (have had my fingers burned once before!) I was just wondering whether the db would work if I just right-click the entire folder and select copy, then copy it to my desktop? I won't delete anything from the original folder, I was just going to see if those conflict files were the issue and if I deleted them, would the container be able to access the data.... I am not sure how the split database works, when it starts up where does it look for info? In a folder path or just in its own folder?

I am at a total loss. I am 99% sure that this is a cloud backup issue, and I am waiting for the support team from my webspace to reply to me. But I am sitting here working myself into a bit of a state over the potential of not paying my employees, or being able to invoice my clients (and having no money in about 3 days!)
OpenOffice Apache 4.0 on Windows 7
Shrimpette
Posts: 63
Joined: Sat Jun 30, 2012 2:07 pm

Re: Recover a split database

Post by Shrimpette »

Thanks RoryOF - whilst I was typing my reply, you suggested chkdsk. Not sure I want to go down that road yet, but will keep it in mind for later... I am wondering if it has something to do with the Windows Updates... everything worked on Friday!!

I will upload my .script, .properties files etc so they can be reviewed. Shall I upload the ones from the database that cannot access anything (error message), or the ones from the database that can be opened but appears to have no tables? The second one allows me to click on tables with no error messages (it's just that the tables area is blank), but when I try to open a form, it says it cannot connect.
OpenOffice Apache 4.0 on Windows 7
Shrimpette
Posts: 63
Joined: Sat Jun 30, 2012 2:07 pm

Re: Recover a split database

Post by Shrimpette »

OK, tried the copy and deleting duplicate files - that didn't work...

Sigh.

Something I did notice - the database with an error message has a .lobs file in the database folder. The database with no tables does not have a .lobs file. Is this relevant?
OpenOffice Apache 4.0 on Windows 7
User avatar
Greengiant224
Posts: 283
Joined: Wed Jun 09, 2010 3:50 pm
Location: All Over The World

Re: Recover a split database

Post by Greengiant224 »

Shrimpette:
I restarted my laptop several times last night (I was doing Windows Updates) so I am now kicking myself.... The last time I opened/closed the database was on Friday.
I've never known MS updates causing conflict with any files.
Looking at your image the (Conflict date/time) is different to the Modified Time and are the icons from the cloud?
I am not sure how the split database works, when it starts up where does it look for info? In a folder path or just in its own folder?
The .odb (front-end) reads the .script and .properties files to open the connection and, populates the tables with the .data file.
(The .data and .backup files are binary, the .backup is a compressed version)
In the status bar of the .odb file it will show the path to the <dbName>, if using Single User File Mode.
If using Multi User File Mode (server) it will state:
hsqldb:hsql://localhost/;default_schema=true;get_column_name=false
either you or someone else would need to start the HSQL server beforehand.

Good luck

Greengiant224
Last edited by Greengiant224 on Mon Jan 27, 2014 5:50 pm, edited 3 times in total.

Win 7, Portable AOO 4.1.14, LibreOffice 6.2, 7.4.5 & 7.6.1 (Java 1.7.81 & 1.8.0_281) utilising HSQL 1.8.10 & 2.4.*, MySQL, PostgreSQL, SQLite
+ Blood, Sweat and Tears (Application, Determination and Perseverance)
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Recover a split database

Post by RoryOF »

I can't say- I don't do databases since dBase II/III. I'd wait for USA volunteers to come online, which should happen shortl.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Recover a split database

Post by Sliderule »

I just sent you a PM ( Private Message ).

Sliderule
User avatar
Greengiant224
Posts: 283
Joined: Wed Jun 09, 2010 3:50 pm
Location: All Over The World

Re: Recover a split database

Post by Greengiant224 »

Looks like Sliderule to the rescue :bravo:

Win 7, Portable AOO 4.1.14, LibreOffice 6.2, 7.4.5 & 7.6.1 (Java 1.7.81 & 1.8.0_281) utilising HSQL 1.8.10 & 2.4.*, MySQL, PostgreSQL, SQLite
+ Blood, Sweat and Tears (Application, Determination and Perseverance)
Shrimpette
Posts: 63
Joined: Sat Jun 30, 2012 2:07 pm

Re: Recover a split database

Post by Shrimpette »

Thanks everyone! Greengiant224 - The icons are from my Cloud drive - I noticed the difference in times as well and that's why I am (fairly) sure that the conflict is cloud-based, not OO based. I am using a Single User file (for now, until I am more confident, then I will switch to Multi-user on a server).

Is it awful if I say I was crossing my fingers and praying that Sliderule was online somewhere?!? I was actually tempted to PM you earlier and ask... but I thought that might be cheeky. Off to check my PMs now.
OpenOffice Apache 4.0 on Windows 7
Shrimpette
Posts: 63
Joined: Sat Jun 30, 2012 2:07 pm

Re: [Solved] Recover a split database

Post by Shrimpette »

Hi everyone

Well, the wonderful Sliderule has spent his very valuable time helping me out (2 HOURS!!) And can I say, that man is the greatest. THE GREATEST!!!

And it is fixed! It was a long road and I am still thinking it is a Cloud issue but I don't care as long as it works (and I am going to stop cloud backups for a few days and backup to an external drive instead).

So thanks to all of you for your ideas and support. I am always grateful for this forum - you are all such stars!

xx
OpenOffice Apache 4.0 on Windows 7
User avatar
Greengiant224
Posts: 283
Joined: Wed Jun 09, 2010 3:50 pm
Location: All Over The World

Re: [Solved] Recover a split database

Post by Greengiant224 »

Shrimpette, glad to hear it has been fixed.
Sliderule must fix 95% of all sbase problems, so hats off to him. :bravo:

Just a little advice, when making a backup of your single user mode file, before closing down AOO first use
Tools>>SQL and issue a shutdown compact; command.

When you have done this, copy and paste to your desktop (the whole folder structure containing the db files).
Then and only then, cut and paste this folder of files on the desktop to your USB drive.
Remember to follow the correct protocol for removing the said USB drive.

Greengiant224

Win 7, Portable AOO 4.1.14, LibreOffice 6.2, 7.4.5 & 7.6.1 (Java 1.7.81 & 1.8.0_281) utilising HSQL 1.8.10 & 2.4.*, MySQL, PostgreSQL, SQLite
+ Blood, Sweat and Tears (Application, Determination and Perseverance)
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] Recover a split database

Post by Sliderule »

Greengiant224:

Good advice . . . however . . . just as an FYI, when I perform BACKUPs of my database, I prefer to use the HSQL utility . . . SqlTool.jar which can read / write to any JDBC or OCBD database.

By using SqlTool.jar , in 'batch' mode, I issue the HSQL database BACKUP and have it ( HSQL database ) write the files, depending on the choice of syntax used, either as one, compressed, *.tar.gz file, or, individual files, for example, mydb.data , mydb.script etc. This way, the 'batch' file ( whether on a Windows computer, Mac computer, or, Linux computer ) . . . can be 'scheduled' to run, either at a specific time each day ( or whatever frequency ) by OS utility . . . OR . . . only when desired ( when user initiates ) . :bravo:

The advantage of the above is, HSQL database is smart enough to know, to 'limit' database change actions while it is happening, AND, it is very fast. :super:

You can read about BACKUP capabilities in HSQL ( version 2.2.1 etc ) at:

http://www.hsqldb.org/doc/2.0/guide/man ... operations

Sliderule
User avatar
Greengiant224
Posts: 283
Joined: Wed Jun 09, 2010 3:50 pm
Location: All Over The World

Re: [Solved] Recover a split database

Post by Greengiant224 »

Sliderule:
I understood that Shrimpette was using the vanilla HSQL 1.8 jar.
I stand corrected, after all you fixed her database so, you would know. :super:

My personal backups are made with a small utility I have built but it is not yet ready for release to the world :crazy:
It makes backups of v1.8 and 2.+ Single Mode User Files incl. .script, .properties, .data, .backup and .log
along with a .tar.gz incl the .odb file if in the same folder (DACM style)
It still needs a little tweaking here and there.
I'm currently looking at zipping up the final backup folder to include a timestamp.

Kind regards

Greengiant224

Win 7, Portable AOO 4.1.14, LibreOffice 6.2, 7.4.5 & 7.6.1 (Java 1.7.81 & 1.8.0_281) utilising HSQL 1.8.10 & 2.4.*, MySQL, PostgreSQL, SQLite
+ Blood, Sweat and Tears (Application, Determination and Perseverance)
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] Recover a split database

Post by Sliderule »

Very good. As always, YMMV . . . Your Mileage/Database May Vary . . . translation . . . whatever works best for you. :bravo:

I am pleased you have found a way to create the needed backups.

Sliderule
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: [Solved] Recover a split database

Post by DACM »

Shrimpette wrote:Is this something to do with my Cloud backup?
Forgive me for being late to this flurry, but it appears that Sliderule has saved the day yet again. Congratulations on the recovery!!!

As I began reading this thread I was immediately relieved when Shrimpette mentioned "Cloud storage." Based on the errors, I too, suspect an issue with the Cloud backup algorithm. I, myself, encountered major issues with SugarSync on Windows 7 64-bit (well-documented on the internet) and was forced to switch services. I now use Google Drive with Boxcryptor without issue (although I'm impressed with the built-in "client-side encryption" offered by SpiderOak, Wuala, and others so I may switch again).

But one thing was conspicuously absent from the discussion. The primary reason to consider Cloud-based file synchronization is the automated "file history" or "versioning" feature. In a single-user scenario, the changes are backed-up to the cloud at the conclusion of each session (multi-user server-mode notwithstanding). So each time you close Base, the "session" is effectively backed-up to the cloud. Cloud services vary, but worst-case you should be able to retrieve the files from the last ten sessions (Wuala being the "worst case"). With most services, you can recover an unlimited number of sessions dating back at least a month. And that's for free. A paid subscription will often secure access to all historical versions for the life of the account (SpiderOak currently does this for free).

Here's a quick reference explaining historical file access from the major cloud services: So assuming these features were operative, there was no reason for panic. I was just wondering if your cloud-backup service offers historical file recovery, and whether you tried this avenue?
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
Shrimpette
Posts: 63
Joined: Sat Jun 30, 2012 2:07 pm

Re: [Solved] Recover a split database

Post by Shrimpette »

Hello all - and thank you for the advice on backups, which I hadn't considered (I copied the entire folder straight to my USB external drive on closing tonight - will remedy that using your tips now). Looking forward to having utilities do this for me (although I think I have a way to go before I am confident)
DACM wrote: I was just wondering if your cloud-backup service offers historical file recovery, and whether you tried this avenue?
DACM, I still haven't heard back from webspace support :crazy: I am furious, as I was unable to access my cloud drive at all (it was my first stop after my last thread where I lost access to the database and you kindly reminded me about the cloud backups - I am supposed to have 720 day history :roll: ). As you can imagine, there have been a lot of emails from me to tech support.... As of this moment, I have only had an email to say they are "working on it". No explanation for why the drive suddenly disappeared (long story short - I have several "drive" locations on my Cloud. Only one is visible and accessible via the website - all are visible on my laptop application, but not accessible as I get error messages when I try. My databases are all backed up to one specific drive, which of course was gone.)

This is the main reason I think it was the Cloud drive that corrupted my files - there were several "conflict" file versions (.script, .properties) dating back a couple of weeks where my laptop could not connect to the Cloud Drive. I think this only happened because on shutting down the database, it tried to backup to the cloud, couldn't connect and dumped the files back into the folder. Eventually causing an issue? I checked the logs and it appears my laptop has had trouble connecting since 15th Jan, but I had no idea until Sunday, when I noticed the icon on my desktop showing an exclamation mark. Then of course on Monday the databases wouldn't open, I couldn't access the cloud... Panic stations!! I have no idea whether this situation will ever affect anyone else, but am not sure how it can be prevented (other than more robust error messages on the part of my cloud provider!)

Again, thank you for taking time to offer help with my situation. I am not sure whether it will help any future users, but I recommend triple, maybe even quadruple backup processes at this point.... I do seem to attract spectacular crashes of software in my vicinity though, so maybe it's just me!
OpenOffice Apache 4.0 on Windows 7
Shrimpette
Posts: 63
Joined: Sat Jun 30, 2012 2:07 pm

Re: [Solved] Recover a split database

Post by Shrimpette »

Just a quick update - I have finally had confirmation that it was in fact the Cloud issue. Tech support said that one of my Drives had been disconnected and this had caused the issue. I doubt this will happen to anyone else (hopefully) but I had to stop my laptop syncing as this was causing the issue. Of course, when it was repaired, I then had issues merging the drives back together (long and boring but essentially the updates I had made whilst not syncing were rejected) but it is all sorted now and I am not sure that posting my recovery steps would help anyone else.

So I think my problem may have been quite unusual, as I couldn't access the Cloud Drive and therefore couldn't get to my old versions. I have used this function several times (especially when I went wrong experimenting with the new database!) and was verrrrry frustrated at what appeared to be the total loss of my data, considering I pay for the webspace and 120GB storage! I do think that Cloud drive backup is a great thing, when it works....

My thanks again to all who offered advice, I will be buying a new external drive solely for backing up the databases (in addition to Cloud backup) as I just cannot go through this again!
OpenOffice Apache 4.0 on Windows 7
Post Reply