[Quartz][Oracle] Unable to store job details when using Quarkus Quartz Scheduler with Oracle #43720

dcdh · 2024-10-05T05:22:40Z

Describe the bug

While doing a migration from an old application using quartz 2 (from my memory) to the last version of quarkus, I notice an issue when the quartz job is stored in the database.

It failed because in qrtz_job_details boolean likes IS_DURABLE is stored using one varchar (0 or 1) type and it expected 5 varchar type for TRUE or FALSE.

Expected behavior

Following the reproducer associated with this issue:

The application should start.
The count property must be incremented each 2 seconds using the Quartz scheduler.
The test should return green when count > 0.

Actual behavior

The reproducer fails to start because at startup the quartz job definition is stored and an sql issue is thrown regarding varchar length too small to store boolean value representation.

How to Reproduce?

git clone https://github.com/dcdh/oracle-quartz-reproducer.git
run SampleTaskTest
It will fail with this kind of stacktrace

Caused by: org.quartz.JobPersistenceException: Couldn't store job: ORA-12899: valeur trop grande pour la colonne "QUARKUS"."QRTZ_JOB_DETAILS"."IS_DURABLE" (réelle : 5, maximum : 1)

FYI, the init script is coming from tables_oracle.sql provided by quartz dependency. I've just commented the delete and drop table blocs because it was failing at startup.

Output of `uname -a` or `ver`

Linux 2a02-8428-dff8-c601-234b-8c10-a3c4-2308.rev.sfr.net 6.10.10-200.fc40.x86_64 #1 SMP PREEMPT_DYNAMIC Thu Sep 12 18:26:09 UTC 2024 x86_64 GNU/Linux

Output of `java -version`

openjdk version "21.0.4" 2024-07-16 OpenJDK Runtime Environment (Red_Hat-21.0.4.0.7-2) (build 21.0.4+7) OpenJDK 64-Bit Server VM (Red_Hat-21.0.4.0.7-2) (build 21.0.4+7, mixed mode, sharing)

Quarkus version or git rev

3.15.1

Build tool (ie. output of `mvnw --version` or `gradlew --version`)

Apache Maven 3.9.6 (Red Hat 3.9.6-6)

Additional information

It failed to store the job because it expect a boolean column definition using a 5 varchar length to store TRUE or FALSE as string.
The oracle table definition has not changed from many years and it used one varchar to store boolean value.
We should keep this definition.

When using an oracle db-kind, the QuarkusStdJDBCDelegate is used. I guess this issue is coming from it. Maybe we should provide a custom implementation for oracle (which is not the case).

I guess to fix it, in QuartzProcressor the buildstep guessDriver should be updated from

    private String guessDriver(Optional<JdbcDataSourceBuildItem> jdbcDataSource) {
        if (!jdbcDataSource.isPresent()) {
            return QuarkusStdJDBCDelegate.class.getName();
        }

        String dataSourceKind = jdbcDataSource.get().getDbKind();
        if (DatabaseKind.isPostgreSQL(dataSourceKind)) {
            return QuarkusPostgreSQLDelegate.class.getName();
        }
        if (DatabaseKind.isH2(dataSourceKind)) {
            return QuarkusHSQLDBDelegate.class.getName();
        }
        if (DatabaseKind.isMsSQL(dataSourceKind)) {
            return QuarkusMSSQLDelegate.class.getName();
        }
        if (DatabaseKind.isDB2(dataSourceKind)) {
            return QuarkusDBv8Delegate.class.getName();
        }

        return QuarkusStdJDBCDelegate.class.getName();
    }

