Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Open
1 task done
pmverma opened this issue Mar 6, 2024 · 6 comments · May be fixed by #26759
Open
1 task done

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

pmverma opened this issue Mar 6, 2024 · 6 comments · May be fixed by #26759
Assignees
@pmverma
Labels
area: bug 🐛 theme: database theme: java theme: liquibase

Comments

@pmverma
Copy link
Member

pmverma commented Mar 6, 2024

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
  • Checking this box is mandatory (this is just to show you read everything)
@pmverma pmverma self-assigned this Mar 9, 2024
@pmverma pmverma changed the title Schema validation will fail when ENUM is used with MySQL Schema validation can fail when ENUM or boolean types are used in entities with MySQL. Mar 9, 2024
@pmverma
Copy link
Member Author

pmverma commented Mar 9, 2024 •
edited
Loading

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
Copy link
Member Author

pmverma commented Mar 9, 2024

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
Copy link
Member Author

pmverma commented Mar 11, 2024

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
Copy link
Member

mshima commented Mar 14, 2024

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
Copy link
Contributor

Tcharl commented May 8, 2024

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

@pmverma pmverma linked a pull request Jul 18, 2024 that will close this issue
Open
6 tasks
@pmverma
Copy link
Member Author

pmverma commented Jul 18, 2024

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

@pmverma pmverma mentioned this issue Jul 18, 2024
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@pmverma pmverma

Labels
area: bug 🐛 theme: database theme: java theme: liquibase
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Fix failing schema validation for mysql when boolean/enum is used pmverma/generator-jhipster
3 participants
@Tcharl @mshima @pmverma