转载

Flyway 执行报错： 'user_variables_by_thread' 表没有SELECT权限问题

分享一个最近在整Flyway时候碰到的一个问题，以及对应的一些解决方案。如果您还不知道Flyway，建议可以先看一下这篇文章 Spring Boot中使用Flyway来管理数据库版本

问题描述

问题出现所描述的工程所用版本信息如下：

Spring Boot：2.1.3
Flyway：5.2.4（非特别指定，随Spring Boot的素材版本管理）

具体错误：在Spring Boot中整和Flyway之后，本地执行ok，但在部署环境出现了这样的错误：

java.sql.SQLSyntaxErrorException: SELECT command denied to user 'test'@'10.10.8.101' for table 'user_variables_by_thread'
 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.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
 at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1003)
 at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeQuery(DruidPooledPreparedStatement.java:227)
 at org.flywaydb.core.internal.jdbc.JdbcTemplate.queryForStringList(JdbcTemplate.java:119)
 at org.flywaydb.core.internal.database.mysql.MySQLConnection.hasUserVariableResetCapability(MySQLConnection.java:84)
 at org.flywaydb.core.internal.database.mysql.MySQLConnection.<init>(MySQLConnection.java:54)
 at org.flywaydb.core.internal.database.mysql.MySQLDatabase.doGetConnection(MySQLDatabase.java:162)
 at org.flywaydb.core.internal.database.mysql.MySQLDatabase.doGetConnection(MySQLDatabase.java:40)
 at org.flywaydb.core.internal.database.base.Database.getConnection(Database.java:122)
 at org.flywaydb.core.internal.database.base.Database.getMainConnection(Database.java:315)
 at org.flywaydb.core.Flyway.prepareSchemas(Flyway.java:550)
 at org.flywaydb.core.Flyway.execute(Flyway.java:487)
 at org.flywaydb.core.Flyway.migrate(Flyway.java:149)
 at com.yonghui.beanstalk.config.flyway.FlywayConfig.migrate(FlywayConfig.java:25)
 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