to

    private String guessDriver(Optional<JdbcDataSourceBuildItem> jdbcDataSource) {
        if (!jdbcDataSource.isPresent()) {
            return QuarkusStdJDBCDelegate.class.getName();
        }

        String dataSourceKind = jdbcDataSource.get().getDbKind();
        if (DatabaseKind.isPostgreSQL(dataSourceKind)) {
            return QuarkusPostgreSQLDelegate.class.getName();
        }
        if (DatabaseKind.isH2(dataSourceKind)) {
            return QuarkusHSQLDBDelegate.class.getName();
        }
        if (DatabaseKind.isMsSQL(dataSourceKind)) {
            return QuarkusMSSQLDelegate.class.getName();
        }
        if (DatabaseKind.isDB2(dataSourceKind)) {
            return QuarkusDBv8Delegate.class.getName();
        }
        if (DatabaseKind.isOracle(dataSourceKind)) {
            return QuarkusOracleDelegate.class.getName();
        }

        return QuarkusStdJDBCDelegate.class.getName();
    }

particular code

        if (DatabaseKind.isOracle(dataSourceKind)) {
            return QuarkusOracleDelegate.class.getName();
        }

with QuarkusOracleDelegate having the same beahvior than other QuarkusdatasourceKindDelegate

The text was updated successfully, but these errors were encountered:

quarkus-bot · 2024-10-05T05:22:45Z

/cc @machi1990 (quartz), @manovotn (quartz,scheduler), @mkouba (quartz,scheduler)

manovotn · 2024-10-06T13:49:37Z

I suppose this is an issue on my end but as of now I am unable to run the reproducer - namely the docker image refuses to start 🤔

dcdh · 2024-10-07T06:32:09Z

@manovotn just run the test SampleTaskTest - I guess you've got an issue with oracle devservice ?

mkouba · 2024-10-07T07:17:40Z

I think that it makes sense to introduce the QuarkusOracleDelegate that extends org.quartz.impl.jdbcjobstore.oracle.OracleDelegate. Actually, I'm quite surprised we don't support it. It might be that (1) OracleDelegate is located in a separate package (org.quartz.impl.jdbcjobstore.oracle vs org.quartz.impl.jdbcjobstore used for other DB types) so we simply overlooked it and (2) there was no Oracle user yet?

@dcdh Would you care to send a pull request?

manovotn · 2024-10-07T07:47:13Z

@manovotn just run the test SampleTaskTest - I guess you've got an issue with oracle devservice ?

Well, the container is started with the test; I guess it's the devservice timing out, yea.

Anyway, I agree with Martin that this looks like we initially overlooked the oracle delegate. If you were to send a PR, we'd be happy to review it :)

dcdh · 2024-10-07T08:26:32Z

Ok, I will provide a PR tonight. Keep you in touch.

dcdh · 2024-10-07T08:41:37Z

I think that it makes sense to introduce the QuarkusOracleDelegate that extends org.quartz.impl.jdbcjobstore.oracle.OracleDelegate. Actually, I'm quite surprised we don't support it. It might be that (1) OracleDelegate is located in a separate package (org.quartz.impl.jdbcjobstore.oracle vs org.quartz.impl.jdbcjobstore used for other DB types) so we simply overlooked it and (2) there was no Oracle user yet?

@dcdh Would you care to send a pull request?

OracleDelegate is declared inside a subpackage of org.quartz.impl.jdbcjobstore : org.quartz.impl.jdbcjobstore.oracle

mkouba · 2024-10-07T08:51:59Z

I think that it makes sense to introduce the QuarkusOracleDelegate that extends org.quartz.impl.jdbcjobstore.oracle.OracleDelegate. Actually, I'm quite surprised we don't support it. It might be that (1) OracleDelegate is located in a separate package (org.quartz.impl.jdbcjobstore.oracle vs org.quartz.impl.jdbcjobstore used for other DB types) so we simply overlooked it and (2) there was no Oracle user yet?
@dcdh Would you care to send a pull request?

OracleDelegate is declared inside a subpackage of org.quartz.impl.jdbcjobstore : org.quartz.impl.jdbcjobstore.oracle

Yes, that's what I meant when I said "org.quartz.impl.jdbcjobstore.oracle vs org.quartz.impl.jdbcjobstore"...

