Java automatic upgrade SQL script flyway tool

Time:2021-7-20

Why use flyway

In daily development, we often encounter the following problems:

  1. I forgot to execute my own SQL in all environments;

  2. We are not sure whether the SQL written by others has been executed in all environments;

  3. Someone has modified the SQL that has been executed and expects to execute it again;

  4. New environment is needed for data migration;

  5. For each release, you need to manually control the DB version first, and then release the application version;

  6. Other scenes

Due to changes in project requirements or design defects in the early stage, it should be a common thing to modify the database in the later stage. If the project is not online, you may delete the table and recreate it. But if the project is online, it can’t be so simple and rude. Every time you deploy the project, you have to manually execute the SQL file. We need to upgrade the existing data table through SQL script.

With flyway, these problems can be well solved.

After using flyway, if you want to upgrade the database version again, you don’t need to use the previous database script to directly create a new database script. When the project starts, it detects that there is a new and higher version of the script, and it will be automatically executed. In this way, it will be much more convenient to work with other colleagues. Because we normally pull the code from git instead of the database script, so if someone updates the database, other colleagues may not be able to receive the latest notification. Using flyway can effectively avoid this problem.

All the scripts, once executed, will be in flyway_ schema_ There are records in the history (Java fhadm. CN) table. If you accidentally make a mistake, you can manually download them from flyway_ schema_ Delete records from the history table, modify the SQL script, and then restart (not recommended for production).

How does flyway work

The workflow of flyway is as follows:

1. After the project starts and the application completes the establishment of the database connection pool, flyway runs automatically.

2. When first used, flyway creates aflyway_schema_historyTable, used to record SQL execution records.

3. Flyway will scan items in the specified path (default isclasspath:db/migration)All SQL scripts, andflyway_schema_historyTable script records. If the database records the executed script records, which are inconsistent with the SQL script in the project, flyway will report an error and stop the project execution.

4. If the verification is passed, the maximum version number will be recorded according to the SQL in the table, and all scripts whose version number is not greater than this version will be ignored. Then according to the version number from small to large, execute the remaining scripts one by one.

Using flyway in projects

Firstly, the core dependency package of flyway is introduced into the POM file

1. Introduce core dependency package:

org.flywaydb
flyway-core
5.2.4

Java automatic upgrade SQL script flyway tool

Version 5.2.4 is used here. After testing, there is a conflict between version 7.0.0 and the current version of springboot, which will cause flyway not to execute. So we try not to use a higher version of flyway.

2. Profile:

Simply configure a property to use

Flyway configuration

spring:

flyway:

#Enable or disable flyway

Properties configuration list of flyway (properties not tested)

A description of the baseline version when performing the migration

flyway.baseline-description

#When it is found that the target schema is not empty and has a table without metadata during migration, the default is false

flyway.baseline-on-migrate =false

#When starting the benchmark migration, label the version of the existing schema. The default value is 1

flyway.baseline-version=1

#Check whether the location of the migration script exists. The default is false

flyway.check-location=false

#Whether to call clean automatically when a verification error is found. The default is false

flyway.clean-on-validation-error=false

#Whether to turn on flywar, the default is true

flyway.enabled=true

#Set the code for migration. The default is UTF-8

flyway.encoding

#Whether to ignore error migration when reading metadata table, default to false

flyway.ignore-failed-future-migration

#The SQL to execute when the connection is initialized

flyway.init-sqls

#The location of the migration script is dB / migration by default

flyway.locations

#Whether to allow unordered migration, the default is false

flyway.out-of-order

#The password of the target database

flyway.password

#Set the prefix of each placeholder, default ${

flyway.placeholder-prefix

#Whether to be replaced or not, the default is true

flyway.placeholder-replacementplaceholders

#Set the suffix of each placeholder, default}

flyway.placeholder-suffix

#Set the value of the placeholder

flyway.placeholders.[placeholder name]

#Set the schema that needs flywar migration. It is case sensitive. The default is the connection default schema

flyway.schemas

#The prefix of the migration file. The default is v

flyway.sql-migration-prefix

#File name separator of migration script, default__

flyway.sql-migration-separator

#The suffix of the migration script is. SQL by default

flyway.sql-migration-suffix

#The name of the metadata table used. The default is schema_ version

flyway.tableflyway

#The target version used for migration is the latest version by default

flyway.target

#The JDBC URL used during migration, if not specified, will use the configured main data source

flyway.url

#The user name of the migration database

flyway.user

#Whether to verify during migration. The default value is true

flyway.validate-on-migrate

YML configuration list of flyway (tested, no problem, recommend using YML format configuration file)

Flyway configuration

spring:

flyway:

#Enable or disable flyway

Spring.flyway.clean-disabled: this attribute is very critical. It indicates whether to clear the table under the existing library. If the script to be executed is V1__ XXX. SQL, then the tables under the existing libraries will be cleared first, and then the scripts will be executed. This is quite convenient in the development environment, but it will be fatal in the production environment. Moreover, it will be cleared by default, and the production environment must be configured and set to true by itself.

