How to prevent addtional SELECT after INSERT to retrieve auto-assigned value of primary key with AUTO_INCREMENT (MySQL)?

[Marcus Gattinger]

Hi Lukas,

I currently compare jOOQ and Hibernate in terms of rendered and executed
statements. For my tests I use MySQL and for the low level profiling I use
the tool "Neor Profile SQL".

Inserting new rows to a table with a primary key declared as AUTO_INCREMENT
shows me that jOOQ renders and executes an explicit

SELECT [pk column] FROM [table] WHERE [pk column] = [value]

after each INSERT statement.

However Hibernate does not execute such an additional SELECT statement.

From the documentation of the JDBC driver for MySQL Connector/J I see that
it supports the method getGeneratedKeys() to retrieve the assigned value.
So an additionall SELECT statement is not necessary, isn't it?

I debugged jOOQ's code execution and come across code lines 234ff. in class
TableRecordImpl:

// [#1859] In some databases, not all fields can be fetched via getGeneratedKeys()
if (REFRESH_GENERATED_KEYS.contains(configuration().family()) && this instanceof UpdatableRecord)
((UpdatableRecord<?>) this).refresh(key.toArray(EMPTY_FIELD));

And to me here is the problem, because the enumeration
REFREH_GENERATED_KEYS contains the MySQL dialect, which seems to be wrong.
Can you confirm this?

If the behaviour is correct, are there any other possibilty the prevent
executing the extra SELECT statement after each INSERT statement.

Kind regards,
Marcus

[Marcus Gattinger]

BTW: Im using Record.store() to insert new rows to the table.

[Samir Faci]

I think that's a limitation of the query you're running. If you want to do
all this in one operation you may want to look at using upserts.

https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

I'm not sure if your version of MySQL supports this but otherwise you are
forced to do a select otherwise.

I'm not 100% sure on the MySQL syntax jooq pattern to use, but something
like this should work.

Record result =
dslContext.insertInto(record.getTable())
.set(record)
.onDuplicateKeyUpdate()
.set(record)
.returning(record.field1())
.fetchOne();
long id = result.getId();

Post by Marcus Gattinger
BTW: Im using Record.store() to insert new rows to the table.
--
You received this message because you are subscribed to the Google Groups
"jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an
For more options, visit https://groups.google.com/d/optout.

[Marcus Gattinger]

Thanks for your response. As I mentioned I've compared the use case with
Hibernate. Both libraries (i. e. Hibernate and jOOQ) use the same JDBC
driver and execute INSERT INTO table (field1, field2, ...) VALUES (value1,
value2, ...).
However Hibernate does not execute an additional SELECT statement to get
the generated key value.

I tried your suggested solution (sligtly modified) to insert the row and as
far as I can see, I get the generated key value without an additional
SELECT statement.

DSLContext database = applicationContext().getBean(DSLContext.class);
ClientRecord record = new ClientRecord();
// insert
record.setName(randomUUID().toString());
Record inserted = database.insertInto(record.getTable()).set(record).returning(record.key().fields()).fetchOne();
record.setId((Integer) inserted.getValue(0)).changed(false);
System.out.println(record.getId());
// update
record.setName(randomUUID().toString());
record.attach(database.configuration());
record.store();


However it would be preferable if jOOQ would not require me to set the id
in the inserted record manually and mark the field as unchanged to make the
update working.

Regards,
Marcus

Post by Samir Faci
I think that's a limitation of the query you're running. If you want to
do all this in one operation you may want to look at using upserts.
https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html
I'm not sure if your version of MySQL supports this but otherwise you are
forced to do a select otherwise.
I'm not 100% sure on the MySQL syntax jooq pattern to use, but something
like this should work.
Record result =
dslContext.insertInto(record.getTable())
.set(record)
.onDuplicateKeyUpdate()
.set(record)
.returning(record.field1())
.fetchOne();
long id = result.getId();

Post by Marcus Gattinger
BTW: Im using Record.store() to insert new rows to the table.
--
You received this message because you are subscribed to the Google Groups
"jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an
For more options, visit https://groups.google.com/d/optout.

--
Thank you
Samir Faci
https://keybase.io/csgeek

[Marshall Pierce]

I see you're using the constructor of the Record type directly in that
bit of sample code -- in the code referenced in your original post, are
you also doing that? Have you tried using `DSLContext#newRecord(<table
type>)` to create the record so that it's bound to the underlying context?

Post by Marcus Gattinger
Thanks for your response. As I mentioned I've compared the use case with
Hibernate. Both libraries (i. e. Hibernate and jOOQ) use the same JDBC
driver and execute INSERT INTO table (field1, field2, ...) VALUES
(value1, value2, ...).
However Hibernate does not execute an additional SELECT statement to get
the generated key value.
I tried your suggested solution (sligtly modified) to insert the row and
as far as I can see, I get the generated key value without an additional
SELECT statement.
|
DSLContextdatabase =applicationContext().getBean(DSLContext.class);
ClientRecordrecord =newClientRecord();
// insert
record.setName(randomUUID().toString());
Recordinserted
=database.insertInto(record.getTable()).set(record).returning(record.key().fields()).fetchOne();
record.setId((Integer)inserted.getValue(0)).changed(false);
System.out.println(record.getId());
// update
record.setName(randomUUID().toString());
record.attach(database.configuration());
record.store();
|
However it would be preferable if jOOQ would not require me to set the
id in the inserted record manually and mark the field as unchanged to
make the update working.
Regards,
Marcus
I think that's a limitation of the query you're running.  If you
want to do all this in one operation you may want to look at using
upserts.
https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html
<https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html>
I'm not sure if your version of MySQL supports this but otherwise
you are forced to do a select otherwise.
I'm not 100% sure on the MySQL syntax jooq pattern to use, but
something like this should work.
|Record result = dslContext.insertInto(record.getTable())
.set(record) .onDuplicateKeyUpdate() .set(record)
.returning(record.field1()) .fetchOne(); long id = result.getId(); |
BTW: Im using Record.store() to insert new rows to the table.
--
You received this message because you are subscribed to the
Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from
For more options, visit https://groups.google.com/d/optout
<https://groups.google.com/d/optout>.
--
Thank you
Samir Faci
https://keybase.io/csgeek
--
You received this message because you are subscribed to the Google
Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send
For more options, visit https://groups.google.com/d/optout.

