Bug #99051 XA commit may do engine commit before MYSQL_BIN_LOG::ordered_commit
Submitted: 24 Mar 2020 11:03 Modified: 26 Mar 2020 7:25
Reporter: dennis GAO (OCA) Email Updates:
Status: Verified 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.