HikariCP failing for HSQL file based DB

Question

I am running HSQLDB on server mode. here is the code snippet.

Server server = new Server();
server.setDatabaseName(0, "testdb");
server.setDatabasePath(0, "file:testdb;MVCC=TRUE;user=" + userName + ";password=" + password);
server.start();

When I am trying to access the DB through HikariCP I am getting unauthorization error.

Here is code snippet to create HikariCP :-

HikariConfig hikariConfig = new HikariConfig();
hikariConfig.setJdbcUrl("jdbc:hsqldb:hsql://localhost:9001/testdb;MVCC=TRUE");
hikariConfig.setDriverClassName(DRIVER);
hikariConfig.setUsername(dbConf.getString("user"));
hikariConfig.setPassword(dbConf.getString("password"));
DataSource dataSource = new HikariDataSource(hikariConfig);

NOTE: instead of using file: if I use mem: everything is working fine.

Update:- Exception log while trying to create HikariDataSource

java.sql.SQLInvalidAuthorizationSpecException: invalid authorization specification: "test"
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.5.0.jar:2.5.0]
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.5.0.jar:2.5.0]
    at org.hsqldb.jdbc.JDBCConnection.<init>(Unknown Source) ~[hsqldb-2.5.0.jar:2.5.0]
    at org.hsqldb.jdbc.JDBCDriver$1.run(Unknown Source) ~[hsqldb-2.5.0.jar:2.5.0]
Caused by: org.hsqldb.HsqlException: invalid authorization specification: "test"
    at org.hsqldb.error.Error.error(Unknown Source) ~[hsqldb-2.5.0.jar:2.5.0]
    at org.hsqldb.ClientConnection.<init>(Unknown Source) ~[hsqldb-2.5.0.jar:2.5.0]
    ... 2 more

Update:- Server Logs:-

[Server@4cc7932f]: [Thread[HSQLDB Server @4cc7932f,5,main]]: server.maxdatabases=10
[Server@4cc7932f]: [Thread[HSQLDB Server @4cc7932f,5,main]]: server.tls=false
[Server@4cc7932f]: [Thread[HSQLDB Server @4cc7932f,5,main]]: server.port=9001
[Server@4cc7932f]: [Thread[HSQLDB Server @4cc7932f,5,main]]: server.trace=false
[Server@4cc7932f]: [Thread[HSQLDB Server @4cc7932f,5,main]]: server.database.0=file:testdb;user=admin;password=m]7>V0H%~TYgUv39
[Server@4cc7932f]: [Thread[HSQLDB Server @4cc7932f,5,main]]: server.restart_on_shutdown=false
[Server@4cc7932f]: [Thread[HSQLDB Server @4cc7932f,5,main]]: server.no_system_exit=true
[Server@4cc7932f]: [Thread[HSQLDB Server @4cc7932f,5,main]]: server.silent=false
[Server@4cc7932f]: [Thread[HSQLDB Server @4cc7932f,5,main]]: server.default_page=index.html
[Server@4cc7932f]: [Thread[HSQLDB Server @4cc7932f,5,main]]: server.address=0.0.0.0
[Server@4cc7932f]: [Thread[HSQLDB Server @4cc7932f,5,main]]: server.dbname.0=testdb
.
.
.
.
[Server@4cc7932f]: [Thread[HSQLDB Server @4cc7932f,5,main]]: openServerSocket() exiting
[Server@4cc7932f]: [Thread[HSQLDB Server @4cc7932f,5,main]]: openDatabases() entered
[Server@4cc7932f]: [Thread[HSQLDB Server @4cc7932f,5,main]]: Opening database: [file:testdb]
[Server@4cc7932f]: Database [index=0, id=0, db=file:testdb, alias=testdb] opened successfully in 778 ms.
[Server@4cc7932f]: [Thread[HSQLDB Server @4cc7932f,5,main]]: openDatabases() exiting
[Server@4cc7932f]: Startup sequence completed in 785 ms.
[Server@4cc7932f]: 2020-06-17 14:12:46.829 HSQLDB server 2.5.0 is online on port 9001

Hikari Logs:-

