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 [email protected].
For more options, visit https://groups.google.com/d/optout.
