search

quarkusio / quarkus

Quarkus: Supersonic Subatomic Java.
https://quarkus.io
Apache License 2.0
13.87k stars 2.71k forks source link

Microsoft Azure SQL Server Compatibility with Quarkus 3.12.3 #42255

Open chvsnraju opened 4 months ago

chvsnraju commented 4 months ago

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[bot] commented 4 months ago

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

yrodiere commented 4 months ago

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 commented 3 months ago

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 commented 3 months ago

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 commented 3 months ago

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 commented 3 months ago

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 commented 3 months ago

@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 commented 3 months ago

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 commented 3 months ago

Ok, so I opted for determining the version based on compatibility level instead when possible. See https://github.com/hibernate/hibernate-orm/pull/8763

chvsnraju commented 3 months ago

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

yrodiere commented 3 months ago

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 commented 3 months ago

Also, thanks @beikov :)

chvsnraju commented 3 months ago

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

chvsnraju commented 3 months ago

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 commented 3 months ago

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 commented 3 months ago

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:

https://github.com/quarkusio/quarkus/blob/693bfdb8690dce86e540be510cdd6de1cffb9ed1/extensions/hibernate-orm/runtime/src/main/java/io/quarkus/hibernate/orm/runtime/service/QuarkusRuntimeInitDialectFactory.java#L46-L52

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 commented 3 months ago

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 commented 2 months ago

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 commented 2 months ago

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 commented 2 months ago

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 commented 2 months ago

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.