Thanks Paolo - sadly same result :( I'm not using pyDAL, just the usual
Web2py DAL
##################################
########305########
##################################
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 554, in <module>
PenguinDoer(db_app)
File "applications/yarpr/models/my_scheduler.py", line 468, in __init__
debug_log(sys._getframe().f_code.co_name, None, start=method_start,
error_details=ee, severity='Fail')
File "applications/yarpr/models/do_debugging.py", line 197, in debug_log
debug_flush()
File "applications/yarpr/models/do_debugging.py", line 201, 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 e
OperationalError: (2006, 'MySQL server has gone away')
Here's the code:
self.dbase.commit()
self.dbase._adapter.close(action=None)
try:
# now the long running task - in the test it's a sleep for 305
seconds.
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:
self.dbase._adapter.reconnect()
this_doer = change_status(
this_doer,
FAILED,
'Failed: ' + str(ee)
)
this_doer.update_record()
debug_log(sys._getframe().f_code.co_name, None,
start=method_start, error_details=ee, severity='Fail')
raise Exception('Failure during method execution: ' + str(ee))
self.dbase._adapter.reconnect()
this_doer = change_status(
this_doer,
COMPLETED,
'Normal exit'
)
On Sunday, March 15, 2015 at 12:04:04 AM UTC+11, Paolo Valleri wrote:
>
> 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.
