Today I have identified, when our application job runs( monthly twice) it creates 4 parallel connections to the DB and INSERTS lot of data into multiple tables. Out of this 4 connections, one of the connection successfully inserted data into the tables initially (around 13000) and after certain time all the INSERTS (around 900) executed by this particular connection were recorded in the audit log but were not executed in the database. During this time rest of the connections were successfully inserting data into those tables, meaning there was no issue with the table locks etc, because if that was the case then other 3 sessions should not INSERT data... At the end of the JOB all the 4 connection successfully log out of the database ( I can see the DISCONNECT from audit logs)..

I assume that audit logging plugin is some how interacting in between the client and the database and stopping the client inserts reaching the database and also not giving any kind of errors back to application and application assumes all the inserts were successful.

I want some insights from MariaDB team to understand the issue.. It would be great if someone have a look into the issue.

Thank You
Jaya

can you attach the relevant part of the audit log, please?

Hi Sergei, Thanks for picking this ticket..

If I include all the missing statements the file size is very big, For reference I picked two queries which are written to audit log but not executed in DB.. Let me know if you need all the statements... I modified the db hostname, username and client IP in below statements.

20231101 09:10:08,db_hostname,DBService,<clinet IP>,160975436,6521714934,QUERY,payment,'DELETE FROM metadata  WHERE `key` in (\'lastUpdate\', \'currentRun\', \'lastPendingAggregation\')',0

20231101 09:12:15,db_hostname,DBService,<clinet IP>,160975436,6522063976,QUERY,payment,'INSERT INTO paymentIdTable (paymentId, sourceTable) VALUES (4558005, \'202011\'),(4562672, \'202011\'),(4591382, \'202011\'),(4643417, \'202011\'),(4659498, \'202011\')',0

What do you mean, there is nothing in binlog? Perhaps you replicate in row mode and DELETE did not find any rows to delete? Then you would not have any Delete_row events in the binary log. INSERT should've always worked, though.

Audit plugin doesn't (or at least shouldn't) interact with the client or error status, it's a passive observer. And it shouldn't be possible for it to log a failed INSERT as successfully completed.

I just pasted two sample queries, but we missed around 800 INSERT statements which are written to audit log with 0 retcode but not executed inside the database and not even logged to binary logs. We are using STATEMENT based binary logging. Later we manually inserted that data taking INSERT statements from audit logs. Let me know if you want me to attache few more statements from audit logs.

Hi Sergei, It would be great if we get some insights from MariaDB Team about the issue we faced.. so we can confidently use the MariaDB for our production databases...

Regards
Jaya

I don't see have any insights to provide and don't see how that could've happened.

Except that if your DML statements were part of the transaction that was later rolled back? Like

START TRANSACTION;

DELETE FROM metadata  WHERE `key` in ('lastUpdate', 'currentRun', 'lastPendingAggregation');

INSERT INTO paymentIdTable (paymentId, sourceTable) VALUES (4558005, '202011'),(4562672, '202011'),(4591382, '202011'),(4643417, '202011'),(4659498, '202011');

ROLLBACK;

You wrote you have autocommit enabled, but a transaction can be started explicitly too
If that was not the case — we need a way to reproduce this bug somehow.

We are not using transactions to run statements, I don't think we can reproduce this issue as we don't have a clue what happened. We have not seen this issue in the past couple of months..

Thanks again for your support. You can close this ticket as I can't reproduce the issue.