HSQL database user lacks privilege or object not found error

Question

I am trying to use hsqldb-2.3.4 to connect from Spring applicastion.

I created data base using the following details

Type : HSQL Database Engine Standalone
Driver: org.hsqldb.jdbcDriver
URL: jdbc:hsqldb:file:mydb
UserName: SA
Password: SA

I created a table named ALBUM under "MYDB" schema

In spring configuration file:

<bean id="jdbcTemplate"
    class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
    <constructor-arg ref="dbcpDataSource" />
</bean>

<bean id="dbcpDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="org.hsqldb.jdbcDriver" />
    <property name="url" value="jdbc:hsqldb:file:mydb" />
    <property name="username" value="SA" />
    <property name="password" value="SA" />
</bean>

And in my spring controller I am doing jdbcTemplate.query("SELECT * FROM MYDB.ALBUM", new AlbumRowMapper());

And It gives me exception:

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT * FROM MYDB.ALBUM]; nested exception is java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: ALBUM
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:982)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861)
javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

If I execute same query through SQL editor of hsqldb it executes fine. Can you please help me with this.

Better if you can share your SQL script which creates MYDB. SELECT * FROM ALBUM instead of SELECT * FROM MYDB.ALBUM. The problem could be with the username and password. By default the password is empty with username as "sa", case insensitive. — Azim, Jul 17 '16 at 07:58

score 12 · Answer 1 · answered Jul 16 '16 at 22:39

12

user lacks privilege or object not found can have multiple causes, the most obvious being you're accessing a table that does not exist. A less evident reason is that, when you run your code, the connection to a file database URL actually can create a DB. The scenario you're experiencing might be you've set up a DB using HSQL Database Manager, added tables and rows, but it's not this specific instance your Java code is using. You may want to check that you don't have multiple copies of these files: mydb.log, mydb.lck, mydb.properties, etc in your workspace. In the case your Java code did create those files, the location depends on how you run your program. In a Maven project run inside Netbeans for example, the files are stored alongside the pom.xml.

answered Jul 16 '16 at 22:39

Arthur Noseda

2,139
16
22

1

I was using PGSQL and running tests with HSQL. I was testing a service but another service was still connected to the PGSQL database. This is what caused the error for me: Having the PGSQL running while trying to test the service with HSQL. After I stopped the PGSQL instance, the error was gone. – DraganescuValentin Jul 27 '17 at 08:24
In my case it was caused by wrong order of test run/cleanup scripts. Tests was not finished while DB was cleaned up. I've got this cryptic errors a lot of times randomly and fixed it by properly ordering of cleanup scripts. – Vladimir Rozhkov Jul 04 '18 at 11:33
I ran into this error due to a keyword used for a column name. This error however was not showing up correctly until I set the `sql.syntax_*` on the connection url as stated below by Damien MIRAS and savier. – Brian Sep 20 '20 at 14:20

score 10 · Answer 2 · answered Dec 11 '17 at 08:27

If you've tried all the other answers on this question, then it is most likely that you are facing a case-sensitivity issue.

HSQLDB is case-sensitive by default. If you don't specify the double quotes around the name of a schema or column or table, then it will by default convert that to uppercase. If your object has been created in uppercase, then you are in luck. If it is in lowercase, then you will have to surround your object name with double quotes.

For example:

CREATE MEMORY TABLE "t1"("product_id" INTEGER NOT NULL)

To select from this table you will have to use the following query

select "product_id" from "t1"

CREATE TABLE DATA_LATEST_BY ( DATASRC VARCHAR(5) NOT NULL, DATE_LATEST VARCHAR(10) NOT NULL ); I am using the above script but still I am getting same issue — sambatha, Feb 13 '18 at 09:12

score 9 · Answer 3 · edited Jun 03 '19 at 21:58

As said by a previous response there is many possible causes. One of them is that the table isn't created because of syntax incompatibility. If specific DB vendor syntax or specific capability is used, HSQLDB will not recognize it. Then while the creation code is executed you could see that the table is not created for this syntax reason. For exemple if the entity is annoted with @Column(columnDefinition = "TEXT") the creation of the table will fail.

There is a work around which tell to HSQLDB to be in a compatible mode for pgsl you should append your connection url with that

"spring.datasource.url=jdbc:hsqldb:mem:testdb;sql.syntax_pgs=true"

and for mysql with

"spring.datasource.url=jdbc:hsqldb:mem:testdb;sql.syntax_mys=true"

oracle

"spring.datasource.url=jdbc:hsqldb:mem:testdb;sql.syntax_ora=true"

note there is variant depending on your configuration it could be hibernate.connection.url= or spring.datasource.url= for those who don't use the hibernate schema creation but a SQL script you should use this kind of syntax in your script

SET DATABASE SQL SYNTAX ORA TRUE;

It will also fix issues due to vendor specific syntax in SQL request such as array_agg for posgresql

Nota bene : The the problem occurs very early when the code parse the model to create the schema and then is hidden in many lines of logs, then the unitTested code crash with a confusing and obscure exception "user lacks privilege or object not found error" which does not point the real problem at all. So make sure to read all the trace from the beginning and fix all possible issues

score 2 · Answer 4 · answered Nov 10 '17 at 07:33

I had the error user lacks privilege or object not found while trying to create a table in an empty in-memory database. I used spring.datasource.schema and the problem was that I missed a semicolon in my SQL file after the "CREATE TABLE"-Statement (which was followed by "CREATE INDEX").

score 1 · Answer 5 · answered Jun 10 '18 at 16:15

1

