Bug 53473 - Embedded HSQLDB calls flush (CHECKPOINT) too often
Summary: Embedded HSQLDB calls flush (CHECKPOINT) too often
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
(earliest affected)
Inherited From OOo
Hardware: All All
: highest normal
Assignee: Not Assigned
Depends on:
Blocks: Database-Connectivity
  Show dependency treegraph
Reported: 2012-08-14 03:43 UTC by Lionel Elie Mamane
Modified: 2017-12-03 16:57 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:

scr (deleted)
2016-06-08 21:05 UTC, Mediot Listo

Note You need to log in before you can comment on or make changes to this bug.
Description Lionel Elie Mamane 2012-08-14 03:43:56 UTC
Originally reported by Fred Toussi as an aside in bug 53333:

> I tested with AOO as I was upgrading from OOo
> 3.2 on Windows.

> While testing with a simple embedded database and AOO, I noticed the CHECKPOINT
> statement is being sent to the embedded database after each SELECT statement
> (these are issued internally while browsing table contents). This should not
> happen. A CHECKPOINT uses significant resources and rewrites the .script and
> .backup files even if the data has not changed at all.

This needs to be checked if it happens also with LibreOffice. It probably does.

It probably comes from the "fix" to https://issues.apache.org/ooo/show_bug.cgi?id=55493.

According to my grepping in the sources, CHECKPOINT is called from the HSQLDB driver's connection::flush(); this in turn is called from:

dbaccess/source/ui/browser/unodatbr.cxx: SbaTableQueryBrowser::impl_releaseConnection()

dbaccess/source/ui/app/AppController.cxx: OApplicationController::disconnect

This "temporary hack" became permanent :-|

What to do about it without reintroducing i#55493, I'm not sure. Maybe save an HSQLDB .log file that will be replayed at the next open of this file? Try to "guess" if each query was a pure data selection or also a modification and commit the storage (and checkpoint) only if there was a modification? I'm unsure how much it will help anyway, since we have to save it inside a ZIP file anyway, which takes O(n), with n=total size of data, for each write (!).
Comment 1 Lionel Elie Mamane 2012-08-14 03:46:17 UTC
Fred, when you wrote:

> [2] A less advanced form of EVENT LOG is supported by HSQLDB 1.8.0 and set via
> a URL property (no SQL statement is available).

you were referring to hsqldb.applog, right?

Note to self & other LibreOffice developers: see http://www.hsqldb.org/doc/1.8/guide/ch04.html
Comment 2 Fred Toussi 2012-08-14 23:16:54 UTC
Re hsqldb.applog: yes, this turns on the event log. In version 1.8.0, you can include this in the .properties file of a database embedded in .odb if you want to try it.

Re the need for flush() and CHECKPOINT in the OO 3.4 code, (I am referring here to the logical files which are zipped by OO):

Among the database files, the .log file is implemented as an OutputStream. HSQLDB writes to this stream, and at regular timed intervals calls the FileDescriptor#sync() method.

An instance of FileAccess is provided by OO. The FileDescriptor is provided by this instance.

It is not necessary to write the changes to the zip at each commit. It can be delayed until the FileDescriptor#sync() is called. The delay can be a few  seconds.

Note HSQLDB 1.8.0 and 2.x write the changes and commits to the .log file somewhat differently, which may explain the frequent flush() and CHECKPOINT I observed in my tests.

It would be worth comparing the code to the CWS, as this was written against versions 2.0 and 2.1.
Comment 3 Jochen 2012-08-30 07:05:04 UTC
Changed status to "NEW"
Comment 4 Fred Toussi 2013-01-20 20:57:58 UTC
I have spent some more time on the pattern of checkpoints. Also read the original OOo bug report.

The purpose of the CHECKPOINT is valid for the current implementation of the internal zip stream in order to save all data as soon as a chenge is made. However, it should be possible to improve it and avoid excessive checkpoints when no data has changed.
Also note that at the time of the bug report version was used, which has advanced to version This probably has different sync and related characteristics.

It seems at the moment two "tricks" are used in tandem as a consequence of the OOo bug report.

When a table is opened directly by double clicking, SET WRITE_DELAY 0 and SET WRITE_DELAY 60 are executed, even when no change is made to the data.

It also seems any read causes the checkpoint. It seems if a checkpoint does not happen, the stream that reads from the .zip is not reset after a read and will throw an exception.

It should be possible to fix the issue without too many alterations.

1. Ensure the read stream is reset after reads. For this, check the code and see what is done with readonly databases and use the same method for all databases.
2. Set the WRITE_DELAY=0 as a permanent setting
3. Whenever a FileDescriptor.sync() is issued on the write stream used for the .log file, check if the stream length has gone up by a lot more than the length of a "COMMIT" string and perform the checkpoint only in this case. A change contains at least "DELETE FROM tablename WHERE x=n" (at least 20 characters). The WIRTE_DELAY=0 ensures the sync is called only at a commit and not at other times.

As mentioned on bug 53333 you can use a custom version 2.2.9 or 2.3.0 jar with logging enabled in order to see which commands are sent to the database.
Comment 5 tommy27 2013-09-07 07:09:00 UTC
hi there. is this bug still present in recent 4.0.5 or 4.1.1 releases?
Comment 6 Michael Meeks 2014-03-25 12:16:35 UTC Comment hidden (obsolete)
Comment 7 tommy27 2014-05-12 19:05:45 UTC
please retest against current release.
if issue is still there, please move it to mab4.2 list since 4.1.x is EOL
Comment 8 Lionel Elie Mamane 2014-05-12 19:13:54 UTC
(In reply to comment #7)
> please retest against current release.
> if issue is still there, please move it to mab4.2 list since 4.1.x is EOL

As developer, I'm fairly sure the issue is still there.
Comment 9 Björn Michaelsen 2014-07-12 20:13:05 UTC
MABs should be priority highest.
Comment 10 tommy27 2014-11-29 17:39:02 UTC
please retest with 4.3.x or 4.4.x branches
if issue is still there please move it to the mab4.3 list since 4.2.x is EOL
Comment 11 Lionel Elie Mamane 2014-11-29 17:52:03 UTC
(In reply to tommy27 from comment #10)
> please retest with 4.3.x or 4.4.x branches
> if issue is still there please move it to the mab4.3 list since 4.2.x is EOL

As developer, I'm fairly sure the issue is still there.
Comment 12 Alex Thurgood 2015-01-03 17:41:09 UTC Comment hidden (no-value)
Comment 13 Robinson Tryon (qubit) 2015-09-03 10:25:16 UTC
(In reply to Lionel Elie Mamane from comment #8)
> (In reply to comment #7)
> > please retest against current release.
> > if issue is still there, please move it to mab4.2 list since 4.1.x is EOL
> As developer, I'm fairly sure the issue is still there.

All bugs with priority=highest should have a Version set. I'll add, but it sounds like this bug goes back much further.
Comment 14 Mediot Listo 2016-06-08 21:05:17 UTC
Created attachment 125560 [details]
Comment 15 Christian Lohmaier 2016-06-09 12:29:10 UTC
The content of attachment 125560 [details] has been deleted for the following reason:

Comment 16 QA Administrators 2017-09-01 11:19:40 UTC
** Please read this message in its entirety before responding **

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present on a currently supported version of LibreOffice 
(5.4.1 or 5.3.6  https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the version of LibreOffice and 
your operating system, and any changes you see in the bug behavior
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave 
a short comment that includes your version of LibreOffice and Operating System

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not 
appropriate in this case)

If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3)


2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to "inherited from OOo";
4b. If the bug was not present in 3.3 - add "regression" to keyword

Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team