Ubuntu 20

I think this might be the same problem that has been described in MXS-3508 but now with an actual test that proves it. Can you check if causal_reads=local makes the problem go away? If so I think we've narrowed the problem down to the global GTID tracking in MaxScale.

It's possible that the global GTID synchronization for some reason isn't picking up the latest change.

I think I've managed to reproduce the problem and I also think I know why it happens. If two connections both execute a transaction, they both receive a GTID and they appear in the correct order in the database. The problem is that the responses that are delivered to MaxScale aren't guaranteed to arrive in order which means the global latest GTID could end up being set to the lower of the two values. The reason why this won't happen with causal_reads=local is due to the lack of parallellism in the traffic.

Many thanks for fixing our issue Markus.
We cannot use causal_reads value as local - in our case microservices are communicating each other through different database connections, by passing over record IDs; therefore local value will made this problem even worse.
Did you run our test created by Domas to see if problem is gone away? When do you expect next release will take place?

I used the test to reproduce the problem and with the fix in place it no longer reports any errors. Unfortunately I don't know when the next release will be.

I have completed several tests and can confirm that the issue still exists with the latest 2.5.15 built directly from repository.

root@maxscale-2:/usr/bin# ./maxscale -V
MaxScale 2.5.15 - 636cf1678fc6459d9c61969e2ecf32e045cb393c
root@maxscale-2:/usr/bin#

I have tested with causal_reads=global.

...
-------> ID not found => 16938
-------> ID not found => 16952
17000 rec inserted.
17100 rec inserted.
17200 rec inserted.
17300 rec inserted.
17400 rec inserted.
17500 rec inserted.
-------> ID not found => 17584
17600 rec inserted.
-------> ID not found => 17623
-------> ID not found => 17699

Hmm, that is strange. The GTID handling should work correctly now. For a sanity check, you could try disabling causal_reads=global and seeing if that makes the test fail a lot faster.

I'll try to reproduce your results locally and see if I can figure out what's going on.

Markus, disabling causal_reads or changing it to "local" value won't solve the issue. In our particular case, we have a microservice infrastructure, where modules are communicating through RabbitMQ queues - therefore, are using different DB connections for same data. As far as we understand, only "global" setting ensures all connections see data changes: https://mariadb.com/docs/reference/mxs/module-parameters/causal_reads/.

I tested again with this modified test script:

<?php

$conn_write = mysqli_connect("127.0.0.1", "maxuser", "maxpwd", "test", 4006);

if (!$conn_write) {

    die("Connection failed: " . mysqli_connect_error() . "\n");

}

$conn_read = mysqli_connect("127.0.0.1", "maxuser", "maxpwd", "test", 4006);

if (!$conn_read) {

    die("Connection failed: " . mysqli_connect_error() . "\n");

}

echo "Connected successfully\n";

$res = $conn_write->query("CREATE TABLE IF NOT EXISTS t1(id SERIAL, LastName VARCHAR(255), FirstName VARCHAR(255), Age INT)");

if (!$res){

    die($conn->error . "\n");

}

while (1){

    $conn_write->begin_transaction();

    $id = -1;

    try {

        $conn_write->query("INSERT INTO t1 (LastName, FirstName, Age) VALUES ('Jonas', 'Testas', 44)");

        $id = $conn_write->insert_id;

        $conn_write->commit();

    } catch (mysqli_sql_exception $exception) {

        $conn_write->rollback();

        throw "Err: " . $exception . "\n";

    }

    if ($id == -1){

        die("ID is -1\n");

    }

    $q = "SELECT @@server_id, count(id), @@gtid_slave_pos FROM t1 WHERE id=" . $id;

    $result = $conn_read->query($q);

    if (!$result){

        echo "ERROR" . $conn_read->error . "\n";

        break;

    }

    $row = $result->fetch_row();

    if (!$row){

        die("Empty result for ${id}\n");

    } else if ($row[1] != 1){

        $server_id = $row[0];

        $rows = $row[1];

        $slave_pos = $row[2];

        $thr_read = $conn_read->thread_id;

        $thr_write = $conn_write->thread_id;

        echo "reader: ${thr_read} writer: ${thr_write} ID not found from ${server_id} ${slave_pos} => $id\n";

        exit(1);

    }

}

?>

Would it be possible for you to test if you still see the problem with this modified script?

Note that it uses two separate connections for reading, doesn't truncate the table at the start and uses an auto-increment field to allow multiple instances of it to be run in parallel. With 50 parallel executions of this script and sysbench with 100 threads, I can't reproduce this with the latest code. Reverting the fix makes the test fail pretty much immediately.

This issue is marked as fixed, but @Domas wrote that they were still able to reproduce this issue even after the fix. It this actually confirmed as resolved @markus makela ?

alexander-zierhut yes, the original problem that was reproduced was fixed. So far we haven't heard of any causality violations and, as stated in the comments, the results they saw weren't reproducible. If you've observed any problems, please let us know and we can investigate the source of them.