[Bug]: MySQL Database Not Reflecting Changes Made by Ballerina Transactional Services

[dsplayerX]

Description

The transactional behavior between the initiator and participant services in Ballerina does not reflect committed changes in the MySQL database.

Expected Behavior

Upon a successful transactional commit, the database should reflect the changes made by the participant service.

Actual Behavior

The database appears empty despite the successful commit response from the services. The auto increment ID increases, but no records are visible in the database upon querying.

Example

When calling the service, the services respond with a 202 "Accepted" status code and the values inserted/changes made are not visible in the database.

Initiator Log

time=2023-11-13T12:26:35.701+05:30 level=DEBUG module=ballerina/http message="Created HTTP 
caching client"
time=2023-11-13T12:26:35.704+05:30 level=DEBUG module=ballerina/http message="Cached response not found for: \'GET /updateDb\'"
time=2023-11-13T12:26:35.706+05:30 level=DEBUG module=ballerina/http message="Sending new request to: /updateDb"
time=2023-11-13T12:26:35.723+05:30 level=DEBUG module=ballerinai/transaction message="Registered remote participant: 50b92b6e-be6c-4458-a426-9ca38a455766:1 for transaction: fecd71d3-a258-4dd4-a1ec-0e2a073a0268"
Response from remote service: 202 Accepted
time=2023-11-13T12:26:35.748+05:30 level=DEBUG module=ballerinai/transaction message="Running 2-phase commit for transaction: fecd71d3-a258-4dd4-a1ec-0e2a073a0268:1"
time=2023-11-13T12:26:35.751+05:30 level=DEBUG module=ballerinai/transaction message="Preparing remote participant: 50b92b6e-be6c-4458-a426-9ca38a455766:1"
time=2023-11-13T12:26:35.768+05:30 level=DEBUG module=ballerinai/transaction message="Remote participant: 50b92b6e-be6c-4458-a426-9ca38a455766:1 prepared"
time=2023-11-13T12:26:35.770+05:30 level=DEBUG module=ballerinai/transaction message="Notify(commit) remote participant: http://10.100.8.135:59898/balcoordinator/participant/2pc/1"  
time=2023-11-13T12:26:35.785+05:30 level=DEBUG module=ballerinai/transaction message="Remote participant: 50b92b6e-be6c-4458-a426-9ca38a455766:1 committed"
> Initiator committed.

Participant Log

time=2023-11-13T12:26:35.712+05:30 level=DEBUG module=ballerinai/transaction message="Registering 
for transaction: fecd71d3-a258-4dd4-a1ec-0e2a073a0268:1 with coordinator: http://10.100.8.135:59893/balcoordinator/initiator/1/register"
time=2023-11-13T12:26:35.726+05:30 level=DEBUG module=ballerinai/transaction message="Registered with coordinator for transaction: fecd71d3-a258-4dd4-a1ec-0e2a073a0268"
time=2023-11-13T12:26:35.729+05:30 level=DEBUG module=ballerinai/transaction message="participant 
registered: fecd71d3-a258-4dd4-a1ec-0e2a073a0268"
Affected row count: 1
Inserted ID: 13
time=2023-11-13T12:26:35.761+05:30 level=DEBUG module=ballerinai/transaction message="Prepare received for transaction: fecd71d3-a258-4dd4-a1ec-0e2a073a0268:1"
time=2023-11-13T12:26:35.763+05:30 level=DEBUG module=ballerinai/transaction message="Prepared transaction: fecd71d3-a258-4dd4-a1ec-0e2a073a0268"
time=2023-11-13T12:26:35.778+05:30 level=DEBUG module=ballerinai/transaction message="Notify(commit) received for transaction: fecd71d3-a258-4dd4-a1ec-0e2a073a0268:1"
> Particiapnt committed.

But when the database is queried, there is no record with ID 13.

Steps to Reproduce

Minimal Code to Reproduce the Issue

• Initiator

import ballerina/http;
import ballerina/io;

service / on new http:Listener(8080) {

    resource function get doTransaction() returns error? {
        transaction {
            transaction:onCommit(commitDone);
            transaction:onRollback(rollbackDone);
            check callRemoteService();
            check commit;
        }
    }
}