3. Create dB / migration

Because flyway is read by defaultresources/db/migrationIf we need to modify this path, we can implement it in the configuration file

Java automatic upgrade SQL script flyway tool

4. Write SQL file

The SQL statement naming here needs to comply with certain specifications, otherwise flyway will report an error when running. There are two main naming rules

  1. SQL names that only need to be executed once start with an uppercase “V”, followed by a combination of “0 ~ 9” numbers. Between the numbers, you can use “.” or underline ““Split, then double underline_Split, followed by the file name, and finally. SQL. For example,V20210707__create_user.sqlV20210707__add_user.sql

  2. Repeatable SQL starts with an uppercase “R”, followed by two underscores, followed by a file name, and ends with. SQL.. For example,R__truncate_user_dml.sql

Among them, the SQL execution priority of V is higher than that of R.

V: fixed capital

20210707.01: 20210707 is the date, followed by. 01 for serial number

Because there is a sequence in the execution of flyway, for example, you executed V2021__ create_ User and V2020_ update_ user。 The reason is that 2020 < 2021. So we have to make sure that the serial numbers are increased in turn.

How does flyway compare the order of two SQL files? It adopts the principle of left alignment, and the vacancy is replaced by 0. For example:

1.0.1.1 is higher than 1.0.1.



1.0.10 is higher than 1.0.9.4.



The version number of 1.0.10 is as high as that of 1.0.010, and the leading 0 of each part of the version number will be ignored.

__This is two_

create_userIs a simple SQL description

.sql: the file suffix ending with. SQL is the Convention

Java automatic upgrade SQL script flyway tool

Java automatic upgrade SQL script flyway tool

Java automatic upgrade SQL script flyway tool

Java automatic upgrade SQL script flyway tool

Java automatic upgrade SQL script flyway tool

We just need to create the flyway database in the database, start the project, and flyway will execute the SQL file, create the user table, and automatically generate aflyway_schema_historysurface

Java automatic upgrade SQL script flyway tool

From this startup log, we can see the execution information of flyway and the execution of database script. At the same time, it is also said that flyway also creates a flyway_ schema_ The history table is used to record the update history of the database.

Java automatic upgrade SQL script flyway tool

flyway_ schema_ History records the execution of SQL files. Every time you start a project, you go to flyway_ schema_ History to see if the SQL has been executed. If it has not been executed, it means that the SQL is a new SQL and will be automatically executed and recorded in the table.

Java automatic upgrade SQL script flyway tool

With this record, we can start the project next time,V20210707.01、V20210707.02、V20210708.01The three script files will not be executed because the system knows that the script has been executed. If you want these scripts to be executed again, you need to delete flyway manually_ schema_ This script will be executed when the project starts.

The file with the beginning of R is slightly different from the file with the beginning of V. as long as the file with the beginning of R is sent for modification, it will be executed once. If the file beginning with V has been executed in general, an error will be reported when it is modified. In order to control the version, we try to use files beginning with V, so that we can clearly see the SQL files in each version.

common problem

Question 1

Caused by: Java. Lang. classnotfoundexception: org. Flywaydb. Core. API. Callback. Flywaycallbac

Reason: springboot version and flyway version are inconsistent, generally flyway version is too high.

Solution: reduce the version of flyway to 5.2.4

Question 2

Springboot integrates flyway, but it doesn’t work. Flyway doesn’t execute SQL automatically

Reason: as above

Reason 2: there is no database configuration in the project, and no sq dependency or configuration is introduced

Solution: as above

Solution 2: introduce SQL dependency and configure SQL information in YML file

Question 3

Flyway error flywayexception: validate failed: detected failed migration to version

Reason: there is a conflict between the SQL script and the database. You need to check where the SQL script is wrong. To put it simply, the SQL file starting with V has already been executed, and it can be found in flyway_ schema_ There is this data in the history table, but you have changed the SQL file, resulting in an error when executing again.

Solution: create a new SQL file, do not modify the original file that starts with V or in flyway_ schema_ Find the file related execution record in the history table, delete it and re execute it.

This work adoptsCC agreementReprint must indicate the author and the link of this article

Recommended Today

Rocketmq installation and configuration process

Official website Official website:http://rocketmq.apache.org Download the source package:https://www.apache.org/dyn/closer.cgi?path=rocketmq/4.8.0/rocketmq-all-4.8.0-source-release.zip Github:https://github.com/apache/rocketmq Linux+JDK1.8 Server configuration Configure the JDK environment in the server and upload the compressed package to / usr / local for decompression tar -zxvf jdk-8u231-linux-x64.tar.gz /usr/local Configure environment variables vim /etc/profile #Insert content export JAVA_HOME=/usr/local/jdk1.8.0_231 export JRE_HOME=$JAVA_HOME/jre export CLASSPATH=.:$JAVA_HOME/lib:$JRE_HOME/lib export PATH=$JAVA_HOME/bin:$PATH #Immediate effect source /etc/profile Enter […]