Introduction

So we have a public repo we've been using to train developers on Liferay, but we're using HSQL.

We did this on purpose because we couldn't guarantee that developers would have a common local database with necessary permissions to reset, overwrite, etc.

For a lot of reasons, using HSQL made a lot of sense.

Except for one - Liferay upgrades.

You see, for the longest time, Liferay would bail rather than upgrade an HSQL database.

This concerned the team a great deal, but since we couldn't find a better way for the first iteration of the course, we just moved forward with HSQL and decided to table the upgrade issue until later.

Well now the 2024 Q2 release is available, so later is here...

Of course I just tried using the new version w/ my old HSQL lportal.script file, and of course it bailed out as I expected with the ever so helpful "You cannot upgrade HSQL..." message.

But, after a bit of playing around, I was actually able to apply the upgrade and start the environment, so I think that upgrades are possible, and I wanted to share the process.

Warning

Steps To Upgrade HSQL

The key to being able to upgrade the HSQL database was to start it up as a standalone database server. This is actually pretty easy to do, so let's just dive in with the steps:

1. Start up Standalone HSQL Database

For this, I actually copied Liferay's tomcat/webapps/ROOT/WEB-INF/shielded-container-lib/hsql.jar directly into the data/hypersonic directory. I did this just to ensure that the paths would be clear.

Then I just issued the following command from within the data/hypersonic directory:

$ java -cp hsql.jar org.hsqldb.Server -database.0 file:lportal -dbname.0 lportal
[Server@6a41eaa2]: Startup sequence initiated from main() method
[Server@6a41eaa2]: Could not load properties from file
[Server@6a41eaa2]: Using cli/default properties only
[Server@6a41eaa2]: Initiating startup sequence...
[Server@6a41eaa2]: Server socket opened successfully in 8 ms.
[Server@6a41eaa2]: Database [index=0, id=0, db=file:lportal, alias=lportal] opened 
  successfully in 1269 ms.
[Server@6a41eaa2]: Startup sequence completed in 1279 ms.
[Server@6a41eaa2]: 2024-07-02 19:21:38.795 HSQLDB server 2.7.1 is online on port 
  9001
[Server@6a41eaa2]: To close normally, connect and execute SHUTDOWN SQL
[Server@6a41eaa2]: From command line, use [Ctrl]+[C] to abort abruptly

And that's it. Your database is actually now running as a standalone database. I'm guessing that you could perhaps use DBeaver or some other tool to connect as long as it could access the driver.

We'll actually have to set that up in the next step...

2. Run the DB Upgrade Client

To run the db upgrade client, it first needs to be configured. The important file for this blog is the portal-upgrade-database.properties file. Mine contained the following:

jdbc.default.driverClassName=org.hsqldb.jdbc.JDBCDriver
jdbc.default.url=jdbc:hsqldb:hsql://localhost/lportal
jdbc.default.username=SA
jdbc.default.password=

Configure the rest as you normally would.

When you're ready, issue the command ./db_upgrade_client.sh (or bat). It will maybe run right away, maybe prompt you for app server details, but it will run.

I got most of the way through but then ended at the gogo prompt and it claimed that there were some failures.

I just issued some commands to complete the task...

• upgrade:check This will check to see if there are any upgrades that have yet to be run. You want to repeat the execution until this results in no pending upgrades.
• upgrade:executeAll This will execute all pending upgrades.
• verify:check This checks the status of the registered verify processes, you want them all to show as succeeded.
• verify:executeAll This will run all of the verify processes.

So even though it said I had some failed upgrades, when i did the upgrade:executeAll and verify:executeAll, everything came out clean.

3. Shut down the HSQL Database

So this was a tiny bit tricky. The console message says you can use Control-C to quit, but that might not be a clean shutdown so you might not end up with the lportal.script file being updated.

In the data/hypersonic folder, I created the following Shutdown.java file:

import java.sql.*;
import javax.sql.*;

public class Shutdown {
  public static void main(final String[] args) throws Exception {
    Connection conn = DriverManager.getConnection(
      "jdbc:hsqldb:hsql://localhost/lportal", "SA", "");
    Statement stmt = conn.createStatement();
    stmt.execute("SHUTDOWN");
    conn.close();
  }
}

This is HSQL's method for cleanly shutting down the database and ensuring the lportal.script file will be written correctly.

With the java file ready, I just compiled it using the command javac Shutdown.java and then I could execute the command using java -cp hsql.jar:. Shutdown and the database shut down cleanly.

4. Fire up Liferay and Test

The last step was to fire up the newer version of Liferay and see if it would accept my updated HSQL database.

My environment did start up cleanly, reported no errors or upgrades that required running, and once I was in it was completely functional.

My conclusion? This actually worked. I'm glad it's only a developer environment, so I could have wiped it out and just started fresh if I needed to, and if things went south I still had the old lportal.script file I could go back to, but so far everything has been going well.

Conclusion

It goes without saying that this is a completely unsupported blog. If you have been using HSQL outside of your development environment and you try this to upgrade and it fails, you cannot reach out to Liferay Support, this configuration is not in the compatibility matrix and they cannot help you upgrade your HSQL database.

Likewise if it fails in your development environment, Support will not be able to help you there either.

No, this is not supported by Liferay in any way, shape or form.

That said, I was able to upgrade my local development environment this way, and I bet you may be able to upgrade yours too if you need to.

But then again, if you're really doing something in a database that you need for longer than say your current sprint, you should consider using Postgres or MariaDB instead of HSQL and avoid all of these complications anyway.