Problems with HSQLDB. Experiencing "user lacks privilege or object not found" and "integrity constraint violation"

[Sajid Nasir]

We are using JOOQ 3.11.1

Our java DB stack was implemented using hibernate, but now we're trying to use JOOQ. We are trying to flip to our JOOQ production code for a small set of entities at a time. We have tests that run on local MYSQL DB instances and work successfully. Our code works in our production MYSQL environments as well. We have integration tests on HSQLDB. These tests run using the hibernate production stack (which use sessions and entity objects for CRUD operations). We run into some issues when using JOOQ for these integration tests. I'm currently just trying to use JOOQ for a couple of entities to insert, select, delete, and list while the other entities still interact using hibernate.

I have run into three types issues so far:

  1. "user lacks privilege or object not found: n_p_c". n_p_c is a table in our DB

  2. "integrity constraint violation: NOT NULL check constraint;  table: n_p_c column: c_s_date"

  3. "user lacks privilege or object not found: DUAL"

Problem 1

- Problem 1. hides problem 2. When problem 1. is fixed, problem 2. appears. I was able to fix problem 1. by adding the RenderNameStyle to AS_IS in our integration test. NOTE: This render style is not in production because we have not run into issues in production.

org.jooq.exception.DataAccessException: SQL [insert into `n_p_c` (`f_number`, `t_number`) values (?, ?)]; user lacks privilege or object not found: n_p_c

at org.jooq_3.11.1.MYSQL.debug(Unknown Source)

at org.jooq.impl.Tools.translate(Tools.java:2380)

at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:802)

at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:364)

at org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:127)

Integration testing settings:

new Settings().withRenderMapping(renderMapping) // renderMapping is just new RenderMapping();

            .withExecuteLogging(true)

            .withRenderFormatted(true)

            .withFetchWarnings(true)

            .withRenderSchema(false);

            .withRenderNameStyle(RenderNameStyle.AS_IS);  // Adding this SPECIFICALLY for integration testing. 

            // This is not needed in production. I found solution to this here: https://groups.google.com/forum/#!searchin/jooq-user/user$20lacks$20privilege$20or$20object$20not$20found%7Csort:date/jooq-user/qDKCmMI9-14/9AXYs-wuHnUJ

Problem 2

- Problem 2 fails to insert a row because my JOOQ query does not explicitly set the c_s_date. c_s_date is a Timestamp that the database sets on creation. 

This is the JOOQ generated field for c_s_date: 

    public final TableField<NPCRecordRecord, Timestamp> C_S_DATE = createField("C_S_DATE", org.jooq.impl.SQLDataType.TIMESTAMP.nullable(false).defaultValue(org.jooq.impl.DSL.field("CURRENT_TIMESTAMP", org.jooq.impl.SQLDataType.TIMESTAMP)), this, "");

    org.jooq.exception.DataAccessException: SQL [insert into n_p_c (f_number, t_number) values (?, ?)]; integrity constraint violation: NOT NULL check constraint;  table: n_p_c column: c_s_date

at org.jooq_3.11.1.MYSQL.debug(Unknown Source)

at org.jooq.impl.Tools.translate(Tools.java:2380)

at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:802)

at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:364)

at org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:127)

Caused by: java.sql.SQLIntegrityConstraintViolationException: integrity constraint violation: NOT NULL check constraint;  table: n_p_c column: c_s_date

at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)

at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)

at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)

at org.hsqldb.jdbc.JDBCPreparedStatement.execute(Unknown Source)

at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)

at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)

at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:209)

at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:432)

at org.jooq.impl.AbstractDMLQuery.execute(AbstractDMLQuery.java:612)

at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:350)

... 64 more

Caused by: org.hsqldb.HsqlException: integrity constraint violation: NOT NULL check constraint;  table: n_p_c column: c_s_date

at org.hsqldb.error.Error.error(Unknown Source)

at org.hsqldb.Table.enforceRowConstraints(Unknown Source)

at org.hsqldb.Table.insertSingleRow(Unknown Source)

at org.hsqldb.StatementDML.insertSingleRow(Unknown Source)

