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.

[yrodiere]

Also, thanks @beikov :)

[chvsnraju]

Thanks, I can't get SNAPSHOT, will wait for CR1 and report you back once I test.

[chvsnraju]

I just tried with 3.14.0.CR1, I am still having the same issue: not able to start the server without adding quarkus.datasource.db-version=12.0.0, I still see below in logs

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).

[beikov]

Yeah I think you will have to configure the dialect explicitly, because Quarkus does not pass the DatabaseMetadata to the dialect i.e. it disables metadata discovery.

[yrodiere]

Yeah I think you will have to configure the dialect explicitly, because Quarkus does not pass the DatabaseMetadata to the dialect i.e. it disables metadata discovery.

That's not the problem. The warning @chvsnraju is seeing happens at runtime, where metadata discovery is called explicitly by Quarkus:

quarkus/extensions/hibernate-orm/runtime/src/main/java/io/quarkus/hibernate/orm/runtime/service/QuarkusRuntimeInitDialectFactory.java

Lines 46 to 52 in 693bfdb

	public Dialect buildDialect(Map<String, Object> configValues, DialectResolutionInfoSource resolutionInfoSource)
	throws HibernateException {
	if (actualDbVersion.isEmpty()) {
	this.actualDbVersion = retrieveDbVersion(resolutionInfoSource);
	}
	return dialect;
	}

The problem is that this explicit metadata discovery is quite basic, and doesn't go through the improvements you implemented, which are located in the dialect.

We could try to go further and actually have Quarkus instantiate a "disposable" dialect at runtime (which would only be used for the purpose of comparing versions), but:

1. This will require additional native image configuration, as dialects are instantiated using reflection.
2. Depending how it's implemented, that configuration may lead to fatter native images.
3. Dialect instantiation must not have any side effects. In particular I'd not want the dialect to issue SQL queries to set up some of its fields, because those would be useless queries.

With that in mind... I wonder if we wouldn't be better off exposing a method in dialects to resolve the full DatabaseVersion from a DialectResolutionInfo? Quarkus could just call that method on the (already available) dialect instantiated at static init.

[yrodiere]

Yeah I think you will have to configure the dialect explicitly, because Quarkus does not pass the DatabaseMetadata to the dialect i.e. it disables metadata discovery.

That's not the problem. The warning @chvsnraju is seeing happens at runtime, where metadata discovery is called explicitly by Quarkus: [...]

That being said, I agree setting quarkus.hibernate-orm.dialect explicitly to AzureSQLServer should work around the problem. At most you might need to set a high db-version (say 999.999) to avoid the exception, but I think the app should start and work as expected.

[beikov]

It's not just about determining the version, there are other settings that a dialect might need to query to work properly. See SybaseASEDialect, MySQLDialect or OracleDialect.
These settings can also be provided by the user explicitly to avoid the need for SQL statements, just like the version can be configured explicitly. I'd argue that the best way forward simply is to use the AzureSQLServerDialect explicitly or set a high version when running on Azure.

[yrodiere]

It's not just about determining the version, there are other settings that a dialect might need to query to work properly. See SybaseASEDialect, MySQLDialect or OracleDialect.
These settings can also be provided by the user explicitly to avoid the need for SQL statements, just like the version can be configured explicitly

True, but that's an entirely different topic: #13522

I'd argue that the best way forward simply is to use the AzureSQLServerDialect explicitly or set a high version when running on Azure.

That's the best workaround for sure. Not the best way for Quarkus to provide Azure integration, though. Ideally we'd add a dedicated db-kind for Azure SQL Server instead, but I'm not sure how to make that work with dev services (which would most likely use a different version of SQL Server).

Anyway, back to the topic at hand, which is making version detection work correctly in Quarkus: what's your opinion on my suggestion in the previous message?

With that in mind... I wonder if we wouldn't be better off exposing a method in dialects to resolve the full DatabaseVersion from a DialectResolutionInfo? Quarkus could just call that method on the (already available) dialect instantiated at static init.

[yrodiere]

Anyway, back to the topic at hand, which is making version detection work correctly in Quarkus: what's your opinion on my suggestion in the previous message?

With that in mind... I wonder if we wouldn't be better off exposing a method in dialects to resolve the full DatabaseVersion from a DialectResolutionInfo? Quarkus could just call that method on the (already available) dialect instantiated at static init.

Never mind, let's discuss this on the Hibernate ORM Zulip; I created a topic there: https://hibernate.zulipchat.com/#narrow/stream/132094-hibernate-orm-dev/topic/DB.20version.20checking.20*after*.20boot

[yrodiere]

Output of that conversation:

1. We'll try to expose, in Hibernate ORM, ways for Quarkus to easily resolve the DB version: https://hibernate.atlassian.net/browse/HHH-18602
2. Longer term we'll think of more extensive checks that the DB matches configuration: https://hibernate.atlassian.net/browse/HHH-18601

In short, we need https://hibernate.atlassian.net/browse/HHH-18602 to get fixed, and to upgrade to the corresponding ORM version in Quarkus, and to make use of the new determineDatabaseVersion method in Quarkus.

[DavideD]

TL;DR: HHH-1862 has been fixed in Hibernate ORM 7, and there's a draft PR to upgrade Hibernate ORM in Quarkus.

When that's done, a draft PR with the fix for this issue has been already sent.