Error validate failed migrations have failed validation

Flyway is a database migration tool that uses SQL scripts (which they call migrations) to create and modify the database schema and the data stored in the database. The migrations have a version number, which is used to apply them in order. When the migrations run for the first time, they are added to a schema history table, which by default is called flyway_schema_history. This table keeps a checksum for each migration, which is verified whenever the application starts up (to make sure the migration has not changed after it was applied to the database). As a general guide, we should create new migrations to make changes, instead of changing existing migrations.

This article discusses common errors that you might run into while working with Flyway and possible solutions. Flyway provides tools such as Flyway Repair, which can be used to automatically update the flyway_schema_history table. I have not been able to use that tool on my project due to security restrictions, so my solutions make changes directly to the flyway_schema_history table. I’m assuming you’re working in a development environment; some of the solutions might not be appropriate if you’re in a production environment.

I created this sample app, which can be useful for troubleshooting Flyway errors using a simple environment.

Flyway failed to initialize

Flyway failed to initialize: none of the following migration scripts locations could be found:
– classpath:db/migration

Cause #1: Your application doesn’t have any migrations yet.

Disable flyway by adding the following property to the application properties:

spring.flyway.enabled=false
Code language: JavaScript (javascript)

Cause #2: Your migrations are not in the default folder: resources/db/migration.

Specify the folder where your migration scripts are located in the application properties. For example, if the migrations are located in resources/database, use:

spring.flyway.locations=classpath:database

Migration checksum mismatch

org.flywaydb.core.api.exception.FlywayValidateException: Validate failed: Migrations have failed validation
Migration checksum mismatch for migration version X.X
-> Applied to database : 2145208862
-> Resolved locally : -1101818402. Either revert the changes to the migration, or run repair to update the schema history.

Cause: A migration that was already applied to the database has been modified. This changes the migration checksum, which is used by Flyway on startup to validate that existing have not changed, thus failing validation.

Avoid changing migrations that already ran. Any changes should be done in a new migration script.

Alternate solution when working in a development environment

If you’re working in a development environment, it might be acceptable to modify a migration after it has been applied to the database. In that case, you can fix this error by deleting the migration record from the flyway_schema_history table. This will cause the updated migration to run. Note that if the original and updated migrations have statements such as CREATE, DROP, etc. you will need to first undo the changes done by the original migration in the database or you will run into SQL exceptions.

If the changes made to the migration were just a formatting change (e.g. you added some blank lines or changed the indentation), you can update the checksum directly in the flyway_schema_history table so it matches the checksum of the updated migration, instead of running the migration script again.

Detected applied migration not resolved locally

org.flywaydb.core.api.exception.FlywayValidateException: Validate failed: Migrations have failed validation
Detected applied migration not resolved locally: X.X. If you removed this migration intentionally, run repair to mark the migration as deleted.

Cause: The code is missing a migration script that was previously applied to the database.

This can happen when multiple people are creating Flyway migrations at the same time. In that case, update your code, so it includes the migration script specified in the error message.

If the migration script was deleted on purpose, then delete the migration record from the flyway_schema_history table to fix this error. Note that deleting the migration record does not undo the changes done by the migration when it first ran.

FlywaySqlScriptException

org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException:
Migration VX.X.X__script_name.sql failed

Cause: There was a SQL error while running a migration script.

Flyway includes a detailed error message about what went wrong; the solution will vary depending on the SQL error. After you fix the migration, delete the failed migration record from flyway_schema_history and start the app again.

This is an example of an error message provided by Flyway; in this case the migration failed because it was trying to create a table that already existed:

SQL State  : 42S01
Error Code : 1050
Message    : Table 'application_user' already exists
Location   : database/V1.0__create_user_table.sql 
Line       : 1
Statement  : CREATE TABLE application_user(
id BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
user_role VARCHAR(100) NOT NULL,
date_created TIMESTAMP
)
Code language: plaintext (plaintext)

Since I test SQL scripts by running them locally, the most common SQL exceptions I’ve run into have to do with version compatibility. For example, if I’m running MySQL 8.0 locally, but the application uses MySQL 5.6 and I write a migration script that uses the ALTER statement with the rename column clause to update a column name; since the rename column clause is only available starting with MySQL 8.0, the script fails with a SQL error when executed on the older MySQL server.

