Workaround to h2 database unique index name

Question

I have a java application which uses Liquibase for migration and till now was using MySQL for running test cases.

I want to run the test cases with the h2 database. For that, I made required changes in JDBC config. But when I ran the mvn test, I get the following error :

Index "idx_workflow_id" already exists; SQL statement:

On debugging I found out two tables were having an index with the same name: idx_workflow_id, and h2 database has some constraint of having unique index names in the database.

Now the problem is the DB structure is already present in staging and prod, and changing the index name is not an option.

Is there any way where I can run the migrations for h2 database, either without creating an index or ignoring them. Or some config change, which would allow index with the same name in DB.

config:

database:
    driverClass: org.h2.Driver
    url: "jdbc:h2:mem:my_db;MODE=MySQL;DATABASE_TO_UPPER=false;IGNORECASE=TRUE;DB_CLOSE_DELAY=-1"
    maxWaitForConnection: 1s
    minSize: ${MIN_DB_CONNECTIONS:-10}
    maxSize: ${MAX_DB_CONNECTIONS:-100}
    defaultTransactionIsolation: READ_COMMITTED
    checkConnectionWhileIdle: false
    checkConnectionOnBorrow: true
    checkConnectionOnConnect: true
    validationQuery: "SELECT 1"
    properties:
      hibernate.dialect: org.hibernate.dialect.H2Dialect
      hibernate.show_sql: false
      hibernate.hbm2ddl.auto: none
      hibernate.session.events.log: false
      hibernate.generate_statistics: true
      org.hibernate.stat: INFO
      charSet: UTF-8

Perfect

Answer 1

I'm occured in the same problem and I found this question so I can share my workaround.

The only way that I found is to ignore che change set where the index is created in the h2 context with dbms="!h2"

    <changeSet id="index-creation" author="ffex" dbms="!h2">
        <createIndex ... />
    </changeSet>

Now you have two choice:

1. Create a changeset with an index with different name for the h2 context:

    <changeSet id="index-creation-test" author="ffex" dbms="h2">
        <createIndex ... />
    </changeSet>

2. Create a changeset with the drop of the index and a create index in every context. I choose this one because is more simple if the DB in the main context and the test context are the same.

    <changeSet id="index-drop" author="ffex" dbms="!h2">
        <dropIndex ... />
    </changeSet>
    <changeSet id="index-creation-new" author="ffex">
        <createIndex ... />
    </changeSet>

You can use also the attribute contextFilter to control the execution of the changesets in contexts.

Act