![[TEST] Document Foundation Community](https://communitytest.documentfoundation.org/images/discourse-logo-sketch.png){kind=logo}
Hi
I've invested a considerable amount of time in constructing tables, forms
and queries for a project of mine. I successfully produced 3 nice reports
using Report Builder. Now, I'm starting to observe strange behavior in
Base. Queries and Reports I've prepared days earlier suddenly disappear
after I've used them in Report Builder to prepare or edit a Report. As an
example, I've just used Report Builder to design a report, saved my work
and closed the file. I then ran the report in the viewer and it was fine,
but needed minor tweaking.I closed the viewer and then right clicked on the
icon for my report in order to continue editing. LO closed down! When I
restarted, LO blithely announced it was recovering my database. It did, but
the new Report and the modifications to a query I made in this session were
gone. I think* most* of this behavior is due to Report Builder. Has anyone
else observed this type of behavior before ?.
Hank
We need some basic information. What version of LibreOffice are you using? What operating system do you use? Do you use backups or AutoRecovery? Have you ever used CHECKPOINT as a SOL command? It closes the database files, rewrites the script file, deletes the log file and opens the database. If CHECKPOINT DEFRAG is specified, this command also shrinks the .data file to its minimal size.
--Dan
Hi Dan,
Thanks for your quick response. I"m using LO version3.6.4.3 Build ID
:2ef5aff on a Windows 7 Pro machine. My machine's processor is an Intel
i7-2600K with 8Gig of memory and 1TB hard drive. I use periodic backups to
another machine on my network. As for AutoRecovery, whenever there is a
catastrophic failure Base auitomatically tries to recover my database file
on the next start-up. Is that what you're referring to.? If not, I was
unaware that Base has such a feature.Is it similar to that used In MS Word?
As for CHECKPOINT, I have not used that SQL command. When would it be
appropriate to use it? As for CHECKPOINT DEFRAG, when should that be used.
That might be useful as Base is getting much less responsive, seems
bloated.. The size of my database file is about 360 KB.
Hank
The HSQLDB user guide for version 1.8 (which is what is used in all LO versions) is available at:
https://wiki.documentfoundation.org/Documentation/Publications. Scroll down to section 5.2. It should be answer your questions. The AutoRecovery is found: Tools > Options > Load/Save > General. Look in the Save section for settings for backups and AutoRecovery. I have not used MS Word since I used Win 98SE in the 90's so I do not know. (I have used Linux for more than ten years.)
CHECKPOINT DEFRAG might be a good command to use to reduce the size of the database. Another command that you could use when you finish working with Base: SHUTDOWN COMPACT. The user guide explains what this will do. But only use it when you are ready to close Base. Then use Tools > SQL and enter SHUTDOWN COMPACT; in the Command to execute box. The semi-colon is required at the end of the command. After executing this command successfully, close Base. If your database is somewhat bloated, this will reduce its size.
I found this in an email I received earlier, perhaps from this mailing list:
- hsqldb embedded databases will now systematically be subjected to a "checkpoint" and/or "checkpoint defrag" on saving of the database file. The aim with this change is to force hsqldb to optimize the size of the db, especially where frequent deletions of data sets are carried out. With a bit of luck, it might actually bring long-term benefits like increased data security and stability (he says, crossing fingers). There may well be a performance hit when closing the ODB file, especially on big databases, but the developer who implemented this change feels that people who want to manipulate large datasets should be using something a bit more appropriate than an embedded hsqldb...
The date on the above email was a couple of weeks ago. So, this statement likely does not apply to LO 3.6.4.3.
--Dan
Hi Dan,
I really appreciate your help in this matter. If I am able to reduce the
crashes I'll post again to let everyone know.
Hank
Hi Hank,
I
restarted, LO blithely announced it was recovering my database. It did, but
the new Report and the modifications to a query I made in this session were
gone. I think* most* of this behavior is due to Report Builder. Has anyone
else observed this type of behavior before ?.
My own personal advice would be not to use the autorecovery feature for
ODB files. What this feature does is save what is in the volatile memory
of the application at a given moment. This would be all well and good,
BUT, and this is the killer : an ODB file does not load all data from
the tables, reports, queries, etc into LO's volatile memory, only parts
of that data (crap design, I know, but it was done initially for
performance reasons). This means that if you get a crash, only a part of
your whole ODB file data is actually saved to a temporary file, which is
then used during the autorecovery process. The fact that you have not
lost more data/queries/reports is pure luck so far.
Instability of Base ODB files when opened within the LO application in
general is one reason (apart from dependence on Java) why there is a
search on for a replacement backend that would allow for more robust
binary streaming. In the end, it may turn out that the ODB file, as with
other db backends, e.g. server backends like mysql and postgres, will
become just a container for forms, queries and reports, and the actual
data will be held in a separate file. This should actually make the data
handling more robust, but performance might become an issue (not that
current hsqldb/ODB performance is stellar, coz it isn't). Someone was
looking at the possibility of using firebird. For the moment, however,
nothing has been decided or actually undertaken as far as I know.
If you really need to rely on your data keeping its coherency and
consistency, my advice would be not to use the default hsqldb/ODB pair
that is provided as the default. It is a shame really, as hsqldb in
itself, certainly in its 2.0 version, is a capable db engine. It is just
the integration with AOO/LO that is one of the root causes of many of
the problems that users experience.
Alex
Hi 
From what i can gather it is better to have the tables as data in an external database. Apparently the internal ancient and heavily tweaked version of HSqlDb is a bit problematic but moving the tables to an external proper and more recent version (or to a different back-end entirely)
http://hsqldb.org/web/hsqlFAQ.html
Unfortunately HSqlDb is currently written in Java and Java has been deteriorating badly over the last 2 years so it might be better to find a different back-end. Apparently Postgresql, MySql/MariaDb are good fairly large scale back-ends but they might be a lot heftier than you really need. I'm still trying to find a good one for smaller amounts of data, such as one for a reasonable address-book.
You might find that increasing a lot of the memory/Ram options might reduce crashes.
Tools - Options - Memory
Most of the defaults are set very low because OOo and LO have typically been used on very low spec machines. Now as people are using them on heftier machines a lot of those could be bumped up significantly or even doubled or more.
Ii was wondering if other programs also crash. If so then it might be a wobbly graphics card and pushing it in firmly can help. Also every year or so my home machine starts crashing because of dust build-up inside the case. Hoovering it carefully, especially the cpu's heat-sink, solves it. Of course one of the dangers is getting rid of static from the hoover's plastic noozle. Swiftly moving air rubbing over plasitc is a great way to build-up static and any movement over carpets is another good way. Even the tiny static charge naturally occuring on skin is enough to fry some components so it's tricky to be careful enough. So, soemtimes i just blow the dust out of the ehat-sink but it's a weird choking dust so i keep a glass of milk or mango juice nearby in case i breathe the dust in. Water or wine tend to be tooo dry.
Regards from
Tom
You can't currently successfully load a second, more recent version of
hsqldb.jar into LO (via the Java classpath/archive declaration dialog)
without untoward consequences, i.e. you can no longer open your existing
hsqldb ODB files without getting a driver loader error...
see here :
https://bugs.freedesktop.org/show_bug.cgi?id=34411
Alex
If you really need to rely on your data keeping its coherency and
consistency, my advice would be not to use the default hsqldb/ODB pair
that is provided as the default. It is a shame really, as hsqldb in
itself, certainly in its 2.0 version, is a capable db engine. It is just
the integration with AOO/LO that is one of the root causes of many of
the problems that users experience.
Isn't hsqldb *still* a (relatively) good choice, as long as it is installed and
used *external* to Base? That does of course mean it's still not the "default"
setting.
Regards
Mark Stanton
Hi
I thought the back-end stayed external and Base would just link to it?
Is the connector for this a bit broken too?
Regards from
Tom
Hi Tom,
I thought the back-end stayed external and Base would just link to it?
Is the connector for this a bit broken too?
It has nothing to do with a connector, I was referring to your question
re the possibility of substituting internal hsqldb for an external one.
This is discussed in the Base user guide, I believe, but it no longer
works, if you want to use a different version of hsqldb than the one
that LO uses internally. Anway, this is off topic with regard to the
initial question.
Alex
As I mentioned in my answer to Tom, if you use an external version of
hsqldb, it can only be the same version number as the internal version
provided with LO, else the old ODB files can no longer be opened. In
other words, you can not install an external hsqldb jar and expect your
old ODB(hsqldb) files to keep working, as this will throw an error.
So yes, it still remains a possibility, but one that you may want to
avoid if you still have old ODB files that use the internal hsqldb with
an older revision number than the externa jar you plug into LO.
Alex
Hi Alex, Mark, Tom,
You guys scare me ! I'm beginning to think I'd better start looking for a
new back end. I've put a lot of work into this project. Tom, this is not a
small database like an address book. After normalizing, my design has 16
tables.( with a few more coming as I've discovered as I feed more data
into it.) If I connect to an external server is there any way I can save my
forms and reports? Or shall I have to start anew ? My tables, queries and
views were all produced using SQL and I have text copies of all of them to
feed any server I might select. Transfer of data becomes a problem, of
course.
Hank
Hi Hank,
I haven't been following this thread closely, but it seem like you are
experiencing some of the same problems I had using Base HSQLDB. I
eventually decided to move my Accounting Project to the H2 Database Engine,
(see http://www.h2database.com/html/main.html).
Some of the many advantages of H2 are -
- Separate files for Data and Project
- Can be run in single or multi-user mode
- Users report successfully running very large databases
- An active users forum
I have found H2 to be very stable and have not regretted making the change.
If you are interested at all, I can point you to a very good tutorial for
replacing HSQLDB with H2.
This may not be what you are looking for, but it's probably worth your
while at least having a look at what H2 has to offer.
Noel
Thanks for answer
After several trys to delete and reinstall libreOfficeImpress I looked for any file in the computer:
sudo find / -name libreoffice* -print
and deleted all of them and installed libreOfficeImpress new
from ubuntuSoftwareCenter
And ... its working
In one of these files must have been the wrong configuration, which happened through copy and paste the 2 minute long video to libreOfficeImpress. I have read, that the correct way to input a video I have to use the "input"-button. Yet I did not try the big video, but I can go on working with libreOffice, which makes me happy
If the problem will occure with the big video, I will try H2
Thanks for help
And to allay your fears a little Hank,
the job of moving your *data* to a new back end can be as easy as
dragging and dropping tables. Setting up the new back end might
introduce you to some new ideas so it might take a little to get your
head around it, but once that's done it might not be hugely
difficult.
It's possible, of course, that there will be some little niggling
problems that need to be resolved manually, but it's possible that
the move can go relatively smoothly and easily.
Regards
Mark Stanton
Hi
That is something that has been said by quite a fe people so i'm kinda happy to rely on it for migrating the database i'm vaguely working on.
At the moment my main worry is getting a couple of the smaller tables into Base's internal back-end and if i manage that then i hope to just simply copy the rest of the tables out of Access and directly into something more sensible. There was an excellent post about doing that eons ago. Although i might have problems finding it, at least i know it's around.
Regards from
Tom
Hi :)
Preferably before trying the big video please go to
Tools - Options - Memory
and radically increase the various settings in there so that the program can cope with the video. Does the whole video really have to be embedded in the slide-show/file or could it just be linked to and then the 2 files carried around together? It might seems a little clunkier that way but it does increase flexibility for the longer-term or if the show needs to appear on lower-spec machines (such as netbooks or tablets or hand-helds). It's not always possible easily so don't worry too much about it. It's just a thought.
Several assumptions there that 'need' to be demolished.
1. LibreOffice is one single program/application with several different parts to it. Those parts should really be called "modules" rather than apps but we tend to use the wrong word to minimise endless repetitions of explanations and discussions which are mostly irrelevant to whatever problems the original poster had. Obviously it back-fires sometimes as in this case. So, Writer, Draw, Impress, Calc and Math are all 'just' modules within the program and the 1 single program is LibreOffice. You dealt with that just fine in the end but sometimes it's a struggle to explain.
2. Reinstalling does not reset settings nor configurations. You fixed a much more serious issue with the reinstall. The program/package itself 'must' have been broken in some way. Perhaps a corrupted download or something.
Generally if you have weird problems and want to "get back to factory defaults" just to see if it was somethign in the settings or perhaps some 3rd party Extension struggling then just rename the User Profile
https://wiki.documentfoundation.org/Documentation/UserProfile
Actually on Friday i was able to rapidly fix a broken Gimp session on a colleague's machine by following that familiar link rather than trying to find my way through Gimp's own documentation. Not that Gimp's is bad, just that i had under a minute to find&fix.
So, welcome in and regards from
Tom
Many of these "advantages" also apply to HSQLDB if you know what you are doing. I refer to using Base to connect to the database rather than it be "embedded". In this case, tables, queries, forms, and reports can be copied from the embedded database to the external one. This is done using HSQLDB 1.8 database engine in Base. Then you should be able to upgrade Base to using HSQLDB 2.8 which includes features not available in the 1.8 version. This does require having a version of LibreOffice dedicated for this purpose (for example, using 3.6.5.2 for HSQLDB 2.8, and 4.0.0 for embedded databases).
The draft of chapter 3 of the Base contains discussion of this in the last section.
--Dan
Hi Noel,
Thank you for pointing me to H2. It appears to offer so many advantages
over the embedded HSQLDB in LO Base that I shall certainly spend some time
examining
its possibilities. I'd definitely like to see the tutorial for replacing
HSQLDB with H2.
Hank