dcdh · 2024-10-07T08:58:58Z

I think that it makes sense to introduce the QuarkusOracleDelegate that extends org.quartz.impl.jdbcjobstore.oracle.OracleDelegate. Actually, I'm quite surprised we don't support it. It might be that (1) OracleDelegate is located in a separate package (org.quartz.impl.jdbcjobstore.oracle vs org.quartz.impl.jdbcjobstore used for other DB types) so we simply overlooked it and (2) there was no Oracle user yet?
@dcdh Would you care to send a pull request?

OracleDelegate is declared inside a subpackage of org.quartz.impl.jdbcjobstore : org.quartz.impl.jdbcjobstore.oracle

Yes, that's what I meant when I said "org.quartz.impl.jdbcjobstore.oracle vs org.quartz.impl.jdbcjobstore"...

Sorry Martin, monday is hard today

mkouba · 2024-10-07T09:08:46Z

I think that it makes sense to introduce the QuarkusOracleDelegate that extends org.quartz.impl.jdbcjobstore.oracle.OracleDelegate. Actually, I'm quite surprised we don't support it. It might be that (1) OracleDelegate is located in a separate package (org.quartz.impl.jdbcjobstore.oracle vs org.quartz.impl.jdbcjobstore used for other DB types) so we simply overlooked it and (2) there was no Oracle user yet?
@dcdh Would you care to send a pull request?

OracleDelegate is declared inside a subpackage of org.quartz.impl.jdbcjobstore : org.quartz.impl.jdbcjobstore.oracle

Yes, that's what I meant when I said "org.quartz.impl.jdbcjobstore.oracle vs org.quartz.impl.jdbcjobstore"...

Sorry Martin, monday is hard today

No problem. It's the same for me! :D

dcdh · 2024-10-07T20:11:15Z

I made the changes, but I do not thinks it is the root cause no matter the use of QuarkusStdJDBCDelegate or QuarkusOracleDelegate.

But, the new way Oracle is handling boolean representation between Oracle23 and version below.

In my reproducer using the default oracle devservice gvenzl/oracle-free:23-slim-faststart will fail for both delegates meanwhile using this version gvenzl/oracle-xe:21-slim-faststart will work on both delegates.

I am unable to find a documentation related on Boolean new type and breaking changes :/

Could you validate it on your side too, to validate what I am saying.

That mind complex ... what should we do ?

dcdh · 2024-10-07T20:22:21Z

Ok I found it from the release note available here https://www.oracle.com/fr/database/technologies/appdev/jdbc-downloads.html for Oracle Database 23ai (23.5.0.24.07) JDBC Driver & UCP Downloads

https://download.oracle.com/otn-pub/otn_software/jdbc/23c/JDBC-UCP-ReleaseNotes-23ai.txt?AuthParam=1728332561_fe1f349d3bea85fc969e271fc9fc098b

- New connection property sendBooleanAsNativeBoolean to restore the old behavior:
  JDBC 23.4 provides a compatibility property "oracle.jdbc.sendBooleanAsNativeBoolean", 
  when set to false (the default is true), will restore the old behavior of sending 
  integer values (0/1) for boolean data type.

The worst part of this sentence is the fact that it will not check the database type used to store the boolean ... breaking change :/

dcdh · 2024-10-07T20:27:54Z

Multiple axes of resolution:

ask for Quartz maintainer to update the script for a specific version of oracle by replacing VARCHAR2(1) to BOOLEAN;
use VARCHAR2(5);
define the property oracle.jdbc.sendBooleanAsNativeBoolean to false to be backward compatible but in this case it should not be possible to use BOOLEAN type on new code (I do not know how Hibernate handle it).

On my side I maintain a legacy infrastructure which is not using Oracle 23c and I guess it should be the case for a lot of organizations.

What should we do ?

mkouba · 2024-10-08T05:56:34Z

What should we do ?