19:42:46.846 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - Driver class org.hsqldb.jdbcDriver found in Thread context class loader java.net.URLClassLoader@70dea4e
19:42:46.846 [vert.x-eventloop-thread-1] [%vcl] INFO  x.y.z.handler.ClusterVerticleDeployHandler - Hikari Properties : {}
19:42:46.847 [vert.x-eventloop-thread-1] [%vcl] INFO  x.y.x.ClusterVerticleDeployHandler - Hikari UserName : admin
19:42:46.847 [vert.x-eventloop-thread-1] [%vcl] INFO  x.y.z.ClusterVerticleDeployHandler - Hikari Password : m]7>V0H%~TYgUv39
19:42:46.852 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - HikariPool-1 - configuration:
19:42:46.855 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - allowPoolSuspension.............false
19:42:46.855 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - autoCommit......................true
19:42:46.855 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - catalog.........................none
19:42:46.855 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - connectionInitSql...............none
19:42:46.856 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - connectionTestQuery.............none
19:42:46.856 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - connectionTimeout...............30000
19:42:46.856 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - dataSource......................none
19:42:46.856 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceClassName.............none
19:42:46.856 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceJNDI..................none
19:42:46.860 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceProperties............{password=<masked>}
19:42:46.861 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - driverClassName................."org.hsqldb.jdbcDriver"
19:42:46.861 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - exceptionOverrideClassName......none
19:42:46.861 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - healthCheckProperties...........{}
19:42:46.862 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - healthCheckRegistry.............none
19:42:46.862 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - idleTimeout.....................600000
19:42:46.862 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - initializationFailTimeout.......1
19:42:46.862 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - isolateInternalQueries..........false
19:42:46.863 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - jdbcUrl.........................jdbc:hsqldb:hsql://localhost:9001/testdb;MVCC=TRUE
19:42:46.863 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - leakDetectionThreshold..........0
19:42:46.863 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - maxLifetime.....................1800000
19:42:46.863 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - maximumPoolSize.................10
19:42:46.864 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - metricRegistry..................none
19:42:46.864 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - metricsTrackerFactory...........none
19:42:46.864 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - minimumIdle.....................10
19:42:46.864 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - password........................<masked>
19:42:46.864 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - poolName........................"HikariPool-1"
21:29:19.718 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - readOnly........................false
21:29:19.718 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - registerMbeans..................false
21:29:19.718 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - scheduledExecutor...............none
21:29:19.718 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - schema..........................none
21:29:19.718 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - threadFactory...................internal
21:29:19.718 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - transactionIsolation............default
21:29:19.718 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - username........................"admin"
21:29:19.718 [vert.x-eventloop-thread-1] [%vcl] DEBUG com.zaxxer.hikari.HikariConfig - validationTimeout...............5000

If it works with mem:, but not with file:, it means something stored in the files does not match. Try deleting the files, or find out what username and password was stored in the existing files. — fredt, Jun 13 '20 at 08:56
@fredt Server is able to start the DB but instantiation of HikariPool is throwing an exception — Ravat Tailor, Jun 15 '20 at 04:32
My advice is still the same. When you start the server with mem: it is guaranteed the database is empty. With file: it reads the database files if they exist. The connection error happens later when you try to connect to server. — fredt, Jun 16 '20 at 12:10
This also I tried, When I start the server it create testdb.tmp and after that it fails. — Ravat Tailor, Jun 16 '20 at 12:22
@fredt The thing is DB server get start successfully even in file based mode but when it try to create Hikari datasource it fails — Ravat Tailor, Jun 16 '20 at 12:27
Try connecting to the db server with DatabaseManager and your user-name and password. Check the username and password match those used for Hiraki. — fredt, Jun 16 '20 at 13:36
Try running the server with silent=false to see the connection attempts when accessing via database manager and Hiraki. Username and/or password should be the same. — fredt, Jun 17 '20 at 10:59
The username in the server log and in your Hiraki config are different! — fredt, Jun 18 '20 at 15:32
Sorry, I made mistake while editing the actual values, I updated the logs again, user Name is same. — Ravat Tailor, Jun 18 '20 at 16:01
I have a suggestion: Try with a simple user name such as 'test' and a simple password such as 'pw' in both configurations. You should see the connection attempt in the server messages. Then check the error message from Hiraki. Good luck. — fredt, Jun 18 '20 at 16:14
1. start the server. 2. connect with DatabaseManager 3. check the server messages. 4. try connect with Hiraki 5. check server messages. You should see a connection attempt in the server messages for each connection. When you first reported, the error was for user=test, but your real server user was testdb. You may be making similar mistakes again with username and password. You need to solve your setting issues yourself. — fredt, Jun 18 '20 at 17:00
I have provided hardcoded user name and password in both server and hikari username=test and password = pwd — Ravat Tailor, Jun 18 '20 at 17:23
In log it shows trying to attempt the connection with username test after so many tries it throws an exception invalid authorisation — Ravat Tailor, Jun 18 '20 at 17:25
Here everything is hard coded, I am not reading it dynamically — Ravat Tailor, Jun 18 '20 at 17:26
@fredt, Sorry for asking again and again, I this the URL jdbc:hsqldb:hsql://localhost:9001/testdb;MVCC=TRUE seems to wrong to connect file based DB because I am not able to connect with Database-manager too — Ravat Tailor, Jun 19 '20 at 05:04
1. start the server. 2. try to connect with DatabaseManager or any other process. 3. if it fails, it means username and/or password is not correct. — fredt, Jun 19 '20 at 12:50

HikariCP failing for HSQL file based DB

0 Answers0