Multiple Flyway Metadata Tables in One Scheme

Question

I am trying to use Flyway for a modular application database version. Each module has its own separate set of tables and migration scenarios that will control versions of this set of tables.

Flyway allows me to specify a different metadata table for each module - so I can deploy each module myself. When I try to update the application, I start the migration process for each module, each with its own table and set of scripts. Please note that these tables are in the same layout.

However, when I try to migrate my application, the first migration is the only one that works. Subsequent migrations fail with the following exception: org.flywaydb.core.api.FlywayException: Found non-empty schema(s) "public" without metadata table! Use baseline() or set baselineOnMigrate to true to initialize the metadata table. org.flywaydb.core.api.FlywayException: Found non-empty schema(s) "public" without metadata table! Use baseline() or set baselineOnMigrate to true to initialize the metadata table.

If I manually create a metadata table for each module, the migration for each module works correctly. Creating the table itself, rather than creating the Flyway for me, seems like a hack to the problem, not a solution in itself.

Is this a legitimate way to manage multiple sets of tables yourself, or is there a better way to do this? Is this the right way to create a metadata table?

Answer 1

The ideal solution for you is to split your modules into circuits. This gives you an effective unit of isolation per module and is also naturally suitable for modular applications (fully isolated and stand-alone modules), instead of dumping everything into a single circuit (especially public). eg,

 application_database ├── public ├── module_1 │  ├── schema_version │  ├── m1_t1 │ └── m1_t2 ├── module_2 │  ├── schema_version │  ├── m2_t1 │ └── m2_t2 ... 

The second option is to use a public schema to host all tables, but use a separate schema for each schema_version . This is less refactoring, but of course a less elegant design than the one mentioned above. eg,

 application_database ├── public │  ├── m1_t1 │ ├── m1_t2 │  ├── m2_t1 │ └── m2_t2 ├── module_1 │  └── schema_version │ ├── module_2 │  └── schema_version ... 

Answer 2

I think you need to complete the base level of each module before doing the migration. You need to pass a table option to override schema_version for each module, for example flyway.table=schema_version_module1 . Since the error message suggests that you can also use baselineOnMigrate, but this is a warning in the docs ( https://flywaydb.org/documentation/commandline/migrate ).

We are considering a similar approach with another schema_version table for applying and recording data fixes that cannot be deployed in each environment.

Answer 3

I have a similar situation in which I have two schemas (master and tenant), the span works fine if they are separate MYSQL DB / SCHEMA, but for the case when I want both of them to run against the same database ( what could SQL Server, ORACLE). Error FLyway.

 $ ./flyway migrate -table=schema_version -schemas=emm -locations=filesystem:sql/tenant Flyway 4.2.0 by Boxfuse Database: jdbc:mysql://localhost:3306/ (MySQL 5.6) Creating schema `emm` ... Creating Metadata table: `emm`.`schema_version` Current version of schema `emm`: null Migrating schema `emm` to version 1 - create umgmt user table Successfully applied 1 migration to schema `emm` (execution time 00:00.162s). $ ./flyway migrate -table=m_schema_version -schemas=emm -locations=filesystem:sql/master Flyway 4.2.0 by Boxfuse Database: jdbc:mysql://localhost:3306/ (MySQL 5.6) Successfully validated 3 migrations (execution time 00:00.011s) **ERROR: Found non-empty schema(s) `emm` without metadata table! Use baseline() or set baselineOnMigrate to true to initialize the metadata table.**