版本

  1. <dependency>
  2.   <groupId>org.flywaydb</groupId>
  3.   <artifactId>flyway-core</artifactId>
  4.   <version>6.0.8</version>
  5. </dependency>

支持的属性，前缀 spring.flyway

  1. FlywayProperties:
  2.   baselineDescription: String
  3.   baselineOnMigrate: boolean
  4.   baselineVersion: String
  5.   batch: Boolean
  6.   checkLocation: boolean
  7.   cleanDisabled: boolean
  8.   cleanOnValidationError: boolean
  9.   connectRetries: int
  10.   createDataSource: boolean
  11.   defaultSchema: String
  12.   dryRunOutput: File
  13.   enabled: boolean
  14.   encoding: Charset
  15.   errorOverrides: String[]
  16.   group: boolean
  17.   ignoreFutureMigrations: boolean
  18.   ignoreIgnoredMigrations: boolean
  19.   ignoreMissingMigrations: boolean
  20.   ignorePendingMigrations: boolean
  21.   initSqls: List<String>
  22.   installedBy: String
  23.   licenseKey: String
  24.   locations: List<String>
  25.   mixed: boolean
  26.   oracleSqlplus: Boolean
  27.   oracleSqlplusWarn: Boolean
  28.   outOfOrder: boolean
  29.   password: String
  30.   placeholderPrefix: String
  31.   placeholderReplacement: boolean
  32.   placeholders: Map<String, String>
  33.   placeholderSuffix: String
  34.   repeatableSqlMigrationPrefix: String
  35.   schemas: List<String>
  36.   skipDefaultCallbacks: boolean
  37.   skipDefaultResolvers: boolean
  38.   sqlMigrationPrefix: String
  39.   sqlMigrationSeparator: String
  40.   sqlMigrationSuffixes: List<String>
  41.   stream: Boolean
  42.   table: String
  43.   tablespace: String
  44.   target: String
  45.   undoSqlMigrationPrefix: String
  46.   url: String
  47.   user: String
  48.   validateMigrationNaming: boolean
  49.   validateOnMigrate: boolean

image.png
image.png

使用 flyway 报错

报错一：找不到对应的表 flyway_schema_history

代码：

  1. @Test
  2. public void testFlyway_001() {
  3.     String url = "jdbc:mysql://localhost:3306/scala_demo?sslMode=DISABLED&serverTimezone=Asia/Shanghai&characterEncoding=utf8&useSSL=false";
  5.     String user = "";
  6.     String password = "xiaohui";
  7.     Flyway flyway = Flyway.configure().dataSource(url, user, password).connectRetries(1).load();
  8.     flyway.migrate();
  10. }

错误信息：

  1. org.flywaydb.core.api.FlywayException: Found non-empty schema(s) `scala_demo` without schema history table! Use baseline() or set baselineOnMigrate to true to initialize the schema history table.

根据提示，需要设置 .baselineOnMigrate(true)

报错二：DB: INSERT, CREATE command denied to user ‘’@’localhost’ for table ‘flyway_schema_history’ (SQL State: 42000 - Error Code: 1142)

代码：

  1. @Test
  2. public void testFlyway_001() {
  3.     String url = "jdbc:mysql://localhost:3306/scala_demo?sslMode=DISABLED&serverTimezone=Asia/Shanghai&characterEncoding=utf8&useSSL=false";
  5.     String user = "";
  6.     String password = "xiaohui";
  7.     Flyway flyway = Flyway.configure().dataSource(url, user, password).baselineOnMigrate(true).connectRetries(1).load();
  8.     flyway.migrate();
  10. }

