MySQL Bugs: #9810: Show Slave Status Hangs

Bug #9810	Show Slave Status Hangs
Submitted:	11 Apr 2005 11:14	Modified:	12 Apr 2005 18:16
Reporter:	Sumit Kumar Roy	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	4.0.17	OS:	Linux (Linux, Kernel 2.4.26)
Assigned to:		CPU Architecture:	Any

Description:
We are designing a High Availability system with MySQL as backend. We have MySQL set up as Master and Slave mode. 

We have a monitoring script which checks the status of the health of both the instances of the Mysql Db server instances. It also checks the replication continuity between the Databases using a monitoring script. We use "show slave status" to check the replication status. 

Replication has been set with the following grant for the master and slave instance:
grant replication slave,super,reload,file on *.* to "$REPL_USER"@'%' identified by "$REPL_PASSWD" ;

We notice that this script hangs sometimes while executing "show slave status". We gave an strace on this process and found that "show slave status" is waiting on a "wait " call. 

We are using the following script to check the replication status.
REPL1=`mysql -u$USER_ID -p$USER_PWD  -P$SLAVE_PORT -h cp_dbsrv -e "show slave status" | grep $MASTER_LOG_BIN_FILE | awk ' { print $10 } '
REPL2=`mysql -u$USER_ID -p$USER_PWD  -P$SLAVE_PORT -h cp_dbsrv -e "show slave status" | grep $MASTER_LOG_BIN_FILE  |awk ' { print $11 } '`

This hanging status goes for over 1 hour. And ultimately we have to kill the monitoring process and start again.

Machine Configuration:
CPU - 4 Processors - Intel(R) Xeon(TM) cpu 2.4.GHz
CPU Mhz - 2399.369
Cache Size = 512 KB

How to repeat:
There is no way to repeate the situstion. It comes randomely

I was unable to reproduce this bug using 4.0.24

We are using 4.0.17. Can you give me a fix?

I have had similar problems with show slave status and show global status just hanging. It seems to happen when there is heavy writing to tmp tables on disk.