[Marcus Gattinger]

Yes, in my original post I use the record constructor, too. It does not
make any difference whether I attach the Record instance to the context
right before the record is saved or if I create the record using
database().newRecord(CLIENT).

[Lukas Eder]

Hi Marcus,

Thanks for your message.

Not excluding any bugs in the below, this additional select statement is
run only on those databases that are not able to fetch non-identity columns
through JDBC's getGeneratedKeys() method, and only if such non-identity
columns are needed. This is the case, e.g.

- When Settings.returnAllOnUpdatableRecord flag is set to true
- When the primary key is composite, or does not coincide 100% with the
identity column

Can you confirm any of the above?

Thanks,
Lukas

Post by Marcus Gattinger
Hi Lukas,
I currently compare jOOQ and Hibernate in terms of rendered and executed
statements. For my tests I use MySQL and for the low level profiling I use
the tool "Neor Profile SQL".
Inserting new rows to a table with a primary key declared as
AUTO_INCREMENT shows me that jOOQ renders and executes an explicit
SELECT [pk column] FROM [table] WHERE [pk column] = [value]
after each INSERT statement.
However Hibernate does not execute such an additional SELECT statement.
From the documentation of the JDBC driver for MySQL Connector/J I see that
it supports the method getGeneratedKeys() to retrieve the assigned value.
So an additionall SELECT statement is not necessary, isn't it?
I debugged jOOQ's code execution and come across code lines 234ff. in
// [#1859] In some databases, not all fields can be fetched via getGeneratedKeys()
if (REFRESH_GENERATED_KEYS.contains(configuration().family()) && this instanceof UpdatableRecord)
((UpdatableRecord<?>) this).refresh(key.toArray(EMPTY_FIELD));
And to me here is the problem, because the enumeration
REFREH_GENERATED_KEYS contains the MySQL dialect, which seems to be wrong.
Can you confirm this?
If the behaviour is correct, are there any other possibilty the prevent
executing the extra SELECT statement after each INSERT statement.
Kind regards,
Marcus
--
You received this message because you are subscribed to the Google Groups
"jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an
For more options, visit https://groups.google.com/d/optout.

[Marcus Gattinger]

Hi Lukas,

no, none of the conditions are met.

I paste the full sample code including the table definition so that you can
reproduce the behaviour on yourself.

/*
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
*/
DSLContext database = applicationContext().getBean(DSLContext.class);
database.settings().setReturnAllOnUpdatableRecord(false);

TestRecord record = database.newRecord(Tables.TEST);
record.setName(UUID.randomUUID().toString());
record.store();
System.out.println(record.getId());


And here is the profile of the SQL statements executed by jOOQ:

[image: profiling.png] <about:invalid#zClosurez>


Hope this helps.

Marcus

[Marcus Gattinger]

BTW: The default value of setting ReturnAllOnUpdatableRecord is false as I
confirmed by replacing the line of code with
System.out.println(database.settings()).

[Lukas Eder]

Thank you very much, Marcus. I'll try to investigate this ASAP. There
should be no additional SELECT statement in your case.

Post by Marcus Gattinger
BTW: The default value of setting ReturnAllOnUpdatableRecord is false as
I confirmed by replacing the line of code with
System.out.println(database.settings()).
--
You received this message because you are subscribed to the Google Groups
"jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an
For more options, visit https://groups.google.com/d/optout.

[Marcus Gattinger]

Hi Lukas,

have you had the chance to figure out, what the problem with the additional
select was? Do you have any update for me?

Kind regards,
Marcus

[Lukas Eder]

Hi Marcus,

I'm very sorry for the delay - thank you very much for the ping. Indeed,
this can be reproduced easily. Your original analysis leading to these
lines was correct:

// [#1859] In some databases, not all fields can be fetched via
getGeneratedKeys()
if (REFRESH_GENERATED_KEYS.contains(configuration().family()) && this
instanceof UpdatableRecord)
((UpdatableRecord<?>) this).refresh(key.toArray(EMPTY_FIELD));


That logic should be guarded by a check of
Settings.returnAllOnUpdatableRecord

I have created an issue for this:
https://github.com/jOOQ/jOOQ/issues/7942

I'll fix it today and include the fix in 3.11.6, which should be released
this or next week

Thanks again for your detailed report and for your patience.
Lukas

Post by Marcus Gattinger
Hi Lukas,
have you had the chance to figure out, what the problem with the
additional select was? Do you have any update for me?
Kind regards,
Marcus
--
You received this message because you are subscribed to the Google Groups
"jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an
For more options, visit https://groups.google.com/d/optout.

[Marcus Gattinger]

Thank you very much, Lukas!