Why is flyway migrating a previous version of a SQL query that no longer exists instead of my current updated one?

Question

I am trying to add migrations to a mysql database using flyway. I am starting from scratch with a fresh database by using mvn flyway:clean and then migrating using mvn flyway:migrate, then I run my program. I get the following tables:

• flyway_schema_history
• trivias

which follows an old schema that I have now scrapped

I expect the tables to be created when I migrate:

• flyway_schema_history
• games

following my migration file V1__Create_games_table.sql

Despite the flyway_schema_history correctly detecting the V1 migration: flyway info

Database: jdbc:mysql://127.0.0.1:3306/trivia_database (MySQL 8.0)
Schema version: 1

+-----------+---------+--------------------+------+---------------------+---------+----------+
| Category  | Version | Description        | Type | Installed On        | State   | Undoable |
+-----------+---------+--------------------+------+---------------------+---------+----------+
| Versioned | 1       | Create games table | SQL  | 2023-08-04 11:47:11 | Success | No       |
+-----------+---------+--------------------+------+---------------------+---------+----------+

Here is my application.properties for reference:

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/trivia_database
spring.datasource.username=root
spring.jpa.database-platform=org.hibernate.dialect.MySQL8Dialect
spring.jpa.hibernate.ddl-auto=update

spring.profiles.active=development

# Flyway configuration
spring.flyway.locations=classpath:db/migration

and flyway.conf:

flyway.url=jdbc:mysql://127.0.0.1:3306/trivia_database
flyway.user=root
flyway.locations=classpath:src/main/resources/db/migration
flyway.cleanDisabled=false

1. I have tried recreating my database completely using MySQL Workbench with SQL queries DROP and CREATE, then using mvn flyway:migrate, I expect that the reset of the database would just clean any metadata involved in previous migrations included in the schema, however I get the same result. A table of an old version 1 is created instead of the new one.

Is there a hidden metadata somewhere that saved that old V1 version that created a trivias table and the cleaning resets to that version instead of the current V1 that creates a games table?

2. I have tried repairing and validating the schema with mvn flyway:repair/validate as well as adding flyway.validateOnMigrate=true in my flyway.conf file and still getting the same tables 'trivias' instead of 'games'

Answer 1

You have configured JPA to create the database schema from the entities with

spring.jpa.hibernate.ddl-auto=update

Solution

You should remove this property as you are using Flyway database migration tool.