OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[SOLVED] Error while trying to open database
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
sjmathis
General User
General User


Joined: 19 Jan 2011
Posts: 5

PostPosted: Wed Jan 19, 2011 9:37 am    Post subject: [SOLVED] Error while trying to open database Reply with quote

This morning after opening my database, as soon as I try to do anything with the data base like opening a table, a form or a query, I get the message

The connection to the data source "database.odb" could not be established.
Error in script file line: 9 Out of memory"

The More button shows two errors, the first one simply a repeat of the message about the connection could not be established, and the second one similar to the second line, except that it includes "SQL status S1000, error code -78"

I am running OpenOffice.org 3.2.1, which I think is the latest version, on Windows XP, SP 3.

My database is related to my book collection, about 2550 books, 2600 titles, 440 authors and a master file linking them of about 2800 entries. At the bottom of the page after opening the program it says "Embedded database" and also "HSQL database engine".

I read everything in the forum that seems to relate to my problem to no avail. I updated a title in the database last night and closed it with no obvious problem.

Have I reached some undocumented limit in the size of the database that can be used?

If so, how do I recover any of my data that is presently inaccessible?


Last edited by sjmathis on Sat Jan 22, 2011 9:46 am; edited 2 times in total
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Wed Jan 19, 2011 10:32 am    Post subject: Reply with quote

Hello

Make first a copy of your database.

Romke
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Wed Jan 19, 2011 10:45 am    Post subject: Reply with quote

Hello

When you have troubles with the database then it is always good to have a copy of the last version.

I think here you can find information about a reason why you get possible this error. As always I point to a answer of Sliderule.

When all your files are in one file then do the next steps. If you can read the script file give line 9 with the reason of the error.

rename database.odb to database.zip
unzip database.zip
now you can show line 9

Romke
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Wed Jan 19, 2011 11:40 am    Post subject: Reply with quote

sjmathis:

Just a thought, you might want to try the following . . . however . . . without 'seeing' your OpenOffice database file ( *.odb ) . . . I cannot guarantee it will work.

  1. Make a BACKUP copy of your OpenOffice database file ( *.odb ) . . . best to always have this . . . just in case. Smile

  2. After opening the OpenOffice database file . .. from the menu:

    Tools -> SQL...

  3. In the Command to execute box, enter:

    Code:
    SHUTDOWN SCRIPT

  4. Press the Execute button

  5. Press the Close button

  6. Exit your OpenOffice Base File

  7. Restart your OpenOffice Base file

Explanation: According to HSQL documentation . . . found at:

http://www.hsqldb.org/doc/guide/ch09.html#shutdown-section

HSQL documentation: SHUTDOWN: http://www.hsqldb.org/doc/guide/ch09.html#shutdown-section wrote:

SHUTDOWN SCRIPT

Similar to SHUTDOWN COMPACT but after writing the script and deleting the existing files, it does not rewrite the .data and text table files. After SHUTDOWN SCRIPT, only the .script and .properties file remain. At the next startup, these files are processed and the .data and .backup files are created. This command in effect performs part of the job of SHUTDOWN COMPACT, leaving the other part to be performed automatically at the next startup.

This command produces a full script of the database which can be edited for special purposes prior to the next startup.

The idea of the above, MAY be because of an error in creating an INDEX internal to the database. By performing the above, the INDEX will be re-created . . . correctly.

I do NOT know, without having your database file . . . if you can get to Tools -> SQL... but, it is worth a try . . . and . . . will only take a minute to try.

Please, be sure to let me / us know if this helps. Smile

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
sjmathis
General User
General User


Joined: 19 Jan 2011
Posts: 5

PostPosted: Wed Jan 19, 2011 12:21 pm    Post subject: Reply with quote

Thanks for the advice about making a copy. I made a copy before I wrote my first post.

RPG: I read the topic about increasing the default Java parameters. I see where to enter it, but I'm not sure what the statement should look like. Did you mean to just enter a line with "Xmx512" in it? That's all?

Sliderule: Thanks for the quick response. I had tried the Tools>SQL idea already, based on an entry that I read in the forum, but as soon as I hit enter after the tools>SQL I immediately get the same error message. Seems to make sense in a way, in that the error happens in connecting to the database, so it's not surprising that as soon as you invoke SQL you would get the error.

