MySQL Bugs: #95381: 'SHOW BINARY LOGS' stalls transactions

Bug #95381	'SHOW BINARY LOGS' stalls transactions
Submitted:	15 May 2019 16:45	Modified:	25 Jul 2019 10:58
Reporter:	Domas Mituzas	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If a server has enough binary logs, there's a chance that 'SHOW BINARY LOGS' command will block writing to network, while holding binary log index mutex. 

as binary log index mutex is needed during write path (e.g. transaction log rotation), that escalates to none of transactions working if there is any slow client doing 'SHOW BINARY LOGS'

How to repeat:
client 1: "SHOW BINARY LOGS" & stall
client 2: "FLUSH LOGS" (should be blocked by 1)
client 3: INSERT.... (should be blocked by 2)

Suggested fix:
* buffer 'SHOW BINARY LOGS' as in-memory temptable (or collect minimum amount of data needed for consistency and then unlock?
* introduce a dirtier mode that would not hold the mutex during the command? 
* allow transactions to proceed during the rotation?

Hi Domas,

Verified as stated. Not much to add to this you said it all.

all best
Bogdan

[15 July 2019 0:46] Sven Sandberg

Thank you for the bug report! This is indeed a problem in 5.6, but it was
fixed in 5.7: see https://dev.mysql.com/worklog/task/?id=5721 . It is a
feature that required a significant refactoring, so unfortunately it is much
too risky to backport to 5.6. Please, consider upgrading the server.