Lost connection to the database during COMMIT query: did the transaction succeed or not?

Question

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:

• Is a Postgres long-running query aborted if the connection is lost/broken?
• What happens if the database node's network fails just after commit and before responding to the client
• Conditions that can make a MySQL COMMIT query fail?
• What if a connection closes in the middle of a commit?
• https://stackoverflow.com/questions/3960189/can-a-commit-statement-in-sql-ever-fail-how

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

Thank you!

Answer 1

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.