MySQL Bugs: #99625: Replaying Binary log returns ER_CANT_SET_GTID_NEXT_WHEN_OWNING

Bug #99625	Replaying Binary log returns ER_CANT_SET_GTID_NEXT_WHEN_OWNING_GTID
Submitted:	19 May 2020 7:33	Modified:	19 May 2020 12:23
Reporter:	Marc Reilly	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: XA transactions	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Linux
Assigned to:		CPU Architecture:	x86
Tags:	ER_CANT_SET_GTID_NEXT_WHEN_OWNING_GTID, gtid_next, xa

Description:
When replaying binary logs containing multiple `XA COMMIT ... ONE PHASE` transactions to a 57 server the binlog replay fails with ER_CANT_SET_GTID_NEXT_WHEN_OWNING_GTID.

Testing on 5.7.29 this is reproducible
Testing on 8.0.11 this is NOT reproducible.

How to repeat:
1. Create 5.7.29  and 8.0.11 db

2. Create test database and table
    CREATE DATABASE xatest;
    create table xatest.t1(id int);

3. 
a. Execute this transaction multiple times on the 5.7.29 db (e.g. 10 times)
XA START 'xatest';
INSERT INTO xatest.t1 VALUES(10);
XA END 'xatest';
XA COMMIT 'xatest' ONE PHASE;
## Load binlog file:
mysqlbinlog mysql-bin.000002 -D | mysql -umyuser -pmypass

OR

b. Attempt to load the attached binlog file:
    mysqlbinlog mysql-bin.000002 -D | mysql -umyuser -pmypass

4. The following will be observed when replaying the 5.7.29 binlog:
    1. 5.7.29: ERROR 1790 (HY000) at line 54: @@SESSION.GTID_NEXT cannot be changed by a client that owns a GTID. The client owns ANONYMOUS. Ownership is released on COMMIT or ROLLBACK.
    2. 8.0.11: SUCCESSFUL

my.cnf
log-error=/var/log/mysql/error/mysql.err
log-bin=/var/log/mysql/binlog/mysql-bin
binlog_format = 'ROW'
gtid-mode = 'OFF_PERMISSIVE'
sql_mode='NO_ENGINE_SUBSTITUTION'

$ mysqlbinlog --version
mysqlbinlog Ver 3.4 for linux-glibc2.12 at x86_64

Suggested fix:
Looking at the release notes there were a number of changes to XA transactions and how GTID_NEXT is handled that may have fixed this. 
I was able to repro on 8.0.4rc which also suggests its fixed by the 8.0.11 XA changes:
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-11.html

Hi Mr. Reilly,

Thank you for your bug report.

I have managed to repeat this bug in the latest 5.7, but not in the latest 8.0.

Hence, this is only 5.7-only bug. It is up to Development to decide whether to fix it or not in mysql-5.7.

Verified as reported.

The issue is also reproducible when GTID is enabled.

I tried same steps after creating a DB instance with the following parameters in the my.cnf:

log-error=/var/log/mysql/error/mysql.err
log-bin=/var/log/mysql/binlog/mysql-bin
binlog_format = 'ROW'
enforce_gtid_consistency = 'ON'
gtid-mode = 'ON'
sql_mode='NO_ENGINE_SUBSTITUTION'

Created same XA transactions as Marc described above and got the following error when loading the binary log:

ERROR 1790 (HY000) at line 90: @@SESSION.GTID_NEXT cannot be changed by a client that owns a GTID. The client owns e37a0ddb-9a0d-11ea-b65a-063704527866:10. Ownership is released on COMMIT or ROLLBACK.

In case GTID is DISABLED, skipping the GTIDs when loading the binary logs can workaround the issue.

mysqlbinlog can skip GTIDs when loading binary logs by adding the following lines in my.cnf:

[mysqlbinlog]
skip-gtids

Again, this is only viable if GTID is DISABLED.

Thank you, Mr. Garieb.