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:
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

[24 Mar 2020 11:03] dennis GAO
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.
[24 Mar 2020 11:13] dennis GAO
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.

[26 Mar 2020 7:25] MySQL Verification Team
Hello dennis GAO,

Thank you for the report and contribution.

[16 Apr 2021 14:11] Ståle Deraas
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
[20 Apr 2021 16:02] Paul DuBois
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.