0

I am using Percona Server for MySQL 8.0.x. I have setup replication between two instances with GTID mode turned OFF. Almost all UPDATE and INSERTS are wrapped in the transaction from the application side.

On the slave server when looking at the binlog coming from master, I noticed there are lots of empty transactions. What are those and why are they present in the binlog when there is nothing to be executed in that transaction.

/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1587391567871055 (2020-04-20 14:06:07.871055 UTC)
# immediate_commit_timestamp=1587391567871055 (2020-04-20 14:06:07.871055 UTC)
/*!80001 SET @@session.original_commit_timestamp=1587391567871055*//*!*/;
/*!80014 SET @@session.original_server_version=80019*//*!*/;
/*!80014 SET @@session.immediate_server_version=80019*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
SET TIMESTAMP=1587391567/*!*/;
BEGIN
/*!*/;
COMMIT/*!*/;
0

After some research and further looking into MySQL documentation, it turned out to be it was an error on my part.

I was using mysqlbinlog -s option to view binlog. Because the replication statement is coming over as Row Based Replication, it is not possible to see the actual statement.

You cannot see on the slave what statements were received from the master and executed.

Advantages and Disadvantages of Statement-Based and Row-Based Replication

So when trying to view the binlog using the mysqlbinlog -s util, it doesn't decode this blob of information and hence shows up as blank/empty commit.

Hope this helps someone else runs into to similar situation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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