Ricardo,
Your use case has been useful to pinpoint some issues with the semantic of
commit/rollback/close in the DAL.
We are working on it to improve ease of use. So thank you!
mic
2013/9/27 Ricardo Cárdenas <ricardo.carde...@gmail.com>
> @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>
>> 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>
>>
>> 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/**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 (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.
>
--
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.
