31

We use the Flyway Gradle plugin to do our migrations offline (i.e. we migrate while the system is down). We recently upgraded to Flyway 5.0.7 and we see this warning now for migrations:

Could not find schema history table XXXXXXX.flyway_schema_history, but found XXXXXXX.schema_version instead. You are seeing this message because Flyway changed its default for flyway.table in version 5.0.0 to flyway_schema_history and you are still relying on the old default (schema_version). Set flyway.table=schema_version in your configuration to fix this. This fallback mechanism will be removed in Flyway 6.0.0.

(I've used the XXXXXXX to obscure the actual schema name).

So, it appears that we can avoid the error by setting flyway.table=schema_version. But, it also says this mechanism will be removed in Flyway 6.0.0.

Are we supposed to do something to make this compatible going forward? Do we have to manually rename the schema_version table to flyway_schema_history? Or is there a way to make Flyway do it? If not, what is going to happen when Flyway 6.0.0 comes out? Will it automatically migrate the data to the appropriate table name?

Tony C.
  • 323
  • 1
  • 4
  • 8
  • And indeed automatic fallback to `schema_version` was removed in Flyway 6: https://github.com/flyway/flyway/commit/f702450ab97476bfc18d605f4a09670835eb699b – Ilya Serbis Oct 09 '20 at 20:08

4 Answers4

36

The default for flyway.table has been changed from schema_version to flyway_schema_history. And they have also provided automatic fallback to old default with a warning to avoid breaking existing installations using the old default.

It means from flyway 5, If you do not specify flyway.table property inside your configuration file, then flyway will look for the table flyway_schema_history in db, and if not found it will look for the table schema_version as a fallback and if the old table is found then will warn with the message that you are getting now. From flyway 6, this fallback mechanism will be removed. If you do not provide flyway.table property, it will look for flyway_schema_history in db, if not found it will not look for schema_version table even if you have any and will create a new table named flyway_schema_history to maintain functionality.

In flyway 6, your existing system will run fine if you set flyway.table=schema_version, you do not need to change table name in db. But if you do not set the property, then you must have to change the table name, otherwise flyway will not recognize existing schema_version table, will treat the system as a new one, will create flyway_schema_history table and will start executing scripts from start.

Hoping it will help.

Monzurul Shimul
  • 8,132
  • 2
  • 28
  • 42
  • Thank you for the clarification. That makes sense. – Tony C. Mar 02 '18 at 15:04
  • 7
    Is there a straightforward way to get Flyway to rename it? – nafg May 15 '18 at 00:49
  • 2
    is there official documentation from flyway about this? – Yaz Aug 19 '19 at 19:58
  • Not supporting `schema_version` as a fallback in newer versions very risky. @nafg If you mean "to set Flyway" it might help: https://flywaydb.org/documentation/configuration/parameters/table @Yaz I couldn't find relative information in Flyway release notes, and I am really suprised while it's a very important change. Currently I'm migrating Flyway from 3.x.x straight to 7.x.x and I didn't expect such change. – EnGoPy Apr 16 '21 at 01:34
  • This comment seems very old, yet I am hoping I may get some help here. I am trying to flyway update an application database. The last time I did this was back in 2019, and I did this command line. I would like to have this all done by spring-boot on application startup, so have updated the properties to be prefixed by `spring.` and **added** a `spring.flyway.table=schema_version` property to the configuration. I should add The schema_version table exists with one `BASELINE` record (this may be important?) and the `flyway_schema_history` table is absent. – Bill Naylor Nov 01 '22 at 18:39
5

On PostgreSQL I have solved it with just one migration on top:

DO $$
  BEGIN
    IF (EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'schema_version')
        AND EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'flyway_schema_history'))
    THEN
        DROP TABLE schema_version;
    END IF ;
    IF (EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'schema_version')
        AND NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'flyway_schema_history'))
    THEN
        CREATE TABLE flyway_schema_history AS TABLE schema_version;
    END IF ;
  END
$$ ;

It works actually in 2 stages:

  • On first reboot it copies history with migration recorded into 'old' history table.
  • On second reboot it drops old history table. Now migration goes into 'new' history and everything is finished.
Roman Nikitchenko
  • 12,800
  • 7
  • 74
  • 110
1

It is possible to migrate from schema_version to flyway_schema_history by mapping a table over the other and copying the relevant records:

DROP TABLE IF EXISTS `flyway_schema_history`;
SET character_set_client = utf8mb4 ;
CREATE TABLE `flyway_schema_history` (
    `installed_rank` int(11) NOT NULL,
    `version` varchar(50) DEFAULT NULL,
    `description` varchar(200) NOT NULL,
    `type` varchar(20) NOT NULL,
    `script` varchar(1000) NOT NULL,
    `checksum` int(11) DEFAULT NULL,
    `installed_by` varchar(100) NOT NULL,
    `installed_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` int(11) NOT NULL,
    `success` tinyint(1) NOT NULL,
    PRIMARY KEY (`installed_rank`),
    KEY `flyway_schema_history_s_idx` (`success`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert into flyway_schema_history (installed_rank, version, description, type, script, checksum, installed_by, installed_on, execution_time, success)
select installed_rank, version, description, type, script, checksum, installed_by, installed_on, execution_time, success
from schema_version;

This is the schema version of flyway_schema_history as of flyway 5.2.2. I recommend, to use this script safely, to migrate before to this version and then move forward.

Please, understand that this script must be executed as it is in your db console. This script is for MySQL only. You have to craft your own for other databases.

Uluaiv
  • 188
  • 2
  • 8
0

I dont think its true: "It means from flyway 5, If you do not specify flyway.table property inside your configuration file, then flyway will look for the table flyway_schema_history in db, and if not found it will look for the table schema_version as a fallback and if the old table is found then will warn with the message that you are getting now."

because I have both tables present, but it still complains: "Could not find schema history table "xxxx"."flyway_schema_history", but found "xxxx"."schema_version" instead. You are seeing this message because Flyway changed its default for flyway.table in version 5.0.0 to flyway_schema_history and you are still relying on the old default (schema_version)."

This means, flyway has the logic backwards, it must have first checked on "schema_version", when it found it, it assumed there is no "flyway_schema_history"

user15710364
  • 1