Liquibase: dropped Table reappear

Question

we are using Liquibase to deploy our changes in the database an we are pretty new in this field. I now discovered something I'm not sure if it is a bug or a feature.

For example let's take this yaml:

databaseChangeLog:
- changeSet:
    id: FOOBAR-1
    failOnError: false
    author: anyA
    changes:
    - createTable:
        columns:
        - column:
            constraints:
              nullable: false
            name: COLA
            type: VARCHAR2(1000 BYTE)
        - column:
            name: COLB
            type: VARCHAR2(1000 BYTE)
        - column:
            name: VOLC
            type: VARCHAR2(1000 BYTE)
        - column:
            name: COLD
            type: VARCHAR2(1000 BYTE)
        tableName: FOOBAR
- changeSet:
    id: FOOBAR-2
    failOnError: false
    author: anyA
    changes:
    - createIndex:
        columns:
        - column:
            name: COLA
        indexName: FOOBAR_PK
        tableName: FOOBAR
        unique: true
- changeSet:
    id: FOOBAR-3
    failOnError: false
    author: anyA
    changes:
    - addUniqueConstraint:
        columnNames: COLA
        constraintName: FOOBAR_PK
        forIndexName: FOOBAR_PK
        tableName: FOOBAR

with a lot of other changesets in the yaml belonging to other tables. We call this yaml now init_2023.yaml. This yaml was imported by Liquibase and everything works fine.

To make the tables independent to each other, because we don't have THE version of a database, it's more like we have a version for each table, we made a yaml for each table. So we made a lot of yaml-Files by splitting the init_2023.yaml to tablename.yaml. So in the example a foobar.yaml. In the foobar.yaml we added now a new changeset:

- changeSet:
    id: FOOBAR-4
    failOnError: false
    author: anyB
    changes:
    - dropTable:
        cascadeConstraints: true
        tableName: FOOBAR

after importing the yaml with drop, the table was gone like expected. Now it get's strange, after reimporting, Liquibase imported the changesets FOOBAR-1, FOOBAR-2 and FOOBAR-3 like it doesn't know they already got imported, but why and how can I make sure Liquibase doesn't retry changesets that are already in the DATABASECHANGLOG-table? Does the filename have a effect? I don't hope so.

The DATABASECHANGLOG-table looks like this (ID, DATEEXECUTED, ORDEREXECUTED, DEPLOYMENT_ID are nor the real numbers, they are just in the correct order):

|    ID    | AUTHOR |  FILENAME      |    DATEEXECUTED     | ORDEREXECUTED |       MD5SUM        | DEPLOYMENT_ID
| -------- | ------ | -------------- | ------------------- | ------------- | ------------------- |
| FOOBAR-3 | anyA   | foobar.yaml    | 23.10.2023 12:00:00 | 517           | 8:foobar3md5sum_abc | 63
| FOOBAR-2 | anyA   | foobar.yaml    | 23.10.2023 12:00:00 | 516           | 8:foobar2md5sum_def | 63
| FOOBAR-1 | anyA   | foobar.yaml    | 23.10.2023 12:00:00 | 515           | 8:foobar1md5sum_ghi | 63
| FOOBAR-4 | anyB   | foobar.yaml    | 23.10.2023 11:00:00 | 504           | 8:foobar2md5sum_def | 52
| FOOBAR-3 | anyA   | init_2023.yaml | 01.09.2023 12:00:00 | 103           | 8:foobar3md5sum_abc | 11
| FOOBAR-2 | anyA   | init_2023.yaml | 01.09.2023 12:00:00 | 102           | 8:foobar2md5sum_def | 11
| FOOBAR-1 | anyA   | init_2023.yaml | 01.09.2023 12:00:00 | 101           | 8:foobar1md5sum_ghi | 11

My exepection is that changesets that are already imported (by ID) won't be executed again.

Answer 1

The following combination of attributes creates a unique changeSet identifier:

• changeSet id
• changeSet author
• changeLog file name
• changeLog location

Check out the documentation

Each changeset contains the id and author tags. The id tag, author tag, search path location, and name of the changelog file create a unique identifier for the changeset.

To make sure liquibase does not execute changeSets that are already run you need to keep all of the above attributes intact, and not alter databasechangelog table.

It's not a good practice to alter existing changeSets in any way. It's a "forward only" thing. If you need to make changes, create new changeSets and keep the existing ones.

The main idea behind this is to maintain consistency of the DB schema between all environments.