Microsoft Azure SQL Server Compatibility with Quarkus 3.12.3

[chvsnraju]

Describe the bug

We are using Azure SQL Server in Azure Cloud, in which Microsoft manages the SQL server, and its versioning differs from that of the regular SQL Server; when I upgraded my project to the latest Quarkus 3.12.3, I saw the following error at startup.

Adding quarkus.datasource.db-version=12.0.0 property works, but the Azure SQL server shouldn't have this issue.

Below is my version when I run this query "Select @@Version"
Microsoft SQL Azure (RTM) - 12.0.2000.8
Jun 19 2024 16:01:48
Copyright (C) 2022 Microsoft Corporation

My DB compatibility level is 160, which is the latest SQL server version.

Startup Error
Caused by: io.quarkus.runtime.configuration.ConfigurationException: Persistence unit '' was configured to run with a database version of at least '13.0.0', but the actual version is '12.0.0'. Consider upgrading your database. Alternatively, rebuild your application with 'quarkus.datasource.db-version=12.0.0' (but this may disable some features and/or impact performance negatively).

Here are some notes from Microsoft

Imp Note : The database engine version numbers for SQL Server and Azure SQL Database are not comparable with each other, and rather are internal build numbers for these separate products. The database engine for Azure SQL Database is based on the same code base as the SQL Server database engine. Most importantly, the database engine in Azure SQL Database always has the newest SQL database engine bits. Version 12 of Azure SQL Database is newer than version 15 of SQL Server.

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver16

Expected behavior

We shouldn't have startup issue when using Azure SQL Database, Quarkus server server should start with adding quarkus.datasource.db-version=12.0.0

Actual behavior

Failing with below error

Persistence unit '' was configured to run with a database version of at least '13.0.0', but the actual version is '12.0.0'. Consider upgrading your database. Alternatively, rebuild your application with 'quarkus.datasource.db-version=12.0.0' (but this may disable some features and/or impact performance negatively).
java.lang.RuntimeException: Failed to start quarkus
at io.quarkus.runner.ApplicationImpl.doStart(Unknown Source)
at io.quarkus.runtime.Application.start(Application.java:101)
at io.quarkus.runtime.ApplicationLifecycleManager.run(ApplicationLifecycleManager.java:111)
at io.quarkus.runtime.Quarkus.run(Quarkus.java:71)
at io.quarkus.runtime.Quarkus.run(Quarkus.java:44)
at io.quarkus.runtime.Quarkus.run(Quarkus.java:124)
at io.quarkus.runner.GeneratedMain.main(Unknown Source)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at io.quarkus.runner.bootstrap.StartupActionImpl$1.run(StartupActionImpl.java:113)
at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: jakarta.persistence.PersistenceException: [PersistenceUnit: ] Unable to build Hibernate SessionFactory
at io.quarkus.hibernate.orm.runtime.boot.FastBootEntityManagerFactoryBuilder.persistenceException(FastBootEntityManagerFactoryBuilder.java:129)
at io.quarkus.hibernate.orm.runtime.boot.FastBootEntityManagerFactoryBuilder.build(FastBootEntityManagerFactoryBuilder.java:89)
at io.quarkus.hibernate.orm.runtime.FastBootHibernatePersistenceProvider.createEntityManagerFactory(FastBootHibernatePersistenceProvider.java:72)
at jakarta.persistence.Persistence.createEntityManagerFactory(Persistence.java:80)
at jakarta.persistence.Persistence.createEntityManagerFactory(Persistence.java:55)
at io.quarkus.hibernate.orm.runtime.JPAConfig$LazyPersistenceUnit.get(JPAConfig.java:154)
at io.quarkus.hibernate.orm.runtime.JPAConfig$1.run(JPAConfig.java:61)
... 1 more
Caused by: io.quarkus.runtime.configuration.ConfigurationException: Persistence unit '' was configured to run with a database version of at least '13.0.0', but the actual version is '12.0.0'. Consider upgrading your database. Alternatively, rebuild your application with 'quarkus.datasource.db-version=12.0.0' (but this may disable some features and/or impact performance negatively).
at io.quarkus.hibernate.orm.runtime.service.QuarkusRuntimeInitDialectFactory.checkActualDbVersion(QuarkusRuntimeInitDialectFactory.java:70)
at io.quarkus.hibernate.orm.runtime.observers.QuarkusSessionFactoryObserverForDbVersionCheck.sessionFactoryCreated(QuarkusSessionFactoryObserverForDbVersionCheck.java:15)
at org.hibernate.internal.SessionFactoryObserverChain.sessionFactoryCreated(SessionFactoryObserverChain.java:35)
at org.hibernate.internal.SessionFactoryImpl.(SessionFactoryImpl.java:322)
at io.quarkus.hibernate.orm.runtime.boot.FastBootEntityManagerFactoryBuilder.build(FastBootEntityManagerFactoryBuilder.java:87)
... 6 more

