Flyway 3.0 Migration Checksum mismatch

Question

after upgrading Flyway Maven plugin from 2.3 to 3.0 I get:

[ERROR] Failed to execute goal org.flywaydb:flyway-maven-plugin:3.0:migrate (default-cli) on project xxx: org.flywaydb.core.api.FlywayException: Validate failed. Found differences between applied migrations and available migrations: Migration Checksum mismatch for migration V003__data_feed_sources_locations.sql: DB=942424992, Classpath=1117634405 -> [Help 1]

Got a similar error on some other project.

If I downgrade back to 2.3 the migration runs ok. Does this has something to do with different platform encoding for calculating checksums?

Any workaround, or better yet, proper solution?

if anyone on spring boot app, then please add in app.yml this line else it won't work `spring.flyway.validateOnMigrate: false` and you can still use latest flyway version no repairing nothing needed. — NeverGiveUp161, Sep 19 '19 at 09:15

score 112 · Accepted Answer · edited Oct 25 '21 at 18:52

112

Flyway 3.0 changed the default of validateOnMigrate to true.

This is however a good thing, as in the spirit of fail fast, errors are discovered sooner.

In your case some scripts did change since they were applied, which is what Flyway is reporting.

You have two options:

suppress the error by setting validateOnMigrate to false (2.3 default behavior)
invoke Flyway.repair() to reallign the checksums

Reference
Flyway Repair

edited Oct 25 '21 at 18:52

ThangLeQuoc

2,272
2
19
30

answered May 21 '14 at 08:50

Axel Fontaine

34,542
16
106
137

2

Hi, I am reading http://flywaydb.org/documentation/maven/repair.html and there is indeed a line saying "Correct wrong checksums", but when I run the "mvn flyway:repair" on 2.3 or 3.0 version of the plugin I get: "Repair of metadata table `xyz`.`schema_version` not necessary. No failed migration detected.". Is there a hidden option or command line switch to recalculate hashes? – bbcooper May 21 '14 at 11:05
1

It should have repaired the checksums anyway. Flyway 3.0 is missing a message to tell you so. The message will be included in 3.1. – Axel Fontaine May 21 '14 at 11:11
Before running the repair goal I checked and saved the checksum value from the schema_version flyway db table to verify the checksum will be modified, but it was not. – bbcooper May 21 '14 at 11:16
OK. Please file an issue with the exact steps to reproduce and we'll get it fixed for 3.1. – Axel Fontaine May 21 '14 at 11:17
I tried repair option but it did not worked for me, I too got "Repair of metadata table xyz.schema_version not necessary. No failed migration detected." So I set validateOnMigrate to false and it worked. – Pratik Goenka Nov 06 '14 at 14:13
4

If you are starting the application from the command line, use the flag `--flyway.validate-on-migrate=false` – Pedro Madrid Jun 12 '17 at 22:27
2

@PedroMadrid That's a Spring Boot flag, not a Flyway flag. Don't confuse them! – Axel Fontaine Jun 13 '17 at 07:09
if anyone on spring boot app, then please add in app.yml this line else it won't work `spring.flyway.validateOnMigrate: false` and you can still use latest flyway version no repairing nothing needed. – NeverGiveUp161 Sep 19 '19 at 09:15

score 24 · Answer 2 · edited Mar 14 '17 at 20:20

24

To add to Axel Fontaine's answer:

I was able to use mvn flyway:repair but I had to point the flyway.locations config property at the folder that contains my db migration scripts. Otherwise I would get the message "Repair of metadata table xyz.schema_version not necessary. No failed migration detected." like other folks mentioned.

I used mvn -Dflyway.locations=filesystem:<project dir>/src/main/resources/db/migrations flyway:repair and I saw the checksum updated in the metadata table, fixing my problem.

edited Mar 14 '17 at 20:20

Axel Fontaine

34,542
16
106
137

answered Dec 18 '14 at 23:02

tfendall

261
2
4

With a bare flyway run use something similar to this: FLYWAY_LOCATIONS="filesystem:/path/to/migrations/sql" /usr/local/bin/flyway migrate – Samuel Åslund Dec 15 '19 at 09:37

score 9 · Answer 3 · answered Mar 16 '18 at 14:55

