sqldatabaseflyway

Flyway failed migration is stuck in pending state

I have created a new flyway migration file and executed flywayMigrate:

ALTER TABLE xxx_user
    ADD creation_date TIMESTAMP WITHOUT TIME ZONE;

ALTER TABLE xxx_user
    ADD deleted BOOLEAN;

ALTER TABLE xxx_user
    ADD last_changed TIMESTAMP WITHOUT TIME ZONE;

ALTER TABLE xxx_user
    ALTER COLUMN deleted SET NOT NULL;

The migration failed with the message: ERROR: column “deleted” of relation “xxx_user” contains null values

I tried to fix the problem by adjusting the alter table command:

ALTER TABLE xxx_user
    ADD deleted BOOLEAN NOT NULL DEFAULT FALSE;

However, I cannot carry out this migration. FlywayMigrate gives the same error, which is supposed to be in an empty line. The status of the migration is pending and flywayRepair does not do anything, as there is no failed migration.

How can I solve this problem? Is there a way to delete or change pending migrations?

Solution

  • Unfortunately, flyway repair only fixes checksums and failed states in the flyway_schema_history table. It does not affect pending migrations or let you rerun modified scripts.

    You have several options here:

    Option 1: delete and recreate the migration file (best practice for pending)

    Since the migration is still pending and not yet recorded in flyway_schema_history, you can safely delete or rename the file, create a corrected one, and run flyway migrate again.

    Option 2: manually clean up the table (quick & risky)

    If for some reason you're stuck and need to fix the pending state now, and you know it's safe to do so:

    Option 3: use flyway clean (destructive, dev only)

    This will drop the entire database and reapply all migrations. Only use this in development environments!