Error when running a previously failed migration

org.flywaydb.core.api.exception.FlywayValidateException: Validate failed: Migrations have failed validation
Detected failed migration to version X.X (script_name). Please remove any half-completed changes then run repair to fix the schema history.

Cause: A migration failed the first time it was run (e.g. it had a SQL exception). You fixed the migration script, but run into this error when you start the app.

Since you have made changes to the script (causing the checksum to change) you will need to delete the migration record from flyway_schema_history, so the updated script runs. You can identify migrations that failed the first time they ran using the success column, which shows ‘0’.

Found more than one migration with version X.X

org.flywaydb.core.api.FlywayException: Found more than one migration with version X.X
Offenders:
-> C:flyway-projecttargetclassesdatabaseVX.X__script1.sql (SQL)
-> C:flyway-projecttargetclassesdatabaseVX.X__script2.sql (SQL)

Cause: You have two migrations with the same version.

This can happen when multiple people are creating Flyway migrations at the same time. The version number must be unique for each migration. To resolve this error, rename one of the migrations to the next available version.

Detected resolved migration not applied to database

Detected resolved migration not applied to database: X.X. To ignore this migration, set -ignoreIgnoredMigrations=true. To allow executing this migration, set -outOfOrder=true

This can happen when multiple people are creating Flyway migrations at the same time, since by default, Flyway expects the migrations to be applied in order according to the version number.

Let’s say you worked on migration version 2.0 and applied it to the database. After that another developer submits their code changes, which include a new migration version 1.4. When you deploy the application, you will get the error above since you have a migration to be applied (version 1.4), which is prior to the latest migration already applied to the database (2.0).

If you’re in a development environment, you can include the out-of-order flag in your application properties to allow migrations to be applied regardless of their version number. Note that this property makes the database migration not reproducible; if you ran the migrations from scratch, you could end up with different results (even errors) since the migrations will run in order this time.

spring.flyway.outOfOrder=true
Code language: JavaScript (javascript)

Error Codes

When Flyway commands fail, they throw an exception with a message to help you identify the problem. They also contain an error code which users of the API or those who have enabled machine readable output can inspect and handle accordingly. Below are details of each error code under the command that causes it along with a suggested solution.

General Error Codes

These error codes may appear from any command, and are indicative of more general errors.

FAULT

• Caused by: An unexpected error within Flyway (e.g. a null pointer exception)
• Solution: Please contact support or create a GitHub issue

ERROR

• Caused by: An error due to invalid configuration or usage not caught by a more specific error code
• Solution: Ensure all configuration and usage is as per the documentation

JDBC_DRIVER

• Caused by: The JDBC driver is unable to be instantiated
• Solution: Check whether the JDBC driver is present on the classpath

DB_CONNECTION

• Caused by: Issues in SQL statements reported by the JDBC driver
• Solution: Check whether the SQL provided to Flyway is correct

CONFIGURATION

• Caused by: Incorrect configuration provided to Flyway
• Solution: Ensure your configuration is as per the documentation

DUPLICATE_VERSIONED_MIGRATION

• Caused by: Multiple versioned migrations having the same version
• Solution: Ensure that all versioned migrations have a unique version

DUPLICATE_REPEATABLE_MIGRATION

• Caused by: Multiple repeatable migrations having the same description
• Solution: Ensure that all repeatable migrations have a unique description

DUPLICATE_UNDO_MIGRATION

• Caused by: Multiple undo migrations that undo the same versioned migration
• Solution: Ensure that there is at most one undo migration per versioned migration

DUPLICATE_DELETED_MIGRATION

• Caused by: Schema history or filesystem corruption causing the same migration to appear to be deleted more than once
• Solution: Ensure that you do not tamper with the schema history and all migrations that have been deleted are removed from locations known to Flyway

Validate Error Codes

These error codes are surfaced when running validate or validateWithResult.

VALIDATE_ERROR

• Caused by: Some migrations have failed validation
• Solution: Inspect the list invalidMigrations on the validate result to see the required actions

