tidb 集成 flyway 报错 denied to user for table global_variables

原创已于 2024-01-04 10:48:37 修改 · 817 阅读

9 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#tidb #flyway

于 2024-01-04 10:23:08 首次发布

文章描述了在使用flyway5.2.1与tidb数据库时遇到的错误，原因在于性能_schema.global_variables表不存在。解决方法包括禁用flyway或升级版本，以及调整MySQL权限设置。

报错内容:

Caused by: java.sql.SQLException: connection disabled
   at com.alibaba.druid.pool.DruidPooledConnection.checkStateInternal(DruidPooledConnection.java:1181)
   at com.alibaba.druid.pool.DruidPooledConnection.checkState(DruidPooledConnection.java:1166)
   at com.alibaba.druid.pool.DruidPooledConnection.getMetaData(DruidPooledConnection.java:887)
   at org.flywaydb.core.internal.jdbc.JdbcUtils.getDatabaseMetaData(JdbcUtils.java:146)
   ... 57 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: SELECT command denied to user 'iot_customer'@'%' for table 'global_variables'
   at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:121)
   at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
   at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
   at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:972)
   at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:3225)
   at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeQuery(FilterEventAdapter.java:459)
   at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:3222)
   at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.executeQuery(PreparedStatementProxyImpl.java:166)
   at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeQuery(DruidPooledPreparedStatement.java:213)
   at org.flywaydb.core.internal.jdbc.JdbcTemplate.queryForString(JdbcTemplate.java:191)
   at org.flywaydb.core.internal.database.mysql.MySQLDatabase.isRunningInPerconaXtraDBClusterWithStrictMode(MySQLDatabase.java:66)
   at org.flywaydb.core.internal.database.mysql.MySQLDatabase.<init>(MySQLDatabase.java:61)
   at org.flywaydb.core.internal.database.DatabaseFactory.createDatabase(DatabaseFactory.java:159)
   at org.flywaydb.core.internal.database.DatabaseFactory.createDatabase(DatabaseFactory.java:92)
   at org.flywaydb.core.Flyway.execute(Flyway.java:1655)

这个错误产生的原因是使用 flyway 5.2.1 , tidb 数据, 其中有一个查询

select VARIABLE_VALUE from performance_schema.global_variables where variable_name = 'pxc_strict_mode';

这个查询, performance_schema.global_variables 并不存在这个表, 所以报错

解决办法只能是tidb暂时用不了flyway, 或者升级到其它版本试试, 但是 jdk1.8只有flyway 5.2.X系列

经过测试, 授权后,可以通过, 这里面注意要大写, 刚开始的时候, 我使用小写, 也不报错, 但是不起作用, 换成大写就好了

grant SELECT on INFORMATION_SCHEMA.* to 'iot_customer'@'%';
grant SELECT on PERFORMANCE_SCHEMA.* to 'iot_customer'@'%';
flush privileges;