SchemaMigrator wants to alter my columns

Hibernate ORM
1

Hi Folks,

I’m converting our JPA project to Hibernate, and I’m using the SchemaMigrator tool to create a database update script based on our current entities. It works well, but version 6.2.3 gives me different output than version 5.6.15, suggesting changes in columns that aren’t necessary or desired.

alter table distributor_invoice modify column totalAmount float(53);
alter table distributor_invoice modify column resolvedTime  datetime(6);
alter table distributor_invoice modify column invoiceStatus  enum ('DUPLICATE','FINALIZING','INVALID','ON_HOLD','VALID');

The database is MySQL 5.7.

For the first line, the entity has a ‘double’ field, and the database has a double ‘column’. float(53) may be another way to express double, but I don’t want that to appear in the update script.

For the second line, the entity has a java.util.Date field, and the database has a ‘datetime’ column. I would prefer to keep the type as datetime.

For the third line, the entity has an ‘enum’ field and the database has a ‘varchar(20)’ column. I would prefer to keep that column type.

Is there a way to configure the type-mapping in the SchemaMigrator, or could this be a bug?

2

I think you might be affected by [HHH-16360] - Hibernate JIRA which is already fixed on main but not yet backported to 6.2. Maybe you can give 6.3.0-SNAPSHOT a try and let us know if that fixes the issue for you?
Using that version requires that you use the http://oss.sonatype.org/content/repositories/snapshots/ maven repository.

3

Version 6.3.0-SNAPSHOT fixed two issues. It no longer turns existing ‘datetime’ columns into ‘datetime(6)’, and it no longer turns existing ‘double’ columns into ‘float(53)’.

But it continues to turn ‘varchar’ columns into ‘enum’ columns for java enums. And it continues to create new columns under a different type than I expect - (maybe that part is configurable?)

Version 6.2.3.Final

alter table distributor_invoice modify column invoiceStatus  enum ('DUPLICATE','FINALIZING','INVALID','ON_HOLD','VALID');
alter table distributor_invoice modify column resolvedTime  datetime(6);
alter table distributor_invoice modify column totalAmount  float(53);
alter table distributor_invoice add column newDateColumn datetime(6);
alter table distributor_invoice add column newDoubleColumn float(53);
alter table distributor_invoice add column newEnumColumn enum ('DUPLICATE','FINALIZING','INVALID','ON_HOLD','VALID');

Version 6.3.0-SNAPSHOT

alter table distributor_invoice modify column invoiceStatus enum ('DUPLICATE','FINALIZING','INVALID','ON_HOLD','VALID');
alter table distributor_invoice add column newDateColumn datetime(6);
alter table distributor_invoice add column newDoubleColumn float(53);
alter table distributor_invoice add column newEnumColumn enum ('DUPLICATE','FINALIZING','INVALID','ON_HOLD','VALID');

Version 5.6.15.Final

alter table distributor_invoice add column newDateColumn datetime(6);
alter table distributor_invoice add column newDoubleColumn double precision;
alter table distributor_invoice add column newEnumColumn varchar(255);
4

But it continues to turn ‘varchar’ columns into ‘enum’ columns for java enums. And it continues to create new columns under a different type than I expect - (maybe that part is configurable?)

That’s expected. If you use an enum in your domain model, then Hibernate will try to make use of the MySQL enum type if you use schema migration.

5

Hello,
Migrating from version 6.1 to 6.2, I’m facing some problems very similar to this topic.

