2

Note: This code used to work as I used PostgreSQL. After switching to MySQL these functions stopped working for some reason.

I double checked: languageId and shopId are valid IDs and there is a valid entry of supported_language resembling the inserted record after fetchOne() but it returns null. 

private Long addSupportedLanguage(Long languageId, Long shopId) {

    Long value = this.ctx.insertInto(SHOP_LANGUAGE)
        .set(SHOP_LANGUAGE.LANGUAGE_ID, languageId)
        .set(SHOP_LANGUAGE.SHOP_ID, shopId)
        .returning()
        .fetchOne() // After this line I see the record in my db but it returns null
        .getValue(SHOP_LANGUAGE.LANGUAGE_ID);

    return value;
}

I also tried using

.returning(RESTAURANT_LANGUAGE.LANGUAGE_ID)

but the result is the same.

I am observing something similar when calling returnin() after a delete() statement:

public void deleteEmailVerificationToken(String token) {

    ShopAdminVerificationTokenRecord shopAdminVerificationTokenRecord = this
            .ctx.delete(SHOP_ADMIN_VERIFICATION_TOKEN)
            .where(SHOP_ADMIN_VERIFICATION_TOKEN.VERIFICATION_TOKEN.eq(token))
            .returning()
            .fetchOne()
            .into(SHOP_ADMIN_VERIFICATION_TOKEN);

    if(shopAdminVerificationTokenRecord != null) {
        LOGGER.debug("Deleted verification token for " + shopAdminVerificationTokenRecord.getUserEmail());
    } else {
        LOGGER.debug("The given token was not found.");
    }
}

I don't understand why this is happening. The new record gets correctly inserted but still I cannot fetch that record immediately after insertion.

I created SHOP_LANGUAGE and all other objects using the jOOQ code generator. This is the SQL CREATE TABLE statement that I used to create the table for a MySQL database:

CREATE TABLE shop_language (

    shop_id BIGINT NOT NULL,

    CONSTRAINT fk__shop_language__shop
        FOREIGN KEY (shop_id)
        REFERENCES shop(id),

    language_id BIGINT NOT NULL,

    CONSTRAINT fk__shop_language__language
        FOREIGN KEY (language_id)
        REFERENCES language(id),

    PRIMARY KEY (shop_id, language_id)          
);
Stefan Falk
  • 23,898
  • 50
  • 191
  • 378
  • Did you use the code generator to generate `SHOP_LANGUAGE` and the other objects? What database are you using? Can you post the `CREATE TABLE` statement of your `SHOP_LANGUAGE` table? – Lukas Eder Jun 02 '16 at 05:42
  • @LukasEder I updated my question to provide further information :) – Stefan Falk Jun 02 '16 at 12:31
  • 1
    Hmm, I see... I don't have the jOOQ integration tests ready, so I can't verify this right now, but I think that the MySQL JDBC driver doesn't support returning any values if there is not an `AUTO_INCREMENT` column. Can you verify this with JDBC directly ([example here](http://stackoverflow.com/a/1376241/521799)). Anyway, why do you need the returning clause in the first place? Is there a trigger on that column? It appears that you're simply returning the value you've just inserted again... – Lukas Eder Jun 02 '16 at 16:39
  • @LukasEder I actually wrote that function just to fill my database quick and dirty with some data for testing stuff - you're right, I could also just return `languageId` here. Although I have another scenario where I delete a `token` that I've sent to the user in order to verify his ownership of the provided email address. Here I also used to return the deleted element for example to verify that the token existed when I used PostgreSQL - now with MySQL this is giving me a `NullPointerException`. – Stefan Falk Jun 02 '16 at 16:58
  • 2
    Yes, `DELETE .. RETURNING` is not supported for MySQL: http://www.jooq.org/javadoc/latest/org/jooq/DeleteReturningStep.html#returning-org.jooq.Field...- (see the `@Support` annotation on that method). You could use the checker framework to validate those support annotations: https://blog.jooq.org/2016/05/09/jsr-308-and-the-checker-framework-add-even-more-typesafety-to-jooq-3-9/ – Lukas Eder Jun 02 '16 at 20:40
  • @LukasEder You never know ... I'm definitely going to check out that checker framework. Looks interesting! – Stefan Falk Jun 02 '16 at 20:45
  • Just had the same issue with MySQL, @LukasEder your comment should be translated into an answer, they actually give the cause of the issue. – L. G. Jul 26 '22 at 09:37
  • @L.G.: Indeed, done – Lukas Eder Jul 27 '22 at 14:28

1 Answers1

1

As mentioned in the comments:

DELETE .. RETURNING is not supported for MySQL as documented by the @Support annotation on the relevant returning() methods.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509