How can I make Hibernate ignore my custom index?

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

Looks like a bug. You should report that in the issue tracker and if it is important to you to get a fix soon, provide a PR with a fix for this.

I have created a bug in the issue tracker: [HHH-15123] - Hibernate JIRA

Before this is fixed in Hibernate itself, I would be interested to know if I have any means of customizing/configuring Hibernate to help me out.

You could register a custom org.hibernate.tool.schema.spi.SchemaManagementTool in a org.hibernate.service.spi.ServiceContributor which essentially allows you to change the InformationExtractor and skip these indexes.

OK, I will give this a try.

I’m trying to extend HibernateSchemaManagementTool, but I have trouble with the dependency injection. The method injectServices() is not called.

I’m using a ServiceContributor, which is registered in the file META-INF/services/org.hibernate.service.spi.ServiceContributor. My ServiceContributor is called and I register the service, like this:

    @Override
    public void contribute(StandardServiceRegistryBuilder serviceRegistryBuilder) {
        serviceRegistryBuilder.addService(SchemaManagementTool.class, new MySchemaManagementTool());
    }

This fails later since injectServices() is not called and therefore the parent’s serviceRegistry stays null. The parent implements ServiceRegistryAwareService. I can see that my child class is instantiated.

What am I missing? Why is injectServices() on the parent not called? I can see that it is called when my ServiceContributor is not used. According to the integration documentation, it should be enough to to implement the contribute() method to add the service, and it should then be configured automatically by calling injectServices() if it implements ServiceRegistryAwareService.

With the default implementation, I see

Initializing service [role=org.hibernate.tool.schema.spi.SchemaManagementTool]

and this is where injectServices() is called.

With my implementation, no such line is logged.

I’ve got it working now. I need to write my own version of a StandardServiceInitiator and need to use

serviceRegistryBuilder.addInitiator(MySchemaManagementTool.INSTANCE);

Maybe the integration guide should be updated accordingly.

I’ve patched getIndexes() to ignore indexes with NULL columns (changes are commented with “ADDED:”):

        @Override
        // copied from AbstractInformationExtractorImpl.getIndexes()
        public Iterable<IndexInformation> getIndexes(TableInformation tableInformation) {
            final Map<Identifier, IndexInformationImpl.Builder> builders = new HashMap<>();
            final QualifiedTableName tableName = tableInformation.getName();
            final Identifier catalog = tableName.getCatalogName();
            final Identifier schema = tableName.getSchemaName();

            final String catalogFilter;
            final String schemaFilter;

            if (catalog == null) {
                catalogFilter = "";
            } else {
                catalogFilter = catalog.getText();
            }

            if (schema == null) {
                schemaFilter = "";
            } else {
                schemaFilter = schema.getText();
            }

            try {
                processIndexInfoResultSet(catalogFilter, schemaFilter, tableName.getTableName().getText(), false, // DO NOT limit to just unique
                        true, // DO require up-to-date results
                        resultSet -> {
                            // ADDED: set
                            Set<Identifier> skipped = new HashSet<>();

                            while (resultSet.next()) {
                                if (resultSet.getShort(getResultSetIndexTypeLabel()) == DatabaseMetaData.tableIndexStatistic) {
                                    continue;
                                }

                                final Identifier indexIdentifier = DatabaseIdentifier.toIdentifier(resultSet.getString(getResultSetIndexNameLabel()));

                                // ADDED: if block
                                if (skipped.contains(indexIdentifier)) {
                                    continue;
                                }

                                IndexInformationImpl.Builder builder = builders.get(indexIdentifier);
                                if (builder == null) {
                                    builder = IndexInformationImpl.builder(indexIdentifier);
                                    builders.put(indexIdentifier, builder);
                                }

                                final Identifier columnIdentifier = DatabaseIdentifier
                                        .toIdentifier(resultSet.getString(getResultSetColumnNameLabel()));

                                // ADDED: if block
                                if (columnIdentifier == null) {
                                    // we've found an undefined index column name, probably due to a function-based index
                                    // skip the whole index
                                    builders.remove(indexIdentifier);
                                    // make sure it is not added again if another normal index column follows
                                    skipped.add(indexIdentifier);
                                    continue;
                                }

                                final ColumnInformation columnInformation = tableInformation.getColumn(columnIdentifier);
                                if (columnInformation == null) {
                                    // See HHH-10191: this may happen when dealing with Oracle/PostgreSQL function indexes
                                    LOG.logCannotLocateIndexColumnInformation(columnIdentifier.getText(), indexIdentifier.getText());
                                }
                                builder.addColumn(columnInformation);
                            }
                            return null;
                        });

            } catch (SQLException e) {
                throw convertSQLException(e, "Error accessing index information: " + tableInformation.getName().toString());
            }

            final List<IndexInformation> indexes = new ArrayList<>();
            for (IndexInformationImpl.Builder builder : builders.values()) {
                IndexInformationImpl index = builder.build();
                indexes.add(index);
            }
            return indexes;
        }
    }

Of course it would have been easier if the Lambda expression would have been externalized into a method instead.

Sorry for the trouble you ran into. I hope you can contribute the fixes back though!

My fix works with MySQL 8 (and probably also MariaDB). However, I don’t know how other databases (Oracle, SQLServer, PostgreSQL) would return index columns if a function-based index is used.

I’ve added a comment into [HHH-15123] - Hibernate JIRA regarding the possible fix above.

You could try to create a test similar to org.hibernate.orm.test.dialect.functional.SQLServerDialectTempTableCollationTest which creates the index on startup and drops it before shutdown.