Using CockroachDB with Postgres JDBC driver causes failure because of database version

[xgp]

Describe the bug

When using CockroachDB with Postgres JDBC driver, there is a version mismatch in the minimum database version required by the CockroachDialect and the database version reported by the DatabaseMetaData from the driver. This causes a failure at startup when building the Hibernate SessionFactory.

Expected behavior

Hibernate SessionFactory should be created without issues.

Actual behavior

ERROR: [PersistenceUnit: test-default] Unable to build Hibernate SessionFactory
ERROR: Persistence unit 'test-default' was configured to run with a database version of at least '22.2.0', but the actual version is '13.0.0'. Consider upgrading your database. Alternatively, rebuild your application with 'quarkus.datasource.db-version=13.0.0' (but this may disable some features and/or impact performance negatively).

How to Reproduce?

Use this example https://quarkus.io/guides/hibernate-orm with the 3.15.1 version of quarkus, specifying cockroach

quarkus.datasource.db-kind = postgresql
quarkus.datasource.db-version = 22.2 
quarkus.datasource.username = hibernate
quarkus.datasource.password = hibernate
quarkus.datasource.jdbc.url = jdbc:postgresql://localhost:26257/hibernate_db

quarkus.hibernate-orm.dialect=Cockroach 

Output of uname -a or ver

Linux miramar-001 6.8.0-45-generic #45-Ubuntu SMP PREEMPT_DYNAMIC Fri Aug 30 12:02:04 UTC 2024 x86_64 x86_64 x86_64 GNU/Linux

Output of java -version

java version "21.0.3" 2024-04-16 LTS Java(TM) SE Runtime Environment (build 21.0.3+7-LTS-152) Java HotSpot(TM) 64-Bit Server VM (build 21.0.3+7-LTS-152, mixed mode, sharing)

Quarkus version or git rev

3.15.1

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

Maven home: /home/garth/.m2/wrapper/dists/apache-maven-3.9.8/af622e91 Java version: 21.0.3, vendor: Oracle Corporation, runtime: /usr/lib/jvm/jdk-21-oracle-x64 Default locale: en_US, platform encoding: UTF-8 OS name: "linux", version: "6.8.0-45-generic", arch: "amd64", family: "unix"

Additional information

It looks like the issue is that the JDBC driver (or Cockroach) is returning 13.0.0 for the postgres version that it is compatible with, rather than the actual Cockroach version (because a select version() returns the right value).

It seems like this enforcement of minimum versions is new. Is there a workaround? Or a way to disable the enforcement? Or a way to tell it what version the database is?

[quarkus-bot]

/cc @barreiro (jdbc), @yrodiere (jdbc)

[yrodiere]

Hello,

It seems the documentation contradicts itself:

Quarkus currently includes the following built-in database kinds:
DB2: db2
Derby: derby
H2: h2
MariaDB: mariadb
Microsoft SQL Server: mssql
MySQL: mysql
Oracle: oracle
PostgreSQL: postgresql, pgsql or pg
To use a database kind that is not built-in, use other and define the JDBC driver explicitly

So... does this work?

quarkus.datasource.db-kind = other
quarkus.datasource.jdbc.driver = org.postgresql.Driver
quarkus.datasource.db-version = 22.2 
quarkus.datasource.username = hibernate
quarkus.datasource.password = hibernate
quarkus.datasource.jdbc.url = jdbc:postgresql://localhost:26257/hibernate_db

quarkus.hibernate-orm.dialect=Cockroach

If so, we'll need to update the documentation.

If not, and if we want db-kind=pg to work with Cockroach DB, which doesn't report a PostgreSQL version but a CockroachDB version, I think we'll need to address the problem similarly to what I described here: delegate the detection of the version to the dialect itself. That will only be possible on Hibernate ORM 7.0 and above.

[xgp]

@yrodiere Will try with your example and report back.

[xgp]

Same result. For some reason it seems to ignore the db-version

[yrodiere]

Thanks for trying. Then this is what we should do:

If not, and if we want db-kind=pg to work with Cockroach DB, which doesn't report a PostgreSQL version but a CockroachDB version, I think we'll need to address the problem similarly to what I described #42255 (comment): delegate the detection of the version to the dialect itself. That will only be possible on Hibernate ORM 7.0 and above.

In the meantime, I will send a PR to:

1. Allow disabling the database version check.
2. Disable the database version check by default when a dialect is set explicitly, because we know it may not work for some dialects that we don't use in our own test suite.

[yrodiere]

In the meantime, I will send a PR to:

1. Allow disabling the database version check.

2. Disable the database version check by default when a dialect is set explicitly, because we know it may not work for some dialects that we don't use in our own test suite.

Done here: #43762

With that PR, your initial configuration should work without a problem (but the DB version won't be checked):

quarkus.datasource.db-kind = postgresql
quarkus.datasource.db-version = 22.2 
quarkus.datasource.username = hibernate
quarkus.datasource.password = hibernate
quarkus.datasource.jdbc.url = jdbc:postgresql://localhost:26257/hibernate_db

quarkus.hibernate-orm.dialect=Cockroach