java.sql.SQLException: Connections could not be acquired from the underlying database! — HSQLDB

Question

I have installed SQL Workbench/J Build 118 and I logged into the HSQL database of Liferay Portal.

Then I closed the SQL Workbench without making any changes to the database. I restarted Liferay tomcat server and then I get the following errors:

ERROR [localhost-startStop-1][DialectDetector:124] java.sql.SQLException:
Connections could not be acquired from the underlying database!

and this warnings:

WARN  [C3P0PooledConnectionPoolManager[identityToken->3c8jofUN]-HelperThread-#4][BasicResourcePool:1851]
com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@3249a129 --
Acquisition Attempt Failed!!! Clearing pending acquires.
While trying to acquire a needed new resource, we failed to succeed more than
the maximum number of allowed acquisition attempts (3).
Last acquisition attempt exception:
java.sql.SQLException: error in script file line: 
7 unexpected token: CONFLICT required: DEADLOCK

_{ps. I am using the latest version of HSQL JDBC Driver (v.2.3.3)}

Answer 1

I resolved the issue by changing this line in the %LIFERAY_DIR%/data/hsql/lportal.script file :

SET DATABASE TRANSACTION ROLLBACK ON CONFLICT TRUE

to

SET DATABASE TRANSACTION ROLLBACK ON DEADLOCK TRUE

then I deleted this line:

SET DATABASE SQL AVG SCALE 0

And I started the server successfully.

Answer 2

hsql in Liferay is good for a quick demo. For anything that goes beyond this, I'd recommend a separate database. With MySql and Postgresql there are two free options that are readily available. hsql stores in a file, I'm not sure about backwards compatibility (in case your workbench uses a different driver version than Liferay) and two processes can't access one file at the same time.

Granted, you can also operate hsql as a server application, but IMHO that would be stretching it too far. Just install a proper database and you have all the tooling you need. There's no real overhead on today's computers.