I'm a bit confused with the Flyway migrations that I'm using for one of my projects and I'm not able to run the migration script. Here is my project set up (which is a Scala project) like below:
I have an application.conf that has the following:
my-platform.jdbc {
driver = "org.h2.Driver"
dbName = "my-platform-h2-test.db;DB_CLOSE_ON_EXIT=FALSE;MODE=MYSQL"
url = "jdbc:h2:~/my-platform/database/"${my-platform.jdbc.dbName}
user = "sa"
migrations-table = "MyPlatformDBSchema"
migrations-locations = [
"classpath:h2/"
]
}
I'm reading that config using the Typesafe config and I traverse over the config and I run my sbt project which then results in the following error below:
INFO: Current version of schema "PUBLIC": << Empty Schema >>
Aug 28, 2023 9:33:23 PM org.flywaydb.core.internal.command.DbMigrate doMigrateGroup
INFO: Migrating schema "PUBLIC" to version "001 - baseline create initial tables"
org.flywaydb.core.internal.exception.FlywaySqlException: Unable to insert row for version '001' in Schema History table "PUBLIC"."MyPlatformDBSchema"
---------------------------------------------------------------------------------------------
SQL State : 42S02
Error Code : 42102
Message : Table "MyPlatformDBSchema" not found; SQL statement:
INSERT INTO "PUBLIC"."MyPlatformDBSchema" ("installed_rank", "version", "description", "type", "script", "checksum", "installed_by", "execution_time", "success") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) [42102-214]
at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.doAddAppliedMigration(JdbcTableSchemaHistory.java:177)
at org.flywaydb.core.internal.schemahistory.SchemaHistory.addAppliedMigration(SchemaHistory.java:192)
at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:402)
at org.flywaydb.core.internal.command.DbMigrate.lambda$applyMigrations$1(DbMigrate.java:274)
I have in my src/main/resources folder a folder called h2, inside which I have my sql file called V001__baseline_create_initial_tables.sql which contains the schema that I want to use for my database like this:
--
-- Drop everything if exists
--
DROP ALL OBJECTS DELETE FILES;
--
-- Schema for my-platform
--
CREATE SCHEMA my_platform_schema AUTHORIZATION SA;
SET SCHEMA my_platform_schema;
....
....
-- The remaining create tables
....
....
What is it I'm doing wrong here?
It turned out to be a simple mistake from my side. In my SQL script, I had the following as the very first line:
DROP ALL OBJECTS DELETE FILES
This was causing the issue. As soon as I removed this line, my migrations started to run!