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] Base Crashes With >19,000 line table
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
Bizzork
General User
General User


Joined: 14 Feb 2011
Posts: 10

PostPosted: Mon Feb 14, 2011 6:28 pm    Post subject: [Solved] Base Crashes With >19,000 line table Reply with quote

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?


Last edited by Bizzork on Mon May 09, 2011 5:01 pm; edited 1 time in total
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: Mon Feb 14, 2011 7:23 pm    Post subject: Reply with quote

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

  1. 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:
    1. Make a BACKUP copy of your OpenOffice Base file ( *.odb ) just in case
    2. Open your OpenOffice Base file ( *.odb )
    3. On the left, press the Tables icon
    4. From the Menu: Tools -> SQL...
    5. In the Command to execute box, enter:

      Code:
      shutdown compact;

    6. Press the Execute button
    7. In a few seconds, when you see Command successfully executed, Press the OK button
    8. Save and Exit your OpenOffice Base file ( *.odb )
    9. 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


  2. 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.
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Wed Feb 16, 2011 11:42 am    Post subject: Reply with quote

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 + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
Bizzork
General User
General User


Joined: 14 Feb 2011
Posts: 10

PostPosted: Fri Feb 18, 2011 8:57 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Bizzork
General User
General User


Joined: 14 Feb 2011
Posts: 10

PostPosted: Fri Feb 25, 2011 9:00 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
r4zoli
Super User
Super User


Joined: 17 May 2005
Posts: 570
Location: Budapest, Hungary

PostPosted: Sat Feb 26, 2011 12:42 am    Post subject: Reply with quote

Upgrade your OOo 3.3 if you use earlier version.
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Sat Feb 26, 2011 1:22 am    Post subject: Reply with quote

Bizzork wrote:
After around 200 entries, Base crashed again.

Do you see 'JDBC' or 'Embedded database' in your Base status bar?
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
Bizzork
General User
General User


Joined: 14 Feb 2011
Posts: 10

PostPosted: Sat Feb 26, 2011 7:45 am    Post subject: Reply with quote

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?)
Back to top
View user's profile Send private message
r4zoli
Super User
Super User


Joined: 17 May 2005
Posts: 570
Location: Budapest, Hungary

PostPosted: Sat Feb 26, 2011 8:27 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Bizzork
General User
General User


Joined: 14 Feb 2011
Posts: 10

PostPosted: Sat Feb 26, 2011 6:25 pm    Post subject: Reply with quote

r4zoli - WIN7 is my operating system. I don't know which version, whatever stock was in May 2010.
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Sat Feb 26, 2011 11:22 pm    Post subject: Reply with quote

Bizzork wrote:
...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?)

Note the bottom of the Base window as shown below.

Do you see this?


or perhaps this?


Since it is possible to hide the status bar, at least programmatically, there are other ways to determine which database configuration you're currently using....
If you right-click within the windows as shown above in Base, do you see all three options or only one?
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
Bizzork
General User
General User


Joined: 14 Feb 2011
Posts: 10

PostPosted: Fri Apr 01, 2011 4:29 pm    Post subject: Reply with quote

dacm - I see the "Embedded Database" as shown above.
Back to top
View user's profile Send private message
Bizzork
General User
General User


Joined: 14 Feb 2011
Posts: 10

PostPosted: Sun Apr 03, 2011 11:38 am    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Sun Apr 03, 2011 2:02 pm    Post subject: Reply with quote

Bizzork wrote:
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? ...should I expect to see a file with a different name after the zip?

The migration of an "Embedded database" to a "multi-mode" HSQLDB database, involves extracting and renaming the database files contained within the source .odb zip file.

Breaking down the .odb zip file:
    The files within the 'database' folder are named: script, properties, data and backup. These filenames do not reflect a database name. When you drag-&-drop them into your 'mydb' folder, they will retain those generic filenames. Notice there's no file extension associated with these filenames.

    To be used with a "multi-mode" configuration (file/server modes) these files must be renamed (pre-pended) to reflect the database name. In this case, you'll need to rename these 4 files as follows: mydb.script, mydb.properties, mydb.data, and so on. So basically, you're adding the 'mydb.' prefix in each case, while the former filename becomes the file extension. You may need to "Show file extensions" in Windows 7, to verify the results when renaming files in this manner.

Once this is accomplished, click on mydb.file.odb ('file: mode') or START.vbs ('server mode') to verify Base access to your newly-extracted Tables.
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
Bizzork
General User
General User


Joined: 14 Feb 2011
Posts: 10

PostPosted: Sun Apr 10, 2011 8:42 am    Post subject: Reply with quote

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.
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