We should definitely file a new issue in the Quartz repo. Unfortunately, the community was not very active until recently. So we will see what happens next.

If I understand it correctly it will work once you change the type of the boolean columns from VARCHAR2(1) to BOOLEAN, right? I'd choose this way.

Also pls send the pull request with the QuarkusOracleDelegate. We should merge it anyway.

dcdh · 2024-10-08T07:21:16Z

@mkouba I confirm that remplacing VARCHAR2(1) to BOOLEAN is working with gvenzl/oracle-free:23-slim-faststart

ok I will raise an issue on Quartz Repo.

I was thinking, meanwhile, what do you think, if we add the specific property to deactivate the feature this way
quarkus.datasource.NAME.jdbc.additional-jdbc-properties."oracle.jdbc.sendBooleanAsNativeBoolean"=false
when Oracle capability is present and Quartz is used on an oracle datasource.
I can do it using the RunTimeConfigurationDefaultBuildItem.

And also add a log and update the guide on Oracle and Quarkus Quartz.

Because, even if we merge the PR, it is a partial fixed. No one will be able to test or run in dev mode with Quartz and Oracle together without specifying a lower version of Oracle.

Or maybe another way: downgrade the version of Oracle used by the dev service. I do not know the legal implication to go from an oracle-free to oracle-xe ?

Please let me know.

mkouba · 2024-10-08T07:37:39Z

@mkouba I confirm that remplacing VARCHAR2(1) to BOOLEAN is working with gvenzl/oracle-free:23-slim-faststart

+1

ok I will raise an issue on Quartz Repo.

+1

I was thinking, meanwhile, what do you think, if we add the specific property to deactivate the feature this way quarkus.datasource.NAME.jdbc.additional-jdbc-properties."oracle.jdbc.sendBooleanAsNativeBoolean"=false when Oracle capability is present and Quartz is used on an oracle datasource. I can do it using the RunTimeConfigurationDefaultBuildItem.

Have you verified that oracle.jdbc.sendBooleanAsNativeBoolean=false does not break things like Hibernate?

I wonder if we shouldn't log a warning and instruct the user to use BOOLEAN instead?

And also add a log and update the guide on Oracle and Quarkus Quartz.

Hm, the guide is postgres-based. So I'm not quite sure where to put this note.

Because, even if we merge the PR, it is a partial fixed. No one will be able to test or run in dev mode with Quartz and Oracle together without specifying a lower version of Oracle.

Or maybe another way: downgrade the version of Oracle used by the dev service. I do not know the legal implication to go from an oracle-free to oracle-xe ?

I have no idea. By the way I've just notice that quarkus-jdbc-oracle still has the "preview" status.

CC @yrodiere

dcdh · 2024-10-08T07:38:01Z

What should we do ?

We should definitely file a new issue in the Quartz repo. Unfortunately, the community was not very active until recently. So we will see what happens next.

If I understand it correctly it will work once you change the type of the boolean columns from VARCHAR2(1) to BOOLEAN, right? I'd choose this way.

Also pls send the pull request with the QuarkusOracleDelegate. We should merge it anyway.

By changing VARCHAR2(1) to BOOLEAN we will breaks all clients (organizations) which are not using Oracle 23 databases (I am on this case).

mkouba · 2024-10-08T08:15:38Z

@dcdh You can use the quarkus.datasource.devservices.image-name config property to specify the image name, or?

dcdh · 2024-10-08T08:20:58Z

It is doable.

Because this property is a build time property, I can raise a ValidationErrorBuildItem if a linked quartz oracle datasource is not using gvenzl/oracle-xe:21-slim-faststart image.
Wrong idea, I should communicate inside my organization to select a compatible image

dcdh · 2024-10-08T08:26:04Z

Waiting for @yrodiere feedbacks

mkouba · 2024-10-08T08:43:00Z

Let me sum up so that we're sure on the same page ;-)

