flyway: MySQL: Migrate fails when the user does not have permission to SELECT on user_variables_by_thread table

Which version and edition of Flyway are you using?

5.2.3

If this is not the latest version, can you reproduce the issue with the latest one as well?

(Many bugs are fixed in newer releases and upgrading will often resolve the issue)

This is the latest version

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin)

Java API

Which database are you using (type & version)?

MySQL 5.7.22, specifically the mysql:5.7.22 docker image

Which operating system are you using?

Mac OS

What did you do?

(Please include the content causing the issue, any relevant configuration settings, the SQL statement that failed (if relevant) and the command you ran.)

Run a migrate() with a simple table creation

What did you expect to see?

The migration to succeed

What did you see instead?

org.flywaydb.core.internal.exception.FlywaySqlException:

Unable to restore connection to its original state
--------------------------------------------------
SQL State  : 42000
Error Code : 1142
Message    : SELECT command denied to user 'test'@'172.17.0.1' for table 'user_variables_by_thread'

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: SELECT command denied to user 'test'@'172.17.0.1' for table 'user_variables_by_thread'

This appears to be due to the fix applied for #2197. It makes the assumption that the user creating the schema has permission to query this table. Reverting back to Flyway 5.2.1 fixes the problem.

About this issue

Original URL
State: closed
Created 6 years ago
Comments: 26 (6 by maintainers)

Commits related to this issue

Fixed #2215: Flyway.migrate() fails for MySQL when the user does not have permission to SELECT on user_variables_by_thread table — committed to flyway/flyway by deleted user 6 years ago
Added notes for flyway/flyway#2215 — committed to flyway/flywaydb.org by deleted user 6 years ago
Added notes for flyway/flyway#2215 — committed to flyway/flywaydb.org by deleted user 6 years ago
workaround untuk https://github.com/flyway/flyway/issues/2215 — committed to idtazkia/aplikasi-akademik by endymuhardin 6 years ago
Fixed #2215: Flyway.migrate() fails for MySQL when the user does not have permission to SELECT on user_variables_by_thread table — committed to dohrayme/flyway by deleted user 6 years ago

Most upvoted comments

5.2.4 still has this problem.

+11

keijack on Apr 23, 2019

This problem is caused by druid When an error occurred in the previous SQL execution(SELECT variable_name FROM performance_schema.user_variables_by_thread WHERE variable_value IS NOT NULL ), druid did a certain amount of processing, which caused this problem in subsequent connections. To solve this problem,you can replace the connection pool , or grant SELECT permission for user on performance_schema.user_variables_by_thread

xyohn on Oct 16, 2020

This bug was introduced in flyway 5.2.2. Latest fix is working.

bHacklv on Nov 27, 2018

It’s not about project, just create a db user without server prillege in Mysql could repruduce it .

CREATE USER `testUser`@`%` IDENTIFIED BY 'testUser';
GRANT ALL PRIVILEGES  ON `testdb`.* TO `testUser`@`%`;

HiJackXD on Oct 9, 2020

I am unable to reproduce this. The original fix is working as intended for me. Providing a minimal project that reproduces this would help identify and fix the issue.

CREATE USER `testUser`@`%` IDENTIFIED BY 'testUser';
GRANT ALL PRIVILEGES  ON `testdb`.* TO `testUser`@`%`;

mysql8.0.20 flyway 6.5.7 springboo 2.3.0.RELEASE druid-spring-boot-starter 1.1.22

spring.datasource.druid.url=jdbc:mysql://localhost:3306/testdb?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true
spring.datasource.druid.username=testUser
spring.datasource.druid.password=testUser
spring.datasource.druid.driver-class-name=com.mysql.jdbc.Driver

HiJackXD on Sep 29, 2020

Just tested it out and it is working. Thankyou!

jhinch on Nov 27, 2018

I change the druid to HikariDataSource and it works !

Phukety on Nov 24, 2020