Unknown system variable 'tx_isolation' when using spring R2DBC to connect with a mySQL server version 8

Question

I created a new spring boot project using the IntelliJ Idea's spring initializer and checked the Web/Spring reactive Web and the SQL/Spring data R2DBC dependencies.

I also added the the dependency to the R2DBC implementation for MySQL

        <dependency>
            <groupId>dev.miku</groupId>
            <artifactId>r2dbc-mysql</artifactId>
            <version>0.8.2.RELEASE</version>
        </dependency>

and the java connector

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

Asking the version of the MySQL server from a console with select version(); I get 8.0.17

The connection factory configuration is like this:

@Configuration
@RequiredArgsConstructor
public class R2dbcConfig extends AbstractR2dbcConfiguration {

    @Bean
    public ConnectionFactory connectionFactory() {
        ConnectionFactoryOptions conf = ConnectionFactoryOptions.builder()
                .option(DRIVER, "mysql")
                .option(HOST, "the.db.url")
                .option(PORT, 33066612)
                .option(USER, "myUserName")
                .option(PASSWORD, "myPassword")
                .option(DATABASE, "aDbName")
                .build();
        return ConnectionFactories.find(conf);
    }
}

Nevertheless, when I run the application I get the following exception

reactor.core.Exceptions$ErrorCallbackNotImplemented: org.springframework.dao.DataAccessResourceFailureException: Failed to obtain R2DBC Connection; nested exception is io.r2dbc.spi.R2dbcNonTransientResourceException: [1193] Unknown system variable 'tx_isolation'
Caused by: org.springframework.dao.DataAccessResourceFailureException: Failed to obtain R2DBC Connection; nested exception is io.r2dbc.spi.R2dbcNonTransientResourceException: [1193] Unknown system variable 'tx_isolation'

Answers to similar questions instructed the asker to update the version of the mysql:mysql-connector-java to 8.+, and according to IntelliJ the versión resolved by maven is 8.0.26. Curiously, if I remove the connector dependency the result is exactly the same.

So, I debbuged the R2DBC implementation to find out what is happening and I discovered that during the handshake, the dev.miku.r2dbc.mysq.QueryFlow#initHandshake method receives a HandshakeRequest message whose headers tell that the server version is "5.5.30". And that causes the R2DBC implementation to use the old "tx_isolation" system variable name instead of the new "transaction_isolation".

Why? What am I missing?

score 2 · Accepted Answer · answered Oct 19 '21 at 00:13

2

https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html says:

The tx_isolation and tx_read_only system variables have been removed. Use transaction_isolation and transaction_read_only instead.

You checked and discovered the server version is 8.0.17. This is more authoritative than the handshake headers message.

I recommend searching your classpath for any leftover jar files with old versions of the connector. You may have both version 8.0.26 of the connector and an older version that hasn't been updated to use the new option names.

answered Oct 19 '21 at 00:13

Bill Karwin

538,548
86
673
828

Do you know how can I check that exhaustively. The list of dependencies that IntelliJ shows does not contain other mysql connector. Are all the transitive dependencies listed there or only the first levels? – Readren Oct 19 '21 at 00:44
2

I would add code to my app to call `conn.getMetaData()` and from the metadata object, call `getDriverVersion()`. That would prove which version of the driver is found by the CLASSPATH at runtime. Then I'd search for the jar file on the filesystem, not using IntelliJ, but using something simpler such as `find`. – Bill Karwin Oct 19 '21 at 01:36

Joaquín Ijalba · Answer 2 · 2021-11-29T17:50:16.577

Could you solved it? I found that R2dbc mysql driver doesn't get my MySql version, because it has a check of it to ask for tx_isolation or transaction_isolation.

The solution I got was changing the driver to Jasync.

//DB
implementation("com.github.jasync-sql:jasync-r2dbc-mysql:2.0.4")
implementation("io.r2dbc:r2dbc-h2")
implementation("org.springframework.boot:spring-boot-starter-data-r2dbc:2.6.0")

And my config:

@Configuration
@EnableTransactionManagement
@EnableConfigurationProperties(DbPropertiesConfig::class)
class DataSourceConfiguration {
@Configuration
    class ProdMySQLConfiguration(@Autowired val dbPropertiesConfig: DbPropertiesConfig) : AbstractR2dbcConfiguration() {

        @Bean
        override fun connectionFactory(): ConnectionFactory =
                JasyncConnectionFactory(MySQLConnectionFactory(
                        com.github.jasync.sql.db.Configuration(
                                database = dbPropertiesConfig.db,
                                port = dbPropertiesConfig.port,
                                username = dbPropertiesConfig.username,
                                host = dbPropertiesConfig.host,
                                password = dbPropertiesConfig.password
                        )
                ))
    }

}

Taking into consideration I have that DbPropertiesConfig in my yml.

Doc/Example: Doc Example

Gitter: https://gitter.im/jasync-sql/support

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Nov 29 '21 at 18:58

Unknown system variable 'tx_isolation' when using spring R2DBC to connect with a mySQL server version 8

2 Answers2