Environment

• Java: 21
• Spring Boot: 3.5.6
• Flyway Core: 10.16.0
• flyway-database-yugabytedb: 10.24.0
• YugabyteDB: 2024.2.6.0-b94

Problem Description

When migrating multiple schemas in YugabyteDB, the lock table YB_FLYWAY_LOCK_TABLE is only created in the first schema. Subsequent schema migrations fail because they cannot find the lock table in their own schema.

Root Cause

The YugabyteDBDatabase.createLockTable() method checks if the lock table exists using:

private static final String LOCK_TABLE_SCHEMA_SQL = 
    "SELECT table_name, column_name FROM information_schema.columns WHERE table_name = 'yb_flyway_lock_table'";

This query searches across ALL schemas in information_schema.columns, not just the current schema. So:

1. First schema migration creates yb_flyway_lock_table in schema1
2. Second schema migration checks if the table exists
3. The query finds the table in schema1 (via information_schema)
4. The extension doesn't create a new table
5. When trying to use the table with search_path=schema2, it fails because the table doesn't exist in schema2

Error Message

org.flywaydb.core.internal.exception.FlywaySqlException: Unable to obtain lock for "schema2"."flyway_schema_history_2" in table YB_FLYWAY_LOCK_TABLE
...
Caused by: com.yugabyte.util.PSQLException: ERROR: relation "yb_flyway_lock_table" does not exist

Expected Behavior

Each schema should have its own independent yb_flyway_lock_table, just like each schema has its own independent schema history table.

Reproduction Steps

1. Create two schemas: schema1 and schema2
2. Configure Flyway for schema1:

   Flyway flyway1 = Flyway.configure()
       .dataSource(jdbcUrl, user, password)
       .schemas("schema1")
       .defaultSchema("schema1")
       .table("flyway_schema_history_1")
       .locations("classpath:db/migration/schema1")
       .load();
   flyway1.migrate(); // SUCCESS

3. Configure Flyway for schema2:

   Flyway flyway2 = Flyway.configure()
       .dataSource(jdbcUrl, user, password)
       .schemas("schema2")
       .defaultSchema("schema2")
       .table("flyway_schema_history_2")
       .locations("classpath:db/migration/schema2")
       .load();
   flyway2.migrate(); // FAILS - relation "yb_flyway_lock_table" does not exist

Suggested Fix

The LOCK_TABLE_SCHEMA_SQL query should filter by the current schema:

private static final String LOCK_TABLE_SCHEMA_SQL = 
    "SELECT table_name, column_name FROM information_schema.columns " +
    "WHERE table_name = 'yb_flyway_lock_table' " +
    "AND table_schema = current_schema()";

Or alternatively, create the lock table with a schema-qualified name:

private static final String CREATE_LOCK_TABLE_DDL = 
    "CREATE TABLE IF NOT EXISTS " + getCurrentSchema() + "." + LOCK_TABLE_NAME + 
    " (table_name varchar PRIMARY KEY, lock_id bigint, ts timestamp)";

Current Workaround (Not tested)

Use initSql to pre-create the lock table in each schema before the YugabyteDBDatabase constructor runs:

String initSql = String.format(
    "SET search_path TO %s; " +
    "CREATE TABLE IF NOT EXISTS yb_flyway_lock_table " +
    "(table_name varchar PRIMARY KEY, lock_id bigint, ts timestamp);",
    schemaName);

Flyway flyway = Flyway.configure()
    .dataSource(jdbcUrl + "?options=-csearch_path%3D" + schemaName, user, password)
    .schemas(schemaName)
    .defaultSchema(schemaName)
    .initSql(initSql)
    .load();

This workaround is not ideal because:

1. It duplicates the extension's internal logic
2. It's hard to maintain if the lock table schema changes
3. It requires knowledge of the extension's implementation details

Additional Context

This issue is similar to how PostgreSQL's standard Flyway extension handles schema-specific tables. Each schema should have its own isolated lock table to support independent multi-schema migrations.

Related Issues

• flyway-database-yugabytedb : flyway fails after changes in lock table schema #97 - Fixed case-sensitive table name issue (different problem)
• [YugabyteDB] Improve locking mechanism during migrations #76 - Improve locking mechanism during migrations
• [YugabyteDB plugin] Use single connection for both schema history table management and applying migrations #53 - Use single connection for both schema history table management and applying migrations