I had already unzipped my file, and line 9 contains the following:

SET TABLE "Titles" INDEX'9125032 0'

I don't know how to interpret this line. The actual number of titles in the database is between 2600 and 2700. There are three other tables in the database. The lines referring to those tables are:

line 7: SET TABLE "Books" INDEX'8866168 0'
line 8: SET TABLE "Authors" INDEX'8787088 0'
line 10: SET TABLE "BTAs" INDEX'8683056 0'

The actual number of Books is around 2550, Authors is around 420, and the BTAs table has about 2800 records. Except for the Authors table, the value of the INDEX seems to be in the same ball park, however, I have no idea what the size of that number actually means.

Also, a final fact: The last thing I did with the database was to update one of the records in the Titles table, using a Form.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Wed Jan 19, 2011 12:51 pm    Post subject: Reply with quote

Since, you said you already have a BACKUP of the the OpenOffice Base file, and, you the SCRIPT file line 9 is:

sjmathis wrote:

SET TABLE "Titles" INDEX'9125032 0'


If it were me ( infamous last words Smile ) I would, using an editor, REMOVE ONLY line 9 from the SCRIPT file, and, rezip the file and open it . . . to see if that resolves the problem.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
sjmathis
General User
General User


Joined: 19 Jan 2011
Posts: 5

PostPosted: Wed Jan 19, 2011 1:50 pm    Post subject: Reply with quote

Yes, I agree that it might solve the problem, but I haven't tried exactly that, since my guess is that my "Titles" table would be gone, along with it's data?

I have tried a couple of other things, which lead me to believe that I have a fairly serious problem.

I read a post discussing how to repair a broken database, and tried to follow what it was suggesting.

When I tried to unzip the file, WinZip reported that the CRC associated with the 'data' file was not correct. So, I erased the 'data' file and replaced it with a copy of the 'backup' file. The I tried to re-create the zip file, but something went wrong and WinZip reported errors in the process. The result didn't look much like the original file - a lot of stuff was missing - but the 'data', 'backup', 'script', and 'properties' files were there, but some other things were missing, I believe related to the forms that I have created. I probably didn't have WinZip set up exactly right, so I may try that again.

I also tried extracting just the 'script' file, editing it and replacing it, but when I did that WinZip complained that my original zip file had a bad structure.

I also tried extracting and editing the 'properties' file in an attempt to change the hsqldb.cache_size_scale parameter (and the related hsqldb.cache_scale parameter), but again I could not get the altered file back into the zip file

I should also have mentioned something about the size of my files. My database.odb file is 6,361 Kb. When you look inside the zip file, the 'data' file is 9,399,152 (packed size: 2,980,061) and the 'backup' file 3,522,387 (packed size is 3,485,128). I believe I read something that indicated that HSQL had problems with files over 6 Meg, so perhaps I am running into some sort of limits.

Anyway, it looks like in order to get at my data at all, I'm going to have to figure out how to make a zip file that works. If you have any suggestions, please post them. Thanks for your help.

If all else fails, I have a backup of the database that is a couple of months old, but that would require hours of work to bring it up to date. Perhaps the next thing I'll try (tomorrow) is to drag out the backup and see what WinZip does with the backup file.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Wed Jan 19, 2011 1:58 pm    Post subject: Reply with quote

I am sending you a PM ( Private Message ). Please check there.

Sliderule
Back to top
View user's profile Send private message
sjmathis
General User
General User


Joined: 19 Jan 2011
Posts: 5

PostPosted: Thu Jan 20, 2011 7:58 am    Post subject: Reply with quote

A brief update.

As others have pointed out, the database.odb file is really a zipped file. Renaming it as database.zip allows you to un-zip the file and look at the contents. Among the objects found in the file is a 'data' file that apparently holds your actual data, a 'backup' file that holds a compressed version of your data, a 'script' file that contains the SQL commands for setting up your tables, a 'properties' file that sets up some parameters used by SQL, and other information.

When I try to extract the 'data' file, I get an error indicating that the result has failed the CRC check, or that my 'data' file is corrupt.

