[Solved] Base Crashes With >19,000 line table

Bizzork · General User Joined: 14 Feb 2011 Posts: 10

First off, I am a noob, so I apologize in advance. I have a table that is 19,000+ lines long, and otherwise unnotable (actually possibly some of the fields are set up with inordinately large cell capacities, but I personally don't think they're the issue). Once I broke the 19k threshhold, Base started crashing after entering a few lines of new data. I would strongly prefer to keep the data I'm entering in one table. The data will eventually get to around 150,000 rows of 14 columns each.

If Base did not repeatedly crash (and occasionally wipe out 100+ new lines), I would have no problem with keeping the table in Base, as it is otherwise a good fit for my needs. That being said, I'm betting that I am overloading Base and can't count on it to handle this many rows. My next idea is to export the table's data out of Base (either into Excel or Access), however this does not seem to work with a copy/paste option. Is there a way I can get the data out of Base and into another database format, or is there anything you could recommend to stave off the crashes?

Sliderule · Posted: Mon Feb 14, 2011 7:23 pm Post subject:

Let me answer you in two ways, a SHORT term answer, and, a LONG term answer.

SHORT TERM: I suspect, that your OpenOffice Base file ( *.odb ) contains UNUSED space, as you may have deleted records, or, modified table settings etc.

Therefore, issue a command to SHRINK the file size back to an optimum. Per HSQL documentation, found at:

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

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

SHUTDOWN COMPACT

Writes out a new .script file which contains the data for all the tables, including CACHED and TEXT tables. It then deletes the existing text table files and the .data file before rewriting them. After this, it backs up the .data file in the same way as normal SHUTDOWN. This operation shrinks all files to the minimum size.

To issue the command . . . do the following:

Make a BACKUP copy of your OpenOffice Base file ( *.odb ) just in case
Open your OpenOffice Base file ( *.odb )
On the left, press the Tables icon
From the Menu: Tools -> SQL...
In the Command to execute box, enter:

Code:

shutdown compact;
Press the Execute button
In a few seconds, when you see Command successfully executed, Press the OK button
Save and Exit your OpenOffice Base file ( *.odb )
Restart your OpenOffice Base file and see if that help. You might want to COMPARE the OpenOffice file size to your backed up file to see if it is alot smaller

LONG TERM I would recommend you use the HSQL database engine, BUT, not as an EMBEDDED version, as delivered out of the box, but rather, in FILE:MODE as described very clearly by DACM in the post below:

http://user.services.openoffice.org/en/forum/viewtopic.php?f=83&t=17567

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.

dacm · Super User Joined: 07 Jan 2010 Posts: 734

And here's a step that may be necessary whether you stick with the SHORT TERM or migrate to the LONG TERM solution, based on your database size. Here's another post that can help you to access the necessary 'properties' file -- note steps (1)-(3) -- as part of the extended SHORT TERM solution.
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Migrating 'Embedded databases'

Bizzork · General User Joined: 14 Feb 2011 Posts: 10

Sliderule and Dacm - Thanks for the replies. I implemented the short term solution, from what it looks like, that dropped the size from 2.33 to 2.21 MB.

Inspite of Dacm's clarity, I'm not sure I implemented the long term option correctly. I downloaded the two files here: http://www.oooforum.org/forum/viewtopic.phtml?p=396523#396523 listed under "Automatically create a new HSQLDB 2.0 multi-mode database". That's all I did though, so I don't know if I read or implemented the solution correctly. The file seems to be working a little more smoothly, although I haven't added a great deal to it yet (about 10 lines). The display appears to be the same, which I'm not convinced was really the goal with adding HSQLDB. That said, I probably botched my interpretation of the solution. Thanks again, let me know if I'm on the right track.

Bizzork · General User Joined: 14 Feb 2011 Posts: 10

After around 200 entries, Base crashed again. Nothing was lost, but I believe the error is reproduceable. Any instance where I overwrite a cell seems to create the instability.

r4zoli · Posted: Sat Feb 26, 2011 12:42 am Post subject:

Upgrade your OOo 3.3 if you use earlier version.

dacm · Super User Joined: 07 Jan 2010 Posts: 734

Bizzork · General User Joined: 14 Feb 2011 Posts: 10

r4zoli - I am running OpenOffice 3.3.0, and I believe I have been for most (if not all) of the crashes.

dacm - No I do not see either of those in my Base status bar. Is there a specific comment that I could reference to get either of these in there? (Or do I want to avoid those?)

r4zoli · Posted: Sat Feb 26, 2011 8:27 am Post subject:

What O? win or linux? 32bit or 64 bit version (on linux)? Java version?

I read in one bug with java 1.6.0_24 which cause OOo Base crash, under linux.

Bizzork · General User Joined: 14 Feb 2011 Posts: 10

r4zoli - WIN7 is my operating system. I don't know which version, whatever stock was in May 2010.

dacm · Super User Joined: 07 Jan 2010 Posts: 734

Bizzork · General User Joined: 14 Feb 2011 Posts: 10

dacm - I see the "Embedded Database" as shown above.

Bizzork · General User Joined: 14 Feb 2011 Posts: 10

I'm understanding the issue better now, but I still don't know if I have a handle on the intricacies. I realize I'm to the point where I have to convert the Embedded Database to file:mode. I downloaded mydb, that's set up. I even created a new database with the correct link in there to use file:mode. The zip/unzip process is proving to be a challenge at this point. I zipped the file, but I'm not understanding the unzip process and remaining steps. When I unzip into the mydb folder, I'm not seeing a file populate in that file with the same name as the zipped one. Is this unexpected? It's entirely possible I messed up the unzip. Would you guess that is likely the issue? Or should I expect to see a file with a different name after the zip?

dacm · Super User Joined: 07 Jan 2010 Posts: 734

Bizzork · General User Joined: 14 Feb 2011 Posts: 10

G. Open mydb.odb in Base and click on the Tables icon to see your tables.

I get through step F, to the above step, and I don't seem to have a file named mydb.odb. The mydb file is a folder housing the data file, script file, etc, but it is not a Base file. I can open my copy of the original database, and see the tables there, but the status bar still displays the "Embedded Database" information. I then attempt to drag and drop my original database reports, forms, and queries, but I get the "not possible" (looks like no smoking) icon.