Incorrect sql generated by jooq on rare occasions

Question

I have a spring cloud gateway application saving authentication data to db. Application runs in kubernetes. I have too different cases, sometimes I have json data to save and other times json data is null. Code to save this data looks like this:

    with(AUTHENTICATED_SESSION) {
        context.insertInto(this, ACCESS_TOKEN_MD5_HASH, REFRESH_TOKEN, CONTEXT_DATA)
            .values(
                accessTokenMd5,
                refreshToken,
                if (contextData != null) {
                    JSONB.jsonb(objectMapper.writeValueAsString(contextData))
                } else {
                    null
                }
            )
            .execute()
    }

Generated column definition

public final TableField<AuthenticatedSessionRecord, JSONB> CONTEXT_DATA = createField(DSL.name("context_data"), SQLDataType.JSONB, this, "");

Saving null value usually works like it should be:

insert into "public"."authenticated_session" ("access_token_md5_hash", "refresh_token", "context_data") values (?, ?, cast(? as jsonb))
    : -> with bind values      : insert into "public"."authenticated_session" ("access_token_md5_hash", "refresh_token", "context_data") values ('hash', 'token', cast(null as jsonb))

Sometimes query looks like this and it causes exception below

insert into "public"."authenticated_session" ("access_token_md5_hash", "refresh_token", "context_data") values (?, ?, ?)
    : -> with bind values      : insert into "public"."authenticated_session" ("access_token_md5_hash", "refresh_token", "context_data") values ('hash', 'token', null)

org.jooq.exception.DataAccessException: SQL [insert into "public"."authenticated_session" ("access_token_md5_hash", "refresh_token", "context_data") values (?, ?, ?)]; ERROR: column "context_data" is of type jsonb but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 121
    at org.jooq_3.14.6.DEFAULT.debug(Unknown Source) ~[na:na]
    at org.jooq.impl.Tools.translate(Tools.java:2880) ~[jooq-3.14.6.jar:na]
    at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:757) ~[jooq-3.14.6.jar:na]
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:389) ~[jooq-3.14.6.jar:na]
    at org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:119) ~[jooq-3.14.6.jar:na]

So it seems that on rare occasions jooq generates incorrect sql (without cast to jsonb).

The problem is really annoying as application is not usable at all when this happens. Restarting the pod helps. Most likely this problem appears only after application is started.

Used versions:

jooq 3.14.6
spring boot 2.3.5.RELEASE
spring cloud gateway 2.2.5.RELEASE
Postgresql 10.12 in docker container
postgresql driver 42.2.5
kotlin 1.4.10

Unrelated: Please upgrade to at least 3.14.7 because of a memory leak in 3.14.5 - 3.14.6: https://github.com/jOOQ/jOOQ/issues/11353 — Lukas Eder, Mar 30 '21 at 17:50

score 5 · Accepted Answer · edited Mar 30 '21 at 18:09

5

There's a hint in your question, and every time I see it, I'm very happy to have implemented this feature:

 at org.jooq_3.14.6.DEFAULT.debug(Unknown Source) ~[na:na]

DEFAULT refers to SQLDialect.DEFAULT, but it should be SQLDialect.POSTGRES in your case. The reason why you're getting invalid SQL is because in those cases, you don't have a correctly configured Configuration.

That should help you track down the problem, keeping in mind that jOOQ Query objects aren't thread safe.

edited Mar 30 '21 at 18:09

Roman-Stop RU aggression in UA

14,905
3
48
53

answered Mar 30 '21 at 17:52

Lukas Eder

211,314
129
689
1,509

1

Nice, could you hyperlink location in source code where you generate them? Thx – bedla.czech Mar 30 '21 at 18:15
2

@bedla.czech: https://github.com/jOOQ/jOOQ/blob/a90999b2dada1c0c07dd939244814a1266f87171/jOOQ/src/main/java/org/jooq/impl/DefaultExecuteContext.java#L667-L685 – Lukas Eder Mar 31 '21 at 07:04
Yes, this is really the reason. Without configuring dialect spring boot tries to determine with the connection (and have failed in these rare occassions): https://github.com/spring-projects/spring-boot/blob/master/spring-boot-project/spring-boot-autoconfigure/src/main/java/org/springframework/boot/autoconfigure/jooq/SqlDialectLookup.java – Mikke Apr 01 '21 at 04:39
@Mikke: Oh interesting. Do you think there's a bug in that discovery? Why doesn't it work some times? Of course, an explicit dialect is always better, but it might be convenient to omit it... – Lukas Eder Apr 01 '21 at 09:32
@LukasEder I have no reason to believe there is a bug in discovery. I guess that my problem is because of our dev environment. CI publishes new images and deploys them to kubernetes. Rarely db container hasn't started before application and causes discovery to fail. Luckily this shouldn't happen in production where db is not in kubernetes pod. – Mikke Apr 02 '21 at 17:49

Incorrect sql generated by jooq on rare occasions

1 Answers1

Linked