Non-updatable tables

[Ben Hood]

Hi,

I'm in the middle of a 2.4 -> 2.5 upgrade and I've found that my generated table record appears to be non-updatable):

java.lang.IllegalStateException: The ON DUPLICATE KEY IGNORE/UPDATE clause cannot be simulated when inserting into non-updatable tables : PUBLIC.ENDPOINTS

at org.jooq.impl.InsertQueryImpl.toMerge(InsertQueryImpl.java:416)

at org.jooq.impl.InsertQueryImpl.toSQL(InsertQueryImpl.java:188)

at org.jooq.impl.DefaultRenderContext.sql(DefaultRenderContext.java:311)

at org.jooq.impl.DefaultRenderContext.render(DefaultRenderContext.java:120)

at org.jooq.impl.Factory.render(Factory.java:681)

at org.jooq.impl.AbstractQueryPart.getSQL(AbstractQueryPart.java:117)

at org.jooq.impl.AbstractQueryPart.getSQL(AbstractQueryPart.java:99)

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

at org.jooq.impl.InsertImpl.execute(InsertImpl.java:95)

Is there some property of the source database that JOOQ reads when generating table records that indicates whether a table is not updatable (I'm using HSQLDB to do the codegen)?

Cheers,

Ben

[Lukas Eder]

> I'm in the middle of a 2.4 -> 2.5 upgrade and I've found that my generated
> table record appears to be non-updatable):
>

> [...]