How to Reproduce?

User Asure SQL with Quarkus 3.12.3

Output of uname -a or ver

No response

Output of java -version

No response

Quarkus version or git rev

No response

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

No response

Additional information

No response

[quarkus-bot]

/cc @DavideD (hibernate-reactive), @gavinking (hibernate-reactive), @gsmet (hibernate-orm), @mswatosh (db2), @yrodiere (hibernate-orm)

[yrodiere]

Hey,

Thanks for reporting.

Imp Note : The database engine version numbers for SQL Server and Azure SQL Database are not comparable with each other, and rather are internal build numbers for these separate products. The database engine for Azure SQL Database is based on the same code base as the SQL Server database engine. Most importantly, the database engine in Azure SQL Database always has the newest SQL database engine bits. Version 12 of Azure SQL Database is newer than version 15 of SQL Server.

Why, of course. Why make it easy...

Seems to me we need a separate ms-sql-azure db-kind and dedicated dialect in Hibernate ORM then... Since versions are completely different.

WDYT @beikov ?

[chvsnraju]

Do you have any thoughts on adding this property and continuing? Does anybody see any issues with this?

quarkus.datasource.db-version=12.0.0

[beikov]

Yeah, we'll need a dedicated AzureDialect or something like that. I created HHH-18463 to track this. @chvsnraju can you please debug into org.hibernate.engine.jdbc.dialect.internal.StandardDialectResolver#resolveDialect or just call java.sql.DatabaseMetaData#getDatabaseProductName directly to determine if there is a difference in reported product name between Azure SQL Server and regular SQL Server, so that we can detect that in Hibernate ORM as well?

[chvsnraju]

I don't have regular SQL server, but here is the data from Azure SQL Server

When I directly query from SQL with Select @@Version, I see below

Microsoft SQL Azure (RTM) - 12.0.2000.8

When I try from Java DatabaseMetaData, I see below.

DatabaseMetaData#getDatabaseProductName() : Microsoft SQL Server
DatabaseMetaData#getDatabaseProductVersion() : 12.00.5624
DatabaseMetaData#getDatabaseMajorVersion() : 12
DatabaseMetaData#getDatabaseMinorVersion() : 0

Not sure what is different but in both places, I connected to the same Azure SQL database

[beikov]

For SQL Server standalone we get:

DatabaseMetaData#getDatabaseProductName() : Microsoft SQL Server
DatabaseMetaData#getDatabaseProductVersion() : 16.00.4115
DatabaseMetaData#getDatabaseMajorVersion() : 16
DatabaseMetaData#getDatabaseMinorVersion() : 0

So it seems we will have to run a SQL statement at the start of the application to determine the flavor. A pity.

[chvsnraju]

@beikov - Thanks for testing on this. I installed my local SQL server, and I saw similar results.

DatabaseMetaData#getDatabaseProductName() : Microsoft SQL Server
DatabaseMetaData#getDatabaseProductVersion() : 16.00.4135
DatabaseMetaData#getDatabaseMajorVersion() : 16
DatabaseMetaData#getDatabaseMinorVersion() : 0

I did some research for Azure server version mismatch, and it seems JDBC DatabaseMetaData#getDatabaseProductVersion() would read the metadata, which may be different than the actual SQL version based on the configuration.

[chvsnraju]

I just queried the SELECT @@Version AS 'Version' with the same Azzure DB connection; here are the results. I think just DatabaseMetaData has different information

SELECT @@Version AS 'Version' : Microsoft SQL Azure (RTM) - 12.0.2000.8
Jul 3 2024 15:47:16
Copyright (C) 2022 Microsoft Corporation

DatabaseMetaData#getDatabaseProductName() : Microsoft SQL Server
DatabaseMetaData#getDatabaseProductVersion() : 12.00.5624
DatabaseMetaData#getDatabaseMajorVersion() : 12
DatabaseMetaData#getDatabaseMinorVersion() : 0

[beikov]

Ok, so I opted for determining the version based on compatibility level instead when possible. See hibernate/hibernate-orm#8763

[chvsnraju]

@beikov - Thanks for looking at this. Our Azure cloud DB shows as compatibility of 160 which is equal to SQL Server 16

[yrodiere]

Okay, so now we've upgraded to ORM 6.6 in Quarkus 3.14.0-SNAPSHOT, I think this might be solved completely by @beikov's patch, even when using SQLServerDialect...

So we might not need to take advantage of AzureSQLDialect -- or at least, not immediately, to fix this. It might still be useful to ensure we always enable all the latest features in the dialect, but that'd be an improvement, and less urgent.

@chvsnraju can you confirm by testing your app against Azure with Quarkus 3.14.0-SNAPSHOT that your problem is gone, especially when you don't set the db-version?
If you can't use a snapshot right now, there should be a 3.14.0.CR1 on August 14th, and then you'll be able to test.