Jooq: `returning` statement does not work in MySQL even with primary key and auto increment

[oripwk]

Preface

I know similar issues were addressed here and here, but the issue I'm describing here is different.

TL;DR

returning doesn't seem to work when working with MySQL without code generation.

Expected behavior and actual behavior:

Actual behavior

The returned Record is null

Expected behavior

The returned record should contain the generated ID

Steps to reproduce the problem:

1. Run a MySQL databases
2. Create the table:

CREATE DATABASE demo;
CREATE TABLE demo.users (
  id int NOT NULL AUTO_INCREMENT,
  name varchar(100) DEFAULT NULL,
  PRIMARY KEY (id)
);

1. Run the following code:

val conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/demo", "root", "password")
val res = DSL.using(conn, SQLDialect.MYSQL)
            .insertInto(DSL.table("users"))
            .columns(DSL.field("name"))
            .values("name1")
            .returning(DSL.field("id"))
            .fetchOne()
println(res)

1. null will be printed

Versions:

• jOOQ: 3.10.5
• Java: target: 1.8; runtime 9.0.4
• Database (include vendor): MySQL 5.7.21
• OS: MacOS High Sierra (client); docker mysql 5 (server)
• JDBC Driver (include name if inofficial driver): mysql-connector-java 6.0.6

[tzachz]

I'm seeing the exact same issue (using jOOQ 3.9.4; Java 1.8)

[tzachz]

Oh - in my case, this was just wrong usage of jOOQ on my behalf - as the issues you referred to suggest, when using MySQL you can only use returning with the table's _identity_ field. How does jOOQ know which field is the table's identity field? Well, you have to tell it, and that's what I was missing:

I'm using a TableImpl implementation such as this Scala class:

object MyTable extends TableImpl[Record]("my_table") {
  val Id: TableField[Record, lang.Long] = createField("id", BIGINT)
  val Status: TableField[Record, String] = createField("status", VARCHAR(20))
}

And I had to modify it to override getIdentity and return the Id field:

object MyTable extends TableImpl[Record]("my_table") {
  val Id: TableField[Record, lang.Long] = createField("id", BIGINT)
  val Status: TableField[Record, String] = createField("status", VARCHAR(20))

  override def getIdentity: Identity[Record, java.lang.Long] = {
    new Identity[Record, java.lang.Long] {
      override def getTable: Table[Record] = MyTable
      override def getField: TableField[Record, java.lang.Long] = Id
    }
  }
}

Which resolved the issue for me.

Hope this helps.

[lukaseder]

Thanks for your report. I do think this is the same issue as https://github.com/jOOQ/jOOQ/issues/2943, even if you're using the DSL and #2943 is not. The point here being that in the absence of formal knowledge of the identity column, it is not possible for jOOQ to correctly guess it without

• either doing some runtime metadata lookup
• making wild assumptions about your id column from the returning clause actually being that identity (could just be a date_modified column)

The workaround described by @tzachz (thanks for documenting!) is the way to go, right now. But at that point, why not just use the code generator...

[oripwk]

I understand the limitations. However, adding an API for this scenario would greatly improve the work with jOOQ. For example, adding an identity argument to DSL.table() factory method:

val table: Table<Record> = DSL.table(sql = "persons", identity = DSL.field("id"))

[lukaseder]

@oripwk : There's that use-case. And then, there's the one that requests a table should know its columns. And then, there's the one that requests a table should know its foreign keys (for navigation), and then...

... I start wondering, what's wrong with @tzachz's approach and / or using the code generator? :-)

[oripwk]

There's that use-case. And then, there's the one that requests a table should know its columns. And then, there's the one that requests a table should know its foreign keys (for navigation), and then...

I completely understand :)

what's wrong with @tzachz's approach and / or using the code generator? :-)

I was just wondering whether inheriting directly from implementation might sort-of-kind-of (?) break encapsulation, or something along these lines.

About code-generation: I might use it going forward when the schema stabilizes more.

Anyway, thanks for the support!

