Is possible to apply undo with dryRun?

Question

Good Morining,

We are using flyway with a Java API. flyway version: 6.5.0 Enterprise. database: h2 (version 1.4.197).

We are trying to apply undo operation programmatically setting a number of a version and applying undo until the current version would be equal. For example undoVersion from 03 to 02.

    private void undoVersion(String lastVersion, Flyway flyway, int limit) {
        MigrationInfoService info = flyway.info();
        String currentVersion = info.current()!=null && info.current().getVersion()!=null ?
            info.current().getVersion().getVersion() : null;

        if (limit > 0 && !lastVersion.equals(currentVersion)) {
            flyway.undo();
            
            undoVersion(lastVersion, flyway, limit - 1);
        }
    }

If we create the flyway instance using dryRunOutput this way the undo operation is not truly executed and therefore the current version never changes. If I remove .dryRunOutput(outputFileName) the undo is performed fine but I can't get the report.

       Flyway.configure()
            .dataSource(countryConfig.getString("url"), flywayUser, countryConfig.getString("password"))
            .licenseKey(FLYWAY_LICENSE)
            .schemas(flyWayConfig.getString("schemas"))
            .encoding(flyWayConfig.getString("encoding"))
            .validateOnMigrate(flyWayConfig.getBoolean("validateOnMigrate"))
            .cleanDisabled(flyWayConfig.getBoolean("cleanDisabled"))
            .baselineOnMigrate(flyWayConfig.getBoolean("validateOnMigrate"))
            .table(flyWayConfig.getString("table"))
            .outOfOrder(flyWayConfig.getBoolean("outOfOrder"))
            .placeholderReplacement(true)
            .locations("filesystem:" + countryConfig.getString("flywayLocation"))
            .dryRunOutput(outputFileName)
            .load();

Is there a way to apply undo with dryRun in order to get the report with the undo queries applied in SQL ?

Thank you in advance.

Best regards Álvaro Navarro

Answer 1

Dry runs ought to work with undo. However, if you intend to undo one migration at a time (the default undo behaviour) that won't work, as the undo-with-dry-run doesn't update the Flyway schema history table and therefore Flyway will always think that the last actually applied migration is the one to be undone.

What you can do is use the target parameter in order to define which migration you want to undo up to, and use that in conjunction with a single dry run.

EDIT: This is now a case on our issue tracker: https://github.com/flyway/flyway/issues/2890

Answer 2

thanks for the answer, actually what is concern us, is that even using undo once single time with the option undo-with-dry-runin the Flyway instance, we cannot see the report created with the UNDO action to do, as we specify in the file U01__drop_table

DROP TABLE AUTO_BOT

Instead we just see a repo with always the same information

-- -====================================
-- Flyway Dry Run (2020-07-23 11:56:02)
-- -====================================

SET SCHEMA "ADMIN_IT";

-- Executing: info (with callbacks)
-- ---------------------------------------------------------------------------------------
SET SCHEMA "PUBLIC";

Answer 3

Thank you very much for your response @Julia Hayward.

In the example which I'm using for testing I have 3 update version files and 3 undo files.

V01__create_auto_bot.sql
V02__add_bot.sql
V03__update_auto_bot.sql
U01__drop_auto_bot.sql
U02__delete_bot.sql
U03__update_auto_bot.sql

The code is very simple

V01__create_auto_bot.sql
CREATE TABLE autobots.auto_bot (ID int not null, NAME varchar(100) not null);

V02__add_bot.sql
INSERT INTO autobots.auto_bot (id, name) VALUES (1, 'Optimus Prime');

V03__update_auto_bot.sql
UPDATE autobots.auto_bot SET name = 'Megatron' WHERE id = 1;

U01__drop_auto_bot.sql
DROP table autobots.auto_bot;

U02__delete_bot.sql
DELETE FROM autobots.auto_bot WHERE id = 1;

U03__update_auto_bot.sql
UPDATE autobots.auto_bot SET name = 'Optimus Prime' WHERE id = 1;

If I perform the migration using the dryRunOutput it works fine and i get this report:

