Hi, first of all use the latest pydal, it contains a fix when trying to close 'broken connection'. Afterwards, for a proper reconnect in your application you should do: - db._adapter.close() - db._adapter.reconnect()
If you are looking for an 'auto-reconnect', namely it will re-execute all queries in the broken transaction, I've been working on it, have a look at https://github.com/ilvalle/pydal/tree/auto-reconnect In this case it will re-execute all commands in the last transaction (but if you do a db.commit() before the operation, then it is fine). a discussion about this new feature is here: https://groups.google.com/d/topic/web2py-developers/e161iZZmp0Y/discussion Finally, I've never tried it with mysql, but it should work since an OperationalError is raised. Paolo On Saturday, March 14, 2015 at 5:50:49 AM UTC+1, Ian Ryder wrote: > > Sorry, formatting was all screwy on the last bit of the OP - 2 processing > running together, one lasting 295 seconds and saving without issue, the > other lasting 305 seconds and failing because the SQL server has gone / > isn't reconnected: > > > ################################## > ########295######### > ################################## > > About to save statusClean exit for doer 2928 > ################################## > ########305######## > ################################## > About to save statusClean exit for doer 2925 > (2013, 'Lost connection to MySQL server during query') > Traceback (most recent call last): > File "/home/ianryder/yarpr/web2py/gluon/restricted.py", line 224, in > restricted exec ccode in environment > File "applications/yarpr/models/my_scheduler.py", line 446, in <module> > PenguinDoer(db_app) > File "applications/yarpr/models/my_scheduler.py", line 382, in __init__ > debug_log(sys._getframe().f_code.co_name, None, start=method_start, > job_status=None) > File "applications/yarpr/models/debug_framework.py", line 196, in > debug_log debug_flush() > File "applications/yarpr/models/debug_framework.py", line 200, in > debug_flushdb_usage.debug_data.bulk_insert(DebugHandler.debug_records) > File "/home/ianryder/yarpr/web2py/gluon/dal.py", line 9425, in bulk_insert > ret = self._db._adapter.bulk_insert(self, items) > File "/home/ianryder/yarpr/web2py/gluon/dal.py", line 1376, in bulk_insert > return [self.insert(table, item) for item in items] > File "/home/ianryder/yarpr/web2py/gluon/dal.py", line 1361, in insert > raise e OperationalError: (2006, 'MySQL server has gone away') > > On Saturday, March 14, 2015 at 8:30:39 AM UTC+11, Ian Ryder wrote: >> >> Hi, we are using Web2py to do some batch processing of data but from day >> one have had show-stopping issues with losing connection to MySQL on >> PythonAnywhere. >> >> We've refactored the code to be much lighter in terms of batch sizes etc >> which has made it much closer to perfect but we still have problems. >> >> Essentially we're going over the 300 second timeout on PA and web2py >> won't reconnect no matter what we try. We have: >> >> - tried pool size 10 on the connection >> - tried pool size 0 on the connection >> - db.commit before kicking off the longer process >> - db.close before kicking off the longer process >> - tried reconnecting the entire DB when it drops >> - tried db._adapter.reconnect() when it drops >> >> Same results every time...the basic steps are: >> >> - scheduled method called >> - it calls batch task which loops through happily, constantly working on >> the database >> - returns to the original method which then tried to close a status >> record off and that's where it bombs >> >> Here's some code: >> self.my_pid = this_task.last_doer_pid >> new_doer = { >> 'doer_name': self.get_doer_name(this_task.id), >> 'task_queue': this_task.id, >> 'method_name': this_task.method_name, >> 'pid': self.my_pid >> } >> this_doer_id = >> self.dbase.task_doer.insert(**self.dbase.task_doer._filter_fields(new_doer)) >> this_doer = self.dbase.task_doer[this_doer_id] >> this_task.update_record(status=RUNNING, last_doer_id=this_doer.id, >> doer_name=new_doer['doer_name']) >> self.dbase.commit() >> >> <snip socket handling> >> <snip method name getting> >> >> if not run_method: >> this_doer.update_record(status=FAILED, status_note='Method %s >> not implemented' % this_task.method_name) >> self.dbase.commit() >> raise Exception('Method %s not implemented' % >> this_task.method_name) >> >> passed_args = passed_vars = None >> # close off the connection so it doesn't drop out during the >> processing >> self.dbase.commit() >> try: >> run_method( >> *loads(this_task.args, object_hook=self._decode_dict), >> **loads(this_task.vars, object_hook=self._decode_dict)) >> #, *passed_vars) >> except Exception as ee: >> this_doer.update_record(status=FAILED, status_note='Failed: ' >> + str(ee)) >> debug_log(sys._getframe().f_code.co_name, None, >> start=method_start, error_details=ee, severity='Fail' ) >> >> raise Exception('Failure during method execution: ' + ee) >> >> print 'About to save status' >> this_doer.update_record(status=COMPLETED, status_note='Normal >> exit') >> self.dbase.commit() >> >> print 'Clean exit for doer ' + str(this_doer.pid) >> >> Just to confirm, here's a test with 2 processes, one waiting 295 seconds >> and closing fine, the other waiting 305 seconds and blowing up: >> >> Database drivers available: SQLite(sqlite3), MySQL(pymysql), >> MySQL(MySQLdb), MySQL(mysqlconnector), PostgreSQL(psycopg2), >> PostgreSQL(pg8000), MSSQL(pyodbc), DB2(pyodbc), Teradata(pyodbc), >> Ingres(pyodbc), MongoDB(pymongo), IMAP(imaplib) >> ################################## ########295######## >> ##################################About to save statusClean exit for >> doer 2928################################## ########305######## >> ##################################(2013, 'Lost connection to MySQL >> server during query')About to save statusClean exit for doer 2925Traceback >> (most recent call last): File >> "/home/ianryder/yarpr/web2py/gluon/restricted.py", line 224, in restricted >> exec ccode in environment File >> "applications/yarpr/models/my_scheduler.py", line 446, in <module> >> PenguinDoer(db_app) File "applications/yarpr/models/my_scheduler.py", >> line 382, in __init__ debug_log(sys._getframe().f_code.co_name, None, >> start=method_start, job_status=None) File >> "applications/yarpr/models/debug_framework.py", line 196, in debug_log >> debug_flush() File "applications/yarpr/models/debug_framework.py", line >> 200, in debug_flush >> db_usage.debug_data.bulk_insert(DebugHandler.debug_records) File >> "/home/ianryder/yarpr/web2py/gluon/dal.py", line 9425, in bulk_insert >> ret = self._db._adapter.bulk_insert(self, items) File >> "/home/ianryder/yarpr/web2py/gluon/dal.py", line 1376, in bulk_insert >> return [self.insert(table, item) for item in items] File >> "/home/ianryder/yarpr/web2py/gluon/dal.py", line 1361, in insert raise >> eOperationalError: >> (2006, 'MySQL server has gone away') >> Any help appreciated! >> >> Cheers >> Ian >> > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
