@michele, @niphlod, @Derek
My solution oddly turned out to be a combination of all of your
suggestions! :)
On Postgres, Michele's snippet below works for me too. On MySQL it works,
but then raises an exception (attached below). Apparently, upon exit web2py
tries to close the dead MySQL connection, and MySQL complains (even though
the connection had no outstanding transactions). The PostgreSQL behavior
seems different; web2py is silent when trying to close a dead connection
with no outstanding transactions.
So I tried db._adapter.close() right after the db.commit(). That eliminated
the MySQL exception. In fact, if there are no pending transactions,
db._adapter.close() is all that is needed.
Problem solved for me. But I wonder whether the dev's might consider
exposing an explicit db.close() and db.open() to avoid calling
db._adapter.close()
and then DAL(...auto_import=True), for the use case where a script does a
lot of processing sans database, and then resumes database use.
Sorry if this was TL;DR for most people... best regards -Ricardo
Traceback (most recent call last):
File "/home/rcardenas/web2py/gluon/shell.py", line 231, in run
BaseAdapter.close_all_instances('commit')
File "/home/rcardenas/web2py/gluon/dal.py", line 560, in
close_all_instances
db._adapter.close(action)
File "/home/rcardenas/web2py/gluon/dal.py", line 540, in close
getattr(self, action)()
File "/home/rcardenas/web2py/gluon/dal.py", line 1740, in commit
if self.connection: return self.connection.commit()
OperationalError: (2013, 'Lost connection to MySQL server during query')
Traceback (most recent call last):
File "web2py.py", line 33, in <module>
gluon.widget.start(cron=True)
File "/home/rcardenas/web2py/gluon/widget.py", line 1120, in start
import_models=options.import_models, startfile=options.run)
File "/home/rcardenas/web2py/gluon/shell.py", line 235, in run
BaseAdapter.close_all_instances('rollback')
File "/home/rcardenas/web2py/gluon/dal.py", line 560, in
close_all_instances
db._adapter.close(action)
File "/home/rcardenas/web2py/gluon/dal.py", line 540, in close
getattr(self, action)()
File "/home/rcardenas/web2py/gluon/dal.py", line 1743, in rollback
if self.connection: return self.connection.rollback()
_mysql_exceptions.OperationalError: (2006, 'MySQL server has gone away')
On Wednesday, September 25, 2013 8:36:01 AM UTC-5, Michele Comitini wrote:
>
>
>
> ---------- Forwarded message ----------
> From: Michele Comitini <michele....@gmail.com <javascript:>>
> Date: 2013/9/25
> Subject: Re: [web2py] Re: batch script - best way to reopen a closed
> database connection?
> To: Ricardo Cárdenas
>
> Try using the auto_import
>
> see the following:
>
> db.commit()
> sleep(20) # <-- here I stop and restart the postgresql service
> db=DAL('postgres://script:script@localhost/script',pool_size=1,
> check_reserved=['all'], auto_import=True)
> print db(db.people).count()
>
>
>
> 2013/9/25 Ricardo Cárdenas <ricardo....@gmail.com <javascript:>>
>
>> Hi Michele, you're right, it's better to instantiate the connection when
>> needed.
>>
>> Since I have all the DAL() and define_tables() calls in db.py, I
>> shouldn't have to rewrite the code elsewhere. I tried "import db" after the
>> wait, but I guess it's not possible to import models/* because I got an
>> import error. How do you suggest I implement your suggestion?
>>
>> thanks -Ricardo
>>
>> On Wednesday, September 25, 2013 4:50:38 AM UTC-5, Michele Comitini wrote:
>>
>>> Yes, but the connection in the pool will get dropped anyway. After some
>>> thought IMHO the correct way for you to fix the problem is:
>>> <start>
>>> ...wait...wait...
>>> db = DAL(...)
>>> <use db>
>>> db.commit() #<- commit or rollback as soon as possible
>>> ...wait...wait...
>>> db = DAL(...)
>>> <use db>
>>> db.commit() #<- commit or rollback as soon as possible
>>> <and so on>
>>>
>>> i.e. init the db instance right before using it. This should avoid
>>> timing out connections, with consequent data loss.
>>> It fixes also the problem that keeping transactions open for such a long
>>> time is no good for anything else trying to work on that database it could
>>> cause all other connections/applications to stop until the transaction is
>>> closed or timedout!
>>>
>>>
>>> 2013/9/25 Ricardo Cárdenas <ricardo....@gmail.com>
>>>
>>> @michele - No, I'm not doing a db.commit() -- because I don't use the db
>>>> connection at all, until many minutes later, when it fails.
>>>>
>>>> However, you bring up an interesting point. Does this mean that if I
>>>> db.commit() at the very beginning of my script (even if there is no
>>>> outstanding transaction), I will in effect be pushing a connection to
>>>> sleep?
>>>>
>>>> Ricardo
>>>>
>>>>
>>>> On Tuesday, September 24, 2013 5:16:20 PM UTC-5, Michele Comitini wrote:
>>>>
>>>>> Did you remember to do a commit at each round?
>>>>>
>>>>> A long running script / daemon can do:
>>>>>
>>>>> db=DAL...
>>>>> <define tables>
>>>>>
>>>>> while True:
>>>>> <do stuff with DAL>
>>>>> db.commit()
>>>>> sleep(1000) # <- just for exampl i.e. don't do anything for a long
>>>>> time
>>>>>
>>>>> The DAL pushes the connection "back to sleep in the pool" [ :-) ] when
>>>>> there is a commit.
>>>>> When one starts operating on the DAL instance (db) a connection is
>>>>> taken from the pool and *tested* for being functioning.
>>>>> If the connection is dead it's replaced by a new one. Then a
>>>>> transaction is started on that connection.
>>>>>
>>>>> mic
>>>>>
>>>>>
>>>>> 2013/9/24 Ricardo Cárdenas <ricardo....@gmail.com>
>>>>>
>>>>>> Derek, thanks for your suggestion. Which is the preferred way to
>>>>>> reopen the connection and define the tables?
>>>>>>
>>>>>> My db.py contains
>>>>>>
>>>>>> db = DAL(connection_string, options...)
>>>>>> db.define_table('table1', Field('f1' ...), Field('f2'...))
>>>>>> db.define_table(...)
>>>>>> db.define_table(...)
>>>>>> ...
>>>>>>
>>>>>> So ideally I wouldn't have to repeat this code in my script. Is it
>>>>>> best I break out the DAL/define_tables calls into a file that I import
>>>>>> both
>>>>>> in my db.py and in my script.py?
>>>>>>
>>>>>>
>>>>>>
>>>>>> On Tuesday, September 24, 2013 4:45:09 PM UTC-5, Derek wrote:
>>>>>>>
>>>>>>> Each time you need to do work, you should open a new connection.
>>>>>>> You'd think there is a large overhead in creating a connection, but
>>>>>>> there
>>>>>>> isn't.
>>>>>>>
>>>>>>> On Tuesday, September 24, 2013 2:33:24 PM UTC-7, Ricardo Cárdenas
>>>>>>> wrote:
>>>>>>>>
>>>>>>>> I have a web2py app running fine on pythonanywhere. I have a minor
>>>>>>>> problem - I think I understand why it is happening, but would seek
>>>>>>>> your
>>>>>>>> advice as to how best to fix it.
>>>>>>>>
>>>>>>>> The app itself works fine. But I also run a scheduled task using
>>>>>>>> PA's scheduler, by executing "python web2py.py -S appname -M -R
>>>>>>>> appname/private/myscript.py". The script does some processing for a
>>>>>>>> few
>>>>>>>> minutes, and only then starts writing to the MySQL database.
>>>>>>>>
>>>>>>>> PA's MySQL database has wait_timeout set to 120 seconds. If my
>>>>>>>> initial processing is less than wait_timeout, everything works fine.
>>>>>>>> But when my initial processing exceeds wait_timeout, I get a
>>>>>>>> 'Lost Connection to MySQL' error when my code tries to write to
>>>>>>>> the database. I am using connection pooling in the call to the DAL,
>>>>>>>> but I
>>>>>>>> guess the connection instantiated by db.py file is not automatically
>>>>>>>> kept
>>>>>>>> warm nor is it automatically replaced by another good connection when
>>>>>>>> it
>>>>>>>> expires.
>>>>>>>>
>>>>>>>> What's the best practice here:
>>>>>>>>
>>>>>>>> - Is there a preferred way to ping the database every once in a
>>>>>>>> while?
>>>>>>>> - Is there a preferred way to detect an expired DAL connection,
>>>>>>>> and to request another one?
>>>>>>>>
>>>>>>>> Thanks for any suggestions or pointers. Sorry if this is in the
>>>>>>>> docs, couldn't find it. warm regards -Ricardo
>>>>>>>>
>>>>>>>> --
>>>>>> Resources:
>>>>>> - http://web2py.com
>>>>>> - http://web2py.com/book (Documentation)
>>>>>> -
>>>>>> http://github.com/web2py/**web2p**y<http://github.com/web2py/web2py>(Source
>>>>>> code)
>>>>>> -
>>>>>> https://code.google.com/p/**web2**py/issues/list<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+un...@**googlegroups.com.
>>>>>>
>>>>>> For more options, visit
>>>>>> https://groups.google.com/**grou**ps/opt_out<https://groups.google.com/groups/opt_out>
>>>>>> .
>>>>>>
>>>>>
>>>>> --
>>>> Resources:
>>>> - http://web2py.com
>>>> - http://web2py.com/book (Documentation)
>>>> - http://github.com/web2py/**web2py
>>>> <http://github.com/web2py/web2py>(Source code)
>>>> -
>>>> https://code.google.com/p/**web2py/issues/list<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+un...@**googlegroups.com.
>>>> For more options, visit
>>>> https://groups.google.com/**groups/opt_out<https://groups.google.com/groups/opt_out>
>>>> .
>>>>
>>>
>>>
>
>
--
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/groups/opt_out.
