search

quarkusio / quarkus

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

UUID and import.sql not working in Quarkus 3.11 with MariaDB 10.11 #40909

Closed tmulle closed 3 months ago

tmulle commented 3 months ago

Describe the bug

According to all the documentation I can find with Quarkus and Hibernate 6, the UUID type should be supported for MariaDB 10.11.

Yet, when I attempt to use the import.sql file to add some test data, startup fails with an error.

It looks like the Hibernate table generator is still using the BINARY(16) column type instead of the UUID type available since MariaDB 10.7.

Links: https://mariadb.com/kb/en/uuid/ https://www.baeldung.com/java-hibernate-uuid-primary-key

Sample project, very simple one table and import.sql

https://github.com/tmulle/quarkus-mariadb-uuidtest

I've tried with and without the @GeneratedValue(strategy = GenerationType.UUID) on the id field and still same thing. The table gets generated as a BINARY(16) in mariadb 10.11 (Dev Services).

This is the output:

[INFO] Scanning for projects...
[INFO] 
[INFO] ---------------------< org.acme:mariadb-uuid-test >---------------------
[INFO] Building mariadb-uuid-test 1.0.0-SNAPSHOT
[INFO]   from pom.xml
[INFO] --------------------------------[ jar ]---------------------------------
[INFO] 
[INFO] --- quarkus:3.11.0:dev (default-cli) @ mariadb-uuid-test ---
[INFO] Invoking resources:3.3.1:resources (default-resources) @ mariadb-uuid-test
[INFO] Copying 2 resources from src/main/resources to target/classes
[INFO] Invoking quarkus:3.11.0:generate-code (default) @ mariadb-uuid-test
[INFO] Invoking compiler:3.12.1:compile (default-compile) @ mariadb-uuid-test
[INFO] Nothing to compile - all classes are up to date.
[INFO] Invoking resources:3.3.1:testResources (default-testResources) @ mariadb-uuid-test
[INFO] skip non existing resourceDirectory /home/tmulle/Downloads/mariadb-uuid-test/src/test/resources
[INFO] Invoking quarkus:3.11.0:generate-code-tests (default) @ mariadb-uuid-test
[INFO] Invoking compiler:3.12.1:testCompile (default-testCompile) @ mariadb-uuid-test
[INFO] No sources to compile
[WARNING] Changed debug port to 40841 because of a port conflict
Listening for transport dt_socket at address: 40841

2024-05-30 15:48:36,472 INFO  [io.qua.dat.dep.dev.DevServicesDatasourceProcessor] (build-4) Dev Services for default datasource (mariadb) started - container ID is e360378173a7
__  ____  __  _____   ___  __ ____  ______ 
 --/ __ \/ / / / _ | / _ \/ //_/ / / / __/ 
 -/ /_/ / /_/ / __ |/ , _/ ,< / /_/ /\ \   
--\___\_\____/_/ |_/_/|_/_/|_|\____/___/   
2024-05-30 15:48:37,364 WARN  [org.mar.jdb.mes.ser.ErrorPacket] (JPA Startup Thread) Error: 1406-22001: Data too long for column 'id' at row 1

2024-05-30 15:48:37,365 WARN  [org.hib.too.sch.int.ExceptionHandlerLoggedImpl] (JPA Startup Thread) GenerationTarget encountered exception accepting command : Error executing DDL "insert into MyEntity (id, entity_name) values (UUID(), 'FooBAR')" via JDBC [(conn=4) Data too long for column 'id' at row 1]: org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "insert into MyEntity (id, entity_name) values (UUID(), 'FooBAR')" via JDBC [(conn=4) Data too long for column 'id' at row 1]
        at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:94)
        at org.hibernate.tool.schema.internal.Helper.applySqlString(Helper.java:233)
        at org.hibernate.tool.schema.internal.Helper.applyScript(Helper.java:255)
        at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applyImportFiles(SchemaCreatorImpl.java:676)
        at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applyImportSources(SchemaCreatorImpl.java:595)
        at org.hibernate.tool.schema.internal.SchemaCreatorImpl.performCreation(SchemaCreatorImpl.java:184)
        at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:142)
        at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:118)
        at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:250)
        at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.lambda$process$5(SchemaManagementToolCoordinator.java:144)
        at java.base/java.util.HashMap.forEach(HashMap.java:1429)
        at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:141)
        at io.quarkus.hibernate.orm.runtime.observers.SessionFactoryObserverForSchemaExport.sessionFactoryCreated(SessionFactoryObserverForSchemaExport.java:21)
        at org.hibernate.internal.SessionFactoryObserverChain.sessionFactoryCreated(SessionFactoryObserverChain.java:35)
        at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:322)
        at io.quarkus.hibernate.orm.runtime.boot.FastBootEntityManagerFactoryBuilder.build(FastBootEntityManagerFactoryBuilder.java:87)
        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)
        at java.base/java.lang.Thread.run(Thread.java:1583)
