How can I detect total MySQL server death from Python? - 【StackMirror】|python|django|mysql-python

I've been doing some HA testing of our database and in my simulation of server death I've found an issue.

My test uses Django and does this:

Connect to the database
Do a query
Pull out the network cord of the server
Do another query

At this point everything hangs indefinitely within the mysql_ping function. As far as my app is concerned it is connected to the database (because of the previous query), it's just that the server is taking a long time to respond...

Does anyone know of any ways to handle this kind of situation? connect_timeout doesn't work as I'm already connected. read_timeout seems like a somewhat too blunt instrument (and I can't even get that working with Django anyway).

Setting the default socket timeout also doesn't work (and would be vastly too blunt as this would affect all socket operations and not just MySQL).

I'm seriously considering doing my queries within threads and using Thread.join(timeout) to perform the timeout.

In theory, if I can do this timeout then reconnect logic should kick in and our automatic failover of the database should work perfectly (kill -9 on affected processes currently does the trick but is a bit manual!).

2012-04-04 19:35
by ColinHowe

"I'm seriously considering doing my queries within threads" - wouldn't it be a good idea anyway - Ivan 2012-04-04 19:38

@Ivan yes... and no... In some cases I'm within threads already, I'd just be adding additional threads specifically for each query - ColinHowe 2012-04-04 19:45

@Ivan yes, there is no good 'pythonic' way to terminate a thread outside of it. Using multithreading potentially can cause loads of connected threads - Maksym Polshcha 2012-04-04 20:08

As an extra piece of information I tried (on a whim) using threading.Timer to call connection.close after X seconds... welcome to segfault land : - ColinHowe 2012-04-04 20:09

I've just rediscovered pymysql... I might just switch to using that. We're using eventlet anyway so this may be a better choice in general : - ColinHowe 2012-04-04 20:11

I would think this would be more inline with setting a read_timeout on your front-facing webserver. Any number of reasons could exist to hold up your django app indefinitely. While you have found one specific case there could be many more (code errors, cache difficulties, etc).

2012-04-17 14:05
by BenH