Description:
Flush logs taking forever and it is in starting state.
As flush logs implicitly calls different flush commands only FLUSH RELAY LOGS is having issue. Rest all working fine without any issues.
Working :
FLUSH BINARY LOGS
FLUSH ENGINE LOGS
FLUSH ERROR LOGS
FLUSH GENERAL LOGS
FLUSH SLOW LOGS
Not Working:
FLUSH RELAY LOGS
The following is the show process list captured from the database.
mysql> show processlist;
+------+-------------+---------------------------------------+-------------+-------------+-------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-------------+---------------------------------------+-------------+-------------+-------+---------------------------------------------------------------+------------------+
| 6 | system user | connecting host | NULL | Connect | 20367 | Queueing master event to the relay log | NULL |
| 9 | monitoring | hostname:40684 | NULL | Sleep | 2 | | NULL |
| 331 | app_user | hostname:55260 | app_database| Sleep | 14 | | NULL |
| 332 | app_user | hostname:55262 | app_database| Sleep | 47 | | NULL |
| 351 | monitoring | hostname:44182 | NULL | Sleep | 25 | | NULL |
| 354 | app_user | hostname:50434 | app_database| Sleep | 14 | | NULL |
| 355 | app_user | hostname:50436 | app_database| Sleep | 38 | | NULL |
| 381 | app_user | hostname:53316 | app_database| Sleep | 14 | | NULL |
| 382 | app_user | hostname:53318 | app_database| Sleep | 117 | | NULL |
| 383 | app_user | hostname:53320 | app_database| Sleep | 279 | | NULL |
| 999 | dbadminuser | localhost | mysql | Killed | 12266 | starting | flush logs |
| 1099 | dbadminuser | localhost | mysql | Query | 11388 | starting | flush logs |
| 1221 | dbadminuser | localhost | mysql | Query | 10487 | starting | flush logs |
| 1334 | dbadminuser | localhost | mysql | Query | 9588 | starting | flush logs |
| 1433 | root | localhost | sys | Query | 7425 | waiting for handler commit | stop slave |
| 1454 | dbadminuser | localhost | mysql | Query | 8691 | starting | flush logs |
| 1566 | dbadminuser | localhost | mysql | Query | 7787 | starting | flush logs |
| 1624 | repl_user | hostname:33896 | NULL | Binlog Dump | 7399 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 1628 | root | localhost | NULL | Query | 6949 | starting | start slave |
| 1685 | root | localhost | NULL | Query | 4252 | starting | FLUSH RELAY LOGS |
| 1687 | dbadminuser | localhost | mysql | Query | 6877 | starting | flush logs |
| 1798 | dbadminuser | localhost | mysql | Query | 5985 | starting | flush logs |
| 1913 | dbadminuser | localhost | mysql | Query | 5085 | starting | flush logs |
| 2024 | dbadminuser | localhost | mysql | Query | 4183 | starting | flush logs |
| 2141 | dbadminuser | localhost | mysql | Query | 3285 | starting | flush logs |
| 2250 | dbadminuser | localhost | mysql | Query | 2383 | starting | flush logs |
| 2349 | monitoring | hostname:62411 | NULL | Sleep | 0 | | NULL |
| 2438 | monitoring | hostname:58438 | NULL | Sleep | 46 | | NULL |
| 2497 | monitoring | hostname:49979 | NULL | Sleep | 18 | | NULL |
| 2549 | root | localhost | NULL | Query | 0 | init | show processlist |
+------+-------------+---------------------------------------+-------------+-------------+-------+---------------------------------------------------------------+------------------+
How to repeat:
The same behavior observed in 2 different master-master replication servers running on MySQL 8.0.25 and we are not able to reproduce the issue manually.
Suggested fix:
NA
