Thread: Severe problems with ColdFusion 5, ODBC and PostgreSQL 7.2.1
Hi! We've recently migrated our frontend database from Oracle on Win2k to PostgreSQL 7.2.1 on SuSE Linux 7.2.1. Everything is running - most of the time. But about once every hour, at least one of our three webservers running ColdFusion 5 is throwing one of the following errors: ODBC Error Code = IM005 (Driver's SQLAllocConnect failed) [Microsoft][ODBC Driver Manager] Fehler beim SQLAllocHandle-Aufruf für Treiber auf SQL_HANDLE_DBC. (Error calling SQLAllocHandle for Driver on SQL_HANDLE_DBC). respectively: ODBC Error Code = S1000 (General error) The database does not exist on the server or user authentication failed. I've got to restart the ColdFusion Application Server service in the services control panel before the server starts working again. It seems like it just forgets how to connect to the databases from one moment to the next. I don't really know what exactly is going on, so could anyone give me some hints on how to determine the cause of all this and how to resolve the issue? Thank you very much! Regards, Markus
"Markus Wollny" <Markus.Wollny@computec.de> writes:
> I've got to restart the ColdFusion Application Server service in the
> services control panel before the server starts working again. It seems
> like it just forgets how to connect to the databases from one moment to
> the next. I don't really know what exactly is going on, so could anyone
> give me some hints on how to determine the cause of all this and how to
> resolve the issue?
Very bizarre. I'd start by looking in the postmaster log to see if
the postmaster notices anything going wrong when this happens...
it might help to turn up the debug message level, too.
regards, tom lane
Hi!
Maximum number of connections in postgresql.conf is more than sufficient
and we configured the CF-Servers to use connection pooling so they
cannot reach the limit set in postgresql.conf. I don't think that this
is the cause of my troubles.
Regards,
Markus
-----Ursprüngliche Nachricht-----
Von: mlw
Gesendet: Sa 10.08.2002 17:09
An: Markus Wollny
Cc: pgsql-odbc@postgresql.org
Betreff: Re: [ODBC] Severe problems with ColdFusion 5, ODBC and
PostgreSQL 7.2.1
Markus Wollny wrote:
>
> Hi!
>
> We've recently migrated our frontend database from Oracle on
Win2k to
> PostgreSQL 7.2.1 on SuSE Linux 7.2.1. Everything is running -
most of
> the time. But about once every hour, at least one of our three
> webservers running ColdFusion 5 is throwing one of the
following errors:
>
> ODBC Error Code = IM005 (Driver's SQLAllocConnect failed)
> [Microsoft][ODBC Driver Manager] Fehler beim
SQLAllocHandle-Aufruf für
> Treiber auf SQL_HANDLE_DBC.
> (Error calling SQLAllocHandle for Driver on SQL_HANDLE_DBC).
>
> respectively:
>
> ODBC Error Code = S1000 (General error)
> The database does not exist on the server or user
authentication failed.
>
> I've got to restart the ColdFusion Application Server service
in the
> services control panel before the server starts working again.
It seems
> like it just forgets how to connect to the databases from one
moment to
> the next. I don't really know what exactly is going on, so
could anyone
> give me some hints on how to determine the cause of all this
and how to
> resolve the issue?
> Thank you very much!
>
> Regards,
> Markus
>
Check the postgresql.conf file for the maximum number of
connections.
It has been my experience that postgresql is not very well tuned
out of the
box. It is a great system, and I have no doubts about its
capability, but it
usually needs to be tweaked a little after a default install.
I think I'll have to resort to the latter - there's nothing much in the
logfiles in default loglevel. But I suspect that the cause must lie
somewhere in between ColdFusion and the ODBC driver anyway...
-----Ursprüngliche Nachricht-----
Von: Tom Lane
Gesendet: Sa 10.08.2002 17:32
An: Markus Wollny
Cc: pgsql-odbc@postgresql.org
Betreff: Re: [ODBC] Severe problems with ColdFusion 5, ODBC and
PostgreSQL 7.2.1
"Markus Wollny" <Markus.Wollny@computec.de> writes:
> I've got to restart the ColdFusion Application Server service
in the
> services control panel before the server starts working again.
It seems
> like it just forgets how to connect to the databases from one
moment to
> the next. I don't really know what exactly is going on, so
could anyone
> give me some hints on how to determine the cause of all this
and how to
> resolve the issue?
Very bizarre. I'd start by looking in the postmaster log to see
if
the postmaster notices anything going wrong when this happens...
it might help to turn up the debug message level, too.
regards, tom lane
If we turned off connection pooling, we'd have to go back to
non-persistant connections. And this causes severe performace
decreasing, as the time the OS takes to fork a new process is coming on
top of every request made to the database. As several people strictly
recommend using connection pooling and persistant connections, I don't
think that switching that off would in fact solve any problem but cause
a bunch of new ones...
-----Ursprüngliche Nachricht-----
Von: mlw
Gesendet: Sa 10.08.2002 19:56
An: Markus Wollny
Cc: pgsql-odbc@postgresql.org
Betreff: Re: [ODBC] Severe problems with ColdFusion 5, ODBC and
PostgreSQL 7.2.1
Try turning off connection pooling and see if the problem goes
away. I have had
strange problems with connection pooling in other databases. If,
for some
reason, the connection pooling layer fails to detect an invalid
state in a
connection, it will try to use that connection, even though it
is invalid.
Using PHP, we would see this problem in Oracle. Ever once in a
while we'd get a
failed query. It would not go away until we restarted apache.
The problem, we later found out, was that the PHP system had a
persistent
connection to Oracle. It was a game of roulette as to which
apache process
would answer a request, while only one Apache process had the
bogus connection.
It could be that a postgres process is dying and not being
detected by the
pooling layer.
Markus Wollny wrote:
>
> Hi!
>
> Maximum number of connections in postgresql.conf is more than
sufficient
> and we configured the CF-Servers to use connection pooling so
they
> cannot reach the limit set in postgresql.conf. I don't think
that this
> is the cause of my troubles.
>
> Regards,
>
> Markus
>
> -----Ursprüngliche Nachricht-----
> Von: mlw
> Gesendet: Sa 10.08.2002 17:09
> An: Markus Wollny
> Cc: pgsql-odbc@postgresql.org
> Betreff: Re: [ODBC] Severe problems with ColdFusion 5,
ODBC and
> PostgreSQL 7.2.1
>
>
>
> Markus Wollny wrote:
> >
> > Hi!
> >
> > We've recently migrated our frontend database from
Oracle on
> Win2k to
> > PostgreSQL 7.2.1 on SuSE Linux 7.2.1. Everything is
running -
> most of
> > the time. But about once every hour, at least one of
our three
> > webservers running ColdFusion 5 is throwing one of
the
> following errors:
> >
> > ODBC Error Code = IM005 (Driver's SQLAllocConnect
failed)
> > [Microsoft][ODBC Driver Manager] Fehler beim
> SQLAllocHandle-Aufruf fÃf¼r
> > Treiber auf SQL_HANDLE_DBC.
> > (Error calling SQLAllocHandle for Driver on
SQL_HANDLE_DBC).
> >
> > respectively:
> >
> > ODBC Error Code = S1000 (General error)
> > The database does not exist on the server or user
> authentication failed.
> >
> > I've got to restart the ColdFusion Application
Server service
> in the
> > services control panel before the server starts
working again.
> It seems
> > like it just forgets how to connect to the databases
from one
> moment to
> > the next. I don't really know what exactly is going
on, so
> could anyone
> > give me some hints on how to determine the cause of
all this
> and how to
> > resolve the issue?
> > Thank you very much!
> >
> > Regards,
> > Markus
> >
>
> Check the postgresql.conf file for the maximum number
of
> connections.
>
> It has been my experience that postgresql is not very
well tuned
> out of the
> box. It is a great system, and I have no doubts about
its
> capability, but it
> usually needs to be tweaked a little after a default
install.
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org