at org.hsqldb.StatementInsert.getResult(Unknown Source)

at org.hsqldb.StatementDMQL.execute(Unknown Source)

at org.hsqldb.Session.executeCompiledStatement(Unknown Source)

at org.hsqldb.Session.execute(Unknown Source)

... 72 more

Problem 3

- Problem 3 stems from trying to query this "DUAL" table. I am not too familiar with this.

org.jooq.exception.DataAccessException: SQL [select 1 as one from dual where exists (select n_p_c.id, n_p_c.f_number, n_p_c.t_number, n_p_c.c_s_date from n_p_c where (n_p_c.t_number = ? and n_p_c.f_number = ?))]; user lacks privilege or object not found: DUAL

at org.jooq_3.11.1.MYSQL.debug(Unknown Source)

at org.jooq.impl.Tools.translate(Tools.java:2380)

at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:802)

at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:364)

at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:393)

at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:380)

at org.jooq.impl.AbstractResultQuery.fetchOne(AbstractResultQuery.java:545)

at org.jooq.impl.SelectImpl.fetchOne(SelectImpl.java:2879)

at org.jooq.impl.DefaultDSLContext.fetchExists(DefaultDSLContext.java:4345)

Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: DUAL

at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)

at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)

at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)

at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)

at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:311)

at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java)

at org.jooq.impl.ProviderEnabledConnection.prepareStatement(ProviderEnabledConnection.java:109)

at org.jooq.impl.SettingsEnabledConnection.prepareStatement(SettingsEnabledConnection.java:73)

at org.jooq.impl.AbstractResultQuery.prepare(AbstractResultQuery.java:239)

at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:322)

... 103 more

Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: DUAL

at org.hsqldb.error.Error.error(Unknown Source)

at org.hsqldb.error.Error.error(Unknown Source)

at org.hsqldb.SchemaManager.getTable(Unknown Source)

at org.hsqldb.ParserDQL.readTableName(Unknown Source)

at org.hsqldb.ParserDQL.readTableOrSubquery(Unknown Source)

at org.hsqldb.ParserDQL.XreadTableReference(Unknown Source)

at org.hsqldb.ParserDQL.XreadFromClause(Unknown Source)

at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source)

at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)

at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)

at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)

at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)

at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)

at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)

at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)

at org.hsqldb.ParserCommand.compilePart(Unknown Source)

at org.hsqldb.ParserCommand.compileStatement(Unknown Source)

at org.hsqldb.Session.compileStatement(Unknown Source)

at org.hsqldb.StatementManager.compile(Unknown Source)

at org.hsqldb.Session.execute(Unknown Source)

... 111 more

Any advice/pointer is much appreciated.

Thanks

[Lukas Eder]

Hi Sajid,

Thank you very much for your message. I will comment inline

On Sun, Jan 13, 2019 at 7:23 PM Sajid Nasir <sajid...@gmail.com> wrote:

We are using JOOQ 3.11.1

Our java DB stack was implemented using hibernate, but now we're trying to use JOOQ. We are trying to flip to our JOOQ production code for a small set of entities at a time. We have tests that run on local MYSQL DB instances and work successfully. Our code works in our production MYSQL environments as well. We have integration tests on HSQLDB.

In general, I strongly advise against this practice. If you're using MySQL in production and in development environments, I suggest using MySQL for your integration tests as well. With Docker and/or https://www.testcontainers.org, and similar tools, this has become comparatively easy to do. Regardless if you're using Hibernate or jOOQ or both, using HSQLDB in your integration test environment will cause many problems that you would not have otherwise.

 

These tests run using the hibernate production stack (which use sessions and entity objects for CRUD operations). We run into some issues when using JOOQ for these integration tests. I'm currently just trying to use JOOQ for a couple of entities to insert, select, delete, and list while the other entities still interact using hibernate.

I have run into three types issues so far:

  1. "user lacks privilege or object not found: n_p_c". n_p_c is a table in our DB

  2. "integrity constraint violation: NOT NULL check constraint;  table: n_p_c column: c_s_date"

  3. "user lacks privilege or object not found: DUAL"

