Spring Boot 2 migration causes index problem on H2 with HikariCP

Question

After migrating Spring Boot in our application from 1.5.13.RELEASE to 2.1.8.RELEASE we noticed that our tests throws errors about index creation, but still passing,

In our production we are using PostgresSQL and in our tests we are using H2 DB version: 1.4.200

After exploring the bug, we noticed the following behaviour:

Lets assume that this our entities inside the application:

@Entity
@Table(indexes = @Index(columnList = "name", name = "name"))
public class Cat {
    @Id
    @GeneratedValue(generator = "uuid")
    @GenericGenerator(name = "uuid", strategy = "uuid2")
    private String id;

    private String name;
}

@Entity
@Table(indexes = @Index(columnList = "name", name = "name"))
public class Dog {
    @Id
    @GeneratedValue(generator = "uuid")
    @GenericGenerator(name = "uuid", strategy = "uuid2")
    private String id;

    private String name;
}

Then if we are running the tests with h2 there is exception:

Caused by: org.h2.jdbc.JdbcSQLException: Index "NAME" already exists; SQL statement:
create index name on dog (name) [42111-196]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.message.DbException.get(DbException.java:179) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.message.DbException.get(DbException.java:155) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.ddl.CreateIndex.update(CreateIndex.java:76) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.CommandContainer.update(CommandContainer.java:101) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Command.executeUpdate(Command.java:260) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:192) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:164) ~[h2-1.4.196.jar:1.4.196]
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95) ~[HikariCP-3.2.0.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-3.2.0.jar:na]
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
    ... 34 common frames omitted

This are our application.properties:

server.port=9090

########## DATABASE CONFIGURATION ##############
spring.datasource.url=jdbc:h2:mem:test
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=test
spring.datasource.password=test

spring.jpa.hibernate.ddl-auto=update
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.h2.console.enabled=true
spring.h2.console.path=/h2-console

Even that Index in the database should create per Table and not on the schema, so the index should not exist already, if we are running the same entities with Postgres then no error is been throwing,

I'm wondering if this is a bug in the H2 driver? or that we are doing something wrong?

After exploring a bit more I found this question from 2010, It's says that the Index is global and has to be unique name, But this don't make any sense because as I mentioned before the index is per table, and not schema.

And here is a proof that in the DB index created per table

But if I'll create same index name for both tables, then only in the first table the index will be created.

Could you provide your spring h2 & hibernate configuration ? — willome, Jan 27 '20 at 16:46
Please see https://stackoverflow.com/questions/27306539/at-what-level-do-postgres-index-names-need-to-be-unique — GuyT, Jan 27 '20 at 17:58
@DanielTaub Can you double check if the index names are not unique on the production system? Maybe one of the implementations is adding a (pre|suf)fix (incorrectly) in the old drivers? — GuyT, Jan 27 '20 at 18:57
What is the question? You already figured out that H2 seems to have a global namespace for indexes. The same is true for example for Oracle. — Jens Schauder, Jan 28 '20 at 09:20
There are similar questions, and all appear to treat this as a global creation (see https://stackoverflow.com/questions/26997441/why-cant-two-tables-have-an-index-of-the-same-name and https://stackoverflow.com/questions/32198962/h2-index-name-uniqueness) - why are you sure it is not so? — orirab, Jan 28 '20 at 17:21
@orirab because in postgres it's creates the index's for the tables, so I didn't catch how it managed to do that — Daniel Taub, Jan 28 '20 at 18:33

Spring Boot 2 migration causes index problem on H2 with HikariCP

0 Answers0