MySQL Bugs: #68465: Make slave_transaction

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

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)

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.

A fix for this issue appears in MySQL 5.7.5.