Caused by: org.flywaydb.core.api.FlywayException: Validate failed. Migration Checksum mismatch for migration 2

Question

I tried to find the solution for the below problem, but none of them worked for me. I am developing Angular + Spring Boot application using MySQL + flyway. Please guide whats going wrong here.

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: Validate failed. Migration Checksum mismatch for migration 2
-> Applied to database : 1499248173
-> Resolved locally    : -1729781252
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1578) ~[spring-beans-4.2.4.RELEASE.jar:4.2.4.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:545) ~[spring-beans-4.2.4.RELEASE.jar:4.2.4.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:482) ~[spring-beans-4.2.4.RELEASE.jar:4.2.4.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306) ~[spring-beans-4.2.4.RELEASE.jar:4.2.4.RELEASE]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230) ~[spring-beans-4.2.4.RELEASE.jar:4.2.4.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302) ~[spring-beans-4.2.4.RELEASE.jar:4.2.4.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197) ~[spring-beans-4.2.4.RELEASE.jar:4.2.4.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:296) ~[spring-beans-4.2.4.RELEASE.jar:4.2.4.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197) ~[spring-beans-4.2.4.RELEASE.jar:4.2.4.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1054) ~[spring-context-4.2.4.RELEASE.jar:4.2.4.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:829) ~[spring-context-4.2.4.RELEASE.jar:4.2.4.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:538) ~[spring-context-4.2.4.RELEASE.jar:4.2.4.RELEASE]
    at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:118) ~[spring-boot-1.3.1.RELEASE.jar:1.3.1.RELEASE]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:764) [spring-boot-1.3.1.RELEASE.jar:1.3.1.RELEASE]
    at org.springframework.boot.SpringApplication.doRun(SpringApplication.java:357) [spring-boot-1.3.1.RELEASE.jar:1.3.1.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:305) [spring-boot-1.3.1.RELEASE.jar:1.3.1.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1124) [spring-boot-1.3.1.RELEASE.jar:1.3.1.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1113) [spring-boot-1.3.1.RELEASE.jar:1.3.1.RELEASE]
    at com.boot.App.main(App.java:9) [classes/:na]