SCHEMA_DOES_NOT_EXIST

• Caused by: The schema being validated against does not exist
• Solution: Manually create the schema or enable createSchemas

FAILED_REPEATABLE_MIGRATION

• Caused by: A failed repeatable migration was detected
• Solution: Remove any incomplete changes then run repair to fix the schema history

FAILED_VERSIONED_MIGRATION

• Caused by: A failed versioned migration was detected
• Solution: Remove any incomplete changes then run repair to fix the schema history

APPLIED_REPEATABLE_MIGRATION_NOT_RESOLVED

• Caused by: A repeatable migration that was applied wasn’t resolved in any supplied locations
• Solution: If you removed this migration intentionally run repair to mark the migration as deleted

APPLIED_VERSIONED_MIGRATION_NOT_RESOLVED

• Caused by: A versioned migration that was applied wasn’t resolved in any supplied locations
• Solution: If you removed this migration intentionally run repair to mark the migration as deleted

RESOLVED_REPEATABLE_MIGRATION_NOT_APPLIED

• Caused by: A repeatable migration that was resolved has not been applied
• Solution: To ignore this migration set ignoreMigrationPatterns to *:ignored

RESOLVED_VERSIONED_MIGRATION_NOT_APPLIED

• Caused by: A versioned migration that was resolved has not been applied
• Solution: To ignore this migration set ignoreMigrationPatterns to *:ignored and to allow executing this migration enable outOfOrder

OUTDATED_REPEATABLE_MIGRATION

• Caused by: An applied repeatable migration was resolved with a newer checksum and can be reapplied
• Solution: Run migrate to execute this migration

TYPE_MISMATCH

• Caused by: The type of the resolved migration (BASELINE, SQL, UNDO_SQL, …) is different from the applied migration’s
• Solution: Either revert the changes to the migration or run repair to update the schema history

CHECKSUM_MISMATCH

• Caused by: The checksum of the resolved migration is different from the applied migration’s
• Solution: Either revert the changes to the migration or run repair to update the schema history

DESCRIPTION_MISMATCH

• Caused by: The description of the resolved migration is different from the applied migration’s
• Solution: Either revert the changes to the migration or run repair to update the schema history

Community Plugins

How to troubleshoot common Flyway errors

Flyway is a database migration tool that uses SQL scripts (which they call migrations) to create and modify the database schema and the data stored in the database. The migrations have a version number, which is used to apply them in order. When the migrations run for the first time, they are added to a schema history table, which by default is called flyway_schema_history. This table keeps a checksum for each migration, which is verified whenever the application starts up (to make sure the migration has not changed after it was applied to the database). As a general guide, we should create new migrations to make changes, instead of changing existing migrations.

This article discusses common errors that you might run into while working with Flyway and possible solutions. Flyway provides tools such as Flyway Repair, which can be used to automatically update the flyway_schema_history table. I have not been able to use that tool on my project due to security restrictions, so my solutions make changes directly to the flyway_schema_history table. I’m assuming you’re working in a development environment; some of the solutions might not be appropriate if you’re in a production environment.

I created this sample app, which can be useful for troubleshooting Flyway errors using a simple environment.

Table of Contents

Flyway failed to initialize

Flyway failed to initialize: none of the following migration scripts locations could be found:
– classpath:db/migration

Cause #1: Your application doesn’t have any migrations yet.

Disable flyway by adding the following property to the application properties:

Cause #2: Your migrations are not in the default folder: resources/db/migration.

Specify the folder where your migration scripts are located in the application properties. For example, if the migrations are located in resources/database, use:

Migration checksum mismatch

org.flywaydb.core.api.exception.FlywayValidateException: Validate failed: Migrations have failed validation
Migration checksum mismatch for migration version X.X
-> Applied to database : 2145208862
-> Resolved locally : -1101818402. Either revert the changes to the migration, or run repair to update the schema history.

Cause: A migration that was already applied to the database has been modified. This changes the migration checksum, which is used by Flyway on startup to validate that existing have not changed, thus failing validation.

Avoid changing migrations that already ran. Any changes should be done in a new migration script.

Alternate solution when working in a development environment

