Description:
Mysql has behaved this way forever but it can be frustrating. There appears to be no clean way to stop a thread, or indicate that the stopped thread is due to a deliberate "scheduled action" or not. Therefore it can be hard to distinguish if something is working as expected.
Examples.
- stopping SQL or I/O threads, report an "error" when the user may have typed STOP SLAVE, or STOP SLAVE SQL_THREAD.
- KILL <thread_id> it would be good to know why the thread is killed, and potentially by which user
- a recent "rogue" script was running which to prevent disk usage issues on a delayed slave would stop the I/O thread if the number of relay logs grew too large. It would have been most useful to have seen that the thread was stopped by a particular user, not that it just "died".
I have a batch process running on this server overnight which is otherwise silent, logging for the day in question (hence previous batch's start plus next day's start of processing shows:
2013-11-18 06:15:58 3254 [Note] Slave SQL thread initialized, starting replication in log 'binlog.003775' at position 13419004, relay log '../log/relaylog.011944' position: 13419133
2013-11-19 00:12:02 3254 [Note] Error reading relay log event: slave SQL thread was killed
The cause of the SQL thread being stopped is a deliberate user interaction.
I've seen this on numerous occasions and because you can not distinguish a deliberate termination of a thread from one that is due to an error or some unexpected issue this can make diagnosis difficult.
How to repeat:
see above.
Suggested fix:
So please where threads terminate, and where it is possible make logging show why the thread stopped so that the cause is clearer to the DBA.
e.g.
2013-11-19 00:12:02 3254 [Note] slave SQL thread stopped by user root@localhost.
For this particular action please also log the "stopping position" if that is possible.
