使用flyway控制数据库版本时启动发生1786错误的解决方案

in 网站建设
关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9

近日在spring-boot:2.2.2.release中启用了flyway。但在生产环境中启动却发生了如下错误:DB: Statement violates GTID consistency: CREATE TABLE ... SELECT. (SQL State: HY000 - Error Code: 1786)

查看日志获得详细信息如下:

-----------------
SQL State  : HY000
Error Code : 1786
Message    : Statement violates GTID consistency: CREATE TABLE ... SELECT.
Location   :  ()
Line       : 1
Statement  : CREATE TABLE `task`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    CONSTRAINT `flyway_schema_history_pk` PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB AS SELECT     1 as "installed_rank",     '1.0.0' as "version",     '<< Flyway Baseline >>' as "description",     'BASELINE' as "type",     '<< Flyway Baseline >>' as "script",     NULL as "checksum",     'task' as "installed_by",     CURRENT_TIMESTAMP as "installed_on",     0 as "execution_time",     TRUE as "success"

根据以上日志获知:flyway在初始化其依赖的flyway_schema_history表时发生了1786错误。查阅相文资料获知:mysql认为create table xxx as select存在一定的风险,所以默认禁止用户这么做。

问题原因猜出来后,解决的方法也就有了。方案有二:
一、将mysql的相关安全设置关闭,即--enforce-gtid-consistency=false
二、手动指定flyway的版本(经测试5.2.4版本并不存在该问题)

笔者选用的第二种。

<dependency>  
 <groupId>org.flywaydb</groupId>  
 <artifactId>flyway-core</artifactId>  
 <version>5.2.4</version>  
</dependency>
关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9
扫一扫关注公众号添加购物返利助手,领红包
Comments are closed.

推荐使用阿里云服务器

超多优惠券

服务器最低一折,一年不到100!

朕已阅去看看