Flyway: Found non-empty schema(s) "public" without schema history table! Use baseline() - on Empty database

Question

I am trying to configure flyway with kotlin Spring boot, jpa and postgreSQL. My gradle dependencies are:

dependencies {
    implementation('org.springframework.boot:spring-boot-starter-data-jpa')
    implementation('org.springframework.boot:spring-boot-starter-web')
    implementation('com.fasterxml.jackson.module:jackson-module-kotlin')
    implementation('org.flywaydb:flyway-core')
    implementation('com.google.code.gson:gson')
    implementation("org.jetbrains.kotlin:kotlin-stdlib-jdk8")
    implementation("org.jetbrains.kotlin:kotlin-reflect")
    runtimeOnly('org.postgresql:postgresql')
    testImplementation('org.springframework.boot:spring-boot-starter-test')
}

My application.properties file is:

spring.datasource.driverClassName=org.postgresql.Driver
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect
spring.datasource.url=jdbc:postgresql://${JDBC_DATABASE_URL}/jpaTestDatabase
spring.datasource.username=${JDBC_DATABASE_USERNAME}
spring.datasource.password=${JDBC_DATABASE_PASSWORD}

flyway.baseline-on-migrate=true
flyway.locations=classpath:src/main/kotlin/db/migration

spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=validate
spring.session.store-type=none

Creating tables and entries using jpa and hibernate works as expected. However a sample migration on an empty database results in:

org.springframework.beans.factory.BeanCreationException: 
Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: 
Invocation of init method failed; nested exception is org.flywaydb.core.api.FlywayException: 
Found non-empty schema(s) "public" without schema history table! Use baseline() or set baselineOnMigrate to true to initialize the schema history table.

my directory structure is the default one generated by spring initializr and my migrations are in: demo/src/main/kotlin/db/migration

I only have a single migration which is the kotlinized version of the example migration found here which I adapted to look line this:

class V1__Sample : BaseJavaMigration() {
  override fun migrate(context: Context?) {
    val statement = context?.connection?.prepareStatement(
      """
        CREATE TABLE article (
          id bigserial primary key,
          name varchar(20) NOT NULL,
          desc text NOT NULL
        );
      """
    )
    statement.use { it?.execute() }
  }
}

What am I missing here? Why does Flyway keep complaining about finding non-empty schema(s) "public" without schema history table, when the database is completelly empty (clean docker image)?

Alien · Accepted Answer · 2018-11-06T13:00:36.717

95

Assuming that you are using spring-boot version 2.

In spring boot 2 the prefix is "spring.flyway" so try adding prefix spring like below.

spring.flyway.baseline-on-migrate = true

OR

spring.flyway.baselineOnMigrate = true

edited Nov 06 '18 at 13:00

answered Nov 06 '18 at 12:46

Alien

15,141
6
37
57

6

+1. This can also be done using environment variables `FLYWAY_BASELINE_ON_MIGRATE=true`. See https://flywaydb.org/documentation/configuration/parameters/baselineOnMigrate. – Aamir Aug 24 '21 at 16:18

score 3 · Answer 2 · answered Mar 27 '21 at 10:25

3

may be you can try mvn flyway:clean && mvn flyway:migrate

answered Mar 27 '21 at 10:25

Zhe Niu

31
1

with settings - ```mvn flyway:migrate -Dflyway.url=jdbc:postgresql://localhost:5432/schema -Dflyway.user=usr -Dflyway.password=pswd``` – Bechyňák Petr Mar 08 '22 at 13:12

score 1 · Answer 3 · answered Aug 05 '22 at 14:58

Please check the search path of your database, if the public schema (on which flyway is creating its log tables) is not in the first place, it may not be able to find the log table and may complain that schema history is not found...

Please note that, if you are baselining, you need to remove old scripts from the scripts folder, else it will re-attempt it.

score 0 · Answer 4 · answered Jun 20 '23 at 09:16

The same error message can also appear when you still have stuff in the recycle bin. After emptying our schema, we still had the debris in the recycle bin and also got the error:

Found non-empty schema(s) "public" without schema history table! Use baseline() or set baselineOnMigrate to true to initialize the schema history table.

After purging the recycle bin the error disappeared. This was on an Oracle 19c database with Flyway 9.5.1.

Rib47 · Answer 5 · 2023-08-11T15:27:11.573

I need to clean up local Postgres DB. So I dropped all tables and views in my DB and then tried to run application. But then I got FlyWay error described in this question.

The only difference is that I have 40+ FlyWay migrations to be processed after DB cleanup.

Unfortunately property from accepted answer didn't help "as is" as FlyWay is trying to create its "baseline" migration with number #1, but I already have own migration with number #1. After a small investigation I found the cure:

spring.flyway.baseline-on-migrate = true
spring.flyway.baseline-version = 0

Now application started with no errors and all migrations processed successfully.

UPDATE
Better way to cleanup local DB with FlyWay and Spring is to change some checksum in table "flyway_schema_history" to provoke FlyWay error, then add properties below and then run your application.

spring.flyway.clean-on-validation-error = true
spring.flyway.clean-disabled = false

FlyWay should detect error and clean up all data in your DB

Flyway: Found non-empty schema(s) "public" without schema history table! Use baseline() - on Empty database

5 Answers5