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

[dcdh]

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?

1. git clone https://github.com/dcdh/oracle-quartz-reproducer.git
2. run SampleTaskTest
3. 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

[quarkus-bot]

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