错误信息：

  1. 21:49:05.581 [main] DEBUG org.flywaydb.core.internal.parser.Parser - Parsing  ...
  2. 21:49:05.589 [main] DEBUG org.flywaydb.core.internal.sqlscript.ParserSqlScript - Found statement at line 1: CREATE TABLE `scala_demo`.`flyway_schema_history` (
  3.     `installed_rank` INT NOT NULL,
  4.     `version` VARCHAR(50),
  5.     `description` VARCHAR(200) NOT NULL,
  6.     `type` VARCHAR(20) NOT NULL,
  7.     `script` VARCHAR(1000) NOT NULL,
  8.     `checksum` INT,
  9.     `installed_by` VARCHAR(100) NOT NULL,
  10.     `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  11.     `execution_time` INT NOT NULL,
  12.     `success` BOOL NOT NULL,
  13.     CONSTRAINT `flyway_schema_history_pk` PRIMARY KEY (`installed_rank`)
  14. ) ENGINE=InnoDB AS SELECT     1 as "installed_rank",     '1' as "version",     '<< Flyway Baseline >>' as "description",     'BASELINE' as "type",     '<< Flyway Baseline >>' as "script",     NULL as "checksum",     '' as "installed_by",     CURRENT_TIMESTAMP as "installed_on",     0 as "execution_time",     TRUE as "success"
  16. 21:49:05.589 [main] DEBUG org.flywaydb.core.internal.sqlscript.ParserSqlScript - Found statement at line 15: CREATE INDEX `flyway_schema_history_s_idx` ON `scala_demo`.`flyway_schema_history` (`success`)
  17. 21:49:05.589 [main] DEBUG org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor - Executing SQL: CREATE TABLE `scala_demo`.`flyway_schema_history` (
  18.     `installed_rank` INT NOT NULL,
  19.     `version` VARCHAR(50),
  20.     `description` VARCHAR(200) NOT NULL,
  21.     `type` VARCHAR(20) NOT NULL,
  22.     `script` VARCHAR(1000) NOT NULL,
  23.     `checksum` INT,
  24.     `installed_by` VARCHAR(100) NOT NULL,
  25.     `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  26.     `execution_time` INT NOT NULL,
  27.     `success` BOOL NOT NULL,
  28.     CONSTRAINT `flyway_schema_history_pk` PRIMARY KEY (`installed_rank`)
  29. ) ENGINE=InnoDB AS SELECT     1 as "installed_rank",     '1' as "version",     '<< Flyway Baseline >>' as "description",     'BASELINE' as "type",     '<< Flyway Baseline >>' as "script",     NULL as "checksum",     '' as "installed_by",     CURRENT_TIMESTAMP as "installed_on",     0 as "execution_time",     TRUE as "success"
  31. 21:49:05.593 [main] WARN org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor - DB: INSERT, CREATE command denied to user ''@'localhost' for table 'flyway_schema_history' (SQL State: 42000 - Error Code: 1142)
  32. 21:49:05.593 [main] DEBUG org.flywaydb.core.internal.jdbc.TransactionTemplate - Rolling back transaction...
  33. 21:49:05.594 [main] DEBUG org.flywaydb.core.internal.jdbc.TransactionTemplate - Transaction rolled back
  34. 21:49:05.595 [main] DEBUG org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory - Schema History table creation failed. Retrying in 1 sec ...

权限不足。

查看用户及权限：

  1. select current_user(), VERSION();
  2. show grants;
  4. -- 结果
  5. user                version
  6. @localhost    5.7.21
  8. -- 
  9. GRANT USAGE ON *.* TO ''@'localhost'
  10. GRANT SELECT (age, id, name) ON `scala_demo`.`user_domain` TO ''@'localhost'

修改权限

  1. -- 使用超管用户 root 授权
  2. grant all PRIVILEGES on scala_demo.* to ''@localhost with grant option;
  4. -- 登录匿名用户，查看新的权限
  5. GRANT USAGE ON *.* TO ''@'localhost'
  6. GRANT ALL PRIVILEGES ON `scala_demo`.* TO ''@'localhost' WITH GRANT OPTION
  7. GRANT SELECT (age, id, name) ON `scala_demo`.`user_domain` TO ''@'localhost'

报错三：没有权限查询 performance_schema.user_variables_by_thread

  1. org.flywaydb.core.internal.database.mysql.MySQLConnection - Disabled user variable reset as performance_schema.user_variables_by_threadcannot be queried (SQL State: 42000, Error Code: 1142)

授权查询 performance_schema.user_variables_by_thread

  1. -- 使用超管权限授权
  2. grant select on performance_schema.user_variables_by_thread to ''@localhost with grant option;
  4. -- 登录当前业务用户查看权限
  5. show grants;
  6. -- 结果
  7. GRANT USAGE ON *.* TO ''@'localhost'
  8. GRANT ALL PRIVILEGES ON `scala_demo`.* TO ''@'localhost' WITH GRANT OPTION
  9. GRANT SELECT (age, id, name) ON `scala_demo`.`user_domain` TO ''@'localhost'
  10. GRANT SELECT ON `performance_schema`.`user_variables_by_thread` TO ''@'localhost' WITH GRANT OPTION

