HSQLDB - user lacks privilege or object not found: SQLState(42501) vendor code(-5501)

Question

I have migrated from hsqldb_1.8.10 to hsqldb_2.3.2 version but suddenly hsqldb stops working. I tried a lot to solve the issue but could not find the root cause of the issue. I have googled it and got numerous links but nothing could solve my problem.

I have copied the log and script file for reference.

DriverManager Log

DriverManager.getConnection("jdbc:hsqldb:hsql://ip_address/database")
trying driver[className=sun.jdbc.odbc.JdbcOdbcDriver,sun.jdbc.odbc.JdbcOdbcDriver@1c695a6]
*Driver.connect (jdbc:hsqldb:hsql://ip_address/database)
trying driver[className=org.hsqldb.jdbc.JDBCDriver,org.hsqldb.jdbc.JDBCDriver@1386918]
getConnection returning       
driver[className=org.hsqldb.jdbc.JDBCDriver,org.hsqldb.jdbc.JDBCDriver@1386918]
SQLState(42501) vendor code(-5501)
java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: RESULTS

Database.script file

SET DATABASE UNIQUE NAME HSQLDB44D4CB4776 SET DATABASE GC 0 SET DATABASE DEFAULT RESULT MEMORY ROWS 0 SET DATABASE EVENT LOG LEVEL 0 SET DATABASE TRANSACTION CONTROL LOCKS SET DATABASE DEFAULT ISOLATION LEVEL READ COMMITTED SET DATABASE TRANSACTION ROLLBACK ON CONFLICT TRUE SET DATABASE TEXT TABLE DEFAULTS '' SET DATABASE SQL NAMES FALSE SET DATABASE SQL REFERENCES FALSE SET DATABASE SQL SIZE TRUE SET DATABASE SQL TYPES FALSE SET DATABASE SQL TDC DELETE TRUE SET DATABASE SQL TDC UPDATE TRUE SET DATABASE SQL TRANSLATE TTI TYPES TRUE SET DATABASE SQL CONCAT NULLS TRUE SET DATABASE SQL UNIQUE NULLS TRUE SET DATABASE SQL CONVERT TRUNCATE TRUE SET DATABASE SQL AVG SCALE 0 SET DATABASE SQL DOUBLE NAN TRUE SET FILES WRITE DELAY 500 MILLIS SET FILES BACKUP INCREMENT TRUE SET FILES CACHE SIZE 10000 SET FILES CACHE ROWS 50000 SET FILES SCALE 32 SET FILES LOB SCALE 32 SET FILES DEFRAG 0 SET FILES NIO TRUE SET FILES NIO SIZE 256 SET FILES LOG TRUE SET FILES LOG SIZE 50 CREATE USER SA PASSWORD DIGEST 'd41d8cd98f00b204e9800998ecf8427e' ALTER USER SA SET LOCAL TRUE CREATE SCHEMA PUBLIC AUTHORIZATION DBA ALTER SEQUENCE SYSTEM_LOBS.LOB_ID RESTART WITH 1 SET DATABASE DEFAULT INITIAL SCHEMA PUBLIC GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.SQL_IDENTIFIER TO PUBLIC GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.YES_OR_NO TO PUBLIC GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.TIME_STAMP TO PUBLIC GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CARDINAL_NUMBER TO PUBLIC GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CHARACTER_DATA TO PUBLIC GRANT DBA TO SA SET SCHEMA SYSTEM_LOBS INSERT INTO BLOCKS VALUES(0,2147483647,0)

Answer 1

I was also getting the same error. At last i solved it myself. The issue was because of the incorrect connection url.

connection = DriverManager.getConnection
             ("jdbc:hsqldb:file:///D:/hsql/testdb;shutdown=true;ifexists=true", "sa", "");

Here testdb is the name of the db you are trying to connect. i was using the eclipse data source plugin to connect to hsql. while configuring the same, i have given the database location as D:/hsql/testdb. so that caused the issue.

I was trying to retrieve a table from the testdb, before creating a table in testdb.

Also if you connecting to db using eclipse data source plugin make sure that you have executed SHUTDOWN command before running the java application, otherwise it will throw following error

Database lock acquisition failure: lockFile:
org.hsqldb.persist.LockFile@d9999e76[file =D:\hsql\testdb.lck, exists=true, locked=false, valid=false, ] 
method: checkHeartbeat read: 2015-09-30 15:09:55 heartbeat - read: -3700 ms

Hope this is helpful.