MySQL Bugs: #99051: XA commit may do engine commit before MYSQL_BIN_LOG::ordered

Bug #99051	XA commit may do engine commit before MYSQL_BIN_LOG::ordered_commit
Submitted:	24 Mar 2020 11:03	Modified:	20 Apr 2021 16:02
Reporter:	dennis GAO (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: XA transactions	Severity:	S3 (Non-critical)
Version:	5.7.25	OS:	Ubuntu (18.04.4 LTS)
Assigned to:		CPU Architecture:	x86
Tags:	Contribution

Description:
In ubuntu 18.04 the xa commit operation may do a engine commit before binlog flush.

If "xa commit" is done on the xid from "xa recover", it may invoke the function ha_commit_or_rollback_by_xid, which will invoke plugin_foreach_with_mask to loop all engine plugins to do xacommit_handlerton.

In ubuntu 16.04, the binlog plugin will be invoked before innodb plugin, so the MYSQL_BIN_LOG::ordered_commit will be done before innobase_commit_by_xid. The binlog will be sync to disk first.

But in ubuntu 18.04, the innodb plugin will be invoked before binlog plugin, if mysql crash after innobase_commit_by_xid and before MYSQL_BIN_LOG::ordered_commit, the innodb engine will in-consistence with the binlog file, which will lead replication broken.

For detail, plugin_foreach_with_mask function may generate different sequence for plugins in ubuntu 16.04 and ubuntu 18.04.
And the innodb plugin may be before binlog plugin.

How to repeat:
Do the following test on Ubuntu 18.04 (can use docker image: "FROM ubuntu:18.04")

In mysql5.7.25 (5.7.29 has the same problem), and use the following compile command:

cmake  -DDOWNLOAD_BOOST=1 -DWITH_BOOST=`pwd`/../boost  -DCMAKE_INSTALL_PREFIX=`pwd`/../install_debug   -DWITH_INNODB_MEMCACHED=ON ..

compile and install mysql, then do the following test by using gdb.

Add a break-point at the beginning of MYSQL_BIN_LOG::ordered_commit, then execute the following sql:

mysql> xa start '1';
mysql> insert into t1 values (1);
mysql> xa end '1';
mysql> xa prepare '1';
mysql> quit

then login again execute:

mysql> xa commit '1';  //-------> blocked by gdb

quit the gdb to simulate the mysql crash, and start-up again.

mysql> select * from t1;

we will find the "1" has been inserted, but in binlog, there is no related "xa commit" event.

Suggested fix:
IMO, the best way is to ensure the sequence in the plugin_hash[1], the binlog plugin should be before all the transnational engine plugins.

And I will provide another solution to adjust the sequence in plugin_foreach_with_mask  to ensue the generated plugins vector will always put the binlog plugin in the first.

adding the patch as contribution (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: ensure_binlog_plugin_first-v4.diff (text/x-patch), 4.44 KiB.

Hello dennis GAO,

Thank you for the report and contribution.

regards,
Umesh

The suggested fix was implemented as part of the Clone work which was
released in 8.0.18.

Change was part of

commit 951464867b53c12d5430f8f6b2d0e50bd849bb8d
Author: Debarun Banerjee <debarun.banerjee@oracle.com>
Date:   Fri May 10 20:28:59 2019 +0530

Posted by developer:
 
Fixed in 8.0.18.

For XA COMMIT statements, invocation order of the plugins involved in
statement execution was nondeterministic, which could lead to
replication problems. Thanks to Dennis Gao for contributing a fix.