集成flyway

这两天有个"菜鸡"在从0开始搭建SpringBoot的项目，在集成flyway的时候遇到了问题，非要让我帮他解决，看到他菜的份上，我就研究了一下。

此次出问题的版本是

Spring Boot: 2.4.1

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.4.1</version>
    <relativePath/> <!-- lookup parent from repository -->
</parent>

Flyway: 5.2.4

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

报错信息

其中yaml配置如下

  flyway:
    baseline-on-migrate: true
    table: schema_version
    enabled: true
    init-sqls: /db/migration/V1_1_1__init.sql

报错信息如下

2021-01-04 15:00:10.174 ERROR 42335 --- [           main] druid.sql.Statement                      : {conn-10010, stmt-20011} execute error. /db/migration/V1_1_1__init.sql

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/db/migration/V1_1_1__init.sql' at line 1
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
	at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
	at com.alibaba.druid.filter.FilterChainImpl.statement_execute(FilterChainImpl.java:3010)
	at com.alibaba.druid.filter.FilterAdapter.statement_execute(FilterAdapter.java:2484)
	at com.alibaba.druid.filter.FilterEventAdapter.statement_execute(FilterEventAdapter.java:188)
	at com.alibaba.druid.filter.FilterChainImpl.statement_execute(FilterChainImpl.java:3008)
	at com.alibaba.druid.filter.FilterAdapter.statement_execute(FilterAdapter.java:2484)
	at com.alibaba.druid.filter.FilterEventAdapter.statement_execute(FilterEventAdapter.java:188)
	at com.alibaba.druid.filter.FilterChainImpl.statement_execute(FilterChainImpl.java:3008)
	at com.alibaba.druid.filter.FilterAdapter.statement_execute(FilterAdapter.java:2484)
	at com.alibaba.druid.filter.FilterEventAdapter.statement_execute(FilterEventAdapter.java:188)
	at com.alibaba.druid.filter.FilterChainImpl.statement_execute(FilterChainImpl.java:3008)
	at com.alibaba.druid.proxy.jdbc.StatementProxyImpl.execute(StatementProxyImpl.java:147)
	at com.alibaba.druid.pool.DruidPooledStatement.execute(DruidPooledStatement.java:633)
	at org.flywaydb.core.internal.jdbc.JdbcTemplate.executeStatement(JdbcTemplate.java:235)
	at org.flywaydb.core.internal.sqlscript.StandardSqlStatement.execute(StandardSqlStatement.java:42)
	at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:189)
	at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.execute(DefaultSqlScriptExecutor.java:125)
	at org.flywaydb.core.internal.database.base.Database.initConnection(Database.java:411)
	at org.flywaydb.core.internal.database.base.Database.getMainConnection(Database.java:365)
	at org.flywaydb.core.Flyway.prepareSchemas(Flyway.java:1757)
	at org.flywaydb.core.Flyway.execute(Flyway.java:1678)
	at org.flywaydb.core.Flyway.migrate(Flyway.java:1356)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.boot.autoconfigure.flyway.FlywayMigrationInitializer.afterPropertiesSet(FlywayMigrationInitializer.java:70)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1847)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1784)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:609)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:531)
	at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335)
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208)
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:322)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208)
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:944)
	at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:923)
	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:588)
	at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:144)
	at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:767)
	at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:759)
	at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:426)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:326)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1309)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1298)
	at com.qudehu.study.StudyApplication.main(StudyApplication.java:12)

问题分析

在确认sql没有任何问题的情况下，一直报sql错误，这一点确实有点令人费解。为此，我debug调试了一下源码，发现，提交到数据库执行的，并不是文件里面的sql，而是文件路径, 如下图所示

问题解决

看到这个问题，下意识是想知道flyway是怎么读取文件路径的。我一路找到了org.flywaydb.core.api.configuration.ClassicConfiguration的setInitSql方法。

但我并不知道这个方法是哪里调用过来的，下载源码又太麻烦了。但就在这个时候，我突然灵机一动，抛异常不是可以得到调用链吗。

那我来介绍一下，在人家的class文件里面，不出错的情况下，怎么自己去抛异常。

• 首先，在需要抛异常的方法处打个断点，如图所示

• 然后以debug模式运行代码，代码会停在我们的断点处

• 在该行代码右键，选择Evaluate Expression

• 好了，抛个异常吧

• 调用链已经出现了，在stackStrace中可以找到

通过查看调用链，我们可以找到，设置初始化sql是org.springframework.boot.autoconfigure.flyway.FlywayAutoConfiguration的configureProperties调用过来的。

但并没有读取文件的操作，而是直接读取的yml的配置，将配置结果读取过来了。我突然开始觉得，是不是我对这个初始化sql的定义理解错了，我百度了一下发现，初始化sql的定义是每次初始化数据库链接需要执行的sql，而我所理解的是每次初始化数据库需要执行的sql，这就闹出笑话了。

第一个解决方案

为此，我想到了第一个解决方案，那就是不要初始化sql的配置了，直接当普通sql处理。将flyway的配置调整了一下，如下：

flyway:
  baseline-on-migrate: true
  table: schema_version
  enabled: true
  # init-sqls: /db/migration/V1_1_1__init.sql

果然执行成功了，但由于之前的项目也是这么配置的，能运行成功，我决定再往下探究一下方案

第二个解决方案

我很容易就想到，去看看之前项目的FlywayAutoConfiguration的代码，结果让我发现，之前的项目的Spring Boot的版本是2.1.6.Release，而2.4.1已经对configureProperties做出了调整。 2.1.6.Release没有对初始化sql有单独处理，直接就当成普通sql初始的，所以没问题。那么第二个方案也就出来了，就是将spring boot的版本降级为2.1.6.Release。

如何集成flyway

说完了，还是简单介绍一下如何集成flyway

版本说明

Spring Boot 2.1.6.Release

Flyway 5.2.4

Druid 1.1.22

加入依赖

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

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.22</version>
</dependency>

配置yml

spring:
  datasource:
    druid:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://127.0.0.1:3306/test?useSSL=false&useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&allowPublicKeyRetrieval=true&allowMultiQueries=true
      username: root
      password: ******
      filters: mergeStat,stat
      initial-size: 10
      max-active: 200
      min-idle: 10
      max-wait: 6000
      validation-query: SELECT 1 FROM DUAL
      test-on-borrow: true
      test-on-return: true
      test-while-idle: true
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 300000
      connection-init-sqls: 'set names utf8mb4;'
      web-stat-filter:
        enabled: true
        url-pattern: /*
        exclusions: '*.js,*.gif,*.jpg,*.bmp,*.png,*.css,/druid/*'
      stat-view-servlet:
        enabled: true
        url-pattern: /druid/*
        reset-enable: false
        login-password: ikey100
        login-username: admin
      pool-prepared-statements: true
      max-open-prepared-statements: 20
  flyway:
    baseline-on-migrate: true
    table: schema_version
    enabled: true

在src/main/resources目录下创建文件夹db/migration, 将待执行的文件放在该文件夹下即可

在这里要注意，sql文件的命名格式为 版本号__SQL简介.sql，注意是两根下划线，执行的时候，会根据两根下划线来分割的方式分割出版本号和sql的简介

sql文件推荐命名示例：V1_1_1__init.sql