-- -====================================
-- Flyway Dry Run (2020-07-24 17:12:39)
-- -====================================

CREATE SCHEMA "autobots";
CREATE TABLE IF NOT EXISTS "autobots"."FLYWAY_schema_history" (
    "installed_rank" INT NOT NULL,
    "version" VARCHAR(50),
    "description" VARCHAR(200) NOT NULL,
    "type" VARCHAR(20) NOT NULL,
    "script" VARCHAR(1000) NOT NULL,
    "checksum" INT,
    "installed_by" VARCHAR(100) NOT NULL,
    "installed_on" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "execution_time" INT NOT NULL,
    "success" BOOLEAN NOT NULL,
    CONSTRAINT "FLYWAY_schema_history_pk" PRIMARY KEY ("installed_rank")
) AS SELECT -1, NULL, '<< Flyway Schema History table created >>', 'TABLE', '', NULL, 'SA', CURRENT_TIMESTAMP, 0, TRUE;
CREATE INDEX "autobots"."FLYWAY_schema_history_s_idx" ON "autobots"."FLYWAY_schema_history" ("success");
INSERT INTO "autobots"."FLYWAY_schema_history" ("installed_rank", "version", "description", "type", "script", "checksum", "installed_by", "execution_time", "success") VALUES (0, null, '<< Flyway Schema Creation >>', 'SCHEMA', '"autobots"', null, 'SA', 0, 1);

-- Executing: migrate (with callbacks)
-- ---------------------------------------------------------------------------------------

-- Executing: migrate -> v01 (with callbacks)
-- ---------------------------------------------------------------------------------------

-- Source: C:\Users\JQ00CT\projects\maggie-flyway\maggie-flyway-infrastructure\target\test-classes\db\autobots\es\V01__create_auto_bot.sql
-- ----------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE autobots.auto_bot (
    ID int not null,
    NAME varchar(100) not null
);
INSERT INTO "autobots"."FLYWAY_schema_history" ("installed_rank", "version", "description", "type", "script", "checksum", "installed_by", "execution_time", "success") VALUES (1, '01', 'create auto bot', 'SQL', 'V01__create_auto_bot.sql', -1088653058, 'SA', 5, 1);

-- Executing: migrate -> v02 (with callbacks)
-- ---------------------------------------------------------------------------------------

-- Source: C:\Users\JQ00CT\projects\maggie-flyway\maggie-flyway-infrastructure\target\test-classes\db\autobots\es\V02__add_bot.sql
-- --------------------------------------------------------------------------------------------------------------------------------
INSERT INTO autobots.auto_bot (id, name) VALUES (1, 'Optimus Prime');
INSERT INTO "autobots"."FLYWAY_schema_history" ("installed_rank", "version", "description", "type", "script", "checksum", "installed_by", "execution_time", "success") VALUES (2, '02', 'add bot', 'SQL', 'V02__add_bot.sql', 1213011392, 'SA', 1, 1);

-- Executing: migrate -> v03 (with callbacks)
-- ---------------------------------------------------------------------------------------

-- Source: C:\Users\JQ00CT\projects\maggie-flyway\maggie-flyway-infrastructure\target\test-classes\db\autobots\es\V03__update_auto_bot.sql
-- ----------------------------------------------------------------------------------------------------------------------------------------
UPDATE autobots.auto_bot SET name = 'Megatron' WHERE id = 1;
INSERT INTO "autobots"."FLYWAY_schema_history" ("installed_rank", "version", "description", "type", "script", "checksum", "installed_by", "execution_time", "success") VALUES (3, '03', 'update auto bot', 'SQL', 'V03__update_auto_bot.sql', 101152142, 'SA', 1, 1);
SET SCHEMA "PUBLIC";
SET SCHEMA "PUBLIC";

But If I do the same with UNDO, as you mention, using the dryRunOutput in conjuntion with the target parameter, it doesn't work :(

I only get this ...

-- -====================================
-- Flyway Dry Run (2020-07-24 17:15:48)
-- -====================================

SET SCHEMA "autobots";

-- Executing: info (with callbacks)
-- ---------------------------------------------------------------------------------------
SET SCHEMA "PUBLIC";