How to Resolve Flyway Permission Errors in Spring Boot Deployments

Sharing a recent Flyway issue encountered during integration with Spring Boot and the corresponding solutions. If you are unfamiliar with Flyway, refer to the linked introductory article.

Problem Description

The project uses the following versions:

Spring Boot: 2.1.3

Flyway: 5.2.4 (default version bundled with Spring Boot)

Specific error when running in the deployment environment:

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)
    ...

Analysis and Solutions

Solution 1: Grant Permissions

The error indicates that user test lacks SELECT permission on the user_variables_by_thread table. Granting the appropriate privileges to the user resolves the issue directly.

Solution 2: Change Flyway Version

If you do not have MySQL administrative rights, consider adjusting the Flyway version. The issue is discussed in several GitHub issues (e.g., https://github.com/flyway/flyway/issues/2215). Although the fix was slated for version 5.2.4, many users still experience the problem in that and later 6.x releases.

Observations show the problem appears starting from Flyway 5.2.2. Downgrading to version 5.2.1 resolves the error.