Flyway setup issues

Question

I am trying to create a simple database migration in Intellij with some dummy tables using flyway.

My dummy tables are placed in "resources/db/migration", and have the format of "V1_0__initial_display.sql".

They might contain things like this:

 CREATE TABLE Fake (
 ID int,
 name varchar(255)
 );

 CREATE TABLE Persons(
 ID int,
 name varchar (30)
 );

 INSERT INTO Persons(ID, name)
 VALUES(1,'AXC');

 INSERT INTO Persons(ID, name)
 VALUES(2,'BB');

My application.properties:

 spring.jpa.hibernate.ddl-auto=validate
 spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
 spring.jpa.show-sql=true
 spring.logging.level.org.hibernate.SQL=debug
 spring.flyway.enabled=true
 spring.flyway.user=${db_username}
 spring.flyway.password=${db_password}
 spring.flyway.url=${database}
 spring.flyway.locations=classpath:/db/migration/V1_0__initial_display.sql

The relevant part of my gradle.build:

dependencies {
implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
implementation 'org.springframework.boot:spring-boot-starter-thymeleaf'
implementation 'org.springframework.boot:spring-boot-starter-web'
implementation 'org.flywaydb:flyway-core'
runtimeOnly 'org.springframework.boot:spring-boot-devtools'
runtimeOnly System.getenv("database")
runtimeOnly 'mysql:mysql-connector-java'
compileOnly 'org.projectlombok:lombok'
testImplementation 'org.springframework.boot:spring-boot-starter-test'
compile "org.flywaydb:flyway-core:5.2.4" +
        ""
flywayMigration "com.xy.z"
}

flyway {
schemas = ['fake', 'persons']
placeholders = [
        'keyABC'          : 'valueXYZ',
        'otherplaceholder': 'value123'
]



url = (System.getenv("database"))
user = (System.getenv("db_username"))
password = (System.getenv("db_password"))
}

The format of my env. variable named database:

jdbc:mysql://localhost/xy

The SpringBoot application itself:

@SpringBootApplication

public class XyApplication {

public static void main(String[] args) {

SpringApplication.run(XyApplication.class, args);
System.out.println("Hello World!");
Flyway flyway = Flyway.configure().dataSource(System.getenv("database"), 
System.getenv("db_username"), System.getenv("db_password")).load();
flyway.clean();
flyway.baseline();
flyway.migrate();   
 }  
}

The error message:

Caused by: org.flywaydb.core.api.FlywayException: Found non-empty schema(s) 
`xy` without schema history table! Use baseline() or set 
baselineOnMigrate to true to initialize the schema history table.

I've recently deleted flyway_schema_history from my database. I think the main problem is with that, and I'm trying to initialize it. Unfortunately this is not the only problem: My implementation had not worked even before I deleted this table, so there might be more conceptual problems.

Could you help me out? How should I solve this issue?

Answer 1

Flyway expects to be the first thing running on a schema. The right way to handle this is to delete everything in the xy schema locally and build everything using the migration files (.sql). If you have anything in the DB you don't want to delete you can use the baseline flag in the call to indicate that you aware of the data in the table.

Usually, FlyWay and other migration tools like this are used to allow developers creating the same schemas in different DBs (like Dev and Production, or different developers machines). If you already have a table in xy which didn't come from FlyWay, you might use it in your code and it will work, but the production and other developers will not have this table.