Database only opens in safe mode after registration

[Azura]

I have a split database that I designed on Linux Mint 19.2 using Libreoffice Base version 6.0.7.3. It works on that computer flawlessly. I need to port an exact copy to another computer. This has been set up with Linux Mint 20.1 and runs Libreoffice 6.4.7.2. After making an exact copy of the files on the first system, I can open up the database on the second system until I register the database. I need to do this in order to allow me to connect a form in Writer. After I register the database I receive the following error when I try to open the form or when I try to move to the tables tab in Base: [error in script file line: 1 Unexpected token UNIQUE, requires COLLATION in statement [SET DATABASE UNIQUE]]

At this point I can get the database to open in Safe Mode. I saw that many people have linked this to a user profile issue. If I reset the user profile and start over I can access the database again, right up to the point when I need to register it. Then the same error occurs. However, I can continue using it in Safe Mode after registering it. It’s not linked to the jar version either, as I am copying across exactly the same files as were used on the first system. Any thoughts on this would be welcome.

[RobertG]

Then have a look at Options → LibreOffice Base → Databases. Does this differ between the working version and the version on Mint 20.1?

The only possibility for getting a HSQLDB 1.8 will be the file hsqldb.jar in the folders of LO. If you don’t use internal hsqldb I would try to rename this file and see, what is happening.

Also one (last?) hint:
What is the content of data source setting in content.xml? It must be linked to your external hsqldb.jar for HSQLDB 2.3.2. This is the content of the file here:
<db:data-source-setting-value>file:///home/robby/Dokumente/LibreOffice/HSQLDB/hsqldb.jar</db:data-source-setting-value>

[Azura]

Thanks for your reply. Going through your ideas one by one:

1. It differs in that the paths are different to reflect the fact that they are stored in different places on each computer (the second computer was set up by someone else and I had no power over their choice of username) BUT they both lead to the correct place. Everything else is the same.

2. I renamed the hsqldb.jar file in usr/lib/libreoffice/program/classes (I hope that’s the right one) - it made absolutely no difference at all.

3. I think you mentioned this before. I checked again and the content of the data source setting in the content.xml definitely points to the correct external hsqldb.jar

[RobertG]

Don’t know what else we can do here. The error shows there must be a hsqldb.jar, which works as HSQLDB 1.8, but the file you load is HSQLDB 2.3.2.

So the only solution seem to be for you: Don’t register database and link the form directly to the file.

[Azura]

I think the most likely explanation is that I have probably made a very silly error whilst setting up the database, the repercussions of which are only being felt now. But at least it’s possible to work around it. Thanks again for all your help; it’s been very much appreciated!

[RobertG]

Seems you are connecting to the database through the internal hsqldb.jar 1.8 when register the database.

Have a look for

db:data-source-setting-value
file:///home/…/hsqldb.jar
</db:data-source-setting-value>

inside the content.xml of the Base file. There must be the link to your external hsqldb.jar version 2.*

You could also link this by makro, which has to be executed only one time in the datsbase file:

SUB Start
Const cPath = "/home/…/hsqldb.jar"
DIM oDataSource AS OBJECT
DIM oSettings AS OBJECT
DIM sURL AS STRING
sURL = ConvertToURL(cPath)
oDataSource = ThisComponent.DataSource
oSettings = oDataSource.Settings
oSettings.JavaDriverClassPath = sURL
END SUB

Note: edit the path to the hsqldb.jar you need.

[Azura]

Thanks for this suggestion - I’ve had a look at the content.xml and the entry for the </db:data-source-setting-value> is definitely the correct path to the hsqldb jar.

[RobertG]

… but the error, which appears, shows definitely: It tries to connect by old HSQLDB to a new database.

HSQLDB 1.8 Scriptfile starts this way:

SET DATABASE COLLATION "German"
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
…

HSQLDB 2.5 Scriptfile starts this way:

SET DATABASE UNIQUE NAME HSQLDB6E22848424
SET DATABASE GC 0
…

I have registered the external database for HSQLDB 2.5 in LO. Works as expected with LO 7.1.5.2 on OpenSUSE 15.2 64bit rpm Linux.

[Azura]

Thanks for your reply. I’ve checked the jar version for the original database, which is 2.3.2 (thank you Ratslinger for the helpful macro you posted elsewhere). This is the external jar that was also transferred to the second computer. Since the external jar must also be 2.3.2 and the redirect to the external jar is correct in the content.xml, does this mean that the database has not split properly and the database is still trying to run the LO embedded version of the jar i.e. 1.8? Is there something else I need to run on the second computer to make it work?

[RobertG]

Seems the external database will run with hsqldb 1.8. This is the information I get from “error in script file …”.

Is there another hsqldb.jar, which appears in Tools → Options → LibreOffice → Advanced → Class Path? There shouldn’t be any entry for Hsqldb.

By the way: You don’t need to register the database for external forms in Writer. You could also link the form to the database. You only need to set the database as datasource for the form.

But: I would prefer to find the reason of the buggy behavior on your system.

[Azura]

Thanks for your continued assistance No, there’s nothing in the Class Path dialogue - I checked this very carefully after reading through a similar thread. There are no entries in there at all.

Well if I don’t need to register the database that is certainly good news as the Safe Mode workaround I’m using at the moment is quite long-winded. But I’m happy to try any other suggestions you may have to solve this bug, especially as this might help someone else as well in the future. Please let me know if you have any other ideas. This is the first time I’ve ever used Libreoffice Base for a project, the first time I’ve ever made such a complicated database and the first time I’ve tried to port it to a different computer, so it wouldn’t surprise me at all if I’ve done something silly when setting it up!

[RobertG]

Seems we need more details, also screenshots.

Here is the statusbar of my external HSQLDB. It links directly to the file.

It will show the connection to the folder /Database . Inside this folder are the 4 files “Sport.backup”, “Sport.database”, “Sport.properties” and “Sport.script”. Name of the database is “Sport”.

Does the content of the statusbar change when you connect before registering the database and after you have registered the database?

Content of the file “Sport.properties” here:

#HSQL Database Engine 2.5.0
#Tue Aug 17 07:29:45 CEST 2021
tx_timestamp=0
modified=no
version=2.5.0

Whats the content in your database before and after registration?

[Azura]

Here’s the screenshot of my status bar:

And here’s the content of the .properties file for my database:

I started again from scratch (i.e. I first deleted the user profile before doing anything else) and then compared the results before and after database registration. Both the status bar and the .properties file showed no changes at all; they were exactly identical.

[RobertG]

I see 4 frontslashes in the statusbar file:////. How did you get this frontslashes? There is only one frontslash in my connection-string.

[Azura]

I have absolutely no idea - the home folder is in the usual location on both computers. The //// feature is also present in the status bar of the database when I’m using it on the original system.

[Azura]

As per Ratslinger’s suggestion on this thread Old split hsqldb won't connect in new Linux Mint 20.2 and LO 6.4.7.2 - #7 by Ratslinger it’s a problem with the LO version bundled with Mint 20. Update to 7.5.1.2 to resolve issue.