Hbm2ddl.auto=update recreating existing indexes after drop

Hibernate ORM
1

Hi,
We are facing problem with the recreation of existing indexes when hbm2ddl.auto is set to ‘update’.
We have tables with millions of data and it is dropping the existing indexes and creating again (which is taking too long to start the server).

Blockquote For example: I can see the following statements while server startup:

Hibernate: alter table order drop index UK_nv4v3d33juoq0x0iwtub87ftw
Hibernate: alter table order add constraint UK_nv4v3d33juoq0x0iwtub87ftw unique (order_id)

We are using Hibernate 5.3.7.Final. In hibernate config file ‘MySQL5Dialect’ dialect being used.

I m not sure if this is an issue? I am referring the link: https://forum.hibernate.org/viewtopic.php?t=928084
Not sure if it is logged anywhere.

Please confirm and if anybody can explain why it is happening.

2

Hi, any news on this?

Another link: https://stackoverflow.com/questions/60138525/hibernate-mysql57dialect-why-is-it-trying-to-drop-indexes-on-update

Of course using hibernate.hbm2ddl.auto=update is not a good idea in production, but I’m creating (flyway-) update-scripts with the help of org.hibernate.tool.hbm2ddl.SchemaUpdate and see the same drop-index-create-index-stuff, although apparently only for unique indexes.

Maybe @vlad has an explanation for this?

Was definitely not the case in Hibernate 4.3.11 (we are in the process of upgrading)

3

@drahkrub Hi. I noticed the mention in your answer. I haven’t been working for Red Hat since March 2019, hence I don’t have the time to answer questions on this forum. You should ping one of the Hibernate ORM core developers. Cheers!

4

@vlad Many thanks for your message! And sorry to hear that - bad for Red Hat for sure and hopefully no problem for you. You’ve done a great work - keep it up!

6

ping @gbadner - do you know the reason for the re-creation of unique indexes?
.
.
.

1 Like
7

Hi. We are having exact same issue with recreating only one (of many) unique index every time we run spring boot app with hibernate.hbm2ddl.auto=update property set.
We are using Hibernate 5.3.10.Final with PostgreSQLDialect.

8

ping @sebersole - do you know the reason for the re-creation of unique indexes? Or does it make more sense to open a jira ticket right away?

9

This behaviour is expected using ddl-auto=update and luckly it’s configurable.

Hibernate contains following key: hibernate.schema_update.unique_constraint_strategy where:

  • DROP_RECREATE_QUIETLY: Default option. Attempt to drop, then (re-)create each unique constraint. Ignore any exceptions being thrown.
  • RECREATE_QUIETLY: Attempts to (re-)create unique constraints, ignoring exceptions thrown if the constraint already existed
  • SKIP: Does not attempt to create unique constraints on a schema update.

In your case, I would recommend to use RECREATE_QUIETLY.

Documentation:
[1] https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#configurations-hbmddl
[2] https://docs.jboss.org/hibernate/orm/5.4/javadocs/org/hibernate/tool/hbm2ddl/UniqueConstraintSchemaUpdateStrategy.html

2 Likes
10

Thanks for the reply @BeardOverflow. Shouldn’t its default option be RECREATE_QUIETLY? As it won’t modify the DB unnecessarily. Not sure why the default value is dropping the unique indexes first and then create. Anyway, Thanks :slightly_smiling_face: it clarifies my question.