0

I am using mysql-binlog-connector to listen to any binlog event and then perform replication on the slave DB. My problem is that, the binlog registers event right after the execution and before commit, so if there is any rollback the event is still picked up from the binlog entry and replicated on the slave. Is there any way around to deal with this issue?

Tamaghna Guha Thakurta
  • 3
  • 1

1 Answers1

1

From the binlog manual I get the impression that the mechanism works slightly differently:

Within an uncommitted transaction, all updates (UPDATE, DELETE, or INSERT) that change transactional tables such as InnoDB tables are cached until a COMMIT statement is received by the server. At that point, mysqld writes the entire transaction to the binary log before the COMMIT is executed.

In other words, until a COMMIT is actually issued, nothing from that transaction gets written to the binary log at all or, alternatively, the fact that the transaction made it to the binary log means that no ROLLBACK was issued!

Once a COMMIT has been issued: then first the complete transaction gets written to binary log then the COMMIT gets executed and lastly, only after the COMMIT has completed successfully, the COMMIT gets logged in the binary log.

The manual then continues with a couple of edge cases and their mitigating measures (sync_binlog=1 & --innodb_support_xa) that may alleviate your concerns:

As of MySQL 5.7.7, the binary log is synchronized to disk at each write by default (sync_binlog=1). Prior to MySQL 5.7.7, it is not (sync_binlog=0). So, prior to MySQL 5.7.7, if the operating system or machine (not only the MySQL server) crashes, there is a chance that the last statements of the binary log are lost. To prevent this, use the sync_binlog system variable to synchronize the binary log to disk after every N commit groups. See Section 5.1.4, “Server System Variables”. The safest value for sync_binlog is 1, but this is also the slowest. Even with sync_binlog set to 1, there is still the chance of inconsistency between the table content and binary log content in case of a crash.

For example, if you are using InnoDB tables and the MySQL server processes a COMMIT statement, it writes many prepared transactions to the binary log in sequence, synchronizes the binary log, and then commits this transaction into InnoDB. If the server crashes between those two operations, the transaction is rolled back by InnoDB at restart but still exists in the binary log. Such an issue is resolved assuming --innodb_support_xa is set to 1

HBruijn
  • 77,029
  • 24
  • 135
  • 201
  • 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. Updates to nontransactional tables are stored in the binary log immediately after execution. Within an uncommitted transaction, all updates (UPDATE, DELETE, or INSERT) that change transactional tables such as InnoDB tables are cached until a COMMIT statement is received by the server. At that point, mysqld writes the entire transaction to the binary log before the COMMIT is executed. – Tamaghna Guha Thakurta Jan 04 '16 at 15:57
  • I read this part from the manual and had that understanding. But it seems I was kinda on the wrong track. Thank you so much for correcting me. It was super helpful. – Tamaghna Guha Thakurta Jan 04 '16 at 15:58