This code isn't using transactions so retrying a failed query should be as simple as creating a new connection to replace the failed one and executing the query again.
Still, I would much prefer to figure out the real cause, as you say. I had sort of given up on that because after a little while researching this error, I couldn't find much helpful info. It's hard to debug because the issue happens in a daily cron job, but it happens less than once a month and the rest of the time everything works fine. I have no way of consistently reproducing the problem or knowing if I've fixed it. I'm pretty sure there is no way that 8 hours could have gone by between the last query and the one that blew up. The basic structure of the cron job is: 1) It start up, does some sql stuff. 2) It forks a worker process using the python processing module. 3a) The worker calls metadata.bind.dispose() so that it won't try to reuse the connection it inherited from the parent. Worker then does some sql stuff. Worker always finishes successfully. 3b) Parent process goes into a loop doing sql stuff. Parent usually finishes successfully, but occasionally dies with the aforementioned MySQL error. I can't tell from the traceback whether it happens during the first iteration of the loop immediately after spawning the child or if it happens later. In principle, this structure is safe, right? 3a and 3b are happening in parallel, so it is indeterminate whether the worker calls dispose() before or during the sql stuff going on in the parent, but that shouldn't mater, right? Is it possible that the call to dispose() is somehow closing the connection in a way that sabotages the parent? On Sun, Dec 21, 2008 at 11:32 PM, Michael Bayer <mike...@zzzcomputing.com> wrote: > > > On Dec 21, 2008, at 11:24 PM, Bobby Impollonia wrote: > >> >> I occasionally have a query fail with 'Lost connection to MySQL server >> during query' which gets converted into a >> sqlalchemy.exceptions.OperationalError. I have not been able to figure >> out why it happens, but the server should always be available. >> >> I would like to tell sqlalchemy that if a query fails with this error, >> it should wait a few seconds and then retry the query (and probably >> give up if it fails again). Does SQLA provide some sort of hooks that >> would allow me to do this without gnarly monkey patching? > > you'd have to organize your code such that the desired operation can > be attempted again when this exception is raised. > > This is a pretty tough road to travel, though, since if the connection > is lost, so is your entire transaction and everything you've loaded/ > persisted within it. A better approach would be to isolate the cause > of the error. This error is commonly caused by a MySQL client > timeout (usually on a connection that's been idle for 8 hours) and is > allevated using the pool_recycle=<some number of seconds> option. > > > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---