your query takes to much time to be executed and the query goes into
timeout.
>From the log I see that you are running a bulk_insert, you can split it
into more operations, placing a db.commit between them.
Mind that in this case the bulk_insert will be executed into few
transactions.
Otherwise, you can run a custom bulk_insert based on an executesql
containing all the inserts.
let us know more information about the query.
Paolo
2015-03-15 2:01 GMT+01:00 Ian Ryder <i.ry...@appichar.com.au>:
> 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 a topic in the
> Google Groups "web2py-users" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/web2py/U-hkmIc3TZM/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> web2py+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>
--
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.