I had similar issue with the error 'org.hsqldb.HsqlException: user lacks privilege or object not found: DAYS_BETWEEN' turned out DAYS_BETWEEN is not recognized by hsqldb as a function. use DATEDIFF instead.

DATEDIFF ( <datetime value expr 1>, <datetime value expr 2> )

answered Jun 10 '18 at 16:15

Nirmal Mangal

774
1
10
25

Had the same issue with `date_sub` function. Updating HSQL to 2.5.0 solved this for me. – Jezor Jan 17 '20 at 12:29

score 1 · Answer 6 · answered Aug 27 '18 at 00:44

When running a HSWLDB server. for example your java config file has:

hsql.jdbc.url = jdbc:hsqldb:hsql://localhost:9005/YOURDB;sql.enforce_strict_size=true;hsqldb.tx=mvcc

check to make sure that your set a server.dbname.#. for example my server.properties file:

    server.database.0=eventsdb 
    server.dbname.0=eventsdb 
    server.port=9005

score 0 · Answer 7 · answered Feb 24 '19 at 15:53

I was inserting the data in hsql db using following script

INSERT INTO Greeting (text) VALUES ("Hello World");

I was getting issue related to the Hello World object not found and HSQL database user lacks privilege or object not found error

which I changed into the below script

INSERT INTO Greeting (text) VALUES ('Hello World');

And now it is working fine.

score 0 · Answer 8 · answered Apr 08 '19 at 15:53

I bumped into kind of the same problem recently. We are running a grails application and someone inserted a SQL script into the BootStrap file (that's the entry point for grails). That script was supposed to be run only in the production environment, however because of bad logic it was running in test as well. So the error I got was:

User lacks privilege or object not found:

without any more clarification...

I just had to make sure the script was not run in the test environment and it fixed the problem for me, though it took me 3 hours to figure it out. I know it is very, very specific issue but still if I can save someone a couple of hours of code digging that would be great.

score 0 · Answer 9 · answered May 20 '19 at 18:29

I was having the same mistake. In my case I was forgetting to put the apas in the strings.

I was doing String test = "inputTest";

The correct one is String test = "'inputTest'";

The error was occurring when I was trying to put something in the database

connection.createStatement.execute("INSERT INTO tableTest values(" + test +")";

In my case, just put the quotation marks ' to correct the error.

score 0 · Answer 10 · answered Jul 22 '19 at 12:34

0

In my case the error occured because i did NOT put the TABLE_NAME into double quotes "TABLE_NAME" and had the oracle schema prefix.

Not working:

SELECT * FROM FOOSCHEMA.BAR_TABLE

Working:

SELECT * FROM "BAR_TABLE"

answered Jul 22 '19 at 12:34

R.S

1,529
16
24

score 0 · Answer 11 · answered Sep 02 '19 at 10:36

had this issue with concatenating variables in insert statement. this worked

// var1, var3, var4 are String variables
// var2 and var5 are Integer variables
result = statement.executeUpdate("INSERT INTO newTable VALUES ('"+var1+"',"+var2+",'"+var3+"','"+var4+"',"+var5 +")");

score 0 · Answer 12 · answered Nov 25 '19 at 17:34

In my case the issue was caused by the absence (I'd commented it out by mistake) of the following line in persistence.xml:

<property name="hibernate.hbm2ddl.auto" value="update"/>

which prevented Hibernate from emitting the DDL to create the required schema elements...

(different Hibernate wrappers will have different mechanisms to specify properties; I'm using JPA here)

score 0 · Answer 13 · answered Jan 02 '20 at 13:28

I had this error while trying to run my application without specifying the "Path" field in Intellij IDEA data source (database) properties. Everything else was configured correctly.

I was able to run scripts in IDEA database console and they executed correctly, but without giving a path to the IDEA, it was unable to identify where to connect, which caused errors.

score 0 · Answer 14 · answered Feb 16 '20 at 22:33

You have to run the Database in server mode and connect.Otherwise it wont connect from external application and give error similar to user lacks privilege. Also change the url of database in spring configuration file accordingly when running DB in server mode.

Sample command to run DB in server mode $java -cp lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:data/mydb --dbname.0 Test

Format of URL in configuration file jdbc:hsqldb:hsql://localhost/Test

score 0 · Answer 15 · edited Jul 05 '20 at 21:56

0

Add these two extra properties:

spring.jpa.hibernate.naming.implicit-strategy=
org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl

spring.jpa.hibernate.naming.physical-strategy=
org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

edited Jul 05 '20 at 21:56

Boken

3,207
9
25
31

answered Jul 05 '20 at 19:52

Anil Kumar

1

1

spring.datasource.url=jdbc:hsqldb:mem:cleapp;sql.syntax_mys=true spring.datasource.username=sa spring.datasource.password= spring.jpa.database=HSQL spring.jpa.database-platform=org.hibernate.dialect.HSQLDialect spring.jpa.hibernate.ddl-auto=none spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl – Anil Kumar Jul 05 '20 at 19:57
1

Please don't extend your answer via your comments. If you have useful information that readers should be aware of, **edit** your answer to include that information. This is especially useful here where the extra information would really benefit from the code formatting options available to you via markdown (which is only available in a post, and not a comment). – Jeremy Caney Jul 05 '20 at 22:47

score 0 · Answer 16 · answered Mar 29 '21 at 23:02

0

In my case, one of the columns had the name 'key' with the missing @Column(name = "key") annotation so that in the logs you could see the query that created the table but in fact it was not there. So be careful with column names

answered Mar 29 '21 at 23:02

Matteoo37

1

HSQL database user lacks privilege or object not found error

16 Answers16

Linked