transactional function callRemoteService() returns error? {
    http:Client httpClient = check new ("http://localhost:9090");
    http:Response response = check httpClient->get("/updateDb");
    io:println("Response from remote service: ", response.statusCode, " ", response.reasonPhrase);
}

isolated function commitDone('transaction:Info info) {
    io:println("> Initiator committed.");
}

isolated function rollbackDone(transaction:Info info, error? cause, boolean willRetry) {
    io:println("> Initiaor rollbacked.");
}

• Participant

import ballerina/http;
import ballerina/io;
import ballerina/sql;
import ballerinax/mysql;
import ballerinax/mysql.driver as _;

sql:ConnectionPool pool = {
    maxOpenConnections: 5,
    maxConnectionLifeTime: 30,
    minIdleConnections: 0
};

service / on new http:Listener(9090) {

    private mysql:Client testDB;

    function init() returns error? {
        self.testDB = check new (host = "localhost",
            user = "root",
            password = "root123",
            port = 3308, database = "test",
            connectionPool = pool,
            options = {useXADatasource: true}
        );
    }

    transactional resource function get updateDb() returns error? {
        transaction:onCommit(commitDone);
        transaction:onRollback(rollbackDone);
        sql:ParameterizedQuery query = `INSERT INTO test (hello) VALUES ('world')`;
        sql:ExecutionResult execResult = check self.testDB->execute(query);
        io:println("Affected row count: ", execResult.affectedRowCount);
        io:println("Inserted ID: ", execResult.lastInsertId);
    }
}

isolated function commitDone('transaction:Info info) {
    io:println("> Particiapnt committed.");
}

isolated function rollbackDone(transaction:Info info, error? cause, boolean willRetry) {
    io:println("> Participant rollbacked.");
}

Create required MySQL database and table

CREATE DATABASE test;
CREATE TABLE test.test (
  id INT NOT NULL AUTO_INCREMENT,
  hello VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);

Test the service
curl http://localhost:8080/doTransaction

Affected Version(s)

2201.8.2

OS, DB, other environment details and versions

MySQL database: 8.1.0 (running in docker)
Operating System: Windows 10

MySQL Connector

[[platform.java17.dependency]]
groupId = "mysql"
artifactId = "mysql-connector-java"
version = "8.0.20"

Related area

-> Runtime

Related issue(s) (optional)

No response

Suggested label(s) (optional)

No response

Suggested assignee(s) (optional)

No response

[dsplayerX]

I tried using the Java JDBC driver with h2 and SQLite databases, and they didn't work either.

[dsplayerX]

Same behavior with RabbitMQ as well. Participant says Notify(commit) received... but the onMessage on rabbitMQ request handler doesn't trigger.

[rdulmina]

Was able to narrow it down to the following removing the http module sample

import ballerina/io;
import ballerina/sql;
import ballerinax/mysql;
import ballerinax/mysql.driver as _;

final sql:ConnectionPool pool = {
    maxOpenConnections: 5,
    maxConnectionLifeTime: 30,
    minIdleConnections: 0
};

final mysql:Client testDB = check new (host = "localhost",
    user = "root",
    password = "root",
    port = 3306, database = "test",
    connectionPool = pool,
    options = {useXADatasource: true}
);

public function main() returns error? {
    transaction {
        transaction:onCommit(commitDone);
        transaction:onRollback(rollbackDone);
        future<error?> result = start callRemoteService();
        check wait result;
        check commit;
    }
}

transactional function callRemoteService() returns error? {
    sql:ExecutionResult execResult = check testDB->execute(`INSERT INTO test (hello) VALUES ('world')`);
    io:println("Affected row count: ", execResult.affectedRowCount);
    io:println("Inserted ID: ", execResult.lastInsertId);
}

isolated function commitDone('transaction:Info info) {
    io:println("> Initiator committed.");
}

isolated function rollbackDone(transaction:Info info, error? cause, boolean willRetry) {
    io:println("> Initiaor rollbacked.");
}

[rdulmina]

This is a regression issue because of the fix #41080. The issue is not there in 2201.7.x versions. Working on a possible fix ATM