Hibernate hbm2ddl in application.properties is not working as mentioned

[SetoKaiba]

Describe the bug
Hibernate hbm2ddl in application.properties is not working as mentioned

Expected behavior

# drop and create the database at startup (use `update` to only update the schema)
quarkus.hibernate-orm.database.generation=drop-and-create

This is mentioned in the documentation.
It should work with update.

Actual behavior
"update" not working. It would still create the tables.

To Reproduce
Steps to reproduce the behavior:

1. https://github.com/SetoKaiba/code-with-quarkus
2. MySQL 8.0.16
3. Windows OS. Chinese.

Configuration

# Add your application.properties here, if applicable.

Screenshots
(If applicable, add screenshots to help explain your problem.)

Environment (please complete the following information):

• Output of uname -a or ver:
• Output of java -version:
• GraalVM version (if different from Java):
• Quarkus version or git rev:

Additional context
(Add any other context about the problem here.)

[SetoKaiba]

It does jump into migration. But it can't get the table information.

2019-11-30 17:39:01,817 WARN [org.hib.too.sch.int.ExceptionHandlerLoggedImpl] (main) GenerationTarget encountered exception accepting command : Error executing DDL "create table user (userType varchar(31) not null, uuid varchar(255) not null, avatarHash varchar(255), creator bit not null, email varchar(255), hashedPassword varchar(255), name varchar(255), primary key (uuid)) engine=InnoDB" via JDBC Statement: org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table user (userType varchar(31) not null, uuid varchar(255) not null, avatarHash varchar(255), creator bit not null, email varchar(255), hashedPassword varchar(255), name varchar(255), primary key (uuid)) engine=InnoDB" via JDBC Statement
at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlString(AbstractSchemaMigrator.java:559)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlStrings(AbstractSchemaMigrator.java:504)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.createTable(AbstractSchemaMigrator.java:277)
at org.hibernate.tool.schema.internal.GroupedSchemaMigratorImpl.performTablesMigration(GroupedSchemaMigratorImpl.java:71)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:207)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:114)
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:184)
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:73)
at org.hibernate.internal.SessionFactoryImpl.(SessionFactoryImpl.java:320)
at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:462)
at io.quarkus.hibernate.orm.runtime.boot.FastBootEntityManagerFactoryBuilder.build(FastBootEntityManagerFactoryBuilder.java:65)
at io.quarkus.hibernate.orm.runtime.FastBootHibernatePersistenceProvider.createEntityManagerFactory(FastBootHibernatePersistenceProvider.java:54)
at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:80)
at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:55)
at io.quarkus.hibernate.orm.runtime.JPAConfig$LazyPersistenceUnit.get(JPAConfig.java:109)
at io.quarkus.hibernate.orm.runtime.JPAConfig.startAll(JPAConfig.java:57)
at io.quarkus.hibernate.orm.runtime.HibernateOrmRecorder.startAllPersistenceUnits(HibernateOrmRecorder.java:82)
at io.quarkus.deployment.steps.HibernateOrmProcessor$startPersistenceUnits27.deploy_0(HibernateOrmProcessor$startPersistenceUnits27.zig:70)
at io.quarkus.deployment.steps.HibernateOrmProcessor$startPersistenceUnits27.deploy(HibernateOrmProcessor$startPersistenceUnits27.zig:36)
at io.quarkus.runner.ApplicationImpl.doStart(ApplicationImpl.zig:142)
at io.quarkus.runtime.Application.start(Application.java:94)
at io.quarkus.runner.RuntimeRunner.run(RuntimeRunner.java:143)
at io.quarkus.dev.DevModeMain.doStart(DevModeMain.java:177)
at io.quarkus.dev.DevModeMain.start(DevModeMain.java:95)
at io.quarkus.dev.DevModeMain.main(DevModeMain.java:66)
Caused by: java.sql.SQLSyntaxErrorException: Table 'user' already exists
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
at io.agroal.pool.wrapper.StatementWrapper.execute(StatementWrapper.java:232)
at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54)
... 25 more

[SetoKaiba]

Is there any update for it?

[SetoKaiba]

Any update on this? I need update ddl feature to continue my projects migration.

[gsmet]

So... I agree there's something fishy here.

What I did to reproduce the issue.

Launch a MySQL database with:

sudo docker run --ulimit memlock=-1:-1 -it --rm=true --memory-swappiness=0 --name quarkus_test_mysql -e MYSQL_USER=hibernate_orm_test -e MYSQL_PASSWORD=hibernate_orm_test -e MYSQL_DATABASE=hibernate_orm_test -e MYSQL_RANDOM_ROOT_PASSWORD=true -p 3306:3306 mysql:8.0.17

Update the application.properties to look like:

# Configuration file
# key = value

# datasource configuration
quarkus.datasource.url=jdbc:mysql://localhost:3306/hibernate_orm_test?serverTimezone=UTC
quarkus.datasource.driver=com.mysql.cj.jdbc.Driver
quarkus.datasource.username=hibernate_orm_test
quarkus.datasource.password=hibernate_orm_test

# drop and create the database at startup (use `update` to only update the schema)
quarkus.hibernate-orm.database.generation=update

Then (once ./gradlew has been made executable):

./gradlew clean quarkusBuild

Then run java -jar build/code-with-quarkus-1.0.0-SNAPSHOT-runner.jar several times.

From what I can see, the map here https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/main/java/org/hibernate/tool/schema/internal/GroupedSchemaMigratorImpl.java#L65 is empty so the user table is created again and again.

It looks like an ORM issue to me. @dreab8 could you have a look?

[gsmet]

/cc @Sanne

[gsmet]

Andrea confirmed it's an Hibernate ORM issue due to the usage of hibernate.temp.use_jdbc_metadata_defaults=false in Quarkus.

He's going to work on it.

[dreab8]

a temporary solution is to use quarkus.hibernate-orm.database.default-catalog to specify the database name (catalog)

[SetoKaiba]

The temp solution is working. Thank you so much. It keeps me on Quarkus for my project now.

[gsmet]

@SetoKaiba thanks for insisting and sorry about the delay!

[dreab8]

I have opened https://hibernate.atlassian.net/browse/HHH-13788

[Sanne]

Hi @SetoKaiba , I'm trying to verify @dreab8 's fix, but couldn't find the reproducer at https://github.com/SetoKaiba/code-with-quarkus . Was it moved maybe?

[SetoKaiba]

@Sanne Because I think the issue is aware. So I deleted the reproducer. I'll create one again.

[SetoKaiba]

Here I uploaded a reproducer again.

[Sanne]

An update from the Hibernate team: we decided the previous fix attempt is insufficient; having all such jdbc metadata is actually important.

Since Quarkus can't (typically) connect to the database during the build phase, we will need to provide configuration properties for each such metadata entry; that's not particularly user friendly, so we'll also want to have a reasonable default for each of these attributes - the best value for each depends on the DB vendor though, so we'll want to have the metadata defaults depend on the Dialect / Driver choice.

[SetoKaiba]

Hi. What's the update for this?

[Sanne]

Hi @SetoKaiba ,
I'm afraid we have not worked on this yet. We'll need to adapt a significant section of Hibernate ORM's bootstrap to be able to configure these correctly without accessing the database; unfortunately we had to prioritize other fixes first.

[Sanne]

This is fixed in hibernate/hibernate-orm#3151 , but we'll close the Quarkus issue when we upgrade the ORM component to include the fix.