Bug #68465 Make slave_transaction_retries work with MTS
Submitted: 22 Feb 2013 18:57 Modified: 10 Jun 2013 18:11
Reporter: Yoshinori Matsunobu (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:5.6.10 OS:Any
Assigned to: Jon Stephens CPU Architecture:Any

[22 Feb 2013 18:57] Yoshinori Matsunobu
Description:
Currently MTS does not support slave_transaction_retries. I can not find this info from online manual but it's clearly described in rpl_slave.cc
----
/* MTS technical limitation no support of trans retry */
if (mi->rli->opt_slave_parallel_workers != 0 && slave_trans_retries != 0
----

Once one of worker threads gets transient errors (i.e. lock wait timeout), the whole SQL threads stop. Here is an example error message.
---
2013-02-21 13:37:38 23838 [Warning] Slave SQL: Error 'Lock wait timeout exceeded; try restarting transaction' on query. Default database:.....
2013-02-21 13:37:38 23838 [Warning] Slave SQL: ... The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details). Error_code: 1756
---

This is an issue especially when running some expensive DML statements directly on slaves (to avoid replication delay). Manual recovery is painful.

How to repeat:
1 Set slave_transaction_retries high enough
2 Run parallel SQL threads (i.e. slave_parallel_workers=30)
3 Connect to a slave from mysql client, then hold an InnoDB lock for a long time (i.e. BEGIN; UPDATE t1 SET a=100)
4 Run conflicting query on a master (i.e UPDATE t1 SET a=100 WHERE id=1; replicating to slave, then waiting for locks)
[10 Jun 2013 18:11] Jon Stephens
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.
[25 Sep 2014 10:26] Jon Stephens
A fix for this issue appears in MySQL 5.7.5.