Hello Lukas, I just had a chance to test release 3.11.6. I completely fixes the issue for me. Thank you for your help and quick turnaround. Regards,
Sterl On Friday, November 2, 2018 at 6:32:08 AM UTC-4, Lukas Eder wrote: > > I can now reproduce this issue in our integration tests. The regression > was introduced in jOOQ 3.11 and can be traced to this change here: > https://github.com/jOOQ/jOOQ/issues/4277 > > In essence, this test currently passes: > > public void testAttachedConfigurationFromFetch() throws Exception { > DSLContext ctx = create(); > > Configuration c = ctx.configuration(); > ConnectionProvider cp = c.connectionProvider(); > var book = ctx.fetchOne(TBook(), TBook_ID().eq(1)); > assertEquals(c, book.configuration()); > assertEquals(cp, book.configuration().connectionProvider()); > } > > As expected, the attached Configuration of the resulting Record is the > same one as the one we started with. > > But this test fails: > > public void testAttachedConfigurationFromReturning() throws Exception { > assumeNotNull(TIdentityPK()); > DSLContext ctx = create(); > > Configuration c = ctx.configuration(); > ConnectionProvider cp = c.connectionProvider(); > var rec = ctx > .insertInto(TIdentityPK()) > .columns(TIdentityPK_VAL()) > .values(1) > .returning() > .fetchOne(); > > assertEquals(c, rec.configuration()); > assertEquals(cp, rec.configuration().connectionProvider()); > } > > Both assertions fail because the returned record references a derived > Configuration instead of the original one (the derived configuration is > used to fix #4277). > > This regression is only present for: > > - DB2 > - Firebird > - HSQLDB > - Oracle (in some cases) > - PostgreSQL > > I.e. databases with native support for RETURNING or a similar clause. > Also, with most DataSources / transaction models, the regression went > unnoticed, because the underlying connection wasn't closed when jOOQ called > Connection.close() on it. So, this might be very specific to how you're > setting up HikariCP as a standalone data source. Having said so, there are > two workaround: > > 1. Work with the JDBC Connection directly instead of the DataSource, or > implement your own ConnectionProvider > 2. Use jOOQ's transaction API to wrap the two database calls > > I have created https://github.com/jOOQ/jOOQ/issues/8006 and will fix this > today. The fix will be backported to 3.11.6, which should also ship today. > > Thanks again for reporting! > Lukas > > > On Tue, Oct 30, 2018 at 8:07 AM Lukas Eder <[email protected] > <javascript:>> wrote: > >> OK, thanks to your instructions, I could easily reproduce the problem. >> Will investigate now >> >> On Tue, Oct 30, 2018 at 8:02 AM Lukas Eder <[email protected] >> <javascript:>> wrote: >> >>> Hi Sterl, >>> >>> Thank you very much for taking the time to provide an MCVE. That looks >>> very thorough and complete. I will look into it these days and provide >>> feedback ASAP. >>> >>> Thanks, >>> Lukas >>> >>> On Thu, Oct 25, 2018 at 10:20 PM <[email protected] <javascript:>> >>> wrote: >>> >>>> Hello Lukas, >>>> >>>> I updated my project dependencies such as the jdbc driver and >>>> HikariCP. I'm getting the same results. >>>> I have created a small separate project just showing the issue with >>>> Jooq in a github repository. (I couldn't upload a zip file of my folder >>>> for >>>> some reason.) The repository is here: >>>> https://github.com/sterling-brown/jooqissue >>>> >>>> It has a gradle driven build with the gradle wrapper installed for >>>> convenience. The README.md file will give you the steps to build and >>>> reproduce. >>>> >>>> Again, thank you for your assistance, >>>> >>>> Regards, >>>> Sterl... >>>> >>>> >>>> >>>> >>>> On Thursday, October 25, 2018 at 12:27:06 PM UTC-4, [email protected] >>>> wrote: >>>>> >>>>> Hello Lukas, >>>>> I will try to upgrade the driver and proceed to create the MCVE. >>>>> By the way thank you for your assistance. >>>>> I will get back to you soon. >>>>> >>>>> Regards, >>>>> Sterl.. >>>>> >>>>> On Thursday, October 25, 2018 at 12:07:55 PM UTC-4, Lukas Eder wrote: >>>>>> >>>>>> Interesting. I don't see anything that looks wrong so far. But then >>>>>> again, our integration tests do not indicate anything wrong in jOOQ >>>>>> either. >>>>>> One thing that is interesting, though, is your usage of PostgreSQL 9.4, >>>>>> which is quite old. I would also imagine that you're hence using a >>>>>> rather >>>>>> old JDBC driver version? Does the problem persist if you upgrade the >>>>>> driver? >>>>>> >>>>>> Right now, I can only guess and not really reproduce the problem. If >>>>>> you are willing to spend some time to create an MCVE, I will definitely >>>>>> look into it more in depth: >>>>>> https://stackoverflow.com/help/mcve >>>>>> >>>>>> Thanks, >>>>>> Lukas >>>>>> >>>>>> On Thu, Oct 25, 2018 at 5:50 PM <[email protected]> wrote: >>>>>> >>>>>>> Hello, >>>>>>> >>>>>>> Yes, data_source is private and static in my DbConnection class and >>>>>>> no one else initializes it. >>>>>>> My usage in this case is single threaded, so no one else is >>>>>>> initializing it and there is no race condition. >>>>>>> No, I didn't get any other exceptions. It is strange because, my >>>>>>> record is created without problems. >>>>>>> No, I don't have any execute listeners and no one else is using the >>>>>>> connection before I use it again to perform the jobRecord.update(). >>>>>>> >>>>>>> I do use transaction is other places in my code base like this: >>>>>>> >>>>>>> >>>>>>> DSLContext db = DbConnection.getPooledConnection() >>>>>>> >>>>>>> db.transaction( configuration -> { >>>>>>> DSLContext innerDb = DSL.using(configuration); >>>>>>> ... >>>>>>> >>>>>>> Do transaction stuff. >>>>>>> >>>>>>> ... >>>>>>> }); >>>>>>> >>>>>>> >>>>>>> >>>>>>> Regards, >>>>>>> Sterl. >>>>>>> >>>>>>> >>>>>>> On Thursday, October 25, 2018 at 11:31:57 AM UTC-4, Lukas Eder wrote: >>>>>>>> >>>>>>>> Hi Sterl, >>>>>>>> >>>>>>>> Thanks for the update. I'm assuming that data_source reference is >>>>>>>> static and no one else initialises it at the same time through a race >>>>>>>> condition - although that wouldn't explain this particular problem. >>>>>>>> >>>>>>>> Hmm, browsing through the Hikari source code, I've noticed there >>>>>>>> are some issues that can cause a connection to be evicted from the >>>>>>>> pool. Do >>>>>>>> you have any other exceptions prior to this one? >>>>>>>> >>>>>>>> Do you perhaps have any execute listeners that may run stuff on the >>>>>>>> same connection while a statement is being executed? >>>>>>>> >>>>>>>> Are you using transactions? If so, how? >>>>>>>> >>>>>>>> On Thu, Oct 25, 2018 at 5:14 PM <[email protected]> wrote: >>>>>>>> >>>>>>>>> Sure, this is how I get my db reference. >>>>>>>>> >>>>>>>>> DSLContext db = DbConnection.getPooledConnection(); >>>>>>>>> >>>>>>>>> >>>>>>>>> Here are my getPooledConnection() and getConnectionPool() methods. >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> public static DSLContext getPooledConnection() >>>>>>>>> { >>>>>>>>> HikariDataSource ds = getConnectionPool(); >>>>>>>>> DSLContext db = DSL.using(ds,SQLDialect.POSTGRES_9_4,new >>>>>>>>> Settings() >>>>>>>>> .withExecuteWithOptimisticLocking(true) >>>>>>>>> .withExecuteLogging(false) >>>>>>>>> ); >>>>>>>>> return db; >>>>>>>>> } >>>>>>>>> >>>>>>>>> static HikariDataSource getConnectionPool() >>>>>>>>> { >>>>>>>>> if( data_source == null ) >>>>>>>>> { >>>>>>>>> Properties props = AppProperties.getProperties("db"); >>>>>>>>> >>>>>>>>> HikariConfig config = new HikariConfig(); >>>>>>>>> config.setJdbcUrl( props.getProperty("jdbc.url")); >>>>>>>>> config.setUsername( props.getProperty("username")); >>>>>>>>> config.setPassword( props.getProperty("password")); >>>>>>>>> config.setConnectionTestQuery("SELECT tablename FROM >>>>>>>>> pg_catalog.pg_tables WHERE schemaname='public'"); >>>>>>>>> data_source = new HikariDataSource(config); >>>>>>>>> } >>>>>>>>> return data_source; >>>>>>>>> } >>>>>>>>> >>>>>>>>> Regards, >>>>>>>>> Sterl... >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> On Thursday, October 25, 2018 at 8:58:23 AM UTC-4, Lukas Eder >>>>>>>>> wrote: >>>>>>>>>> >>>>>>>>>> This is most likely related to how you set up your connection >>>>>>>>>> pool / data source / etc inside of that db reference. Would you mind >>>>>>>>>> showing relevant code / configuration logic? >>>>>>>>>> >>>>>>>>>> Alternatively, someone else could be using your data source >>>>>>>>>> (other than jOOQ) and is handling connections incorrectly. >>>>>>>>>> >>>>>>>>>> Thanks, >>>>>>>>>> Lukas >>>>>>>>>> >>>>>>>>>> On Wed, Oct 24, 2018 at 7:48 PM <[email protected]> wrote: >>>>>>>>>> >>>>>>>>>>> HI, I'm getting the this weird exception when I call update() on >>>>>>>>>>> UpdatableRecord after creating it. >>>>>>>>>>> >>>>>>>>>>> org.jooq.exception.DataAccessException: SQL [update >>>>>>>>>>> "public"."job" set "context" = ?, "modified_at" = cast(? as >>>>>>>>>>> timestamp) >>>>>>>>>>> where ("public"."job"."id" = ? and "public"."job"."modified_at" = >>>>>>>>>>> cast(? as >>>>>>>>>>> timestamp))]; Connection is closed >>>>>>>>>>> at org.jooq_3.11.5.POSTGRES_9_4.debug(Unknown Source) >>>>>>>>>>> at org.jooq.impl.Tools.translate(Tools.java:2384) >>>>>>>>>>> at >>>>>>>>>>> org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:811) >>>>>>>>>>> at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:364) >>>>>>>>>>> at >>>>>>>>>>> org.jooq.impl.UpdatableRecordImpl.storeUpdate0(UpdatableRecordImpl.java:253) >>>>>>>>>>> at >>>>>>>>>>> org.jooq.impl.UpdatableRecordImpl.access$100(UpdatableRecordImpl.java:84) >>>>>>>>>>> at >>>>>>>>>>> org.jooq.impl.UpdatableRecordImpl$2.operate(UpdatableRecordImpl.java:209) >>>>>>>>>>> at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:125) >>>>>>>>>>> at >>>>>>>>>>> org.jooq.impl.UpdatableRecordImpl.storeUpdate(UpdatableRecordImpl.java:205) >>>>>>>>>>> at >>>>>>>>>>> org.jooq.impl.UpdatableRecordImpl.update(UpdatableRecordImpl.java:155) >>>>>>>>>>> at >>>>>>>>>>> org.jooq.impl.UpdatableRecordImpl.update(UpdatableRecordImpl.java:150) >>>>>>>>>>> at channellister.jobs.JobManager.submitJob(JobManager.java:91) >>>>>>>>>>> at channellister.jobs >>>>>>>>>>> .JobDefinition.submit(JobDefinition.java:58) >>>>>>>>>>> at >>>>>>>>>>> channellister.clutil.commands.JobsSubmit.lambda$processCommand$0(JobsCommand.java:95) >>>>>>>>>>> at >>>>>>>>>>> infomorph.dbconnection.DbThrowingConsumer.accept(DbThrowingConsumer.java:20) >>>>>>>>>>> at >>>>>>>>>>> infomorph.dbconnection.DbConnection.dbAction(DbConnection.java:61) >>>>>>>>>>> at >>>>>>>>>>> channellister.clutil.commands.JobsSubmit.processCommand(JobsCommand.java:94) >>>>>>>>>>> at >>>>>>>>>>> channellister.clutil.ClAbstractCommand.processSubCommand(ClAbstractCommand.java:69) >>>>>>>>>>> at >>>>>>>>>>> channellister.clutil.ClAbstractCommand.processCommand(ClAbstractCommand.java:55) >>>>>>>>>>> at >>>>>>>>>>> channellister.clutil.ClAbstractCommand.processSubCommand(ClAbstractCommand.java:69) >>>>>>>>>>> at channellister.clutil.ClUtil.processCommand(ClUtil.java:76) >>>>>>>>>>> at channellister.clutil.ClUtil.main(ClUtil.java:91) >>>>>>>>>>> Caused by: java.sql.SQLException: Connection is closed >>>>>>>>>>> at >>>>>>>>>>> com.zaxxer.hikari.pool.ProxyConnection$ClosedConnection$1.invoke(ProxyConnection.java:469) >>>>>>>>>>> at com.sun.proxy.$Proxy1.prepareStatement(Unknown Source) >>>>>>>>>>> at >>>>>>>>>>> com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:310) >>>>>>>>>>> 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.ProviderEnabledConnection.prepareStatement(ProviderEnabledConnection.java:109) >>>>>>>>>>> at >>>>>>>>>>> org.jooq.impl.SettingsEnabledConnection.prepareStatement(SettingsEnabledConnection.java:73) >>>>>>>>>>> at org.jooq.impl.AbstractQuery.prepare(AbstractQuery.java:416) >>>>>>>>>>> at >>>>>>>>>>> org.jooq.impl.AbstractDMLQuery.prepare(AbstractDMLQuery.java:512) >>>>>>>>>>> at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:322) >>>>>>>>>>> ... 18 more >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> Simplified Example code: >>>>>>>>>>> >>>>>>>>>>> JobRecord job = db.insertInto( JOB, JOB.CODE, JOB.DESCRIPTION, >>>>>>>>>>> JOB.STATUS, JOB.CONTEXT, JOB.STEP ) >>>>>>>>>>> .values( jobCode, jobDescription, jobStatus, jobContext, jobStep >>>>>>>>>>> ) >>>>>>>>>>> .returning() >>>>>>>>>>> .fetchOne(); >>>>>>>>>>> >>>>>>>>>>> // Create Job Execution Context >>>>>>>>>>> int jobId = jobRecord.getId(); >>>>>>>>>>> JSONObject executionContext = new JSONObject(); >>>>>>>>>>> executionContext.put("id", String.valueOf( jobId ) ); >>>>>>>>>>> String executionContextString = executionContext.toString(); >>>>>>>>>>> >>>>>>>>>>> // Set the Execution Context on the Job Record >>>>>>>>>>> jobRecord.setContext( executionContextString ); >>>>>>>>>>> jobRecord.update(); >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> Environment: >>>>>>>>>>> Jooq 3.11.5, Postgres 9.4, Java 10 >>>>>>>>>>> >>>>>>>>>>> Thanks in advance for any assistance you can offer. >>>>>>>>>>> Sterl... >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> -- >>>>>>>>>>> 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. >>>>>>>>> >>>>>>>> -- >>>>>>> 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] <javascript:>. >>>> 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.
