Hello Alexey, Thank you very much for reporting this. This might indeed be a bug, which we'll be tracking as: https://github.com/jOOQ/jOOQ/issues/4277
Some background: Few databases have native support for INSERT .. RETURNING, among which: - PostgreSQL - Firebird - DB2 (via SELECT * FROM FINAL TABLE (INSERT ...) ) Others support fetching arbitrary result columns via JDBC getGeneratedKeys(), among which: - HSQLDB - Oracle Unfortunately, MySQL only allows for returning ID values via getGeneratedKeys(), which is why jOOQ has to issue an additional select statement to fetch also the remaining columns. Most users are not affected by this issue because they either use a standalone JDBC Connection in their jOOQ Configuration, or a managed DataSource, which returns the same Connection within a transaction every time jOOQ acquires it. This would be the workaround I suggest to you for the time being (e.g. via a Spring or Java EE DataSource). Thanks again for reporting this, Lukas 2015-05-06 1:30 GMT+02:00 Alexey Belostotsky <[email protected]>: > INSERT...RETURNING acquires two connections from DataSource with MYSQL > dialect and not returning first connection until whole query executed. > > When INSERT...RETURNING is executed with configuration configured to use > some DataSource I have encountered a bug where JOOQ acquires connection > for INSERT part and then tries to acquire another connection for > "returning" part, but after the first part of query was executed connection > wasn't closed (and therefore not returned to connection pool) and wasn't > reused to execute the other part of query. > If connection instance is explicitly provided to query builder like > DSL.using(connection) everything goes fine. > The problem should be easily reproducible with pooled DataSource > implementation that has maximum pool size of 1, the INSERT part of query > will be executed successfully, but it will try to acquire connection for > SELECT part and will fail to do so. I haven't tested problem with other > dialects/databases or JOOQ versions. > Configuration code: > > Configuration jooqConfiguration = new DefaultConfiguration() > .derive(dataSource) > .set(SQLDialect.MYSQL); > > Query code: > > Record record = DSL.using(configuration) > .insertInto(SESSION, ...) > .values(...) > .returning().fetchOne(); > > Database i use is MySQL. > JOOQ version: 3.6.1 > > -- > 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 > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- 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 email to [email protected]. For more options, visit https://groups.google.com/d/optout.