问题

  1. baseline-on-migrate=true，后版本从 2 开始，不设定为 true，不会自动创建 flyway_schema_history

    1. flyway.baseline-description= # 执行基线时标记已有Schema的描述
    2. flyway.baseline-version=1 # 基线版本默认开始序号默认为1. 
    3. flyway.baseline-on-migrate=false # 针对非空数据库是否默认调用基线版本，这也是我们上面版本号从2开始的原因
    4. flyway.check-location=false # 是否开启脚本检查,检查脚本是否存在,默认false
    5. flyway.clean-on-validation-error=false # 验证错误时,是否自动清除数据库,高危操作！！！
    6. flyway.enabled=true # 是否启用 flyway.
    7. flyway.encoding=UTF-8 # 脚本编码.
    8. flyway.ignore-failed-future-migration=true # 在读元数据表时，是否忽略失败的后续迁移.
    9. flyway.init-sqls= # S获取连接后立即执行初始化的SQL语句
    10. flyway.locations=classpath:db/migration # 脚本位置， 默认为classpath: db/migration.
    11. flyway.out-of-order=false # 是否允许乱序（out of order）迁移
    12. flyway.placeholder-prefix= # 设置每个占位符的前缀。 默认值： ${ 
    13. flyway.placeholder-replacement=true # 是否要替换占位符。 默认值：true
    14. flyway.placeholder-suffix=} # 设置占位符的后缀。 默认值： }
    15. flyway.placeholders.*= # 设置占位符的值。
    16. flyway.schemas= # Flyway管理的Schema列表，区分大小写。默认连接对应的默认Schema
    17. flyway.sql-migration-prefix=V # 迁移脚本的文件名前缀, 默认值：V
    18. flyway.sql-migration-separator=__ # 迁移脚本的分割符 默认双下划线
    19. flyway.sql-migration-suffix=.sql # 迁移脚本的后缀 默认 .sql
    20. flyway.table=schema_version # Flyway使用的Schema元数据表名称 默认schema_version
    21. flyway.url= # 待迁移的数据库的JDBC URL。如果没有设置，就使用配置的主数据源
    22. flyway.user= # 待迁移数据库的登录用户
    23. flyway.password= # 待迁移数据库的登录用户密码
    24. flyway.validate-on-migrate=true # 在运行迁移时是否要自动验证。默认值：true

    只好调整 .baselineVersion(“0”) 的值，默认值是 1，当 baseline-on-migrate=true ，只会迁移 大于 baselineVersion 版本的脚本文件。

    代码

    ```java @Test public void testFlyway_001() { String url = “jdbc:mysql://localhost:3306/scala_demo?sslMode=DISABLED&serverTimezone=Asia/Shanghai&characterEncoding=utf8&useSSL=false”;

    String user = “”; String password = “xiaohui”; Flyway flyway = Flyway.configure().dataSource(url, user, password)

    1.  /**
    2.           * 是否在针对没有架构历史记录表的非空架构执行迁移时自动调用基线。 然后，在执行迁移之前，此架构将使用基线版本作为baselineVersion 。 然后只会应用高于baselineVersion迁移。
    3.           * 这对于具有现有数据库的项目的初始 Flyway 生产部署很有用。
    4.           * 启用此功能时要小心，因为它删除了确保 Flyway 不会在配置错误的情况下迁移错误数据库的安全网！
    5.           *
    6.           * 参数：
    7.           * BaselineOnMigrate – 如果基线应该为非空模式调用，则为true否则为false 。 （默认： false ）
    8.           */
    9.  .baselineOnMigrate(true)
    10.  /**
    11.           * 设置版本以在执行基线时标记现有模式。
    12.           *
    13.           * 参数：
    14.           * 基线版本 – 执行基线时标记现有模式的版本。 （默认值：1）
    15.           */
    16.  .baselineVersion("0")
    17.  //                .schemas("mysql", "scala_demo")
    18.  .connectRetries(1).load();

    flyway.migrate();

} ```