I'm having a problem with Z Psycopg. From time to time (quite often) it looses the connection with the database and I get: Site Error An error was encountered while publishing this resource. Error Type: OperationalError Error Value: no connection to the server Then I have to login then close and open the connection. It then works for a while before bailing again. Does anyone know what would be causing this? Zope Version (Zope 2.7.3-0, python 2.3.4, linux2) Python Version 2.3.4 (#1, Oct 13 2004, 21:44:19) [GCC 2.95.4 20011002 (Debian prerelease)] And ZPsycopg is version 1.11 Thanks, Jason.
Jason Leach wrote:
I'm having a problem with Z Psycopg. From time to time (quite often) it looses the connection with the database and I get: Site Error An error was encountered while publishing this resource. Error Type: OperationalError Error Value: no connection to the server
Then I have to login then close and open the connection. It then works for a while before bailing again.
Does anyone know what would be causing this?
Zope Version (Zope 2.7.3-0, python 2.3.4, linux2) Python Version 2.3.4 (#1, Oct 13 2004, 21:44:19) [GCC 2.95.4 20011002 (Debian prerelease)] And ZPsycopg is version 1.11
Not sure, but I can report seeing the same thing on a different setup FreeBSD 4.8 Zope 2.7.5 Python 2.3.5 psycopg 1.1.18 (linked to PostgreSQL 8.0.1 client library) (PostgreSQL server is also 8.0.1, but on FreeBSD 5.2.1) On the very same machine I also have Zope 2.6.1 Python 2.1.3 psysopg 1.0.6 (linked to an older PostgreSQL 7.2 client library) running simultaneously, connecting to the very same DBs, but it hasn't had the same connection problems. I was wondering if it might be something with psycopg and the new pgsql 8.0.1 library. What pgsql client lib is you psycopg linked to? Barry
I have the same problem, but I eventually "corrected" it, by forcibly closing the connection each time prior to running the queries. Fortunately, my scenario uses page caching to reduce the number of hits to the db so the additional effort of reopening the connection is neglible. I'd sure like to know what the problem is, though... RHEL Zope 2.7.3 Python 2.3.4 psycopg 1.1.18 PostgreSQL 7.3
Barry Pederson wrote at 2005-4-4 22:15 -0500:
... Not sure, but I can report seeing the same thing on a different setup
FreeBSD 4.8 Zope 2.7.5 Python 2.3.5 psycopg 1.1.18 (linked to PostgreSQL 8.0.1 client library)
(PostgreSQL server is also 8.0.1, but on FreeBSD 5.2.1)
On the very same machine I also have
Zope 2.6.1 Python 2.1.3 psysopg 1.0.6 (linked to an older PostgreSQL 7.2 client library)
running simultaneously, connecting to the very same DBs, but it hasn't had the same connection problems.
I was wondering if it might be something with psycopg and the new pgsql 8.0.1 library.
Almost looks like a PostgreSQL 8.0.1 client library bug... -- Dieter
Jason Leach wrote:
I'm having a problem with Z Psycopg. From time to time (quite often) it looses the connection with the database and I get: Site Error An error was encountered while publishing this resource. Error Type: OperationalError Error Value: no connection to the server
Garth B. wrote:
I have the same problem, but I eventually "corrected" it, by forcibly closing the connection each time prior to running the queries. Fortunately, my scenario uses page caching to reduce the number of hits to the db so the additional effort of reopening the connection is neglible.
I'd sure like to know what the problem is, though...
I've been looking at the differences between ZPsycopg that comes with 1.0.x and 1.1.x, and it looks like the older ZPsycopg had code to automatically reconnect on an OperationalError (in db.py, the DB.query() method), but the corresponding method in the newer ZPsycopg doesn't have that. Looks like it was removed in 1.1.11 or 1.1.12 I'm trying this patch to see if it gets back the reliability my old setup had - although I'm not sure what other bad stuff this could cause -------------------------- --- db.py.original Fri Oct 29 19:19:27 2004 +++ db.py Tue Apr 5 12:56:46 2005 @@ -222,7 +222,12 @@ result = c.fetchall() desc = c.description self.failures = 0 - + + except psycopg.OperationalError, err: + if hasattr(self, 'db'): + del c; del self.db + return self.query(query_string, max_rows, query_data) + except StandardError, err: self._abort() raise err -------------------------- Barry
Jason Leach wrote at 2005-4-4 15:15 -0700:
I'm having a problem with Z Psycopg. From time to time (quite often) it looses the connection with the database and I get: Site Error An error was encountered while publishing this resource. Error Type: OperationalError Error Value: no connection to the server
Usually, this means that the postgres server process died...
Then I have to login then close and open the connection. It then works for a while before bailing again.
I do not have to do this because "ZPsycopgDA" automatically reconnects when an "OperationalError" is raised. Thus, this is strange. -- Dieter
On Tue, 5 Apr 2005, Dieter Maurer wrote:
I'm having a problem with Z Psycopg. From time to time (quite often) it looses the connection with the database and I get: Site Error An error was encountered while publishing this resource. Error Type: OperationalError Error Value: no connection to the server Usually, this means that the postgres server process died...
I did run into something recently where the local firewall was killing the connection to the DB server due to a timeout, so one of the local admin types wrote a small zsql method that gets hit by a cronjob about once an hour or so. Granted the end effect is the same (zope thinks that the DB server has died) but in this case it was the connection itself.
Jeff Gentry wrote:
On Tue, 5 Apr 2005, Dieter Maurer wrote:
Usually, this means that the postgres server process died...
I did run into something recently where the local firewall was killing the connection to the DB server due to a timeout, so one of the local admin types wrote a small zsql method that gets hit by a cronjob about once an hour or so.
Granted the end effect is the same (zope thinks that the DB server has died) but in this case it was the connection itself.
Yeah, I think Jeff nailed it. I had FreeBSD's IPFilter set to drop idle states after an hour. Turns out FBSD by default does TCP keepalive, but only after 2 idle hours. I changed the net.inet.tcp.keepidle sysctl to lower it to a half-hour, so it now gooses the firewall often enough that I haven't seen a dropped connection since then. Looking back at the pgsql logs, I think I've always had this problem, but just didn't realize it until I tried a new ZPsycopgDA that lacked the auto-reconnect feature. Even though tweaking TCP fixed the problem, I think it'd still be good if ZPsycopgDA was able to auto-reconnect, because you could lose connections for other reasons - such as restarting postgres - and it'd be nice to not have to go and restart Zope too. (I think it's kind of funny that I didn't find out about my TCP problem until ZPsycopgDA failed to auto-reconnect, and I wouldn't have found out so soon about ZPsycopgDA not auto-reconnecting if I didn't have the TCP problem :) Barry
Dieter Maurer wrote:
Jason Leach wrote at 2005-4-4 15:15 -0700:
I'm having a problem with Z Psycopg. From time to time (quite often) it looses the connection with the database and I get: Site Error An error was encountered while publishing this resource. Error Type: OperationalError Error Value: no connection to the server
Usually, this means that the postgres server process died...
I checked my pgsql logs, and it seemed like the zope connection was dying first, and then later on pgsql showed an unexpected EOF. So I don't think it's the postgres server dying first.
Then I have to login then close and open the connection. It then works for a while before bailing again.
I do not have to do this because "ZPsycopgDA" automatically reconnects when an "OperationalError" is raised. Thus, this is strange.
Like I mentioned in another post - it looks like that feature was removed in the ZpsycopgDA that comes with psycopg 1.1.11 or 1.1.12 I've tried patching the auto-reconnect back in, and so far so good. Looks like a couple connections recently died, but the site is still going OK. Barry
On Apr 5, 2005 1:03 PM, Dieter Maurer <dieter@handshake.de> wrote:
Jason Leach wrote at 2005-4-4 15:15 -0700:
I'm having a problem with Z Psycopg. From time to time (quite often) it looses the connection with the database and I get: Site Error An error was encountered while publishing this resource. Error Type: OperationalError Error Value: no connection to the server
Usually, this means that the postgres server process died...
Probably the case.
Then I have to login then close and open the connection. It then works for a while before bailing again.
I do not have to do this because "ZPsycopgDA" automatically reconnects when an "OperationalError" is raised. Thus, this is strange.
In my case it will not reconnect. It just keeps giving the OpperationalError.
Hi, try to ask frederico fog@initd.org the maintainer zpsycopg/da directly. We use postgresql 8.0.1 and psycopg 1.1.18 with out this problems. best regards Lutz On Mon, 4 Apr 2005 15:15:17 -0700 Jason Leach <jason.leach@gmail.com> wrote:
I'm having a problem with Z Psycopg. From time to time (quite often) it looses the connection with the database and I get: Site Error An error was encountered while publishing this resource. Error Type: OperationalError Error Value: no connection to the server
Then I have to login then close and open the connection. It then works for a while before bailing again.
Does anyone know what would be causing this?
Zope Version (Zope 2.7.3-0, python 2.3.4, linux2) Python Version 2.3.4 (#1, Oct 13 2004, 21:44:19) [GCC 2.95.4 20011002(Debian prerelease)] And ZPsycopg is version 1.11
-- Lutz Steinborn mailto:l.steinborn@4c-ag.de 4c AG Tel +49 6092 999592 Stefan-George-Ring 8 Fax +49 89 99341 399 81929 München http://www.4c-shopping.de http://www.4c-wohnen.de http://www.4c-parfum.de
participants (6)
-
Barry Pederson -
Dieter Maurer -
Garth B. -
Jason Leach -
Jeff Gentry -
Lutz Steinborn