Error moving split database to new computer (error in script file line: 1 Unexpected token UNIQUE)

[Doranwen1]

I’m running Linux Mint 21.2, with LO 7.3.7.2. I was using Mint 18.1 previously and finally managed to get the hardware to build a new system, after which I’ve been configuring and moving data over. When I tried moving my fanfic database over, a split HSQLDB one, I ended up with the following error when trying to open and use it:

The connection to the data source "Fanfic" could not be established.
error in script file line: 1 Unexpected token UNIQUE, requires COLLATION in statement [SET DATABASE UNIQUE] ./connectivity/source/drivers/jdbc/Object.cxx:175

It appears to be the same issue as this issue (and possibly this one), but the other LO one was fixed by upgrading to a LO version lower than mine. And then the last comment in that thread was someone having trouble with the version of LO that I have, which doesn’t bode well for a solution.

I tried replacing the hsqldb.jar in my database with a more recent one - but 2.7.2, the most recent I could find, does not work. Does anyone have any ideas? This database is relatively complex (it’s got at least 8 junction tables) and the thought of trying to rebuild it all over again… No, please no, lol. Someone tell me there’s a solution to this!

[Wanderer]

@Doranwen1

replacing the hsqldb.jar in my database with a more recent one - but 2.7.2, the most recent I could find, does not work

I’d say @Villeroy is the expert here, bit if I’m remembering right, you have to chose a version before 2.5. In the following Link 2.4.1 seems to work.

error in script file line: 1 Unexpected token UNIQUE, requires COLLATION in statement [SET DATABASE UNIQUE]

Having previously created and used many times, ‘split’ databases using hsqldb.jar (ver 2.4.0), I now cannot gain access to the actual database tables; the above message appears. The same message is displayed when attempting to open a form, the form requiring access to the database tables. I have ‘googled’ for a solution without success, the general consensus being that LO (along with hsqldb.jar) has been updated since being created with hsqldb.jar (ver 1.8.n) It has been quite a while since I …

[Villeroy]

Go back to the version that is specified in the database’s property file.
You can not upgrade from 1.8 to 2.5 or later.
From 1.8 you can upgrade to 2.4.1 and from there to a later version.

[Doranwen1]

The version specified in the properties file is 2.3.2, which is what I already have, I believe. Can I confirm that you’re suggesting to replace it with the 2.4.1 file first, and try to open it with that? (I tried it just now with a fresh extraction of the database - from the zip file I used to transfer it over - and it still complains about the error.) After that should I be trying to upgrade it further, or is there something else that I should be doing to make it work?

[Villeroy]

I don’t know.

[UnklDonald]

I’m not sure that this will help but if you still have a working database on another computer at ToolS>SQL execute

SHUTDOWN SCRIPT

then close the working database.
That copies all the table data into the .script file

Making sure LO is not running, copy all the files to the target computer.

If you want to use a different version of HSQL now is the time to replace the hsqldb.jar file.

When you restart Base, it should copy all the table data into the .data file in a format compatible with the HSQL version currently in use.

[Doranwen1]

I do still have access to the old computer - it has to run for quite a while as I’m slowly transferring things over and configuring stuff. I tried the shutdown script command and closed LO entirely on the new computer, but alas, it did not work.

I’m wondering whether I ought to try uninstalling the version of LO that was in my repo and install a newer version from a deb file instead. I hate making drastic changes like that to installed packages, but this is a critical thing that has to be sorted out. The post on the OpenOffice forums did have the user find success with it after doing that.

[Doranwen1]

I figured it out - I did indeed have to remove LO entirely from my computer and manually install a new version (7.6.4.1). At that point I dropped the latest hsqldb.jar in - and it worked from then on, no complaining. I guess 7.3.7.2 just doesn’t work with HSQL no matter what.

Anyway, I definitely recommend people try that. Uninstalling all of LO was a bit tricky but I just set apt to removing every package starting with libreoffice and it worked. I moved out a few extensions and dictionaries I didn’t think I needed, but otherwise I installed all the deb files included in the manual installation. Had to update any program shortcuts on panels to manually point to libreoffice7.6 instead of just libreoffice, but it’s all working now.