there is a breaking change in Oracle 23+ JDBC driver and oracle.jdbc.sendBooleanAsNativeBoolean=false reverts to the previous behavior
the default image for Oracle devservice is docker.io/gvenzl/oracle-free:23-slim-faststart
the tables_oracle.sql provided by Quartz is not compatible with this change
therefore, if a user attempts to use this script and start a Quarkus app test/dev mode it fails at runtime

Workarounds:

change the type of the boolean columns in the script from VARCHAR2(1) to BOOLEAN
use a different image for the dev service; e.g. quarkus.datasource.devservices.image-name=gvenzl/oracle-xe:21-slim-faststart
set the quarkus.datasource.NAME.jdbc.additional-jdbc-properties."oracle.jdbc.sendBooleanAsNativeBoolean" config property to false

Action items

File an issue in the Quartz repo; [Oracle 23] VARCHAR2(1) breaking change quartz-scheduler/quartz#1161
Help a Quarkus user to fix the problem; i.e. either log some warning or fail the build

dcdh · 2024-10-09T06:36:15Z

@mkouba is it the responsibility to the developer to import by himself the quartz tables ? I guess it is the case, because I did not found any way from Quarkus to create all tables if not present, but I may be wrong - could you confirm please ?

So in this case we can do nothings programmatically to check the column format used for Oracle Boolean representation. Thus, we can only update the Quarkus Oracle guide and put a reference to it from the Quarkus Quartz guide.

What do you think about it ?

mkouba · 2024-10-09T07:32:37Z

@mkouba is it the responsibility to the developer to import by himself the quartz tables ? I guess it is the case, because I did not found any way from Quarkus to create all tables if not present, but I may be wrong - could you confirm please ?

Yes, it is.

So in this case we can do nothings programmatically to check the column format used for Oracle Boolean representation. Thus, we can only update the Quarkus Oracle guide and put a reference to it from the Quarkus Quartz guide.

+1

dcdh · 2024-10-09T07:54:38Z

Ok so I will just update the guide.
I'll do it tonight.

Keep you in touch

yrodiere · 2024-10-09T09:18:05Z

I was thinking, meanwhile, what do you think, if we add the specific property to deactivate the feature this way quarkus.datasource.NAME.jdbc.additional-jdbc-properties."oracle.jdbc.sendBooleanAsNativeBoolean"=false when Oracle capability is present and Quartz is used on an oracle datasource. I can do it using the RunTimeConfigurationDefaultBuildItem.

Have you verified that oracle.jdbc.sendBooleanAsNativeBoolean=false does not break things like Hibernate?

I wonder if we shouldn't log a warning and instruct the user to use BOOLEAN instead?

-1 to change default behavior of JDBC drivers, regardless of how Hibernate ORM behaves. It would just be confusing as documentation on the Oracle JDBC driver would advertise one behavior, and in Quarkus we'd have another one.

+1 to document the breaking change in the Oracle JDBC driver when using Oracle 23+.

FWIW before using BOOLEAN, Hibernate ORM was using BIT for booleans in Oracle, not VARCHAR; the switch from BIT to BOOLEAN was made without an entry in the migration guide, so I suspect you can work with BOOLEAN in the JDBC driver, while still using BIT in your DB schema, and Oracle (on the DB side) will just convert implicitly (1 <=> true, 0 <=> false). Actually I'm sure that's the case, I just checked.
All that to say: people might be able to support both Oracle 23+ and 22- by using number(1, 0) (BIT in JDBC) in their DDL script instead of VARCHAR.

I have no idea. By the way I've just notice that quarkus-jdbc-oracle still has the "preview" status.

See #43462

dcdh added the kind/bug Something isn't working label Oct 5, 2024

quarkus-bot bot added the area/scheduler label Oct 5, 2024

dcdh added a commit to dcdh/quarkus that referenced this issue Oct 7, 2024

Fixes quarkusio#43720 quartz: handle oracle database

86b7b55

dcdh mentioned this issue Oct 8, 2024

