Jooq: INSERT ... SET ... RETURNING returns null

[lukaseder]

It appears that this doesn't work:

TodosRecord persisted = jooq.insertInto(TODOS)
                .set(TODOS.CREATION_TIME, currentTime)
                .set(TODOS.DESCRIPTION, todo.getDescription())
                .set(TODOS.ID, id)
                .set(TODOS.MODIFICATION_TIME, currentTime)
                .set(TODOS.TITLE, todo.getTitle())
                .returning()
                .fetchOne();

        //persisted is always null

... whereas this does:

TodosRecord persisted = jooq.insertInto(TODOS)
                .set(createRecord(todo))
                .returning()
                .fetchOne();

        //Persisted is found

---

See also:
https://groups.google.com/forum/#!topic/jooq-user/fOsaaZq44Ac

[pkainulainen]

I noticed that persisted is sometimes null in the second case as well. This doesn't happen all the time but it happens quite often. You can find the example project from here:

https://github.com/pkainulainen/jooq-with-spring-examples/tree/master/jooq-only

The easiest way to reproduce this is to run the integration tests by using command:

mvn clean verify -P integration-test (you can do this from IDE as well).

Sometimes the integration test for the add() repository method fails because NPE is thrown (persisted record was null).

[lukaseder]

Hmm, what would be the fastest way to run these tests from Eclipse? I'd like to debug-step through them, but I can't get Eclipse to pick up Spring configurations right away...

[lukaseder]

... the error I'm getting in Eclipse is this one:

ERROR - TestContextManager         - Caught exception while allowing TestExecutionListener [org.springframework.test.context.support.DependencyInjectionTestExecutionListener@47125a42] to prepare test instance [net.petrikainulainen.spring.jooq.todo.repository.ITJOOQTodoRepositoryTest@183b1e8b]
java.lang.IllegalStateException: Failed to load ApplicationContext
    at org.springframework.test.context.CacheAwareContextLoaderDelegate.loadContext(CacheAwareContextLoaderDelegate.java:99)
    at org.springframework.test.context.DefaultTestContext.getApplicationContext(DefaultTestContext.java:101)
    at org.springframework.test.context.support.DependencyInjectionTestExecutionListener.injectDependencies(DependencyInjectionTestExecutionListener.java:109)
    at org.springframework.test.context.support.DependencyInjectionTestExecutionListener.prepareTestInstance(DependencyInjectionTestExecutionListener.java:75)
    at org.springframework.test.context.TestContextManager.prepareTestInstance(TestContextManager.java:319)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.createTest(SpringJUnit4ClassRunner.java:212)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner$1.runReflectiveCall(SpringJUnit4ClassRunner.java:289)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.methodBlock(SpringJUnit4ClassRunner.java:291)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:232)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:89)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:175)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'JOOQTodoRepository' defined in file [C:\Users\Lukas\git\jooq-with-spring-examples\jooq-only\target\classes\net\petrikainulainen\spring\jooq\todo\repository\JOOQTodoRepository.class]: Unsatisfied dependency expressed through constructor argument with index 0 of type [net.petrikainulainen.spring.jooq.common.service.DateTimeService]: : No qualifying bean of type [net.petrikainulainen.spring.jooq.common.service.DateTimeService] found for dependency: expected at least 1 bean which qualifies as autowire candidate for this dependency. Dependency annotations: {}; nested exception is org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type [net.petrikainulainen.spring.jooq.common.service.DateTimeService] found for dependency: expected at least 1 bean which qualifies as autowire candidate for this dependency. Dependency annotations: {}
    at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:741)
    at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:185)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireConstructor(AbstractAutowireCapableBeanFactory.java:1114)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1017)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:504)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:475)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:304)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:300)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:195)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:700)
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:760)
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:482)
    at org.springframework.test.context.support.AbstractGenericContextLoader.loadContext(AbstractGenericContextLoader.java:121)
    at org.springframework.test.context.support.AbstractGenericContextLoader.loadContext(AbstractGenericContextLoader.java:60)
    at org.springframework.test.context.support.AbstractDelegatingSmartContextLoader.delegateLoading(AbstractDelegatingSmartContextLoader.java:100)
    at org.springframework.test.context.support.AbstractDelegatingSmartContextLoader.loadContext(AbstractDelegatingSmartContextLoader.java:250)
    at org.springframework.test.context.CacheAwareContextLoaderDelegate.loadContextInternal(CacheAwareContextLoaderDelegate.java:64)
    at org.springframework.test.context.CacheAwareContextLoaderDelegate.loadContext(CacheAwareContextLoaderDelegate.java:91)
    ... 25 more