If you’re working in a development environment, it might be acceptable to modify a migration after it has been applied to the database. In that case, you can fix this error by deleting the migration record from the flyway_schema_history table. This will cause the updated migration to run. Note that if the original and updated migrations have statements such as CREATE, DROP, etc. you will need to first undo the changes done by the original migration in the database or you will run into SQL exceptions.

If the changes made to the migration were just a formatting change (e.g. you added some blank lines or changed the indentation), you can update the checksum directly in the flyway_schema_history table so it matches the checksum of the updated migration, instead of running the migration script again.

Detected applied migration not resolved locally

org.flywaydb.core.api.exception.FlywayValidateException: Validate failed: Migrations have failed validation
Detected applied migration not resolved locally: X.X. If you removed this migration intentionally, run repair to mark the migration as deleted.

Cause: The code is missing a migration script that was previously applied to the database.

This can happen when multiple people are creating Flyway migrations at the same time. In that case, update your code, so it includes the migration script specified in the error message.

If the migration script was deleted on purpose, then delete the migration record from the flyway_schema_history table to fix this error. Note that deleting the migration record does not undo the changes done by the migration when it first ran.

FlywaySqlScriptException

org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException:
Migration VX.X.X__script_name.sql failed

Cause: There was a SQL error while running a migration script.

Flyway includes a detailed error message about what went wrong; the solution will vary depending on the SQL error. After you fix the migration, delete the failed migration record from flyway_schema_history and start the app again.

This is an example of an error message provided by Flyway; in this case the migration failed because it was trying to create a table that already existed:

Since I test SQL scripts by running them locally, the most common SQL exceptions I’ve run into have to do with version compatibility. For example, if I’m running MySQL 8.0 locally, but the application uses MySQL 5.6 and I write a migration script that uses the ALTER statement with the rename column clause to update a column name; since the rename column clause is only available starting with MySQL 8.0, the script fails with a SQL error when executed on the older MySQL server.

Error when running a previously failed migration

org.flywaydb.core.api.exception.FlywayValidateException: Validate failed: Migrations have failed validation
Detected failed migration to version X.X (script_name). Please remove any half-completed changes then run repair to fix the schema history.

Cause: A migration failed the first time it was run (e.g. it had a SQL exception). You fixed the migration script, but run into this error when you start the app.

Since you have made changes to the script (causing the checksum to change) you will need to delete the migration record from flyway_schema_history, so the updated script runs. You can identify migrations that failed the first time they ran using the success column, which shows ‘0’.

Found more than one migration with version X.X

org.flywaydb.core.api.FlywayException: Found more than one migration with version X.X
Offenders:
-> C:flyway-projecttargetclassesdatabaseVX.X__script1.sql (SQL)
-> C:flyway-projecttargetclassesdatabaseVX.X__script2.sql (SQL)

Cause: You have two migrations with the same version.

This can happen when multiple people are creating Flyway migrations at the same time. The version number must be unique for each migration. To resolve this error, rename one of the migrations to the next available version.

Detected resolved migration not applied to database

Detected resolved migration not applied to database: X.X. To ignore this migration, set -ignoreIgnoredMigrations=true. To allow executing this migration, set -outOfOrder=true

This can happen when multiple people are creating Flyway migrations at the same time, since by default, Flyway expects the migrations to be applied in order according to the version number.

Let’s say you worked on migration version 2.0 and applied it to the database. After that another developer submits their code changes, which include a new migration version 1.4. When you deploy the application, you will get the error above since you have a migration to be applied (version 1.4), which is prior to the latest migration already applied to the database (2.0).

If you’re in a development environment, you can include the out-of-order flag in your application properties to allow migrations to be applied regardless of their version number. Note that this property makes the database migration not reproducible; if you ran the migrations from scratch, you could end up with different results (even errors) since the migrations will run in order this time.

Источник

A hybrid conference in Seattle and online

Flyway Documentation

Error Codes

When Flyway commands fail, they throw an exception with a message to help you identify the problem. They also contain an error code which users of the API or those who have enabled machine readable output can inspect and handle accordingly. Below are details of each error code under the command that causes it along with a suggested solution.

General Error Codes

