backup MTS slave appear dead lock of the binlog

Bug #1738943 reported by luojia on 2017-12-19
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona XtraBackup moved to https://jira.percona.com/projects/PXB
New
Undecided
Unassigned

Bug Description

mysql vesion:5.7.20-18-log Percona Server (GPL), Release 18, Revision 7ce54a6deef
variables :slave_preserve_commit_order=1
innobackupex:2.4.5 Linux (x86_64) (revision id: e41c0be)
command:sudo ${innodbackup} --defaults-file=${mycnf} --user=${user} --password=${password} --slave-info --lock-wait-threshold=5 --lock-wait-query-type=all --lock-wait-timeout=60 --ftwrl-wait-timeout=60 --ftwrl-wait-threshold=60 --stream=tar /opt/tmp --no-timestamp --socket=${socket} | lz4 -B4 | pv -q -L${netlimit} | nc $localhost $port
we have two problem, one list:
the host show processlist info
2017-12-15 09:55:28 297531 system user Connect 1961580 Waiting for an event from Coordinator 1961580684 0
2017-12-15 09:55:28 297528 system user Connect 1961580 Waiting for an event from Coordinator 1961580686 0
2017-12-15 09:55:28 297529 system user Connect 1961580 Waiting for an event from Coordinator 1961580685 0
2017-12-15 09:55:28 297526 system user Connect 13826 Waiting for an event from Coordinator 13826667 0
2017-12-15 09:55:28 297527 system user Connect 26859 Waiting for an event from Coordinator 26858667 0
2017-12-15 09:55:28 297524 system user Connect 10047 Waiting for backup lock insert into sbtest 10047471 0
2017-12-15 09:55:28 297525 system user Connect 10047 Waiting for preceding transaction to commit 10047470 0
2017-12-15 09:55:28 2006785 backup localhost Query 9440 Waiting for binlog lock LOCK BINLOG FOR BACKUP 9440487 0
2017-12-15 09:55:28 297522 system user Connect 1961580 Waiting for master to send event 1961580688 0
2017-12-15 09:55:28 297523 system user Connect 10047 Waiting for dependent transaction to commit 10047468 0
it happen deadlock, lock long time util we kill thread no 2006785.sbtest is MyiSAM,backup execute "lock table" before "lock binlog".the slave thread had lock binlog.
it can repeat,you insert a innodb table and a MyiSaM table.

another one:
the host show processlist info
2017-12-14 06:24:18 588 system user Connect 3750333 Waiting for an event from Coordinator 3750332324 0
2017-12-14 06:24:18 586 system user Connect 3750333 Waiting for an event from Coordinator 3750332325 0
2017-12-14 06:24:18 587 system user Connect 3750333 Waiting for an event from Coordinator 3750332324 0
2017-12-14 06:24:18 584 system user Connect 3750333 Waiting for an event from Coordinator 3750332325 0
2017-12-14 06:24:18 585 system user Connect 3750333 Waiting for an event from Coordinator 3750332325 0
2017-12-14 06:24:18 582 system user Connect 2515123 Waiting for an event from Coordinator 2515122828 0
2017-12-14 06:24:18 583 system user Connect 3750333 Waiting for an event from Coordinator 3750332326 0
2017-12-14 06:24:18 580 system user Connect 1839 Waiting for an event from Coordinator 1838976 0
2017-12-14 06:24:18 581 system user Connect 9613 Waiting for an event from Coordinator 9612453 0
2017-12-14 06:24:18 578 system user Connect 1304 Waiting for an event from Coordinator 1303357 0
2017-12-14 06:24:18 579 system user Connect 1308 Waiting for an event from Coordinator 1308041 0
2017-12-14 06:24:18 576 system user Connect 1292 Waiting for preceding transaction to commit 1291873 0
2017-12-14 06:24:18 577 system user Connect 1294 Waiting for an event from Coordinator 1293441 0
2017-12-14 06:24:18 574 system user Connect 1292 Waiting for preceding transaction to commit 1291873 0
2017-12-14 06:24:18 575 system user Connect 1292 Waiting for preceding transaction to commit 1291873 0
2017-12-14 06:24:18 573 system user Connect 1292 Waiting for binlog lock 1291704 0
2017-12-14 06:24:18 9412666 backup localhost Query 1292 Waiting for binlog lock LOCK BINLOG FOR BACKUP 1291869 0
2017-12-14 06:24:18 571 system user Connect 3750333 Waiting for master to send event 3750332332 0
2017-12-14 06:24:18 572 system user Connect 1292 Waiting for dependent transaction to commit

We hope this feature that locktimeout can be used,thanks

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXB-1490

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers