Скачать или смотреть Resolving the Flyway Migration Mystery: Why Old Tables Keep Reappearing

Discover how to troubleshoot and correctly configure `Flyway` migrations to prevent old tables from reappearing in your MySQL database.
---
This video is based on the question https://stackoverflow.com/q/76832553/ asked by the user 'Rigene Salang' ( https://stackoverflow.com/u/22312931/ ) and on the answer https://stackoverflow.com/a/76833802/ provided by the user 'Mar-Z' ( https://stackoverflow.com/u/14294053/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Why is flyway migrating a previous version of a SQL query that no longer exists instead of my current updated one?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Introduction

If you’ve ever faced an issue where an outdated SQL query keeps reappearing in your database, even after you've tried to clean it, you’re not alone. A common error arises when using Flyway—a database migration tool—alongside JPA (Java Persistence API) in Spring Boot applications. This guide will explore why your previous tables seem to return and how you can gracefully update to the latest schema version using Flyway.

The Problem

You're starting fresh with a MySQL database, intending to migrate using Flyway starting with a clean slate. After running the commands mvn flyway:clean and mvn flyway:migrate, you expect to see a new games table, but instead, you find the old trivias table reappearing.

Key Details from Your Scenario:

Current Migration File: V1__Create_games_table.sql

Expected Tables after Migration:

flyway_schema_history

games

Existing Tables after Migration:

flyway_schema_history

trivias

Flyway Configuration: Correctly set to point to your migration files.

Solution Overview

The root of your problem lies in a conflicting configuration that inadvertently interferes with Flyway's process.

Configuration Culpability

In your application.properties file, you've enabled a setting for JPA that automatically updates the database schema:

[[See Video to Reveal this Text or Code Snippet]]

This setting can create or modify tables based on your entity classes. When this setting is active, it generates the tables defined within your Java code rather than strictly adhering to your Flyway migrations. Hence, the trivias table gets re-created based on the last schema saving the configuration.

Step-by-Step Solution

Remove the Contradictory Setting:

Go to application.properties and comment out or delete the line:

[[See Video to Reveal this Text or Code Snippet]]

Ensure Clean Usage of Flyway:

After removing the above setting, run the following commands:

[[See Video to Reveal this Text or Code Snippet]]

This action enables Flyway to take full control of your database schema without interference.

Validation Post-Migration:

After migrating, you can check the current state of migrations using:

[[See Video to Reveal this Text or Code Snippet]]

This command will confirm whether Flyway has registered and executed your new migration.

Additional Tools

Repairing Flyway: If you are still encountering issues, consider using:

[[See Video to Reveal this Text or Code Snippet]]

This command can fix potential metadata issues in the Flyway schema history.

Validation Checks: Adding this to your flyway.conf can ensure Flyway validates the migrations before executing:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

In summary, the unexpected reappearance of older tables during your Flyway migration process can often be traced back to overlapping configurations with JPA. By carefully managing settings and ensuring Flyway's configurations are upheld without conflicting declarations, you can achieve a clean and effective database migration.

By following the steps outlined above, you can successfully migrate your SQL schema and ensure your database reflects the latest version as designated by your migration scripts.