---------- Forwarded message ----------
From: Michele Comitini <michele.comit...@gmail.com>
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.carde...@gmail.com>
> 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.
