Flyway - normalising between "schema_version" / "flyway_schema_history" table names - "Found non-empty schema(s) "PUBLIC" but no schema history table"

Question

I've been using Flyway in my application, where each use has their own persistent H2 database.

Flyway v5 changed the metadata table name from schema_version to flyway_schema_history, and could handle both - but with the caveat Flyway v6 would not do this.

Due to the databases persisting, and Flyway being upgraded from v4 to v5, some users have a schema_version table, while others have a flyway_schema_history table.

Obviously Flyway v6 will not work for the databases with a schema_version table.

Initially I was blocked, but then discovered callbacks that allow SQL to be run at arbritrary points. So the obvious solution was a beforeMigrate.sql that renamed the schema_version table to flyway_schema_history.

However when I try this, even though I can see from the debug logs the command is executed, I get the following erorr. I even get the error when I manually rename the table in the database outside of the callback functionality.

org.flywaydb.core.api.FlywayException: Found non-empty schema(s) "PUBLIC" but no schema history table. Use baseline() or set baselineOnMigrate to true to initialize the schema history table.

I'm getting this whether I tentatively upgrade to Flyway v6 or the latest v8.

Is there something obvious I'm missing? Or is there a smarter way to move these existing databases to a format compatible with the latest flyway versions?

I've created an MVCE using Maven but it's obviously slightly complex as it involves a database etc.

Directory structure:

flywaytest
    src
        main
            java
                com
                    me
                        flywaytest
                            FlywayTest.java
            resources
                db
                    migration
                        beforeMigrate.sql
                        V1__Intial.sql
    pom.xml

FlywayTest.java:

package com.me.flywaytest;

import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.text.MessageFormat;

import org.flywaydb.core.Flyway;
import org.h2.engine.Constants;

public class FlywayTest {
    private static final String databaseName = "fruits";
    private static final String h2URL = MessageFormat.format("jdbc:h2:~/{0}", databaseName);
    private static final Path databaseLocation = Paths.get(System.getProperty("user.home"))
        .resolve(databaseName + Constants.SUFFIX_MV_FILE);

    public static void main(final String[] args) throws Exception {
        Files.deleteIfExists(databaseLocation);

        createOldDatabase();

        Flyway.configure().dataSource(h2URL, null, null).load().migrate();
    }

    private static Connection getConnection() throws Exception {
        return DriverManager.getConnection(h2URL, null, null);
    }

    /** Creates an example database as created by Flyway v4.2.0 */
    private static void createOldDatabase() throws Exception {
        // Old Flyway database generated by rolling back Flyway and running an initial
        // migration and dumping it's SQL using the following:
        // Flyway flyway = new Flyway();
        // flyway.setDataSource(h2URL, null, null);
        // flyway.migrate();
        // try (Connection connection = getConnection()) {
        // Script.process(connection,
        // Paths.get(System.getProperty("user.home")).resolve(databaseName +
        // ".sql").toString(), "", "");
        // }

        // Which created the following:
        final String fruitsSQL = """
            ;
        CREATE USER IF NOT EXISTS "" SALT '' HASH '' ADMIN;
        CREATE CACHED TABLE "PUBLIC"."schema_version"(
            "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 DEFAULT CURRENT_TIMESTAMP NOT NULL,
            "execution_time" INT NOT NULL,
            "success" BOOLEAN NOT NULL
        );
        ALTER TABLE "PUBLIC"."schema_version" ADD CONSTRAINT "PUBLIC"."schema_version_pk" PRIMARY KEY("installed_rank");
        -- 1 +/- SELECT COUNT(*) FROM PUBLIC.schema_version;
        INSERT INTO "PUBLIC"."schema_version" VALUES
        (1, '1', 'Initial', 'SQL', 'V1__Initial.sql', 691111646, '', TIMESTAMP '2021-12-26 00:07:37.878797', 0, TRUE);
        CREATE INDEX "PUBLIC"."schema_version_s_idx" ON "PUBLIC"."schema_version"("success");
        CREATE CACHED TABLE "PUBLIC"."FRUITS"(
            "NAME" CHARACTER VARYING
        );
        -- 0 +/- SELECT COUNT(*) FROM PUBLIC.FRUITS;

        """;

        try (Statement statement = getConnection().createStatement()) {
            statement.execute(fruitsSQL);
        }
    }
}

beforeMigrate.sql:

ALTER TABLE IF EXISTS "schema_version" RENAME CONSTRAINT "schema_version_pk" TO "flyway_schema_history_pk";
ALTER TABLE IF EXISTS "schema_version" RENAME TO "flyway_schema_history";
ALTER INDEX IF EXISTS "schema_version_s_idx" RENAME TO "flyway_schema_history_s_idx";

V1__Initial.sql:

CREATE TABLE FRUITS(
    NAME CHARACTER VARYING
);

pom.xml:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.me</groupId>
    <artifactId>flywaytest</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <dependencies>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>1.4.200</version>
        </dependency>
        <dependency>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-core</artifactId>
            <version>8.3.0</version>
        </dependency>
    </dependencies>
</project>

Answer 1

I personally have never used Flyway's callbacks but please, be aware that, according to the library source code, the beforeMigrate callback will be run in DbMigrate after the schema history table existence is checked in the Flyway class, so try renaming the history table may not work.

As a possible solution, you could try providing explicitly the table name for your databases, keeping the previous one, schema_version. For example:

Flyway.configure()
  .table("schema_version")
  .dataSource(h2URL, null, null)
  .load()
  .migrate()
;

Probably using the beforeValidate callback could work as well, as that callback seems to be executed before the actual schema history table existence check. I renamed your script from beforeMigrate.sql to beforeValidate.sql and it worked properly. Just be sure that validation on migrate is enabled - it should be by default.