Schema validation can fail when ENUM or boolean types are used in entities with MySQL.

[pmverma]

Overview of the issue

When Hibernate schema validation is on, the application or tests fail when an enum is used in an entity with MySQL.

Motivation for or Use Case

JHipster generated codes should pass schema validation.

Reproduce the error

1. Create a new project with MySQL
2. Create an entity which contains an ENUM
3. Update src/test/resources/config/application-testdev.yml to use spring.jpa.hibernate.ddl-auto=validate and spring.jpa.properties.hibernate.hbm2ddl.auto=validate. Don't know why two entries for the same thing!
4. Execute IT tests using ./mvnw clean verify
5. You will see an error similar to the following

Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: wrong column type encountered in column [provider] in table [third_party_providers]; found [varchar (Types#VARCHAR)], but expecting [enum ('sample_provider') (Types#ENUM)]

Related issues

Suggest a Fix

Hibernate documentation, 3.11. Enumerated types clearly states that even @Enumerated(STRING) is used, it will still map to ENUM column type for MySQL.

In Hibernate 6, an enum annotated @Enumerated(STRING) is mapped to:

a VARCHAR column type with a CHECK constraint on most databases, or

an ENUM column type on MySQL.

What I have found is to force the use of the String value, column definition needs to be added.

@NotNull
@Enumerated(EnumType.STRING)
@Column(name = "provider", nullable = false, columnDefinition = "VARCHAR(255)") // columnDefinition = "VARCHAR(255)" is added
private ThirdPartyProviders provider;

Then the tests will pass.

JHipster Version(s)

8.1.0

JHipster configuration

Entity configuration(s) entityName.json files generated in the .jhipster directory

Browsers and Operating System

• [x] Checking this box is mandatory (this is just to show you read everything)

[pmverma]

It is surprising to see that it is failing for boolean datatype from User.java as well.

Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: wrong column type encountered in column [activated] in table [jhi_user]; found [tinyint (Types#TINYINT)], but expecting [bit (Types#BOOLEAN)]
	at org.hibernate.tool.schema.internal.AbstractSchemaValidator.validateColumnType(AbstractSchemaValidator.java:165)
	at org.hibernate.tool.schema.internal.AbstractSchemaValidator.validateTable(AbstractSchemaValidator.java:152)
	at org.hibernate.tool.schema.internal.GroupedSchemaValidatorImpl.validateTables(GroupedSchemaValidatorImpl.java:46)
	at org.hibernate.tool.schema.internal.AbstractSchemaValidator.performValidation(AbstractSchemaValidator.java:97)
	at org.hibernate.tool.schema.internal.AbstractSchemaValidator.doValidation(AbstractSchemaValidator.java:75)
	at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:295)
	at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.lambda$process$5(SchemaManagementToolCoordinator.java:145)
	at java.base/java.util.HashMap.forEach(HashMap.java:1429)
	at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:142)
	at org.hibernate.boot.internal.SessionFactoryObserverForSchemaExport.sessionFactoryCreated(SessionFactoryObserverForSchemaExport.java:37)
	at org.hibernate.internal.SessionFactoryObserverChain.sessionFactoryCreated(SessionFactoryObserverChain.java:35)
	at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:315)
	at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:450)
	at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1507)
	at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:75)
	at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:388)
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:409)

After some investigation it found out that it is liquibase generating datatype as TINYINT which is technically correct for MySQL.

--  Changeset config/liquibase/changelog/00000000000000_initial_schema.xml::00000000000001::jhipster
CREATE TABLE jhi_user (id BIGINT AUTO_INCREMENT NOT NULL, login VARCHAR(50) NOT NULL, password_hash VARCHAR(60) NULL, first_name VARCHAR(50) NULL, last_name VARCHAR(50) NULL, email VARCHAR(191) NULL, image_url VARCHAR(256) NULL, activated TINYINT NOT NULL, lang_key VARCHAR(10) NULL, activation_key VARCHAR(20) NULL, reset_key VARCHAR(20) NULL, created_by VARCHAR(50) NOT NULL, created_date timestamp NULL, reset_date timestamp NULL, last_modified_by VARCHAR(50) NULL, last_modified_date timestamp NULL, CONSTRAINT PK_JHI_USER PRIMARY KEY (id), CONSTRAINT ux_user_login UNIQUE (login), CONSTRAINT ux_user_email UNIQUE (email)) AUTO_INCREMENT=1050;

Seems Hibernate schema validation issue.

[pmverma]

For boolean issue, https://hibernate.atlassian.net/browse/HHH-17829

For enum issue, Will fix using column definition if there is no other solution.

[pmverma]

For boolean issue, Hibernate team is clearly in favour of using BIT while starting from Liquibase 4.24.0+ (used in JHipster 8.0.0 and later), it has moved from away from BIT to TINYINT as mentioned in Using Liquibase with MySQL - note at the bottom of the page

Although we let liquibase decide column datatype, this one needs to be resolved from our side IMO. @jhipster/developers Should we keep using BIT like Hibernate or move to TINYINT like Liquibase?

To be clear, - all applications generated before version 8.0.0 are running using BIT - and from 8.0.0 and later generated are using TINYINT

So upgrading the application might break in either case, in theory.

[mshima]

I think that changing the type in Liquibase is easier than in Hibernate. We must be sure that we don't change the changelog for existing applications otherwise we will create a changelog mismatch error. Existing database migration if needed should be handled by the user, we cannot do anything about it.

[Tcharl]

Which is why relying on the liquibase-maven-plugin and the diff between the referenceUrl being the annotated entities is useful!

[pmverma]

Following https://hibernate.atlassian.net/browse/HHH-17829?focusedCommentId=115604 for boolean