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:
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
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.
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]
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.
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.
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.
