The following text is copied over from GitHub issue #12.

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|https://github.com/mariadb-corporation/mariadb-connector-r2dbc/blob/master/src/main/java/org/mariadb/r2dbc/client/ClientBase.java#L306] 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 config 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.