[Oracle 23] VARCHAR2(1) breaking change quartz-scheduler/quartz#1161

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

[Quartz][Oracle] Unable to store job details when using Quarkus Quartz Scheduler with Oracle #43720

[Quartz][Oracle] Unable to store job details when using Quarkus Quartz Scheduler with Oracle #43720

dcdh commented Oct 5, 2024 •

edited

Loading

quarkus-bot bot commented Oct 5, 2024

manovotn commented Oct 6, 2024

dcdh commented Oct 7, 2024 •

edited

Loading

mkouba commented Oct 7, 2024

manovotn commented Oct 7, 2024

dcdh commented Oct 7, 2024

dcdh commented Oct 7, 2024

mkouba commented Oct 7, 2024

dcdh commented Oct 7, 2024

mkouba commented Oct 7, 2024

dcdh commented Oct 7, 2024 •

edited

Loading

dcdh commented Oct 7, 2024 •

edited

Loading

dcdh commented Oct 7, 2024 •

edited

Loading

mkouba commented Oct 8, 2024

dcdh commented Oct 8, 2024 •

edited

Loading

mkouba commented Oct 8, 2024

dcdh commented Oct 8, 2024 •

edited

Loading

mkouba commented Oct 8, 2024

dcdh commented Oct 8, 2024 •

edited

Loading

dcdh commented Oct 8, 2024

mkouba commented Oct 8, 2024

dcdh commented Oct 9, 2024

mkouba commented Oct 9, 2024

dcdh commented Oct 9, 2024

yrodiere commented Oct 9, 2024 •

edited

Loading

[Quartz][Oracle] Unable to store job details when using Quarkus Quartz Scheduler with Oracle #43720

[Quartz][Oracle] Unable to store job details when using Quarkus Quartz Scheduler with Oracle #43720

Comments

dcdh commented Oct 5, 2024 • edited Loading

Describe the bug

Expected behavior

Actual behavior

How to Reproduce?

Output of uname -a or ver

Output of java -version

Quarkus version or git rev

Build tool (ie. output of mvnw --version or gradlew --version)

Additional information

quarkus-bot bot commented Oct 5, 2024

manovotn commented Oct 6, 2024

dcdh commented Oct 7, 2024 • edited Loading

mkouba commented Oct 7, 2024

manovotn commented Oct 7, 2024

dcdh commented Oct 7, 2024

dcdh commented Oct 7, 2024

mkouba commented Oct 7, 2024

dcdh commented Oct 7, 2024

mkouba commented Oct 7, 2024

dcdh commented Oct 7, 2024 • edited Loading

dcdh commented Oct 7, 2024 • edited Loading

dcdh commented Oct 7, 2024 • edited Loading

mkouba commented Oct 8, 2024

dcdh commented Oct 8, 2024 • edited Loading

mkouba commented Oct 8, 2024

dcdh commented Oct 8, 2024 • edited Loading

mkouba commented Oct 8, 2024

dcdh commented Oct 8, 2024 • edited Loading

dcdh commented Oct 8, 2024

mkouba commented Oct 8, 2024

dcdh commented Oct 9, 2024

mkouba commented Oct 9, 2024

dcdh commented Oct 9, 2024

yrodiere commented Oct 9, 2024 • edited Loading

dcdh commented Oct 5, 2024 •

edited

Loading

Output of `uname -a` or `ver`

Output of `java -version`

Build tool (ie. output of `mvnw --version` or `gradlew --version`)

dcdh commented Oct 7, 2024 •

edited

Loading

dcdh commented Oct 7, 2024 •

edited

Loading

dcdh commented Oct 7, 2024 •

edited

Loading

dcdh commented Oct 7, 2024 •

edited

Loading

dcdh commented Oct 8, 2024 •

edited

Loading

dcdh commented Oct 8, 2024 •

edited

Loading

dcdh commented Oct 8, 2024 •

edited

Loading

yrodiere commented Oct 9, 2024 •

edited

Loading