Bug 119694 - HSQLDB driver not loading causes BASE error in embedded hsqldb database
Summary: HSQLDB driver not loading causes BASE error in embedded hsqldb database
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.1.0.3 release
Hardware: All All
: highest major
Assignee: Not Assigned
URL:
Whiteboard: target:6.2.0 target:6.1.1
Keywords: bibisected, bisected, haveBacktrace, regression
Depends on:
Blocks: Database-Connectivity
  Show dependency treegraph
 
Reported: 2018-09-04 13:27 UTC by HTK300
Modified: 2018-09-10 23:47 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
screenshots (20.90 KB, image/png)
2018-09-04 13:29 UTC, HTK300
Details
screenshot 2 (19.92 KB, image/png)
2018-09-04 13:30 UTC, HTK300
Details
screenshot 3 (17.89 KB, image/png)
2018-09-04 13:31 UTC, HTK300
Details
screenshot 4 (19.12 KB, image/png)
2018-09-04 13:31 UTC, HTK300
Details
HSQLDB embedded screenshot (110.44 KB, image/png)
2018-09-05 07:37 UTC, HTK300
Details
JDK 9.0.1 screenshot (38.35 KB, image/png)
2018-09-05 07:39 UTC, HTK300
Details
JDK 9.0.1 screenshot (38.35 KB, image/png)
2018-09-05 07:40 UTC, HTK300
Details
odb BASE file with reduced sensitive record data (56.16 KB, application/vnd.oasis.opendocument.database)
2018-09-05 08:18 UTC, HTK300
Details
lldb backtrace (16.80 KB, text/plain)
2018-09-05 10:08 UTC, Alex Thurgood
Details

Note You need to log in before you can comment on or make changes to this bug.
Description HTK300 2018-09-04 13:27:19 UTC
Description:
> connection to data source cannot be established when trying to edit the records of the Tables of a BASE file

The connection to the data source "2011_FIRMS" could not be established
               The driver class " could not be loaded
The driver class " could not be loaded

   b) Error x: Error code: 1000 
               The driver class " could not be loaded

   c) Error x: SQL Status: HY000
               The driver class " could not be loaded


Steps to Reproduce:
1. double click on the BASE file 2011_FIRMS.odb
    (comment: this file has been created years ago and been in use growing over time and releases without problems, There are 104 records created within both Windows and macOS)
2. file opens up in the Forms chapter of the base
3. click on Tables chapter
4. error appears saying
   a) ERROR x: Description: The connection to the data source "2011_FIRMS" could not be established
               The driver class " could not be loaded

   b) Error x: Error code: 1000 
               The driver class " could not be loaded

   c) Error x: SQL Status: HY000
               The driver class " could not be loaded


After installing 6.1 Libreoffice the problem persisted

Actual Results:
I was able to recover my database by installing the previously used LibreOffice 6.0.4 that was the most recent version when records were created/added.

Expected Results:
tables shall successfully be able to open


Reproducible: Always


User Profile Reset: No