Caused by: org.flywaydb.core.api.FlywayException: Validate failed. Migration Checksum mismatch for migration 2
-> Applied to database : 1499248173
-> Resolved locally    : -1729781252
    at org.flywaydb.core.Flyway.doValidate(Flyway.java:1108) ~[flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.Flyway.access$300(Flyway.java:62) ~[flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:1012) ~[flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:1006) ~[flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.Flyway.execute(Flyway.java:1418) ~[flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.Flyway.migrate(Flyway.java:1006) ~[flyway-core-3.2.1.jar:na]
    at org.springframework.boot.autoconfigure.flyway.FlywayMigrationInitializer.afterPropertiesSet(FlywayMigrationInitializer.java:66) ~[spring-boot-autoconfigure-1.3.1.RELEASE.jar:1.3.1.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1637) ~[spring-beans-4.2.4.RELEASE.jar:4.2.4.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1574) ~[spring-beans-4.2.4.RELEASE.jar:4.2.4.RELEASE]
    ... 18 common frames omitted

application.properties

logging.level.org.springframework.web=DEBUG

server.port=8080

spring.h2.console.enabled=true
spring.h2.console.path=/h2

## For H2 DB
#spring.datasource.url=jdbc:h2:file:~/dasboot
#spring.datasource.username=sa
#spring.datasource.password=
#spring.datasource.driver-class-name=org.h2.Driver

## For MYSQL DB
spring.datasource.url=jdbc:mysql://localhost:3306/dasboot
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

spring.datasource.max-active=10
spring.datasource.max-idle=8
spring.datasource.max-wait=10000
spring.datasource.min-evictable-idle-time-millis=1000
spring.datasource.min-idle=8
spring.datasource.time-between-eviction-runs-millis=1

flyway.baseline-on-migrate=true
spring.jpa.hibernate.ddl-auto=false;

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


datasource.flyway.url=jdbc:mysql://localhost:3306/dasboot
datasource.flyway.username=root
datasource.flyway.password=root
datasource.flyway.driver-class-name=com.mysql.jdbc.Driver

pom.xml

<parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.3.1.RELEASE</version>
    </parent>

    <name>das-boot</name>
    <url>http://maven.apache.org</url>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
        </dependency>
        <dependency>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-core</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
    </dependencies>

V2__create_shipwreck.sql

-- For H2 DB
--CREATE TABLE SHIPWRECK(
--  ID INT AUTO_INCREMENT,
--  NAME VARCHAR(255),
--  DESCRIPTION VARCHAR(2000),
--  CONDITION VARCHAR(255),
--  DEPTH INT,
--  LATITUDE DOUBLE,
--  LONGITUDE DOUBLE,
--  YEAR_DISCOVERED INT
--);

CREATE TABLE `dasboot`.`shipwreck` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `NAME` VARCHAR(255) NULL,
  `DESCRIPTION` VARCHAR(2000) NULL,
  `CONDITION` VARCHAR(255) NULL,
  `DEPTH` INT NULL,
  `LATITUDE` DOUBLE NULL,
  `LONGITUDE` DOUBLE NULL,
  `YEAR_DISCOVERED` INT NULL,
  PRIMARY KEY (`ID`));

Answer 1

Flyway is comparing the checksum of the SQL script with that of the previously run checksum. This exception typically occurs if you change a SQL script that has already been applied by Flyway, thus causing a checksum mismatch.

If this is development, you can drop your database and start the migrations from scratch.

If you're in production, never edit SQL scripts that have already been applied. Only create new SQL scripts going forward.

Answer 2

Here the solution which worked for me when I had this issue in my local system.

1. Go to flyway_schema_history in your DB
2. Delete the row containing the sql migration script

Answer 3

I had the same issue and deleted the complete schema from the database, yet the issue remained.

I solved this by running the repair() command of flyway:

flyway.repair();

Alternatively with Flyway Maven plugin:

mvn flyway:repair

Maven plugin addition into pom.xml:

<plugin>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <version>5.2.4</version>
</plugin>

BTW: I did not find what exactly went wrong.

With Gradle (as per comment from Raf):

./gradlew flywayRepair

Answer 4

The best solution would be to do these steps :

1. Delete the file called - V2__create_shipwreck.sql, clean and build the project again.

2. Run the project again, login into h2 and delete the table called "schema_version".

   drop table schema_version;

3. Now make V2__create_shipwreck.sql file with ddl and rerun the project again.

4. Do remember this, add version 4.1.2 for flyway-core in pom.xml like

   <dependency>
       <groupId>org.flywaydb</groupId>
       <artifactId>flyway-core</artifactId>
       <version>4.1.2</version>
   </dependency>
   

It should work now. Hope this will help.

Answer 5

I would simply delete from schema_version the migration/s that deviates from migrations to be applied. This way you don't throw away any test data that you might have.

For example:

SELECT * from schema_version order by installed_on desc

V_005_five.sql
V_004_four.sql
V_003_three.sql
V_002_two.sql
V_001_one.sql

Migrations to be applied

V_005_five.sql
* V_004_addUserTable.sql *
V_003_three.sql
V_002_two.sql
V_001_one.sql

Solution here is to delete from schema_version

V_005_five.sql
V_004_four.sql

AND revert any database changes caused. for example if schema created new table then you must drop that table before you run you migrations.

when you run flyway it will only re apply

V_005_five.sql
* V_004_addUserTable.sql *

new schema_version will be

V_005_five.sql
* V_004_addUserTable.sql *
V_003_three.sql
V_002_two.sql
V_001_one.sql

Hope it helps

Answer 6

If you are sure that the modification in the SQL file has no impact on your existing schema, you can also update the checksum of the existing schema.

I did this following a slight change in the sql file.

Here is how I updated the checksum:

update flyway_schema_history set checksum = '-1934991199' where installed_rank = '1';

Answer 7

IF YOU ARE NOT IN PRODUCTION, you can look in your flywayTable in the data base and remove the line which contain the name of the script which has been applied.

flywayTable is a project option which define the name of the table in the db used by flyway which contain information about the version of this db, already applied scripts...

Answer 8

Flyway changed the way it calculates the checksums from version 3 to version 5. You can re-calculate the checksums. Since the Flyway plugin doesn't properly read the Spring datasource properties, you have to manually specify them on the command line (or one of the other various ways Flyway accepts).

mvn flyway:repair -Dflyway.user=root -Dflyway.password= -Dflyway.url=jdbc:mysql://localhost:3306/mydatabase -Dflyway.table=schema_version

Flyway also changed the table it stores the checksums, so you also have to specify flyway-table=schema_version to use your old table, or else it will give you a warning (and probably an error in version 6).

[INFO] Repairing Schema History table for version 2 (Description: create sources, Type: SQL, Checksum: 2125962141)  ...
[INFO] Repairing Schema History table for version 3 (Description: create stats, Type: SQL, Checksum: 389912194)  ...
[INFO] Repairing Schema History table for version 4 (Description: add user encrypted, Type: SQL, Checksum: 182607572)  ...

Answer 9

use this query in your local DB.

1. select * from schema_version delete from schema_version where checksum Column = -1729781252;

   Note: -1729781252 is the "Resolved locally" value.

2. Build and start the server.

Answer 10

What I did when faced this issue, was connect to DB and update the checksum field correspondent to the mistaken version, putting there the value resolved locally by FlyWay.

For the following error:

nested exception is org.flywaydb.core.api.FlywayException: Validate failed.
Migration Checksum mismatch for migration 1.12
    -> Applied to database : 1029320280
    -> Resolved locally    : -236187247

I simply did this:

UPDATE schema_version SET checksum = -236187247 WHERE version_rank = 12 AND checksum = 1029320280;

And problem solved..

NOTE: You have to be sure your schema is actually correct, check your tables and their structure, if everything is OK, then you can apply this solution; otherwise, you should repair your schema manually first using plain and native SQL.

Answer 11

I had the same issue and I believe this occurred because of checksum between linux and windows (also mac). you can use repair() command in flyway.

flyway.repair();

Be careful , if you are in production environment , make sure that you did not change the migration SQL file; because when you run the flyway.repair(); it means you saying to flyway that your are sure about the migration version and their checksum and then the flyway repair them !

Answer 12

Just add

spring.flyway.enabled=false

in application.properties file if you do not want flyway to check the checksum every time you run the application.

Answer 13

After your .sql applied to DB, you edited the .sql, flyway may show this error.

If using Postgresql, a quick solution

After dropping tables "flyway_schema_histrory" and the table my .sql created, the error disappeared. delete table

Answer 14

Update your schema_version record to mach the "Resolved locally" value which in your case is -1729781252

Answer 15

Actually there is another solution, but it's a workaround, that should not be done in a properly managed project. However I met a situation, where it was not possible to go down the better road :)

You can update the schame_version table, and actually change the checksum to the new one. This will cause the migration to go through, but can have other side effects.

When deploying to different environments (test, uat, prod, etc) then it might happen, that you have to update the same checksum on more environments. And when it comes to gitflow, and release branches, you can easily mix up the whole.

Answer 16

simple solution will be change spring.datasource.url=jdbc:h2:file:~/dasboot in application.properties to new file name like : spring.datasource.url=jdbc:h2:file:~/dasboots

Answer 17

1-Delete the migration file. 2-connect to your database and drop the table created by the migration. 3-recreate the file of the migration with the the right sql.

Answer 18

If you have your problem in production, you must have V2__create_shipwreck.sql identically to the one you have in your latest version where it has not been modified.

Then the checksum will be correct again

Answer 19

I had the same issue and tried all the options (delete schema, delete specific row, update checksum) but nothing works.

In my case flyway-core dependency got corrupted somehow.

Solution:

1. Delete flyway-core folder from ~.m2\local repository path...\org\flywaydb\flyway-core.

2. Execute "maven clean install" so that it will download new one and add it into a project.

I hope this will help.

Answer 20

There are 3 ways to solve this issue while development. Any one from below can solve this issue.
1) provide the changes in new migration sql file with incrementing version
2) change the schema name in db url we provide
datasource.flyway.url=jdbc:h2:file:~/cart3
datasource.flyway.url=jdbc:h2:file:~/cart4
3) delete the .mv and .trace files in users home directory
ex: cart3.mv and cart3.trace under c://users/username/

Answer 21

just don't validate:

flyway.setValidateOnMigrate(false);

Answer 22

I fixed this error with these three steps:

1. In your database, go to the flyway_schema_history table
2. Delete the last row containing the most recent flyway migration script ("V6__xxx" or whatever)

Start your application again. I got another flyway error message that was kind of a dump of SQL to insert some rows in some tables.

3. Go to those tables and delete those new rows.

I think the flyway migration script was trying to run again, but it had already run once, was trying to do the same thing again - insert the same rows, and that was the final conflict/error. Deleting those rows allowed it to run normally/successfully