[lukaseder]

I was just wondering whether inheriting directly from implementation might sort-of-kind-of (?) break encapsulation, or something along these lines.

Yes, unfortunately. The TableImpl is internal API (used by the code generator). It can be used in the same way as generated code itself, but there's risk of breaking changes between minor releases.

Perhaps, the way to read your request here should be that the TableImpl API should be refactored in a way to become publicly available through a nicer API (e.g. a method call like you suggested) rather than this weird subtype polymorphic approach. Perhaps, there is a better way. I've created an issue for this:
https://github.com/jOOQ/jOOQ/issues/7444

Note, the main reason why this isn't being done is simply the fact that there's a cyclic dependency between Table and TableField, whose hairy initialisation is easier to encapsulate with the current approach. But perhaps that's not going to be an issue with a well-designed API

About code-generation: I might use it going forward when the schema stabilizes more.

I'm curious about this approach. Wouldn't code generation help you even more in this case, as stuff will break and you will know it, rather than stuff quietly compiling but then not working at runtime?

[oripwk]

I'm curious about this approach. Wouldn't code generation help you even more in this case, as stuff will break and you will know it, rather than stuff quietly compiling but then not working at runtime?

It's not only that. There are several reasons:

1. Code generation currently does not support Kotlin
2. Code generation is another build step that requires preparation and Ops.
3. You need connectivity to Production database
4. Schema evolution becomes complicated since you need to generate the classes from current schema and then manually change the generated code to the new schema you're aiming at.
5. It raises some questions such as "Do the generated classes need to be checked in to version control?"
6. Gradle does not have a good plugin for it
7. It requires some infra investment that I do not have time for currently

[lukaseder]

Thanks for your detailed response:

Code generation currently does not support Kotlin

Does that really matter? There would be 1-2 nice Kotlin language features that we could leverage, but with Kotlin's focus on interoperability, jOOQ works out of the box...

Code generation is another build step that requires preparation and Ops.

Can't disagree here, although some people are happy running the code generator in their IDEs and checking in the generated sources. Depends on the project setup.

You need connectivity to Production database

Have you considered using any of the DDLDatabase, XMLDatabase, JPADatabase?

• https://www.jooq.org/doc/latest/manual/code-generation/codegen-ddl/
• https://www.jooq.org/doc/latest/manual/code-generation/codegen-xml/
• https://www.jooq.org/doc/latest/manual/code-generation/codegen-jpa/

Schema evolution becomes complicated since you need to generate the classes from current schema and then manually change the generated code to the new schema you're aiming at.

Without generation, you're postponing these changes to the runtime, which quite possibly simply breaks due to an overlooked effect of schema evolution...

It raises some questions such as "Do the generated classes need to be checked in to version control?"

Indeed, that's a very opinionated topic.

Gradle does not have a good plugin for it

What's missing in Etienne Studer's plugin? (He works for Gradle, btw)

It requires some infra investment that I do not have time for currently

Sure, that can be a reason, but you'll pay the price later on :) Specifically, because you're doing what you're doing right now. Emulating code generation by hand-writing meta data...

It's up to you, of course.

[lukaseder]

I'm improving a few things in this area. A better Table construction API might be considered, separately, but clearly, the original example should work, with only a minor adaptation:

val res = DSL.using(conn, SQLDialect.MYSQL)
            .insertInto(DSL.table("users"))
            .columns(DSL.field("name"))
            .values("name1")
            .returning(DSL.field("id", INTEGER.identity(true))) // Adaptation here
            .fetchOne()
println(res)

Here's what has improved:

• #10397: It is no longer necessary to override Table.getIdentity(). It will be sufficient if a column uses DataType.identity() in the table definition, if someone decides to do this manually, e.g. as in this workaround: https://github.com/jOOQ/jOOQ/issues/7411#issuecomment-384779211
• #10400: In the absence of table meta data, users can provide INTEGER.identity(true) as a data type of the field in the returning() clause, which will be considered as the identity column

So, this will now work via the implementation of #10400.