Caused by: java.sql.SQLSyntaxErrorException: (conn=4) Data too long for column 'id' at row 1
        at org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:289)
        at org.mariadb.jdbc.export.ExceptionFactory.create(ExceptionFactory.java:378)
        at org.mariadb.jdbc.message.ClientMessage.readPacket(ClientMessage.java:172)
        at org.mariadb.jdbc.client.impl.StandardClient.readPacket(StandardClient.java:915)
        at org.mariadb.jdbc.client.impl.StandardClient.readResults(StandardClient.java:854)
        at org.mariadb.jdbc.client.impl.StandardClient.readResponse(StandardClient.java:773)
        at org.mariadb.jdbc.client.impl.StandardClient.execute(StandardClient.java:697)
        at org.mariadb.jdbc.Statement.executeInternal(Statement.java:1035)
        at org.mariadb.jdbc.Statement.execute(Statement.java:1165)
        at org.mariadb.jdbc.Statement.execute(Statement.java:493)
        at io.agroal.pool.wrapper.StatementWrapper.execute(StatementWrapper.java:220)
        at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:80)
        ... 21 more

2024-05-30 15:48:37,369 INFO  [io.quarkus] (Quarkus Main Thread) mariadb-uuid-test 1.0.0-SNAPSHOT on JVM (powered by Quarkus 3.11.0) started in 7.710s. 
2024-05-30 15:48:37,370 INFO  [io.quarkus] (Quarkus Main Thread) Profile dev activated. Live Coding activated.
2024-05-30 15:48:37,370 INFO  [io.quarkus] (Quarkus Main Thread) Installed features: [agroal, cdi, hibernate-orm, hibernate-orm-panache, jdbc-mariadb, narayana-jta, smallrye-context-propagation]

Expected behavior

I would think the generator would use the UUID type instead of the old BINARY(16) type since it is now using Hibernate 6.x?

Actual behavior

No response

How to Reproduce?

No response

Output of uname -a or ver

No response

Output of java -version

JDK 17

Quarkus version or git rev

3.11

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

maven

Additional information

No response

quarkus-bot[bot] commented 3 months ago

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

DavideD commented 3 months ago

I will give it a look

DavideD commented 3 months ago

I don't know why, but Hibernate ORM reads the wrong database version when picking the dialect. @tmulle, as a workaround, you can set the following property in the application.properties:

quarkus.datasource.db-version=10.11

@yrodiere, any idea?

tmulle commented 3 months ago

@DavideD thanks that workaround does work. I will leave that in-place until an official fix is ready.

yrodiere commented 3 months ago

I don't know why, but Hibernate ORM reads the wrong database version when picking the dialect.

What do you mean, "reads the wrong database version"? There is nothing to read, the dialect is picked offline, at build time.

Generally when db-version is not set, we pick Hibernate ORM's earliest supported version; in the case of MariaDB, it's 10.4, though for history reasons, Quarkus will default to 10.6 instead.

@tmulle, as a workaround, you can set the following property in the application.properties:

quarkus.datasource.db-version=10.11

This is not a workaround, this is the proper solution to this problem.

See https://quarkus.io/guides/hibernate-orm#hibernate-dialect-supported-databases:

By default, the dialect is configured to target the minimum supported version of the database. In order for Hibernate ORM to generate more efficient SQL, to avoid workarounds and to take advantage of more database features, you can set the database version explicitly:

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

As a rule, the version set here should be as high as possible, but must be lower than or equal to the version of any database your application will connect to.

Closing this issue, as this is not a bug: you really should configure your DB version if you expect features beyond what the oldest supported version of your database can offer.

DavideD commented 3 months ago

What do you mean, "reads the wrong database version"? There is nothing to read, the dialect is picked offline, at build time.

Ok, I get it and I didn't read the whole documentation, so I didn't know this was the expected behaviour.

But it's different than what happens with regular Hibernate ORM. So, unless somebody has read the documentation in detail, there's nothing that indicates that this is the correct behaviour. Not to mention that somebody might decide to switch database and not realizing that the new one is not supported.

It would be nice to have a message at start up containing the selected dialect with the version and a note that it's not supported so the default version will be used instead.

DavideD commented 3 months ago

It would be nice to have a message at start up containing the selected dialect with the version and a note that it's not supported so the default version will be used instead.

I guess I'm not the only one that thinks this is important info to have: https://github.com/quarkusio/quarkus/issues/40922

yrodiere commented 3 months ago

It would be nice to have a message at start up containing the selected dialect with the version and a note that it's not supported so the default version will be used instead.

The default version is used for supported dialects too.

DavideD commented 3 months ago

This is not what the documentation says, though:

For supported databases, the Hibernate ORM dialect does not need to be set explicitly: it is selected automatically based on the datasource.

DavideD commented 3 months ago

Ok... I guess the dialect is selected but the correct version isn't?

Am I the only one that thinks a bit more clarity would be nice?

yrodiere commented 3 months ago

Ok... I guess the dialect is selected but the correct version isn't?

Yes.

Am I the only one that thinks a bit more clarity would be nice?

You're the first to report this particular problem, yes.

Feel free to send a PR if you have an idea how to improve the wording.