CODEWITHSUNDEEP
spring-bootflyway
pbount
pbount

Reputation: 1803

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

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)?

Upvotes: 60

Views: 94136

Answers (6)

Yousra ADDALI
Yousra ADDALI

Reputation: 412

Flyway basically refuses to migrate on a non empty database for which it does not have a schema history table. To overcome this issue, we need to provide a baseline rto flyway. a baseline is a way to tell flway that don’t care what happended till this version and apply any changes above this version. With spring config we achieve this by adding below property to your application.

spring:
  flyway:
    baseline-on-migrate: true
    baseline-version: '0'

Upvotes: 1

Rib47
Rib47

Reputation: 2546

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

Upvotes: 5

Maarten Folkers
Maarten Folkers

Reputation: 101

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.

Upvotes: 0

Adeeb
Adeeb

Reputation: 51

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.

Upvotes: 1

Zhe Niu
Zhe Niu

Reputation: 41

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

Upvotes: 4

Alien
Alien

Reputation: 15908

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

Upvotes: 106

Related Questions