Caused by: org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type [net.petrikainulainen.spring.jooq.common.service.DateTimeService] found for dependency: expected at least 1 bean which qualifies as autowire candidate for this dependency. Dependency annotations: {}
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.raiseNoSuchBeanDefinitionException(DefaultListableBeanFactory.java:1100)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:960)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:855)
    at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:805)
    at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:733)
    ... 43 more

Any ideas?

[lukaseder]

OK, don't know what's going on with Spring, but inlining the DateTimeService in the JOOQTodoRepository worked around the above issue:

    @Autowired
    public JOOQTodoRepository(DefaultDSLContext jooq) {
        this.dateTimeService = new DateTimeService() {

            @Override
            public LocalDateTime getCurrentDateTime() {
                LOGGER.debug("Returning constant datetime: {}", TestDateUtil.CURRENT_TIMESTAMP);
                return TestDateUtil.parseLocalDateTime(TestDateUtil.CURRENT_TIMESTAMP);
            }

            @Override
            public Timestamp getCurrentTimestamp() {
                LOGGER.debug("Returning constant timestamp: {}", TestDateUtil.CURRENT_TIMESTAMP);
                return Timestamp.valueOf(TestDateUtil.CURRENT_TIMESTAMP);
            }
        };
        this.jooq = jooq;
    }

I can now reproduce your issue when I run all tests, but not when I run only this test. Could be some erroneous test setup? Let's see...

[lukaseder]

I can reproduce and debug through this issue like this:

1. Build and run integration tests with Maven
2. Run integration tests with Eclipse, set a breakpoint in org.jooq.impl.AbstractStoreQuery:350
3. Observe how the H2 / JDBC PreparedStatement doesn't return any generated keys:

                case DERBY:
                case H2:
                case MARIADB:
                case MYSQL: {
                    listener.executeStart(ctx);
                    result = ctx.statement().executeUpdate();
                    ctx.rows(result);
                    listener.executeEnd(ctx);

                    rs = ctx.statement().getGeneratedKeys(); // line 350

I wonder what's causing this?

[pkainulainen]

Ah, sorry about that. I forgot to mention that there are spring profiles: application and test. The test profile uses a DateTimeService implementation which always returns the same time (useful for testing).

The Javadoc of the Profile annotation (http://docs.spring.io/spring/docs/4.0.x/javadoc-api/org/springframework/context/annotation/Profile.html) says that:

"A profile is a named logical grouping that may be activated programmatically via ConfigurableEnvironment.setActiveProfiles(java.lang.String...) or declaratively through setting the spring.profiles.active property, usually through JVM system properties, as an environment variable, or for web applications as a Servlet context parameter in web.xml."

You can also annotate the test class with the ActiveProfiles annotation (http://docs.spring.io/spring/docs/4.0.x/javadoc-api/org/springframework/test/context/ActiveProfiles.html). I don't use this because I would have to add it to every test class, but this might be the easiest way to fix this (it makes debugging a lot easier).

[lukaseder]

Aha, I see. Thanks for the explanation :-) I think this will save me 1-2 times in the future, before I'll start looking through debugging these things.

Anyway, have you been able to play around with this a little more? So far, I couldn't find any way to reproduce it without all the Spring / BoneCP dependencies. I'd like to rule out a subtle bug in those dependencies, or in H2

[pkainulainen]

I will take a closer look at this today. I could try using a different connection pool and database. That should give us some additional information about this.

[pkainulainen]

I tested this with MySQL and I cannot reproduce it. I ran mvn clean verify -P integration-test 20 times in a row and all tests passed every time. I know that this isn't a scientific proof that this problem cannot happen with MySQL but it happened pretty much every time with H2 (when I cleaned before running tests).

I used MySQL JDBC driver version 5.1.26 and MySQL 5.6.10.

[lukaseder]

Thanks for the feedback. That makes me feel a bit safer :-)

I could reproduce your issue with Maven / H2 every time I ran the test. But with Eclipse, the behaviour was usually (after building with Maven on the console):

• Fail
• Fail (maybe)
• Pass (indefinitely)
• Fail (after rebuild with Maven on the console)
• Pass (indefinitely)

From the jOOQ side, the behaviour should be exactly the same for any of these databases:

• Derby
• H2
• MariaDB
• MySQL

As can be seen here:
https://github.com/jOOQ/jOOQ/blob/9d6099cde405595c8c345687360cf6f71ad3ea81/jOOQ/src/main/java/org/jooq/impl/AbstractStoreQuery.java#L355

Maybe, JDBC-logger (http://jdbclogger.sourceforge.net/) could help tracing down the exact sequence of JDBC API calls needed to reproduce this? I suspect it's really a very tough corner-case!

[lukaseder]

@pkainulainen : Just discoverd this issue on Stack Overflow:
http://stackoverflow.com/q/22660640/521799

Someone seems to have discovered a nasty issue with BoneCP, which disappeared when they switched to Apache DBCP. We'll probably replace the connection pools as well in our tutorial (#3159). Do you think these issues are related?

[pkainulainen]

I can replace BoneCP with HikariCP and see if it changes anything (If it does, I will probably use it in my tutorial). I will test this today and report back to you.

[pkainulainen]

I tested this with HikariCP 1.3.4 and I could still reproduce the problem (h2 database).

[lukaseder]

OK, that helps us exclude one of the "guilty candidates". I'll try to dig into H2 today

[lukaseder]

Hmm, I tried for another hour - but without a deterministically reproducible test case (in Eclipse), I'm having trouble tracking it down :-)

I'll have to check again, next week.

[pkainulainen]

I haven't been able to reproduce this problem if the clean Maven lifecycle phase is not invoked. Maybe this problem has got something to do with the fact that the H2 database is deleted when the build is cleaned?

[lukaseder]

Hmm, very interesting. What happens in the clean phase but not afterwards? The CREATE TABLE statement?

[pkainulainen]

By the way, I noticed one more thing:

In the schema.sql file I had a private key field with auto increment, but I still fetched the id from a sequence because I wanted to demonstrate how this works if the db doesn't support auto increment. Anyway, I removed the auto increment from the private key field and now the test fails every time.

[lukaseder]

Thanks for the update. I'll need to double-check that situation. The auto-increment column acts as an IDENTITY column, which is often the only way to actually generate a key through JDBC's getGeneratedKeys().

Did you update your own test code with these changes for me to review?

[pkainulainen]

Yes. All changes are in the Github repo.

I can try to test the current version with MySQL before I remove the sequence completely and replace it with auto-increment column (and test this with H2 and MySQL).

I will do this today and I will report my findings here.

[pkainulainen]

I forgot that MySQL doesn't have sequences... ;) Anyway, after I changed the example so that it uses auto increment instead of sequence, the tests are passing. I am starting to think that this was a stupid programmer error and not a bug. What do you think?

[lukaseder]

I am starting to think that this was a stupid programmer error and not a bug. What do you think?

Well, the whole discussion certainly unsettled me a bit. There _may_ be an issue in jOOQ, as IDENTITY columns are poorly supported across SQL dialects, even if the notion of IDENTITY is standardised in SQL. This is an area in jOOQ where each dialect behaves very differently.

But on the other hand, I haven't been aware of anyone reporting a similar issue, so maybe, this was indeed just a test-setup error :-)

So, what do _you_ think, how we should proceed?

[pkainulainen]

Well, I think that since no one hasn't reported a similar issue (and jooq seems to be used quite widely) you can close this issue.

[viki85in]

The issue is still reproducible though. I am using mysql with jooq.

DSLContext dslContext = DSL.using(connection, SQLDialect.MYSQL);
            SubscriberRecord subscriberRecord = dslContext.insertInto(SUBSCRIBER,SUBSCRIBER.NAME, SUBSCRIBER.CONFIGURATION_ID, SUBSCRIBER.NATIVE_ID)
            .values(subscriber.getName(), subscriber.getConfigurationId(), subscriber.getNativeId())
            .returning()
            .fetchOne();

Note that the "subscriberRecord" is null.

[lukaseder]

@viki85in Thank you very much for reporting. Can you provide me with the DDL to create the SUBSCRIBER table, as well as your MySQL version and MySQL JDBC driver version?

[viki85in]

@lukaseder I figured out the problem. And I think a lot of developers have bumped into the same scenario. I had added the identify auto increment after generating the objects.

Once I regenerated the objects, the issue is not reproducible. Sorry for the confusion. My bad.

I think it is safe to close this issue. For good. :)

[lukaseder]

Oh I see. Yes, jOOQ needs the "identity" information on the right column, otherwise it won't be able to fetch the right identity value (in MySQL)

Thanks a lot for the feedback!

[tmjeee]

Any chance this going into 3.9.x branch?

[lukaseder]

Hi @tmjeee: Thanks for your message. I'm not sure what you mean - this issue was resolved as "worksforme" - there is currently no fix. Maybe, you experienced a similar issue? Would you mind creating a new issue on GitHub?

[txsmith]

As discussed in #2123, I'm trying to get INSERT .. RETURNING working with ON DUPLICATE KEY.
@lukaseder Suggested to cast the type returned by InsertOnDuplicateStep.onDuplicateKeyIgnore() to InsertReturningStep<R> and call returning() on that, but when I do that the query always returns a null record.

To demonstrate I created a repo with one test that describes my problem.

I'd love your advice on this!

[deinspanjer]

I am stuck really needing this capability as well, and seeing the same result. I turned on debug logging, and the resulting INSERT statement ends with on conflict do nothing, it does not include any returning clause.

[lukaseder]

@txsmith: I'm not sure if this is really the same problem, although, if it is, then it's not a problem. Your test case is simply incorrect:

// Insert the first entry (if not exists)
db.insertInto(PERSON, PERSON.FIRST_NAME, PERSON.LAST_NAME)
        .values("Thomas", "Smith")
        .onDuplicateKeyIgnore()
        .execute();

Integer records = db.selectCount().from(PERSON).fetchOneInto(Integer.class);

assertThat(records).isOne();

// Insert the duplicate entry
InsertReturningStep<?> query = (InsertReturningStep<?>)
        db.insertInto(PERSON, PERSON.FIRST_NAME, PERSON.LAST_NAME)
        .values("Thomas", "Smith")
        .onDuplicateKeyIgnore();

Record result = query.returning(PERSON.FIRST_NAME).fetchOne();
assertThat(result).isNotNull();

If you run the second query in PostgreSQL directly, you'll get an empty result set:

insert into "example"."person" (
  "first_name", 
  "last_name"
)
values (
  'Thomas', 
  'Smith'
)
on conflict do nothing
returning "example"."person"."first_name"

Yielding...

fetchOne() in jOOQ consistently returns null if there was no record, throughout the jOOQ API. Perhaps, fetchOptional() is more aligned with your expectations?

@deinspanjer: I'm happy to look into any steps to reproduce this. The test case @txsmith provided does generate that returning clause...

[txsmith]

Right, so it turns out that I got the query wrong in the first place. My intention was to have the query always return the first_name regardless of any conflicts. As you pointed out, this is not the way on conflict do nothing returning ... works, so I switched to on conflict do update ... returning ...:

// Insert the duplicate entry
InsertReturningStep<?> query = (InsertReturningStep<?>)
        db.insertInto(PERSON, PERSON.FIRST_NAME, PERSON.LAST_NAME)
        .values("Thomas", "Smith")
        .onConflict(PERSON.FIRST_NAME, PERSON.LAST_NAME)
        .doUpdate()
        .set(PERSON.FIRST_NAME, "Thomas");

Record result = query.returning(PERSON.FIRST_NAME).fetchOne();

It seems like kind of a hack on the sql side, but at least this works! Thanks for pushing me in the right direction :)

[deinspanjer]

I was basing my code off of txsmith's work, so let me try again and if I can reproduce a failing case I'll share it.

I was wanting to do the same thing as him, return the id of the existing item if I try to insert a duplicate, otherwise, insert and return the new id.

[jakesyl]

Thanks so much @txsmith, this worked for me. Would still love to see a PR adding .returning() to InsertOnDuplicateSetStep though.