[1.0.0] Transaction is not persisted because commit is not called

[MatusMak]

When running update statements in transaction, changes are executed successfully but are not persisted as they are not committed.

Description of the problem

I'm studying r2dbc-client implementation to understand how clients are being built and I decided to test it against my local MariaDB databse of version 10.1.34 (later I managed to reproduce it on 10.4.13 hosted on AWS).

Statements executed in auto commit mode worked. However, when I run statement in transaction, it was executed successfully, but results were not persisted in database. When checking the table, I could see that while no new rows were inserted, AUTO_INCREMENT value was increased by 1.

I tried downgrading driver to 0.8.4-rc, but the behavior was the same. However, when swapping driver with r2dbc-mysql, it worked as expected. My assumption therefore is that client and database are working as expected and it's the driver that is faulty.

It's worth noting that I used this driver in Spring WebFlux application and had no issues with Spring's annotation based transactions.

Investigation

I did a bit of debugging and it seems that org.mariadb.r2dbc.client.Context#serverStatus is not changed to correct one. Even after org.mariadb.r2dbc.client.ClientBase.LockAction#beginTransaction is called, status has value 2, which is flag for auto commit. So when org.mariadb.r2dbc.client.ClientBase.LockAction#commitTransaction is later called, condition for active transaction is evaluated as false and therefore COMMIT query is never sent to the database.

I would not only expect server status to contain flag for 1 ( transaction), but also for flag 2 (auto commit) to be unset, as that would correspond to behavior described by R2DBC SPI.

Steps to reproduce

1. Create new application and ensure you have the following dependencies:

• io.r2dbc:r2dbc-spi:0.8.3.RELEASE
• org.mariadb:r2dbc-mariadb:1.0.0
• dev.miku:r2dbc-mysql:0.8.2.RELEASE

2. Create database with the following table:

CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `first_name` varchar(255) COLLATE utf16_slovak_ci NOT NULL,
 `last_name` varchar(255) COLLATE utf16_slovak_ci NOT NULL,
 `email` varchar(255) COLLATE utf16_slovak_ci NOT NULL,
 PRIMARY KEY (`id`)
);

3. Execute this minimal Java code (replace database name or queries if necessary):

import dev.miku.r2dbc.mysql.MySqlConnectionConfiguration;
import dev.miku.r2dbc.mysql.MySqlConnectionFactory;
import io.r2dbc.client.R2dbc;
import org.mariadb.r2dbc.MariadbConnectionConfiguration;
import org.mariadb.r2dbc.MariadbConnectionFactory;

public class ClientTestJava {

    public static void main(String[] args) {
        R2dbc maria = getMariaDB();
        maria.useTransaction(handle -> handle.execute(
                "INSERT INTO `users` (`first_name`, `last_name`, `email`) VALUES ('MariaDB', 'Row', 'mariadb@test.com')"
        )).block();

        R2dbc mysql = getMySQL();
        mysql.useTransaction(handle -> handle.execute(
                "INSERT INTO `users` (`first_name`, `last_name`, `email`) VALUES ('MySQL', 'Row', 'mysql@test.com')"
        )).block();
    }

    private static R2dbc getMariaDB() {
        MariadbConnectionConfiguration config = MariadbConnectionConfiguration.builder()
                .host("localhost")
                .database("test")
                .username("root")
                .password("password")
                .build();
        return new R2dbc(new MariadbConnectionFactory(config));
    }

    private static R2dbc getMySQL() {
        MySqlConnectionConfiguration config = MySqlConnectionConfiguration.builder()
                .host("localhost")
                .database("test")
                .username("root")
                .password("password")
                .build();
        return new R2dbc(MySqlConnectionFactory.from(config));
    }
}

4. Check data in table and observe that only row inserted via MySQL driver is present.

[MatusMak]

I was not familiar with MariaDB versioning and I only just noticed that 10.1.x is no longer supported. I will later try on higher version to check if that couldn't be an issue but as I mentioned above, other driver worked fine.

[MatusMak]

Update: I connected to the same DB hosted on AWS with MariaDB version 10.4.13 and I got the same result.

[rusher]

thanks for detailed reporting. I'll investigate the issue

[MatusMak]

Hello,

I've tried version 1.0.1 and I can confirm that the issue has been resolved.

[shuai93]

I may have the same problem. when I use the annotation of spring boot @Transactional.

The following dependencies：
org.mariadb.r2dbc-mariadb version "1.0.3"
org.springframework.boot version "2.6.6"