Slave fails to apply relay log if certain XA transaction exists
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
||||
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.5 |
New
|
Undecided
|
Unassigned | |||
5.6 |
New
|
Undecided
|
Unassigned | |||
5.7 |
Fix Released
|
Undecided
|
Vlad Lesin |
Bug Description
There is some error in slave SQL thread which makes it to fail applying XA transactions, existing in the binary log.
It fails with different errors for the same binary log:
ast_SQL_Error: Error 'XAER_DUPID: The XID already exists' on query. Default database: 'test'. Query: 'XA START X'6532363831316
ast_Error: Error 'XAER_RMFAIL: The command cannot be executed when global transaction is in the ACTIVE state' on query. Default database: 'test'. Query: 'XA START X'3963316134656
Exec_Master_
Error in Xid_log_event: Commit could not be completed, 'XAER_RMFAIL: The command cannot be executed when global transaction is in the NON-EXISTING state'
How to repeat:
1. Setup master-slave, but don't start slave yet
2. Copy binary log file into master's data directory, edit index file accordingly
3.
stop slave;
reset slave all;
drop database nsm;
reset master;
change master to master_
start slave;
4. Wait when slave receives error
Unfortunately we were not able to find sequence of actions which lead to creation of such a binary log, but I believe this happens in code which encodes/decodes XID name
All details and data in ticket #159852. Contact Sveta, Przemek or Marcos if you need help in getting corrupted binary log.
tags: | added: upstream |
I was able to repeat generation of such binary logs on slave using dump of customer's data and entries in their binary log. I cannot repeat same issue with artificial SQL.
Instructions:
1. Use mysql-5.7.17 or Percona Server 5.7.17. I used MySQL, thus master port is 13000 and slave port is 13001. In case if you use Percona Server adjust ports accordingly (13001 and 13002). I also assume you put test files into same mysql-test dir. If not adjust paths accordingly. --gtid_ mode=ON --mysqld= --log-slave- updates --mysqld= --enforce- gtid-consistenc y --mysqld= --binlog- format= row --mysqld= --lower- case-table- names --mysqld= --transaction- isolation= read-committed &
2. cd mysql-test
3. ./mtr --start --suite=rpl rpl_alter --mysqld=
4. ../bin/mysql -uroot -h127.0.0.1 -P13000 -e "create database XXX"
5. ../bin/mysql -uroot -h127.0.0.1 -P13000 XXX < XXX.sql
6. Now connect two clients to master and run tests (order matters!):
session 1> source 36742_nogtids.sql.1
session 2> source 36743_nogtids.sql.1
session 2> source 36743_nogtids.sql.2
session 1> source 36742_nogtids.sql.2
session 1> source 36742_nogtids.sql.3
session 1> source 36742_nogtids.sql.4
session 1> source 36742_nogtids.sql.5
session 1> source 36742_nogtids.sql.6
session 2> source 36743_nogtids.sql.3
session 2> source 36743_nogtids.sql.4
session 2> source 36743_nogtids.sql.5
session 2> source 36743_nogtids.sql.6
session 1> \d ; cf17-8d3c- fc43-18416dffb3 9f,-a3ad- 0e5355dde20f' ;
session 1> xa commit 'e26811b9-
session 2> \d ; 2b85-2e8f- 1c8b-50de970226 f0,-863b- f8bfb9554802' ;
session 2> xa commit '9c1a4eec-
7. Now connect to slave and run SHOW SLAVE STATUS\G
You will see something like this:
Slave_ SQL_Running_ State: System lock
Retrieved_ Gtid_Set: b2184f1d- fa76-11e6- 9631-90b11c5531 2a:1-1429
Executed_ Gtid_Set: b2184f1d- fa76-11e6- 9631-90b11c5531 2a:1-1426
...
8. After some time slave will fail with error:
Last_Errno: 1205 on_retries variable.
Last_Error: Slave SQL thread retried transaction 10 time(s) in vain, giving up. Consider raising the value of the slave_transacti
All data is in the ticket.