Liquibase: Executing same changeSet multiple times with different properties throws ValidationFailedException changesets had duplicate identifiers

General Discussion
1

I have to execute a Liquibase changeSet multiple times. Each time with different properties.

Imagine this example:

<!-- changeset_foo.xml -->
<databaseChangeLog ...>
    <changeSet author="me" id="INSERT_FOO">
        <insert tableName="FOO">
            <column name="ID" valueSequenceNext="ID_SEQ"/>
            <column name="NAME" value="${FOO_NAME}"/>
        </insert>
    </changeSet>
</databaseChangeLog>

Now, this will be used in two different changeSets with different property:

<!-- changeset_apple.xml -->
<databaseChangeLog ...>
    <property name="FOO_NAME" value="Apple"/>
    <include file="changeset_foo.xml"/>
</databaseChangeLog>

<!-- changeset_banana.xml -->
<databaseChangeLog ...>
    <property name="FOO_NAME" value="Banana"/>
    <include file="changeset_foo.xml"/>
</databaseChangeLog>

And of course both are mentioned in master changelog:

<!-- changelog_master.xml -->
<databaseChangeLog ...>
    <include file="changeset_apple.xml"/>
    <include file="changeset_banana.xml"/>
</databaseChangeLog>

The result is some kind of expected. As I defined only one changeset and it is assumed that the changeSet Id is unique there is a validation exception:

Caused by: liquibase.exception.CommandExecutionException: ...
     Caused by: liquibase.exception.ValidationFailedException: Validation Failed:
          1 changesets had duplicate identifiers
               changeset_foo.xml::INSERT_FOO::me

But how can I solve this behavior?

What I tried so far:

  1. Setting runOnChange, runAlways and validCheckSum does not work (though mentioned here: sql - execute parameterized liquibase changeset multiple times with different parameters - Stack Overflow) and produces the same error
<!-- changeset_foo.xml -->
<databaseChangeLog ...>
   <changeSet author="me" id="INSERT_FOO" runOnChange="true" runAlways="true">
       <validCheckSum>any</validCheckSum>
       <insert tableName="FOO">
           <column name="ID" valueSequenceNext="ID_SEQ"/>
           <column name="NAME" value="${FOO_NAME}"/>
       </insert>
   </changeSet>
</databaseChangeLog>
  1. Extending the id with the property did not work
<!-- changeset_foo.xml -->
    <changeSet author="me" id="INSERT_FOO_${FOO_NAME}">

Here the error is 1 changesets had duplicate identifiers changeset_foo.xml::INSERT_FOO_Apple::me or if I swap the order of the changsets in the master changelog file, the exceptions is 1 changesets had duplicate identifiers changeset_foo.xml::INSERT_FOO_Banana::me

  1. I am using Liquibase 4.24.0. I read in Liquibase documentation that with version 4.25.1 and later you can set allow-duplicated-changeset-identifiers. But even that would only execute the changeset once

your duplicated changesets are not deployed to your database, but Liquibase will not treat the duplicates like errors

I asked the same question here: Liquibase: Executing same changeSet multiple times with different properties throws ValidationFailedException changesets had duplicate identifiers - Stack Overflow

2

Overall, this is a bad idea, which is what Liquibase is trying to tell you.

Each insert statement should either be separate in the same changeset, or in different changesets. The method you are using will be very confusing to troubleshoot.

<!-- changeset_foo.xml -->
<databaseChangeLog ...>
    <changeSet author="me" id="INSERT_FOO">
        <insert tableName="FOO">
            <column name="ID" valueSequenceNext="ID_SEQ"/>
            <column name="NAME" value="Apple"/>
        </insert>
        <insert tableName="FOO">
            <column name="ID" valueSequenceNext="ID_SEQ"/>
            <column name="NAME" value="Banana"/>
        </insert>
    </changeSet>
</databaseChangeLog>
3

In my case and because I know what I do, that is not a bad idea. Code duplication is even not good practice :wink:

May be I have to explain my usecase. I have to insert a lot of entries (>1000) in the database and basically I have to insert these entries twice. The only difference is that the entries are associated once to ‘Apple’ and once to ‘Banana’ (lets call this root entities)

In my example I could be able to create unique IDs wit the following example. But unfortunately that is not supported by Liquibase. Maybe a nice enhancement!