Additional Info:
the problem was reproducable in both Windows and macOS, meaning the BASE file could only be opened in the lower version 6.0.4.
Comment 1 HTK300 2018-09-04 13:29:53 UTC
Created attachment 144666 [details]
screenshots
Comment 2 HTK300 2018-09-04 13:30:33 UTC
Created attachment 144667 [details]
screenshot 2
Comment 3 HTK300 2018-09-04 13:31:02 UTC
Created attachment 144668 [details]
screenshot 3
Comment 4 HTK300 2018-09-04 13:31:43 UTC
Created attachment 144669 [details]
screenshot 4
Comment 5 Xisco Faulí 2018-09-04 14:09:14 UTC
Thank you for reporting the bug. Please attach a sample document, as this makes it easier for us to verify the bug. 
(Please note that the attachment will be public, remove any sensitive information before attaching it. 
See https://wiki.documentfoundation.org/QA/FAQ#How_can_I_eliminate_confidential_data_from_a_sample_document.3F for help on how to do so.)

I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' once the requested document is provided.
Comment 6 Alex Thurgood 2018-09-04 14:41:45 UTC
@HTK : please also provide the following information :

- type and version number of db engine ;
- name and version number of db driver ;
- version number of JDK (assuming that your ODB file requires Java).
Comment 7 HTK300 2018-09-05 07:37:24 UTC
Created attachment 144686 [details]
HSQLDB embedded screenshot
Comment 8 HTK300 2018-09-05 07:39:03 UTC
Created attachment 144687 [details]
JDK 9.0.1 screenshot

yes, the database requires Java !
Comment 9 HTK300 2018-09-05 07:40:52 UTC
Created attachment 144688 [details]
JDK 9.0.1 screenshot

yes Java is required by database
Comment 10 HTK300 2018-09-05 07:58:54 UTC
I found in ask.libreoffice.org this
mdb files not loading to base
which tells me, many users seem to have that problems that points to LO 6.1

Well, by removing personal data from my database I will try to send you the troubleshome file.

BTW I have always been working on a second base file which surprisingly does not have a problem with the update from LO 6.0.4 to 6.1
Comment 11 HTK300 2018-09-05 08:18:11 UTC
Created attachment 144689 [details]
odb BASE file with reduced sensitive record data

this is the base file that caused a problem opening from within LO 6.1.
It is now reduced by sensitive private data using LO 6.0.6 where I was able to work with the file.

I haven't yet installed LO 6.1 to verify if this file still cannot be opened in LO 6.1 with the reduced file content, but will do soon.

many thanks.

Harry
Comment 12 Alex Thurgood 2018-09-05 09:28:36 UTC
Confirming that the reduced sample file as provided by the OP will not show the table view with:

Version: 6.1.0.3
Build ID: efb621ed25068d70781dc026f7e9c5187a4decd1
Threads CPU : 4; OS : Mac OS X 10.13.6; UI Render : par défaut; 
Locale : fr-FR (fr_FR.UTF-8); Calc: group threaded
Comment 13 Alex Thurgood 2018-09-05 09:31:22 UTC
I'm using Oracle JDK 9.0.4.
Comment 14 Alex Thurgood 2018-09-05 09:39:17 UTC
The sample file loads and displays fine with

Version: 6.0.6.2
Build ID: 0c292870b25a325b5ed35f6b45599d2ea4458e77
Threads CPU : 4; OS : Mac OS X 10.13.6; UI Render : par défaut; 
Locale : fr-FR (fr_FR.UTF-8); Calc: group
Comment 15 Alex Thurgood 2018-09-05 09:49:46 UTC
Contents of properties file within ODB:

#HSQL Database Engine 1.8.0.10
#Wed Sep 05 10:03:20 CEST 2018
hsqldb.cache_version=1.7.0
sql.enforce_strict_size=true
hsqldb.compatible_version=1.8.0
hsqldb.lock_file=true
hsqldb.cache_scale=13
hsqldb.nio_data_file=false
version=1.8.0
runtime.gc_interval=0
readonly=false
hsqldb.cache_file_scale=1
hsqldb.cache_size_scale=8
hsqldb.log_size=10
hsqldb.script_format=0
hsqldb.default_table_type=cached
hsqldb.original_version=1.8.0
modified=yes
Comment 16 Alex Thurgood 2018-09-05 09:51:44 UTC
So, why isn't the correct driver being loaded ?

Anything to do with the changes made by Tamas ?
Comment 17 Alex Thurgood 2018-09-05 09:53:36 UTC
script file content:
SET DATABASE COLLATION "German"
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE CACHED TABLE "FIRMS"("SEQ" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"DATE" DATE,"JOBID" VARCHAR(75),"JOB_DESCR_" VARCHAR(99),"FIRSTNAME" VARCHAR(50),"LASTNAME" VARCHAR(50),"DEPARTMENT" VARCHAR(50),"COMPANY1" VARCHAR(75),"COMPANY2" VARCHAR(50),"STREET" VARCHAR(75),"EMAIL" VARCHAR(75),"CITY" VARCHAR(50),"POSTALCODE" DECIMAL(5),"COUNTRY" VARCHAR(50),"WORKPHONE" VARCHAR(25),"REMARK" VARCHAR(2047))
SET TABLE "FIRMS" INDEX'240 108'
ALTER TABLE "FIRMS" ALTER COLUMN "SEQ" RESTART WITH 108
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 60
Comment 18 Alex Thurgood 2018-09-05 10:04:09 UTC
Edited title to reflect findings
Comment 19 Alex Thurgood 2018-09-05 10:07:43 UTC
I'm seeing a sigsegv in master 6.2 alpha, backtrace attached.
Comment 20 Alex Thurgood 2018-09-05 10:08:25 UTC
Created attachment 144692 [details]
lldb backtrace
Comment 21 Lionel Elie Mamane 2018-09-05 10:23:08 UTC
I note that the content.xml of that odb file has:

<db:data-source db:connection-resource="sdbc:embedded:hsqldb">

while a newly created one in 6.1 has:

<db:connection-resource xlink:href="sdbc:embedded:hsqldb" xlink:type="simple"/>

I'm not sure this change is on purpose or a side-effect of another change. If it was on purpose, it is neither forwards-compatible nor backwards-compatible and needs to be seriously reevaluated!
Comment 22 Regina Henschel 2018-09-05 12:13:37 UTC
<db:data-source db:connection-resource="sdbc:embedded:hsqldb"> is invalid in ODF, because the <db:data-source> element has no attributes.
Comment 23 Julien Nabet 2018-09-05 12:15:23 UTC
I'm not sure since I'm not at home with my laptop but it seems the line is generated here:
https://opengrok.libreoffice.org/xref/core/dbaccess/source/filter/xml/xmlExport.cxx#636

632              else
633              {
634                  AddAttribute(XML_NAMESPACE_XLINK, XML_HREF,sValue);
635                  AddAttribute(XML_NAMESPACE_XLINK, XML_TYPE, XML_SIMPLE);
636                  SvXMLElementExport aElem(*this,XML_NAMESPACE_DB, XML_CONNECTION_RESOURCE, true, true);
637              }

Looking at the if:
597              m_aTypeCollection.extractHostNamePort(sValue,sDatabaseName,sHostName,nPort);
598              if ( sHostName.getLength() )
which leads there https://opengrok.libreoffice.org/xref/core/dbaccess/source/core/misc/dsntypes.cxx#198
and seeing history of this file, we may find something interesting.

Of course, the best would be to add some traces on sValue and other vars like sDatabaseName and above all sHostname.
Comment 24 Lionel Elie Mamane 2018-09-05 12:44:15 UTC
(In reply to Regina Henschel from comment #22)
> <db:data-source db:connection-resource="sdbc:embedded:hsqldb"> is invalid in
> ODF, because the <db:data-source> element has no attributes.

While I see that in ODF 1.2, I cannot find any reference to this, either as element or attribute. And anyway, to ensure backwards compatibility, we need to recognise it even if invalid.

In dbaccess/source/filter/xml/xmlDataSource.cxx around line 75, we see the recognition of db:connection-resource as attribute of db:data-source. From gawping at the code, it seems to still be there in LibreOffice 6.1. And it is doing exactly the same as the recognition of the xlink attribute of a db:connection-resource child of a db:data-source in dbaccess/source/filter/xml/xmlConnectionResource.cxx around line 65:

  xDataSource->setPropertyValue(PROPERTY_URL,makeAny(sValue));

so whatever is happening is more subtle.

The error message says that LibreOffice tries to load a JDBC driver with an empty class name; so somehow it recognises that it is a JDBC connection (and that is correct, embedded HSQLDB is a JDBC connection), but it does not recognise that it is an embedded HSQLDB...
Comment 25 Drew Jensen 2018-09-05 16:07:21 UTC
Also using attached test file under Ubuntu 18.04, LibreOffice 6.0 opened the file no problem, 6.1 and 6.2 do not. (nor do the later versions display the form file in that file before the connection)

Bibisect against updated 6.1 repo finds commit where anomaly arrived

https://cgit.freedesktop.org/libreoffice/core/commit/?
id=ebb34571c19c5ac939fbf5aed2ab66ee18e298dc

dbaccess: split up ODBFilter::CreateContext()

This was handling XML elements at 3 different levels; add some more
context classes to handle the nesting better.

Author: Michael Stahl 2017-11-30 18:00:53 +0100 (adding to CC)
Comment 26 Xisco Faulí 2018-09-06 09:25:48 UTC
Adding Cc: to Michael Stahl
Comment 27 Julien Nabet 2018-09-06 19:14:28 UTC
(In reply to Drew Jensen from comment #25)
> ...
> Bibisect against updated 6.1 repo finds commit where anomaly arrived
> 
> https://cgit.freedesktop.org/libreoffice/core/commit/?
> id=ebb34571c19c5ac939fbf5aed2ab66ee18e298dc
> ...
This commit has been done at 2017-11-30, isn't it included in 6.0 branch where the bug can't be reproduced?
Comment 28 Drew Jensen 2018-09-06 19:22:17 UTC
(In reply to Julien Nabet from comment #27)
> (In reply to Drew Jensen from comment #25)
> > ...
> > Bibisect against updated 6.1 repo finds commit where anomaly arrived
> > 
> > https://cgit.freedesktop.org/libreoffice/core/commit/?
> > id=ebb34571c19c5ac939fbf5aed2ab66ee18e298dc
> > ...
> This commit has been done at 2017-11-30, isn't it included in 6.0 branch
> where the bug can't be reproduced?

I didn't experience the problem in the 6.0 version I tested, but what I will do is to run the bibisect tool against the 6.0 repo also and see if it pops up there too.
Comment 29 Julien Nabet 2018-09-06 20:02:15 UTC
(In reply to Drew Jensen from comment #28)
> ...
> I didn't experience the problem in the 6.0 version I tested, but what I will
> do is to run the bibisect tool against the 6.0 repo also and see if it pops
> up there too.

Don't bother, I think you're right.
I'm gonna submit a patch to review. It's a small addition in the quoted commit.
Comment 30 Julien Nabet 2018-09-06 20:06:47 UTC
I submitted a patch on gerrit
https://gerrit.libreoffice.org/#/c/60117/

BTW, let's increase the importance since it concerns too Linux.
Comment 31 Commit Notification 2018-09-07 08:39:46 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=4b5194bd1d0dd893720ec0b311ee215a3168219f

tdf#119694: read embedded hsqldb file generated before 6.1

It will be available in 6.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 32 Julien Nabet 2018-09-07 08:41:33 UTC
Backport for 6.1 branch in gerrit: https://gerrit.libreoffice.org/#/c/60131/
Comment 33 Commit Notification 2018-09-07 11:20:11 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-6-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=6e0e3872f30c177559446b2c050b2a6582f257ec&h=libreoffice-6-1

tdf#119694: read embedded hsqldb file generated before 6.1

It will be available in 6.1.2.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 34 Commit Notification 2018-09-07 19:32:51 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-6-1-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=7d1612ca3e29dce226700551040ceac1de99299b&h=libreoffice-6-1-1

tdf#119694: read embedded hsqldb file generated before 6.1

It will be available in 6.1.1.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 35 Drew Jensen 2018-09-10 16:57:22 UTC
Verified fix using Ubuntu 18.04 and 
Version: 6.2.0.0.alpha0+
Build ID: 2789bbfd607240f260dfb38b6e9c19c9cf49fca9
Time: 2018-09-09_22:35:03

Can't verify the 6.1 build as it hasn't done so since the patch was added (but I'm sure not because of the patch being added ;)
Comment 36 Drew Jensen 2018-09-10 17:12:14 UTC
also I download the binary for the RC of 6.1.1
The anomaly is still in that binary.
Comment 37 Adolfo Jayme 2018-09-10 23:47:06 UTC
The change will appear in RC2