Problem 1

- Problem 1. hides problem 2. When problem 1. is fixed, problem 2. appears. I was able to fix problem 1. by adding the RenderNameStyle to AS_IS in our integration test. NOTE: This render style is not in production because we have not run into issues in production.

org.jooq.exception.DataAccessException: SQL [insert into `n_p_c` (`f_number`, `t_number`) values (?, ?)]; user lacks privilege or object not found: n_p_c

at org.jooq_3.11.1.MYSQL.debug(Unknown Source)

at org.jooq.impl.Tools.translate(Tools.java:2380)

at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:802)

at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:364)

at org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:127)

Integration testing settings:

new Settings().withRenderMapping(renderMapping) // renderMapping is just new RenderMapping();

            .withExecuteLogging(true)

            .withRenderFormatted(true)

            .withFetchWarnings(true)

            .withRenderSchema(false);

            .withRenderNameStyle(RenderNameStyle.AS_IS);  // Adding this SPECIFICALLY for integration testing. 

            // This is not needed in production. I found solution to this here: https://groups.google.com/forum/#!searchin/jooq-user/user$20lacks$20privilege$20or$20object$20not$20found%7Csort:date/jooq-user/qDKCmMI9-14/9AXYs-wuHnUJ

This is one of the more frequent problems you might experience when using several RDBMS without actually needing them. MySQL and HSQLDB have different understandings of what is an identifier, especially with respect to casing. This can be worked around with jOOQ as you've seen.

From your message, I cannot tell why the `n_p_c` table has not been found. It could be due to:

- Lacking qualification. You've removed the schema name from the generated SQL. Perhaps the table is in a different schema?

- Case sensitivity. Perhaps the table is not in lower case, but in upper case? Depending on your MySQL settings and the operating system you're using, this may or may not be relevant, in MySQL

- Wrong SQLDialect (as seen below). If this is also being executed on HSQLDB, then setting the SQLDialect to HSQLDB will generate "n_p_c" (with double quotes rather than backticks)

Your generated code seems to indicate that a default expression exists on the n_p_c table. But does it exist in your MySQL database as well? Or did you generate the jOOQ code from HSQLDB and the default expression exists only there?

Notice also how you're using the MySQL dialect in jOOQ (see synthetic stack trace element at the top), but you're connecting to an HSQLDB database. This doesn't work of course. You have to use SQLDialect.HSQLDB when connecting to HSQLDB.

Same as before. You're generating your SQL statement for MySQL, but you're executing it on HSQLDB

I hope this helps,

Lukas

[Sajid Nasir]

I've responded to your comments in line as well.

From your message, I cannot tell why the `n_p_c` table has not been found. It could be due to:

- Lacking qualification. You've removed the schema name from the generated SQL. Perhaps the table is in a different schema?

- Case sensitivity. Perhaps the table is not in lower case, but in upper case? Depending on your MySQL settings and the operating system you're using, this may or may not be relevant, in MySQL

- Wrong SQLDialect (as seen below). If this is also being executed on HSQLDB, then setting the SQLDialect to HSQLDB will generate "n_p_c" (with double quotes rather than backticks)

Setting the render name style to "AS_IS" fixed the issue of `n_p_c` table not being available for problem 1.

Your generated code seems to indicate that a default expression exists on the n_p_c table. But does it exist in your MySql database as well? Or did you generate the jOOQ code from HSQLDB and the default expression exists only there?

The JOOQ code was generated from MySQL

Notice also how you're using the MySQL dialect in jOOQ (see synthetic stack trace element at the top), but you're connecting to an HSQLDB database. This doesn't work of course. You have to use SQLDialect.HSQLDB when connecting to HSQLDB.

Good catch on this one. I need two instances of my application running for the integration test. One of the application's dialect was not configured to HSQLDB. That's why I was seeing Problem 3 with "DUAL" table. I updated the dialect for the second instance and that problem is resolved.

Thank you very much for your advice on using MySQL for the integration tests. I will look into that as a next step after discussing it w/ the bigger team.

Thank you for your reply.