9

I found the easiest way to resolve this issue was to literally update the checksum in the schema table to the value flyway expected. I knew for a fact that my migration files had not changed and that the current state of the database was what it needed to be. I also did not want to spend time reading documentation and messing around with Flyway.repair() or other methods that could potentially mess things up even more. A simple sql update resolved it right away

answered Mar 16 '18 at 14:55

aarbor

1,398
1
9
24

We do something similar for our deploys. There is a bit of code in startup that checks to see if certain checksums for specific version migration files are not what we want and repairs them on the fly before startup. We've had to do this in the past when we discovered a bug in an older migration script that only hits edge cases. We update the script, add this little bit of code to fix the checksum for existing systems so that future migrations don't have issues. Probably not the best way but it works for us. – K.Niemczyk Mar 07 '19 at 13:38

score 9 · Answer 4 · answered Apr 25 '18 at 07:20

First, it looks for checksum changes. These changes occur if we update migration files which are already applied to a db instance.

FlywayException: Validate failed: Migration checksum mismatch for migration version 18.2.6

-> Applied to database : 90181454

-> Resolved locally : 717386176

repair() method would fix up checksum issue by updating the flyway_schema_history table with local checksum value.

However, it would neglect updated statements in same migration file. So, new changes in same file would be neglected as there is already an entry for version in flyway_schema_history table. setValidateOnMigrate() method has no effect in this scenario. We should follow incremental approach, schema changes should be supplied through new files.

Arar · Answer 5 · 2016-04-25T03:59:09.823

The issue happen right after I changed the V1_2__books.sql ddl file, There should be a better way to force flyway to recognize the new changes!!!

I tried to run mvn flyway:repair but it did not work, I ended up changing the schema url in the application.properties file [datasource.flyway.url] to books2

I removed the below files as well (books is my old schema name )

~ @~:rm books.mv.db 
~ @~:rm -r books.trace.db

datasource.flyway.url=jdbc:h2:file:~/books2
datasource.flyway.username=sa
datasource.flyway.password=
datasource.flyway.driver-class-name=org.h2.Driver

I ran the application and BINGO :)

score 1 · Answer 6 · answered Jul 23 '18 at 07:25

Just wanted to add, that in order for the checksum to be updated by repair. Flyway has to have access to the directory where all the migrations are. Otherwise flyway just goes about it's business and outputs

"Repair of failed migration in metadata table xyz.schema_version not necessary. No failed migration detected."

score 1 · Answer 7 · answered May 06 '20 at 12:38

The checksum needs to be updated using the flyway repair command (run the Flyway command as stated in “Upgrade procedure” but replace “migrate” with “repair”).

I recommend you do not intrude directly to database, sql scripts, etc. It can be dangerous

Example:
./flyway repare -user=root -password=changeme -url=jdbc:mysql://localhost/mypath -table=my_flyway_schema_version_table -locations=filesystem:/mypath_sql_scripts

score 1 · Answer 8 · answered Sep 07 '20 at 08:28

1

if you are running on local db u can delete the flyway_schema_history table

answered Sep 07 '20 at 08:28

Kumar Shanoo

125
4

how do you delete it? where is it located exactly? – Mohamed Ali Jul 10 '21 at 17:56
mostly in you db public schema .There will be a flyway table named 'schema_version_*' – Kumar Shanoo Jul 12 '21 at 11:40

score 1 · Answer 9 · answered Jan 28 '22 at 05:52

Invoke Flyway.repair() directly from configurations.

Add below bean to your configuration class or create a new class with @Congiguration annotation and add the below code.

    @Bean
    public FlywayMigrationStrategy repairFlyway() {
        return flyway -> {
            // repair each script's checksum
            flyway.repair();
            // before new migrations are executed
            flyway.migrate();
        };
    }

score -4 · Answer 10 · answered Nov 09 '17 at 13:44

-4

There is yet another solution. You can drop your migration from schema_version table.

answered Nov 09 '17 at 13:44

Denis

3,595
12
52
86

2

Double operation will happen incase of insert, update or delete query – Yunus Einsteinium Mar 05 '18 at 07:40

Flyway 3.0 Migration Checksum mismatch

10 Answers10