Although the original error message that I got indicated a problem in my script file, it seems pretty clear that my problem is that my 'data' file is corrupt. My question now is: How do I use the 'backup' file to restore my 'data' file? Simply replacing the file didn't seem to work. Perhaps it needs to be un-compressed? How do you do this? WinZip doesn't recognize it as an archive file.

As always - any help is deeply appreciated.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Thu Jan 20, 2011 8:04 am    Post subject: Reply with quote

I think you have made several assumptions that may NOT be true.

The data file, is NOT a 'readable' file. Therefore, you do NOT know there is a problem with that file. It is a binary file. :smile;

We did talk, in a PM about some alternatives.

If you really want to get into this, you could, use the hsqldb.jar file and set up a database file connected to the SCRIPT, PROPERTIES and DATA file ( unzipped from the Open Office zip ) and use the jar file to 'manipulate' it.

HSQL documentations:

http://hsqldb.org/doc/guide/guide.html


Sliderule
Back to top
View user's profile Send private message
sjmathis
General User
General User


Joined: 19 Jan 2011
Posts: 5

PostPosted: Fri Jan 21, 2011 12:39 pm    Post subject: Reply with quote

I have managed to fix my problem.

Turns out that I had a couple of problems with my database.odb file.

I copied the file, and renamed it to database.zip. Tried to un-zip it, but WinZip reported errors during the un-zipping process. In particular, when I tried to extract the 'data' file, I got a CRC error and a file-size that didn't match what it was supposed to be, according to the information inside the archive. My guess is that the 'data' file was at least mildly mangled in some fashion. Since it's a binary file, there's no way to look at it to find the error, if there is one.

I managed to extract the 'script' file and found the offending line in the file that was causing the "out-of-memory" error, but it turns out that the problem was elsewhere, and I assume it was being caused by the error in the 'data' file.

After some very interesting reading, I found some instructions in the hsqldb documentation that described how to repair a broken 'data' file. (Thanks Sliderule!) Basically, it suggests creating an empty 'data' file, and setting the "modified=no" line in the 'properties' file to "modified=yes". This tells the system that the database was not closed properly, and causes it to re-build the 'data' file from the 'backup' file.

I was able to extract the 'properties' file and edit it with Notepad to alter the line to "modified=yes". I wasn't able to simply insert this file back in the old archive, as it seems that my old archive was also corrupted in some fashion, and when I tried to insert the modified file, WinZip reported errors and quit. I ended up extracting all the files in the archive, fixing the 'properties' file, and replacing the 'data' file with a copy of the 'backup' file. Then I re-created the archive, and renamed it to database.odb.

I fired up Base, clicked on the Tables icon, and waited. After a few seconds, it started up normally. I'm now back in business, with all my data intact, including my forms, queries, and other stuff. Everything appears to work.

For good measure, since I have no idea what caused the problem, I ran the SQL command "Checkpoint defrag", in order to reduce the size of my file. The database file dropped from 6.3 Mb to 594 Kb. Finally, I made fresh backups of everything.

Thanks to everyone.
Back to top
View user's profile Send private message
problem-finder
General User
General User


Joined: 06 Jun 2012
Posts: 11

PostPosted: Wed Jun 06, 2012 7:59 am    Post subject: nearly similar problem Reply with quote

Dear all,

I have a similar problem. But in my case I have an unreadable backup file. Has anybody an idea?

Kind regards
P.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Wed Jun 06, 2012 8:06 am    Post subject: Reply with quote

problem-finder:

I am sending you a PM ( Private Message ).

Sliderule
Back to top
View user's profile Send private message
problem-finder
General User
General User


Joined: 06 Jun 2012
Posts: 11

PostPosted: Fri Jun 08, 2012 6:18 am    Post subject: Once again: Solved Reply with quote

Dear Sliderule,

thanks for your quick help and your exellent bugfixing! Without your help, I woulod have been in big trouble. Thank you so much!!!!!!!!!!!!!!!! Very Happy Very Happy Very Happy

Kind regards
P.
Back to top
View user's profile Send private message
Dskeans
Newbie
Newbie


Joined: 26 Jun 2012
Posts: 2

PostPosted: Tue Jun 26, 2012 6:30 pm    Post subject: Reply with quote

Sliderule,
It seems you are the master of fixing this. I am having the same problem. Could you assist?
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group