>
> Is there some property of the source database that JOOQ reads when
> generating table records that indicates whether a table is not updatable
> (I'm using HSQLDB to do the codegen)?

The only criteria for jOOQ to decide whether a table is updatable is
the fact whether it finds a primary key (or at least one unique key)
on it. Did your meta-data change? Can you provide log4j debug-log
output from your code generation? The relevant parts would be where
primary keys are fetched

Cheers
Lukas

[Ben Hood]

On Wednesday, 5 September 2012 11:23:09 UTC+1, Lukas Eder wrote:

The only criteria for jOOQ to decide whether a table is updatable is
the fact whether it finds a primary key (or at least one unique key)
on it. Did your meta-data change?

Not knowingly, although I have noticed HSQLDB throw some errors when accessing the DB meta-data during the DDL generation phase (which is upstream of the JOOQ generation phase), so I will look into that.

 

Can you provide log4j debug-log
output from your code generation?

I've attached the output from the codgen.

[Lukas Eder]

> Not knowingly, although I have noticed HSQLDB throw some errors when
> accessing the DB meta-data during the DDL generation phase (which is
> upstream of the JOOQ generation phase), so I will look into that.

OK, thanks

>> Can you provide log4j debug-log
>> output from your code generation?
>
> I've attached the output from the codgen.

I'm sorry, by log4j debug-log I meant using log4j with something like
this configuration file here:
https://github.com/jOOQ/jOOQ/blob/master/jOOQ-test/src/log4j.xml
http://logging.apache.org/log4j/2.x/

It should be sufficient to put log4j and the log4j.xml file on the
classpath for the code generator

Cheers
Lukas

[Ben Hood]

On Wednesday, 5 September 2012 12:25:13 UTC+1, Lukas Eder wrote:

> Not knowingly, although I have noticed HSQLDB throw some errors when
> accessing the DB meta-data during the DDL generation phase (which is
> upstream of the JOOQ generation phase), so I will look into that.

So I think the root cause of this bug is some kind of strange interaction with HSQL during the DDL generation phase of our build that renders the INFORMATION_SCHEMA in HSQL useless, and I have no idea why. So I have a workaround for this specific issue which basically involves making 110% that the project is clean before any DDL generation or it's subsequent application to an HSQL instance is performed.

In other words, the root cause has nothing to do with JOOQ.

 

>> Can you provide log4j debug-log
>> output from your code generation?
>
> I've attached the output from the codgen.

I'm sorry, by log4j debug-log I meant using log4j with something like
this configuration file here:
https://github.com/jOOQ/jOOQ/blob/master/jOOQ-test/src/log4j.xml
http://logging.apache.org/log4j/2.x/

It should be sufficient to put log4j and the log4j.xml file on the
classpath for the code generator

The interesting thing about the INFORMATION_SCHEMA being made unavailable for JOOQ code gen is that the codegen succeeded, but the table classes were extending TableImpl instead of UpdatableTableImpl. Doing a mrproper clean before attempting the codegen now results in inheritance from UpdatableTableImpl, so the problem is solved.

Unfortunately I've tried a lot of different configurations for log4j to get it to log something sensible, but alas, I'm struggling to get it to work. I think this is a product of using a maven build that uses logback all over the place instead of log4j, coupled with the general maven classloading issues. I'll try to see if I can convince it to output something, in order to track this down.

Cheers,

Ben

  

[Ben Hood]

On Wednesday, 5 September 2012 12:45:35 UTC+1, Ben Hood wrote:

Unfortunately I've tried a lot of different configurations for log4j to get it to log something sensible, but alas, I'm struggling to get it to work. I think this is a product of using a maven build that uses logback all over the place instead of log4j, coupled with the general maven classloading issues. I'll try to see if I can convince it to output something, in order to track this down.

So I've managed to reproduce the issue and also have convinced logback to append the JOOQ codegen to it's own file, which I have attached. At the top of the log you will see a bunch of Hibernate stuff (which we still use in our DDL generator, since we haven't gotten around to porting this away yet), which is fairly harmless. In the middle of the file you see the root cause, which is due to a bug in HSQL (it seems to be down to some FK definitions I have just added, because commenting these out seems to solve the HSQL problem, but obviously this has nothing to do with JOOQ). Then you see the JOOQ codegen.

When this HSQL bug occurs, JOOQ completes the codegen, but all of the classes inherit from TableImpl. Is there some kind of setting I need to add to get JOOQ to fail early? This is the config I am using:

<plugin>

        <groupId>org.jooq</groupId>

        <artifactId>jooq-codegen-maven</artifactId>

        <dependencies>

          <dependency>

            <groupId>org.hsqldb</groupId>

            <artifactId>hsqldb</artifactId>

            <version>${hsqldb.version}</version>

          </dependency>

          <dependency>

            <groupId>ch.qos.logback</groupId>

            <artifactId>logback-classic</artifactId>

            <version>${logback.version}</version>

          </dependency>

          <dependency>

            <groupId>ch.qos.logback</groupId>

            <artifactId>logback-core</artifactId>

            <version>${logback.version}</version>

          </dependency>

        </dependencies>

        <configuration>

          <jdbc>

            <driver>${jdbcDriverClass}</driver>

            <url>${jdbcUrl}</url>

            <user>${jdbcUser}</user>

            <password>${jdbcPass}</password>

          </jdbc>

          <generator>

            <name>org.jooq.util.DefaultGenerator</name>

            <database>

              <name>org.jooq.util.hsqldb.HSQLDBDatabase</name>

              <includes>.*</includes>

              <excludes />

              <inputSchema>PUBLIC</inputSchema>

            </database>

            <generate>

              <relations>true</relations>

              <deprecated>false</deprecated>

            </generate>

            <target>

              <packageName>net.lshift.diffa.schema</packageName>

              <directory>${basedir}/target/generated-sources/jooq</directory>

            </target>

          </generator>

        </configuration>

      </plugin>

Cheers,

Ben

[Lukas Eder]

> So I think the root cause of this bug is some kind of strange interaction
> with HSQL during the DDL generation phase of our build that renders the
> INFORMATION_SCHEMA in HSQL useless, and I have no idea why. So I have a
> workaround for this specific issue which basically involves making 110% that
> the project is clean before any DDL generation or it's subsequent
> application to an HSQL instance is performed.

That's pretty crazy. Reminds me of the good old days when Oracle's
DUAL was a physical table that you could write data into. Once you did
that, your database instance really became an "oracle", mumbling
incomprehensible things.

Thanks for the feedback!

Cheers
Lukas

[Ben Hood]

On Wednesday, 5 September 2012 13:08:49 UTC+1, Ben Hood wrote:

So I've managed to reproduce the issue and also have convinced logback to append the JOOQ codegen to it's own file, which I have attached. At the top of the log you will see a bunch of Hibernate stuff (which we still use in our DDL generator, since we haven't gotten around to porting this away yet), which is fairly harmless. In the middle of the file you see the root cause, which is due to a bug in HSQL (it seems to be down to some FK definitions I have just added, because commenting these out seems to solve the HSQL problem, but obviously this has nothing to do with JOOQ). Then you see the JOOQ codegen.

So I've tracked the root cause down:

HSQL was failing partially (silently) when I tried to add an two-column FK where there was no unique constraint for the two referenced columns in the parent table. The statement:

alter table A add constraint fk_a_b foreign key (FOO, BAR) references B

fails silently if there is no unique constraint on B.FOO and B.BAR. Qualifying the FK include the parent columns actually causes HSQL to fail:

alter table A add constraint fk_a_b foreign key (FOO, BAR) references B (FOO, BAR)

with

a UNIQUE constraint does not exist on referenced columns: B

So sorry to spam the list with what is fundamentally an HSQL issue, but the resultant JOOQ codegen did make me wonder.

Cheers,

Ben

[Lukas Eder]

Thanks for this log output. What's curious is that the ERROR message
is properly logged with log4j, but not with the fallback
java.util.logging that was shown in your first log output. I'll
double-check this: #1793
https://github.com/jOOQ/jOOQ/issues/1793

Also, there may be general room for improvement for jooq-codegen log output:
https://github.com/jOOQ/jOOQ/issues/1791

Cheers
Lukas

[Lukas Eder]

> alter table A add constraint fk_a_b foreign key (FOO, BAR) references B
> fails silently if there is no unique constraint on B.FOO and B.BAR.

Interesting syntax... Specifically because it seems to severely
corrupt the database meta data. You should report this to HSQLDB

> So sorry to spam the list with what is fundamentally an HSQL issue, but the
> resultant JOOQ codegen did make me wonder.

No worries. Thanks for the update.

Cheers
Lukas