The migrator tries to alter columns annotated with JPA annotation @Lob, but It seems to not be allowed when using an Oracle database (Oracle 19c currently). Then I run into a lot of errors ORA-22859 like those :

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL “alter table Message modify textMess clob” via JDBC [ORA-22859: invalid modification of columns
org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL “alter table Person modify photo blob” via JDBC [ORA-22859: invalid modification of columns

Then, I would like to know if it exists a workaround to bypass these alter tables ?

6

That’s interesting. One issue I see with the DB’s enum type is that the values in the alter statement are ordered alphabetically, instead of by their java ordinal. But I think I will avoid the DB’s enum type to maintain flexibility. It looks like this annotation will do the trick:

    @Enumerated(EnumType.STRING)
    @Column(columnDefinition = "varchar(20)")
    private InvoiceStatus invoiceStatus
7

What was the type of the columns before? If it wasn’t clob/blob, then you should not use the @Lob annotation. The @Lob annotation is specifically for these special data types in the database that allow chunk-wise data processing outside of a regular query.

8

That’s interesting. One issue I see with the DB’s enum type is that the values in the alter statement are ordered alphabetically, instead of by their java ordinal. But I think I will avoid the DB’s enum type to maintain flexibility. It looks like this annotation will do the trick:

Defining a custom column definition can be used to avoid this, but why would you not want to use the features of your database? By using an enum the DB can figure out statistics easier.

The alphabetical ordering was brought up before and might be fixed. Try searching for a JIRA issue for this, and if you can’t find one, please consider creating a bug report.

9

Yes these columns are annotated with @Lob and of type blob and clob for a long time now. The two examples in my previous post are very simple and looks like this :

@NotNull
@Lob
@Size(min = 0, max = 255)
public String getTextMess() {
	return textMess;
}

@Lob 
public byte[] getPhoto() {
	return photo;
}

I’m not sure about the relevance of the first @Lob on String but it’s relatively old code.

10

I’m not sure what you mean about statistics. My reluctance is because we add values to enums from time to time, which would become more difficult, and also we have some ETLs to other databases like redshift that handle enum columns differently.

11

Yes these columns are annotated with @Lob and of type blob and clob for a long time now. The two examples in my previous post are very simple and looks like this

How does your schema look like currently? If your schema doesn’t use clob/blob then remove the @Lob annotations.

12

I’m not sure what you mean about statistics.

I assume that a database can offer better statistics (e.g. selectivity of a filter) if all possible values are known and hence choose better query plans. Not sure if MySQL does that, but why else would they add support for enum types?

My reluctance is because we add values to enums from time to time, which would become more difficult, and also we have some ETLs to other databases like redshift that handle enum columns differently.

Adding enum values shouldn’t be an issue, schema migration will take care of it. The problem is only when you remove/rename them, but you will have to do data migration then anyway.

It’s up to you. Use the columnDefinition if you don’t like the enum types.

13

Defining a custom column definition can be used to avoid this, but why would you not want to use the features of your database? By using an enum the DB can figure out statistics easier.

Afaict that’s currently not possible: [HHH-16670] - Hibernate JIRA

The alphabetical ordering was brought up before and might be fixed. Try searching for a JIRA issue for this, and if you can’t find one, please consider creating a bug report.

I created an issue for this last week: [HHH-16694] - Hibernate JIRA I didn’t find any previous discussion about this behaviour.

1 Like
14

Currently the schema contains the clob/blob columns. If I try to remove the @Lob annotations on these columns to change the column type, It results in the same Oracle error ORA-22859 looking like this :

 org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "alter table Message modify textMess varchar2(255 char)" via JDBC [ORA-22859: invalid modification of columns
 org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "alter table Person modify photo raw(255)" via JDBC [ORA-22859: invalid modification of columns

I think Oracle definitly don’t want to alter columns of type clob/blob.

15

Currently my schema contains the columns of type clob/blob. If I try to remove the @Lob annotation to change the type of these columns, It runs into the same Oracle error ORA-22859.

 org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "alter table Message modify textMess varchar2(255 char)" via JDBC [ORA-22859: invalid modification of columns
 org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "alter table Person modify photo raw(255)" via JDBC [ORA-22859: invalid modification of columns

I think Oracle definitely doesn’t want to alter columns currently typed as clob/blob.

16

Currently the schema contains the clob/blob columns.

Ok, then it’s fine to use the @Lob annotation.

Please create an issue in the issue tracker(https://hibernate.atlassian.net) with a test case(https://github.com/hibernate/hibernate-test-case-templates/blob/master/orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java) that reproduces the issue.

17

Now I have a problem with the schema tool wanting to generate an incorrect foreign key constraint.

I have a set of polymorphic classes using the InheritanceType.JOINED strategy with ComputerSystem as the base class. These constraints are produced:

alter table customer_computer_system      add constraint FK... foreign key (id) references computer_system (id);
alter table computer_system               add constraint FK... foreign key (id) references customer_computer_system (id);
alter table data_provider_computer_system add constraint FK... foreign key (id) references computer_system (id);
alter table distributor_computer_system   add constraint FK... foreign key (id) references computer_system (id);
alter table manufacturer_computer_system  add constraint FK... foreign key (id) references computer_system (id);

The second one does not belong, because not all computer systems are customer_computer_systems.

I searched for similar problems but didn’t find any. Does this problem sound familiar? I’m working to produce a small test case with no luck so far.

18

Please create a new topic and also share the entity model for that.

19

Hi,
I’ve published a Jira ticket that contains the test project to reproduce the issue.
If you’d like to take a look it’s available here : [HHH-16762] - Hibernate JIRA

1 Like
20

6.2.5.Final using ddl-auto=update, gives me alot of alter tables on every start and none entity was altered between starts
when using ddl-auto=validade it pass on validation but with ddl-auto=update it keep changing tables on every start
6.3.0-SNAPSHOT fixes all my alter table problems