Flyway Mutli Schema: How to specify the schema name in the migration script sql file

Which version and edition of Flyway are you using?

flywayVersion=7.7.0

If this is not the latest version, can you reproduce the issue with the latest one as well? (Many bugs are fixed in newer releases and upgrading will often resolve the issue)

I have tried with 7.10.0, It didn't work. But not sure I am using it the right way given my usecase

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin)

Java API

Which database are you using? (Type & version)

MySQL 5.5

Which operating system are you using?

Windows

What did you do? (Please include the content causing the issue, any relevant configuration settings, the SQL statement(s) that failed (if any), and the command you ran)

Use Case:

1. I have two schemas say Schema-1,Schema-2

2. I have few entities says, Table-1, Table-2, Table-3, Table-4, Table-5

3. Table-1 and Table-2 belongs to Schema-1

4. Table-3, Table-4, Table-5 belongs to Schema-2

5. I have configured my SpringBootApplication to write the data to the tables in different schemas, by configuring multiple datasource configurations and its working as expected.

6. I wanted to handle migrations. During migration, I have to create/update/alter Table-1, and Table-2 under Schema-1

7. I wanted to handle migrations. During migration, I have to create/update/alter Table-3, and Table-4, Table-5 under Schema-2

8. I wanted to tell my migration script to which schema to use during migration.

9. I have tried to explicitly prefix schema name to table name. for eg: CREATE TABLE schema-2.table3 ( apl_name varchar(60) NOT NULL, apl_info varchar(30) NOT NULL, update_date date NOT NULL, PRIMARY KEY (apl_name) )

   Here I have prefixed schema-2 to table3 to indicate create table3 in schema-2.

10. Assume my defaultSchema is schema1

11. Then during migration table3 is created under schema-1, with the table name being schema-1.schema-2.table3.

12. It doesn't honor the schema prefix

What did you expect to see?

1. I expect to create tables under relevant schemas during migration.

What did you see instead?

1. All the tables are getting created under the default schema. I can't manage creation of tables under their relevant schemas
2. Let me know how to achieve this use case?
3. Is it possible to achieve it in as part of flyway migrations.

Thanks Krishna