Bug #104687 purge binlog,show variables,binlog rotate will cause deadlock
Submitted: 23 Aug 2021 7:33 Modified: 30 Aug 2021 10:00
Reporter: lockli lockli Email Updates:
Status: Unsupported Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.7.22-5.7.24 OS:Any
Assigned to: CPU Architecture:Any
Tags: purge binlog,show variableles,binlog rotate,deadlock

[23 Aug 2021 7:33] lockli lockli
We use MySQL 5.7.24, we have notice MySQL will hang.Can not write record to DB.when this three operations  happen:
1,show binlog or binlog rotate
2,show variabels
3,purge binlog

Cause this problem:
1,Not write any record to DB.
2,New connection can not attact to DB.finaly ,Instance will report error "to many connection" .I quess,the connections have up to max connection =3500 ,
3,The long live connection  can execute select,So we HA can not change master to semi slave.

How to repeat:
1,In test DB.Execute "flush logs",Create many binlog file.
2,Ready two script,one is execute "purge binlog" ,another execute "show variables". 

when we execute the two scripts. It will repeat .
[23 Aug 2021 13:21] MySQL Verification Team
Hi Mr. lockli,

Thank you for your bug report.

We have run your test case on the latest 5.7.35 and we were not able to repeat it.

Also, do note that 5.5 is not supported any more and that your release of 5.7 is couple of years old.

Last, but not least, do note that flushing logs require all current DML operations and background threads to finish before it could be executed.
[30 Aug 2021 10:00] lockli lockli
fix it: the problem version is :5.7.22-5.7.24
[30 Aug 2021 12:26] MySQL Verification Team
Hi Mr. lockli,

Thank you for ordering us to fix the ancient releases.

What you report are not independent versions that are separately maintained. In this case, the version is MySQL 5.7 and all bugs are released in the new releases. Hence, you need to test your problem on the latest release of the 5.7.

Also, do note our explanation in the last comment, which is also valid for 5.7.35, as well. 

Next, deadlocks are expected with the transactional storage engines, particularly when dealing with handing of the external files, like logs. This is , actually, expected behaviour. You should do your log maintenance in the single thread, in order to avoid deadlocks and long waits.