Forums

Hi, my web2py app does some screen scaping and database updating (PA MySQL database) every half hour. Today between 3pm and 6pm GMT, my logs show that there were MySQL database connection problems ("Operational Error 2013 Lost connection to MySQL server during query", "Operational Error 2006 MySQL server has gone away" are the specific web2py error messages).

Is this in line with your monitoring of the service? Want to know if we had a temporary MySQL availability issue or if I should re-check my code.

best regards -Ricardo

quick update for others who may find this thread: found this page on Managing MySQL pooled connections; perhaps I can find a solution to my problem there. Will review.

It's always a good idea to make your code tolerant of DB failures. For read-only access it's usually just a case of catching the error, reconnecting and retrying the query. For writes you have to be a little more careful - it's possible (although very unlikely) that your update actually got through successfully even if the connection dropped. This is why you always want to use transactions and explicitly commit them - then you know that either all or none of your updates happened. It's still possible to have a race during commit where you're not sure if it committed or not when you get an error, but as I said this is very rare.

There's discussion of a PA utility library which might include, for example, functions to make it easy to maintain a MySQL connection pool which checks the connectivity of each connection as it's removed from the pool. Still, this is probably some way off - as you've discovered, there are a number of solutions to be found on the web already.

One of the plus sides of using the Django ORM is that at least it does manage connections for you...

Hi Hansel, do you mean to say perhaps that web2py doesn't manage the connections? I had the impression it did. From the web2py book:

In case of connection pooling it is possible that a pooled connection that stays open but unused for some time is closed by the database end. Thanks to the retry feature web2py tries to re-establish these dropped connections.

best -Ricardo

Hi Ricardo,

Nope, I assumed you were just using MySQLdb directly. If it was just intermittent and has now stopped happening then it is possible (but unlikely) that there was some kind of outage that we didn't notice. How many retries does Web2py make? How many times does it retry before giving up? I would make my code fault tolerant but also log the failures. If you never see them again then great! If they are happening quite frequently then but don't coincide with general outages affecting everyone then you could start looking down into the Web2py stack to see what it is doing.

EDIT: Hm, just noticed that the post above links to the same sections I link to below - damn, I could have saved myself 4 seconds on Google! Sorry, completely missed that link. (^_^)

From a brief Google I turned up this documentation section which rather implies that by default web2py doesn't do connection pooling, but you can specify a pool size to the database abstraction class constructor which enables it. Also, the subsequent section appears to be where the quote above is from - the missing part is that web2py retries up to 5 times at 1-second intervals.

However, this automatic retry doesn't necessarily help - I've seen connection pooling implementations which only check a connection when it's removed from the pool, and then they tend to assume the connection stays valid until it's replaced into the pool, presumably on the assumption that web frameworks tend to process requests quickly so there's no chance for the connection to idle out. If web2py is doing this and in fact the failure is occurring whilst the conneciton isn't idle, then it's quite possible that it would cause these errors. It's also possible that web2py's database layer is simply buggy - connection failures are the sort of thing you often don't see on small systems and test environments so it's quite possible that few people have seen this case.