In my Spring Boot 2.6.4 application (using Hibernate 5.6.5), I need to create a custom function-based MySQL 8 unique index to fulfil a database constraint. Since JPA doesn’t support defining function-based indexes, I have to create that index manually. But as soon as I create the index, the Hibernate schema migrator fails at startup, since it doesn’t expect an index to have index columns which have NULL names. For function-based indexes, MySQL returns the index metadata as using NULL as the column name if an expression is used, because then the expression is stored and returned separately. In my case, the index consists of two parts, one table column and one expression, so Hibernate sees the table column as the first column name and NULL as the second column name.
I create the index like this:
create unique index custom_nonpublished ON articles (account_id,(case when status='PUBLISHED' then null else 'x' end));
(the idea here is to enforce on database level that an account may have any number of articles in status PUBLISHED but at most one in another status)
In the MySQL client, “show keys from articles;” shows this for the index:
+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+---------------------------------------------------------------------------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+---------------------------------------------------------------------------------+
| articles | 0 | custom_nonpublished | 1 | account_id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| articles | 0 | custom_nonpublished | 2 | NULL | A | 0 | NULL | NULL | YES | BTREE | | | YES | (case when (`status` = _utf8mb4\'PUBLISHED\') then NULL else _utf8mb4\'x\' end) |
+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+---------------------------------------------------------------------------------+
How can I make Hibernate ignore my index when doing the schema migration? At first glance, I did not find a suitable way to patch anything (like InformationExtractorJdbcDatabaseMetaDataImpl or DatabaseMetaDataUsingInfoSchema) to ignore my index. Also, SchemaFilter does not support filtering out indexes.
Any hints are welcome.
The exception I get at startup is this:
Caused by: java.lang.IllegalArgumentException: null was passed as an object name
at org.hibernate.engine.jdbc.env.internal.NormalizingIdentifierHelperImpl.toMetaDataObjectName(NormalizingIdentifierHelperImpl.java:196) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
at org.hibernate.tool.schema.extract.internal.TableInformationImpl.getColumn(TableInformationImpl.java:75) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
at org.hibernate.tool.schema.extract.internal.AbstractInformationExtractorImpl.lambda$getIndexes$8(AbstractInformationExtractorImpl.java:1149) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.processIndexInfoResultSet(InformationExtractorJdbcDatabaseMetaDataImpl.java:118) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
at org.hibernate.tool.schema.extract.internal.AbstractInformationExtractorImpl.getIndexes(AbstractInformationExtractorImpl.java:1125) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
at org.hibernate.tool.schema.extract.internal.TableInformationImpl.indexes(TableInformationImpl.java:122) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
at org.hibernate.tool.schema.extract.internal.TableInformationImpl.getIndex(TableInformationImpl.java:138) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.findMatchingIndex(AbstractSchemaMigrator.java:361) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applyIndexes(AbstractSchemaMigrator.java:345) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
at org.hibernate.tool.schema.internal.GroupedSchemaMigratorImpl.performTablesMigration(GroupedSchemaMigratorImpl.java:88) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:220) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:123) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:192) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:81) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:335) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:471) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1498) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58) ~[spring-orm-5.3.16.jar:5.3.16]
at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365) ~[spring-orm-5.3.16.jar:5.3.16]
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:409) ~[spring-orm-5.3.16.jar:5.3.16]
... 128 common frames omitted