These error codes may appear from any command, and are indicative of more general errors.

FAULT

• Caused by: An unexpected error within Flyway (e.g. a null pointer exception)
• Solution: Please contact support or create a GitHub issue

ERROR

• Caused by: An error due to invalid configuration or usage not caught by a more specific error code
• Solution: Ensure all configuration and usage is as per the documentation

JDBC_DRIVER

• Caused by: The JDBC driver is unable to be instantiated
• Solution: Check whether the JDBC driver is present on the classpath

DB_CONNECTION

• Caused by: Issues in SQL statements reported by the JDBC driver
• Solution: Check whether the SQL provided to Flyway is correct

CONFIGURATION

• Caused by: Incorrect configuration provided to Flyway
• Solution: Ensure your configuration is as per the documentation

DUPLICATE_VERSIONED_MIGRATION

• Caused by: Multiple versioned migrations having the same version
• Solution: Ensure that all versioned migrations have a unique version

DUPLICATE_REPEATABLE_MIGRATION

• Caused by: Multiple repeatable migrations having the same description
• Solution: Ensure that all repeatable migrations have a unique description

DUPLICATE_UNDO_MIGRATION

• Caused by: Multiple undo migrations that undo the same versioned migration
• Solution: Ensure that there is at most one undo migration per versioned migration

DUPLICATE_DELETED_MIGRATION

• Caused by: Schema history or filesystem corruption causing the same migration to appear to be deleted more than once
• Solution: Ensure that you do not tamper with the schema history and all migrations that have been deleted are removed from locations known to Flyway

Validate Error Codes

These error codes are surfaced when running validate or validateWithResult .

Источник

Getting ‘ERROR: Validate failed’ after upgrading from 5.0.7 to 5.1.0 #2027

Which version and edition of Flyway are you using?

If this is not the latest version, can you reproduce the issue with the latest one as well?

This issue is caused by the latest version.

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin)

Which database are you using (type & version)?

Which operating system are you using?

What did you do?

Ran migrate command

What did you expect to see?

Normal response as with prior versions i.e.

/ (PostgreSQL 10.3) Successfully validated migrations (execution time 00:00.061s) Current version of schema » «: 20180521112500 Schema » » is up to date. No migration necessary.»>

What did you see instead?

/ (PostgreSQL 10.3) ERROR: Validate failed: Migration checksum mismatch for migration version 20171006100000 -> Applied to database : -698211182 -> Resolved locally : 1049070299″>

The text was updated successfully, but these errors were encountered:

Is this a filesystem or a classpath location? Could you create a small repo that reproduces the issue?

It is filesystem location

I just updated to 5.1.0 from 5.0.7 as well and had checksum mismatches for about 1/3 of my migrations. I just went in and manually changed all the checksums by hand in flyway_schema_history because I know the *.sql patch files had not changed. This is a tedious process, so an easier way to recover would be much appreciated.

We have currently reverted downloads to 5.0.7 by default while we investigate this.

Any repo with migration files affected by this would be immensely useful for tracking this down. Could you create one to help speed up resolution of this issue?

Also no need to adjust checksums manually. Flyway 5.1.1 will fix this specific issue and in general the repair command can do this for you as well.

Here are the migration files of which about 1/3 caused checksum issues for me:

Let me know if you need to know exactly which ones had different checksums.

I’ve just upgraded to 5.1, seem to be having this same problem. Also postgres.

Источник

ERROR: Validate failed: Migration checksum mismatch for migration #2255

Which version and edition of Flyway are you using?

Flyway Community Edition 5.2.4 by Boxfuse

If this is not the latest version, can you reproduce the issue with the latest one as well?

(Many bugs are fixed in newer releases and upgrading will often resolve the issue)

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin)

Which database are you using (type & version)?

Which operating system are you using?

Im using Docker images only

What did you do?

(Please include the content causing the issue, any relevant configuration settings, the SQL statement that failed (if relevant) and the command you ran.)

What did you expect to see?

Successfull or already migrated

What did you see instead?

Additional Information

this bug feels like Issue #2027. maybe is the same error, which came back? At least, when i switch to image boxfuse/flyway:5.1.1 the error doesn’t show up. with the latest boxfuse/flyway(:latest) im not successful.

