What error is returned from the postgres call after the DB connection has been reset? You said that the DB call in the php script returns no data. If it returns with no data, I would expect an error to be set.
Bob On Dec 11, 2008, at 5:14 PM, Krawetz, Neal wrote: > Hi Folks, > > Mary and I just debugged the "every morning" scheduler hang. Not only > can we repeat it, we can make APACHE hang (without needing > FOSSology!). > > The problem: > When PostgreSQL 8.1 does a log rotate, it ends up hanging > connections to > the DB. > > Since the Scheduler keeps one connection open forever, that connection > hangs. As a result, all of the scheduler hangs. > > With Apache, DB connections are sustained until they timeout. There > are > also multiple DB connections. What we are seeing: some of the Apache > connections appear hung -- the PHP scripts that access the DB return > no > data and sometimes the session resets. Simply reloading tells Apache > to > use a different (random/round-robin) DB connection that will probably > work (unless you are unlucky and reload using the same hung DB > connection). > > > The test: > Mary configured postgresql to rotate its logs every 2 minutes. Poof: > PHP > scripts that connect to the DB under Apache begin to die -- SQL > queries > act inconsistent (some return results, yet on reload they return > nothing) and sessions randomly time out. Also, the scheduler hangs. > > > One possible workaround: > When postgresql rotates its logs, we need to restart the scheduler. > This > can be done in /etc/logrotate.d/postgresql-common. Add a section to > the > post-rotate: > > postrotate > /etc/init.d/fossology restart > /dev/null > > Alternately, this can be put in /etc/logrotate.d/fossology, but be > sure > it runs AFTER postgres rotates, and not before. > > In either case, this is ugly since it means that a stopped scheduler > will be restarted the next time the logs rotate. > > I'm open to other solutions if anyone else knows a good/elegant way to > detect and reset DB connections. > > > FAQ: > Why haven't we seen the Apache hangs? > Apache disconnects from the DB after a period of inactivity. > Since logs rotate between 4:30am and 6:30am, nobody is around to play > with the UI. All connections time out so Apache does not notice the > rotation. > > Also, Apache's logs rotate around the same time as Postgres, and > apache's postrotate says to restart apache. Thus, any potentially hung > connections are released. > > > So to summarize: > The scheduler hangs are not due to the scheduler. They are due to > PostgreSQL screwing up active connections when they rotate their logs. > > -Neal > > > _______________________________________________ > fossology mailing list > [email protected] > http://fossology.org/mailman/listinfo/fossology _______________________________________________ fossology mailing list [email protected] http://fossology.org/mailman/listinfo/fossology
