1

PROBLEM

I recently encountered the error 2013 in MySql (Lost connection to MySQL server during query) while executing the COMMIT statement for a transaction (the previous update statements succeeded). This was due to a network downtime error during the commit.

Then I started a new connection, and upon checking the transaction effects I could see that the transaction was indeed committed successfully and there were no errors (the changes were applied).

QUESTIONS

How can I be 100% sure that the commit succeeded when there are errors during the COMMIT query? Did anyone find a similar problem? What would be the best approach here when this ambiguity arises? Is there a way to avoid this 'commit ambiguity'?

PREVIOUS RESEARCH

I found some related questions to other databases and MySql here:

But it doesn't seem that there is a way to avoid this problem?

Thank you!

1 Answer 1

0

What immediately comes to mind is the binary log.

The COMMIT; command is always logged in the binary logs. You can prove that by running the mysqlbinlog program against a binary log and you should see some.

Someone posted this example in Is reply to client about transaction commit, before binary log or after?

# at 1016
[..]
SET TIMESTAMP=1458127130/*!*/;
create table tr (i int) engine=innodb
[...]
# at 1281
[..]
SET TIMESTAMP=1458127136/*!*/;
BEGIN
/*!*/;
# at 1384
[..]
SET TIMESTAMP=1458127136/*!*/;
insert into tr values(1),(2),(3)
/*!*/;
# at 1514
[...]
COMMIT/*!*/;

Please note what the MySQL Documentation says in "The Binary Log"

Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done. This ensures that the log is logged in commit order.

This implies that even if the COMMIT; does not make it to the binary log, the changes are recorded in the binary log before the COMMIT; is.

1
  • Thanks! Is there a way to check the binary logs from a MySQL client? Ideally I would want to know if the commit failed or not at the application level, because if I get the 2013 error the state seems to be ambiguous! Commented May 22, 2023 at 2:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.