The text was updated successfully, but these errors were encountered:

Please share a small repo that reproduces this issue.

Closing due to lack of response.

i have fixed this error by write an statement as last in the V1 init script:
delete from flyway_schema_history where installed_rank = 1;

the reason for this is flyway make twice entries for Version 1 migration script and the first entry will produce the exception:
+—————-+———+————-+——+——————-+————+—————+———————+—————-+———+
| installed_rank | version | description | type | script | checksum | installed_by | installed_on | execution_time | success |
+—————-+———+————-+——+——————-+————+—————+———————+—————-+———+
| 1 | 1 | Init | SQL | V1__Init.sql | 0 | root | 2019-05-11 12:03:44 | 2 | 1 |
| 2 | 1 | Init | SQL | V1__Init.sql | 262691744 | root | 2019-06-15 11:06:04 | 187 | 1 |

i put this line in my property file and seems to be okay:
spring.flyway.validate-on-migrate=false

© 2023 GitHub, Inc.

You can’t perform that action at this time.

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.

Источник

Introduction

Most of the services that we generally encounter in our daily job will simply take some input from users and populate database , and read from database
and show it on UI. Each database has a schema, now it is rarely happens that we know each nitty gritty of our domain and come up with a perfect schema at day one. Most of the time we go thorugh an iterative process of modifying our schema as and when requirments change. Now all these migrations can reside separately to the application and can be handled by database teams but what if we can keep this migration script along side of our application in a versioned way and apply them on application startup? That’s what flyway does for us.

