Flyway: non-empty schema without metadata table

Question

Found non-empty schema "public" without metadata table! Use init() or set initOnMigrate to true to initialize the metadata table.

I'm using Postgres 9.2 with Postgis 2.0. This means that by default when I create a new database there will be a table created in public schema called spatial_ref_sys.

When I run flyway migrate on this database, I get the above error. Running init seems to create the public.schema_version table and mark version 1 as SUCCEDED without actually running the the migration file. I've also tried combinations of initOnMigrate with no success. Flyway is not configured to manage any schemas.

Any ideas on how I can run a migration in this scenario?

I've altered the title of the question even further to simply state the error message. The original title "Migrate a virgin database causes errors" was simply incorrect as stated in the comments below. — markdsievers
– markdsievers, Commented Apr 10, 2017 at 9:22

Axel Fontaine · Accepted Answer · 2017-05-19 04:59:49Z

30

The title is somewhat contradictory, as the database is indeed not virgin as you installed, through the PostGIS extension, a number of objects in the public schema.

You can either

set flyway.schemas to a new schema, say my_app, which will then be created automatically by Flyway. Your application should then use this one instead of public (recommended)
set flyway.baselineOnMigrate to true or invoke flyway.baseline() against the public schema. This will work, but public will then contain a mix of both your application objects and the PostGIS objects

edited May 19, 2017 at 4:59

answered Aug 30, 2014 at 8:31

Axel Fontaine

35.4k19 gold badges110 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

markdsievers Over a year ago

Fair point, altered the title and content to reflect the caveat. Thanks for the answers again Axel, will hit it again on Monday and come back with my results.

markdsievers Over a year ago

As an aside, if you have any comments on this Flyway / EJB integration question, I'd love to hear any of your comments.

markdsievers Over a year ago

What I have done is added flyway to flyway.schemas so schema_version is completely isolated. We have ~7 schemas at the moment, so in reference to your answer on my previous question I am going with the "nothing" approach. With this setup, migrating a virgin* database works. On an existing database however, init -initVersion=2 will work as desired but migrate -initOnMigrate=true -initVersion=2 will try to run all migrations. This seems contradictory to the documentation.

markdsievers Over a year ago

I've added an issue on github regarding initOnMigrate and also contributed a work in progress patch in the comments section for further discussion.

user2964500 Over a year ago

Using JPA, you can tell your entities about the new schema by the "schema" property. e.g. @Table(name = "posts", schema = "blog").

|

Vic Seedoubleyew · Accepted Answer · 2019-05-27 14:03:13Z

If you are using Gradle you can run

./gradlew -Dflyway.schemas=public flywayClean flywayMigrate

Where public is the name of the database containing the schema_versions table. That should delete the table and metadata as well as running the migrations to get it back up to date.

Caution!

This will delete all data in public schema

Alien · Accepted Answer · 2018-11-06 12:42:55Z

I think this error comes only with latest version of Flyway i.e. above 4.03. I didn't received in the earlier project but got it when I am using Flyway version 5.07 in my latest project. Putting the code here that resolve my issues

public class FlywayConfig { @Autowired DataSource dataSource; @Autowired Config config; @Bean public Flyway flyway(){ Flyway flyway = new Flyway(); flyway.setDataSource(dataSource); flyway.setSqlMigrationPrefix("V"); flyway.setLocations(new String[] { config.getSqlLocation() }); flyway.setBaselineOnMigrate(true); // *******************flyway.clean(); ********************// this will wipe out the DB, be careful flyway.migrate(); return flyway; } }

Vicentex360 · Accepted Answer · 2019-10-15 00:23:51Z

this work for me , i were figthin with the same problema a lot of time

my project was building on maven

Flyway flyway = new Flyway(); flyway.setDataSource(dataSource); flyway.setLocations("db/your_db"); flyway.setTable("name_of_schema");

next a added this line

flyway.setBaselineOnMigrate(true); flyway.clean();

next this lines

 MigrationInfo migrationInfo = flyway.info().current(); flyway.migrate();

and i let you the URL of my references from flyway.org Flyway.org/documentation/commandline/baseline

moji · Accepted Answer · 2020-01-20 13:52:12Z

In my case the problem started when I deleted all the rows in the table myschema.schema_version

./gradlew flywayInit did the trick and the error is not showed anymore.

Collectives™ on Stack Overflow

Flyway: non-empty schema without metadata table

5 Answers 5

6 Comments

Caution!

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

6 Comments

Caution!

Comments

Comments

Comments

Comments

Linked

Related