php - MySQL Transaction loses record after SELECT statement

Question

I encountered a quite strange bug. The situation is as follows: We have a client based order-software (online, PHP) that currently runs the websites of 3 clients. We have one server divided into 2 separate webspaces:

• client 1 and 2 share the same code, they both sit on webspace 1
• client 3 sits on a separate webspace and uses an identical copy of the code (apparently this was necessary "because of reasons" a few years ago)

Every client connects to the same database (InnoDB). All MySQL-Queries for an order are executed in a transaction (isolation level SERIALIZABLE, autocommit = 0). The database tables don't have any triggers or functions.

SOMETIMES (once or twice a day maximum) client 2 or 3 suddenly "lose" a record (the one that holds the order) that has been created in the first few queries of the transaction! Client 1 NEVER loses anything despite having MUCH more orders per day than client 2 and 3 (client 1 >500 orders, client 2 and 3 between 10 and 20 orders). According to our provider there is no MySQL-Error visible inside the logs. That matches what my debugging looks like.

Now it gets really strange. After weeks of gradually debugging down the rabbit hole I found the one single query that causes the order record to disappear - it's a simple SELECT!

Debugging:

// ... start transaction
// ... create order record
// ... many other mysql requests

// order_exists() just checks if the order exists using a simple select statement
$s_order_debug .= "1-A: ".var_export(mod_shop_model::order_exists($i_order_id), true).' ('.$this->get_error($i_link_index).')'."
";

$dbres_query_return = mysqli_query($this->arr_link[$i_link_index], $s_query);

$s_order_debug .= "1-B: ".var_export(mod_shop_model::order_exists($i_order_id), true).' ('.$this->get_error($i_link_index).') '."
";
$s_order_debug .= $s_query."
";

// ... some other requests that fail because the order record is missing
// ... commit transaction

Output:

1-A: true ()
1-B: false ()
SELECT `product_id`, `senddate`, `specialprice_pickup_amount` FROM `mod_shop_cart`  WHERE `client_id` = '2' AND `customer_id` = '10107';

The error occurs with client_id = 2 or client_id = 3, but never with client_id = 1. And like I said, it only happens occasionally.

Honestly, I am absolutely clueless why this happens. Sure, there are many queries inside that transaction, but if it was generally too much, the error would appear more often, doesn't it? It just occurs once or twice a day at differents times of the day.

Do you have a clue on how to possibly fix this?

question from:https://stackoverflow.com/questions/66061937/mysql-transaction-loses-record-after-select-statement

Answer 1

Waitting for answers