Share a recent problem encountered throughout Flyway and some solutions.If you don't know Flyway yet, I suggest you take a look at this article first Use Flyway in Spring Boot to manage database versions

Problem Description

The version information used for the project described in the problem appears as follows:

• Spring Boot: 2.1.3
• Flyway:5.2.4 (not specified, with material version management of Spring Boot)

Specific error: After integrating and Flyway in SpringBoot, ok is executed locally, but this error occurs 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)
 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 

Analysis and Solution

Solution 1: Open permissions

The error message is that the test user does not have select permission on the user_variables_by_thread table.

Therefore, the most direct solution is to set permissions for the corresponding users.

Solution 2: Modify Flyway version

What if you don't have administrative rights to MySQL for this environment?

There are many issues discussed on Github to address this Issue, such as: https://github.com/flyway/flyway/issues/2215.

As you can see, this problem has been officially repaired and the repaired version is 5.2.4, but like us, many foreign friends seem to have not solved the problem in this version:

Various versions after 5.2.4, including the latest 6.x version, have failed to solve this problem.

I noticed this message after:

The original problem started after version 5.2.2, so try version 5.2.1 as you like!

Sure, after replacing this version, everything will be fine!

Welcome to my public number, Programmed Ape DD, for exclusive learning resources and daily dry delivery.
If you are interested in my topic, you can also follow my blog: didispace.com