Flyway (https://flywaydb.org/documentation/) is an open source database migration tool that is used to migrate your database schema. It can be used standalone but it has nice integration with spring boot. Liquibase in another option for database migration most of the time we will see one of these two being used in production services.

Flyway supports a vast list of databases check in documentation for more details.

Flyway Convention

Flyway favours simplicity and convention over configuration.

• Each flyway migration script file has the format of V<version>__<description>.sql
• If we want to undo a migration, we can put that script in a file names U<version>__<description>.sql
• Flyway also stores the checksum of file. So once a file has been applied the content cannot be changed.
• It tracks the migration history in a table named flyway_schema_history .

Flyway with Spring Boot

To show case flyway with spring boot I am going to use our last inventory-service. So our intention is to start creating a schema and that should be applied by flyway.

To integrate flyway with spring boot we start with adding below dependency to our service.

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
</dependency>

We start by adding spring-boot-starter-data-jpa this gives us jpa and hibernate capabilities to our application.

As we are using here postgresql we need to add postgresql driver dependency.

And last but not the least we need to add flyway-core dependency.

Flyway uses spring datasource configuration to find out database uri and credentials. Let’s add the details of our database to our application.

For this blog I have created a db named inventory and a R/W user named inventory_rw . So our application config looks like as following.

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/inventory
    username: inventory_rw
    password: '*****'

So till now flyway knows which database to connect with which credentials. Now we need to provide the migration script.
We name our first migration script as V1__init.sql and it looks like as following-

CREATE SCHEMA IF NOT EXISTS inv;

SET search_path TO inv,public;

CREATE TABLE IF NOT EXISTS products(
 id UUID PRIMARY KEY,
 product_name  VARCHAR(255) NOT NULL,
 stock NUMERIC NOT NULL default 0,
 manufacturer VARCHAR(255) NOT NULL
);

Now if we run the application, the application starts up. We see some logs like below —

 Successfully applied 1 migration to schema "public", now at version v1 (execution time 00:00.046s)

if we connect to our database we see the following —

So there is our table . We also see the owner is our user .

if we check in public schema we can say the flyway_schema_history table is also created.

And here is how it looks like —

Now let’s try to change our migration script. We get below exception

Caused by: org.flywaydb.core.api.exception.FlywayValidateException: Validate failed: Migrations have failed validation
Migration checksum mismatch for migration version 1
-> Applied to database : 2071614183
-> Resolved locally    : 387884339. Either revert the changes to the migration, or run repair to update the schema history.

So once your schema is applied by default we cannot change the script. However if we want we can disable this check by spring.flyway.validate-on-migrate=false.

Let’s add a column to our table. To do this we need to add a new migration script with version number greater than previous version.

Let’s create a new file name V2_created_on_column.sql and add the following script.

ALTER TABLE products ADD COLUMN created_on TIMESTAMP NOT NULL ;

On logs we see below lines —

 Current version of schema "public": 1
 Migrating schema "public" to version "2 - created on"
 Successfully applied 1 migration to schema "public", now at version v2 (execution time 00:00.051s)

if we check our table we can see the new column is added.

Introducing flyway in already existing database

If we already have a database that is not created using flyway and the public schema (or the schema in which we want our flyway-schema-history-table to be created) already has other tables we will see below exception.

Found non-empty schema(s) "public" but no schema history table. Use baseline() or set baselineOnMigrate to true to initialize the schema history table.

Flyway basically refuses to migrate on a non empty database for which it does not have a schema history table.
To overcome this issue, we need to provide a baseline to flyway. A baseline is a way to tell flway that don’t care what happended till this version and apply any changes above this version.
With spring config we achieve this by adding below property to our application.

spring:
  flyway:
    baseline-on-migrate: true
    baseline-version: '0'

Now here’s a small catch if you don’t provide which version to baseline the default value provided is 1.
So here I am providing a custom value 0 so that my scripts like V1 onwards are applied.

The schema history table will look somehting like below —

We now have a new entry called <<Flyway Baseline>> in our schema history table.

Flyway custom user

Till now in our configuration, flyway is using the user provided in spring datasource.
However it may be possible that your application uses a read-only user .
In this scenario to provide a different user to flyway we need to specify user for flyway.

The below config uses a separate R/W user to do the migration —

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/inventory
    username: inventory_ro
    password: '******'
  flyway:
    baseline-on-migrate: true
    baseline-version: '0'
    user: inventory_rw
    password: '******'

Flyway integration of spring boot has a lot of properties to configure, for example we can define custom schema where flyway_schema_history table will be created, cherry pick migration script, default schema name etc. A list of these properties can be found https://docs.spring.io/spring-boot/docs/current/reference/html/application-properties.html.

So that’s it from this post. We now know how to iterate over our database and apply migrations using flyway. In next post wit this knowledge I will show how to integrate a database with spring boot application.

Flyway is an Opensource Database migration tool, well known for its simplicity and effectiveness. This can be used from command-line, in Java projects, Maven plug-in, and Gradle plug-in. It is based on only 7 commands 7 basic commands: Migrate, Clean, Info, Validate, Undo, Baseline, and Repair. For more information one can check the official documentation here Documentation – Flyway by Redgate • Database Migrations Made Easy. (flywaydb.org)

Spring Boot and Flyway

Spring Boot comes with out-of-the-box integration for Flyway. To integrate and run flyway integration in spring boot you just need to add the following dependency in your spring boot project using Gradle or you can include this using your project build platform.

	implementation 'org.flywaydb:flyway-core:8.4.4'

Important note for Mysql 8 and Maria DB support you need to add an additional dependency otherwise you will get the following exception

Caused by: org.flywaydb.core.api.FlywayException: Unsupported Database: MySQL 8.0

Addition dependencies can be found here and added as given below

	implementation 'org.flywaydb:flyway-mysql:8.4.4'

Spring Boot will then automatically autowire Flyway with its default DataSource and invoke it on startup. It will automatically look for migration SQL scripts on the default location using the default versioning naming convention. You can also change Flyway DB migration properties using the application.properties or application.yaml file.

The default directory of the Flyway migration script in the spring boot project is classpath:db/migration but one can change using property files or configuration using Springboot Java configuration. You can also user classpath or filesystem path as script location property as given below.

	spring.flyway.locations=classpath:db/migration,filesystem:/opt/migration

The above property will tell the Flyway migration tool to lookup scripts first in classpath then system path /opt/migration.

Script files name Typically, migrations are scripts in the form V<VERSION>__<NAME>.sql (with <VERSION> an underscore-separated version, such as ‘1’ or ‘2_1’).version and name should be separated with a double underscore. eg V1__intitial.sql or V1_2__secondrev.sql.

Spring Boot Flyway Simple Example

For example, I added the following dependencies in my Spring Boot project in build.gradle file

	implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
	runtimeOnly 'mysql:mysql-connector-java'
	implementation 'org.flywaydb:flyway-core:8.4.4'
	implementation 'org.flywaydb:flyway-mysql:8.4.4'

and added the following connection properties in the application.properties file

    spring.datasource.url=jdbc:mysql://192.168.1.30:3306/authdb?createDatabaseIfNotExist=true
    spring.datasource.username=user1
    spring.datasource.password=password
    spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect

Now created migration directory db/migrations inside resources directory and create V1__intial.sql file as shown below

Now run your spring boot project. you can see the following logs of spring boot in the console

2022-02-13 10:22:49.621  INFO 15180 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2022-02-13 10:22:49.654  INFO 15180 --- [           main] o.f.c.internal.license.VersionPrinter    : Flyway Community Edition 8.4.4 by Redgate
2022-02-13 10:22:49.654  INFO 15180 --- [           main] o.f.c.i.database.base.BaseDatabaseType   : Database: jdbc:mysql://192.168.1.30:3306/authdb (MySQL 8.0)
2022-02-13 10:22:49.693  INFO 15180 --- [           main] o.f.core.internal.command.DbValidate     : Successfully validated 1 migration (execution time 00:00.015s)
2022-02-13 10:22:49.707  INFO 15180 --- [           main] o.f.c.i.s.JdbcTableSchemaHistory         : Creating Schema History table `authdb`.`flyway_schema_history` ...
2022-02-13 10:22:49.795  INFO 15180 --- [           main] o.f.core.internal.command.DbMigrate      : Current version of schema `authdb`: << Empty Schema >>
2022-02-13 10:22:49.803  INFO 15180 --- [           main] o.f.core.internal.command.DbMigrate      : Migrating schema `authdb` to version "1 - Initial"
2022-02-13 10:22:49.927  INFO 15180 --- [           main] o.f.core.internal.command.DbMigrate      : Successfully applied 1 migration to schema `authdb`, now at version v1 (execution time 00:00.140s)
2022-02-13 10:22:50.059  INFO 15180 --- [           main] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [name: default]

It created 2 tables and one index by executing 3 scripts in the database and it will also create one schema history table name flyway_schema_history in the database for tracking of version.

It tracks changes by migration scripts name and their checksum if any file will modify after its execution it will throw an exception, this is the basic working of the flyway. for any change in schema and DB create another higher version.

The exception will look as given below

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.exception.FlywayValidateException: Validate failed: Migrations have failed validation
Migration checksum mismatch for migration version 1
-> Applied to database : -419208586
-> Resolved locally    : -1090537089. Either revert the changes to the migration, or run repair to update the schema history.

For the migration test, I create another version V1_2__Intial.sql with another table creation statement.

2022-02-13 10:31:51.543  INFO 10176 --- [           main] o.f.c.internal.license.VersionPrinter    : Flyway Community Edition 8.4.4 by Redgate
2022-02-13 10:31:51.543  INFO 10176 --- [           main] o.f.c.i.database.base.BaseDatabaseType   : Database: jdbc:mysql://192.168.1.30:3306/authdb (MySQL 8.0)
2022-02-13 10:31:51.589  INFO 10176 --- [           main] o.f.core.internal.command.DbValidate     : Successfully validated 2 migrations (execution time 00:00.023s)
2022-02-13 10:31:51.600  INFO 10176 --- [           main] o.f.core.internal.command.DbMigrate      : Current version of schema `authdb`: 1
2022-02-13 10:31:51.608  INFO 10176 --- [           main] o.f.core.internal.command.DbMigrate      : Migrating schema `authdb` to version "1.2 - Intial"
2022-02-13 10:31:51.680  INFO 10176 --- [           main] o.f.core.internal.command.DbMigrate      : Successfully applied 1 migration to schema `authdb`, now at version v1.2 (execution time 00:00.086s)

Now it created another table and the database is migrated successfully

If your sqlfile checksum is not matching and you are sure nothing is changed then update the checksum in DB with resolve locally checksum value.