Webware for Python

On Thu, 6 Nov 2003 ChuckEsterbrook@... wrote:

> There is a long standing problem that MiddleKit doesn't like MySQLdb
> 0.9.2, although it gets along great with 0.9.1 and many versions prior.
> Under MySQLdb 0.9.2, some of the MiddleKit tests yield "Too many
> connections":

FYI, I've had this problem as well, under RH 8.0, Python 2.3 beta 1 (yes, 
I know), MySQL 3.23.46, and, of course, MySQLdb 0.9.2.

My gut feeling is that this is a connection garbage collection issue 
*within* MySQLdb, and that it's not actually freeing up connections when 
it's supposed to (or not freeing up statement handles, or something like 
that).

On Fri, 7 Nov 2003, Tripp Lilley wrote:

> My gut feeling is that this is a connection garbage collection issue 
> *within* MySQLdb, and that it's not actually freeing up connections when 
> it's supposed to (or not freeing up statement handles, or something like 
> that).

Looking at diffs between 0.9.1 and 0.9.2 just now, I'm even more inclined 
to go with this gut feeling, just given the quantity of "small" changes, 
as well as what appears to be a somewhat large-scale stylistic retooling 
of the MySQLdb internals. Naming conventions, internal data structures, 
etc., seem like they might have undergone some significant changes (but 
I'm just skimming, so I might be reading more into this than there is).

In any case, it seems to me like writing a test case against MySQLdb that 
creates and then discards large numbers of connections would be a quick 
way to expose the behaviour for further study. Don't count on me to beat 
you to it this week, though :-)

On Fri, 7 Nov 2003 19:53:00 -0500 (EST), Tripp Lilley wrote:
>=A0On Fri, 7 Nov 2003, Tripp Lilley wrote:
>
>>=A0My gut feeling is that this is a connection garbage collection
>>=A0issue *within* MySQLdb, and that it's not actually freeing up
>>=A0connections when it's supposed to (or not freeing up statement
>>=A0handles, or something like that).
>>
>=A0Looking at diffs between 0.9.1 and 0.9.2 just now, I'm even more
>=A0inclined to go with this gut feeling, just given the quantity of
>=A0"small" changes, as well as what appears to be a somewhat large-
>=A0scale stylistic retooling of the MySQLdb internals. Naming
>=A0conventions, internal data structures, etc., seem like they might
>=A0have undergone some significant changes (but I'm just skimming, so
>=A0I might be reading more into this than there is).
>
>=A0In any case, it seems to me like writing a test case against
>=A0MySQLdb that creates and then discards large numbers of connections
>=A0would be a quick way to expose the behaviour for further study.
>=A0Don't count on me to beat you to it this week, though :-)

Agreed on all points. Thanks for the input.

-Chuck
-- 
http://ChuckEsterbrook.com/

Alright, here we go. The attached program exhibits the problem for me on=
 MySQLdb 0.9.2, but not 0.9.1. Admittedly, other variables changed too.

Can a couple more people try it and report their results? A Linux build of=
 0.9.3 beta would be especially interesting.

Once I collect a few more data points, then I'll send this on to Andy=
 Dustman and see what he says...

-Chuck
-- 
http://ChuckEsterbrook.com/

FYI - I'm using MySQLdb 0.9.2 without WebWare and I get the out of 
connection error.

Here' my exact stack trace:

   File "/usr/local/lib/python2.3/site-packages/MySQLdb/__init__.py", 
line 63, in Connect
     return apply(Connection, args, kwargs)
   File "/usr/local/lib/python2.3/site-packages/MySQLdb/connections.py", 
line 115, in __init__
     self._make_connection(args, kwargs2)
   File "/usr/local/lib/python2.3/site-packages/MySQLdb/connections.py", 
line 41, in _make_connection
     apply(super(ConnectionBase, self).__init__, args, kwargs)
OperationalError: (1040, 'Too many connections')

vic

On Saturday, November 8, 2003, at 01:42  PM, Chuck Esterbrook wrote:

> Alright, here we go. The attached program exhibits the problem for me 
> on MySQLdb 0.9.2, but not 0.9.1. Admittedly, other variables changed 
> too.
>
> Can a couple more people try it and report their results? A Linux 
> build of 0.9.3 beta would be especially interesting.
>
> Once I collect a few more data points, then I'll send this on to Andy 
> Dustman and see what he says...
>
> -Chuck
> --
> http://ChuckEsterbrook.com/
> <mysql-prob.py>

FWIW, if you close the connection before reassigning the variable to a new
connection, your test script succeeds on my system (RedHat 7.3, MySQLdb
0.9.2, mysql 4.0.13-standard, python 2.2.3). Just add "conn.close()" at the
end of the body of the for loop.

It seems like the __del__ method isn't being called when the conn variable
is reassigned to the new connection. It could be that MySQLdb keeps an
internal reference to the connection so the reference count does drop to 0
when the conn variable is reassigned. I have not reviewed the MySQLdb code
so that's only a guess.

We have had a bunch of issues with memory leaks due to unclosed db
connections using MySQLdb 0.9.2. It's now strict policy in code reviews to
make sure all db connections and cursors are explicitly closed using
<connection>.close().

- Ben

> -----Original Message-----
> From: webware-devel-admin@...
> [mailto:webware-devel-admin@... Behalf Of Chuck
> Esterbrook
> Sent: Friday, November 07, 2003 6:43 PM
> To: webware-devel@...
> Subject: Re: [Webware-devel] MiddleKit & MySQLdb 0.9.2 problem
>
>
> Alright, here we go. The attached program exhibits the problem
> for me on MySQLdb 0.9.2, but not 0.9.1. Admittedly, other
> variables changed too.
>
> Can a couple more people try it and report their results? A Linux
> build of 0.9.3 beta would be especially interesting.
>
> Once I collect a few more data points, then I'll send this on to
> Andy Dustman and see what he says...
>
> -Chuck
> --
> http://ChuckEsterbrook.com/
>

On Sun, 9 Nov 2003 01:56:40 -0800, Ben Parker wrote:
>=A0FWIW, if you close the connection before reassigning the variable
>=A0to a new connection, your test script succeeds on my system (RedHat
>=A07.3, MySQLdb 0.9.2, mysql 4.0.13-standard, python 2.2.3). Just add
>=A0"conn.close()" at the end of the body of the for loop.
>
>=A0It seems like the __del__ method isn't being called when the conn
>=A0variable is reassigned to the new connection. It could be that
>=A0MySQLdb keeps an internal reference to the connection so the
>=A0reference count does drop to 0 when the conn variable is
                       ^ not
>=A0reassigned. I have not reviewed the MySQLdb code so that's only a
>=A0guess.
>
>=A0We have had a bunch of issues with memory leaks due to unclosed db
>=A0connections using MySQLdb 0.9.2. It's now strict policy in code
>=A0reviews to make sure all db connections and cursors are explicitly
>=A0closed using <connection>.close().

In my real application, the method that creates the connection lives in a=
 library and returns the connection so that it can be optionally reused. So=
 I don't know if there is a straightforward fix in my situation. For now,=
 I'm using 0.9.1.


-Chuck
-- 
http://ChuckEsterbrook.com/

> -----Original Message-----
> From: webware-devel-admin@...
> [mailto:webware-devel-admin@... Behalf Of Chuck
> Esterbrook
> Sent: Sunday, November 09, 2003 1:11 PM
> To: webware-devel@...
> Cc: andy@...
> Subject: RE: [Webware-devel] MiddleKit & MySQLdb 0.9.2 problem
>
>
> On Sun, 9 Nov 2003 01:56:40 -0800, Ben Parker wrote:
> > FWIW, if you close the connection before reassigning the variable
> > to a new connection, your test script succeeds on my system (RedHat
> > 7.3, MySQLdb 0.9.2, mysql 4.0.13-standard, python 2.2.3). Just add
> > "conn.close()" at the end of the body of the for loop.
> >
> > It seems like the __del__ method isn't being called when the conn
> > variable is reassigned to the new connection. It could be that
> > MySQLdb keeps an internal reference to the connection so the
> > reference count does drop to 0 when the conn variable is
>                        ^ not
> > reassigned. I have not reviewed the MySQLdb code so that's only a
> > guess.
> >
> > We have had a bunch of issues with memory leaks due to unclosed db
> > connections using MySQLdb 0.9.2. It's now strict policy in code
> > reviews to make sure all db connections and cursors are explicitly
> > closed using <connection>.close().
>
> In my real application, the method that creates the connection
> lives in a library and returns the connection so that it can be
> optionally reused. So I don't know if there is a straightforward
> fix in my situation. For now, I'm using 0.9.1.
>
>
> -Chuck
> --
> http://ChuckEsterbrook.com/
>

Argh!, apologies for the dropped word there. funny how 3 letters can change
the whole meaning of a sentence...

Yeah, it's tough having optionally reusable connections. You may want to
consider having a pool for reusable connections and a separate method for
opening a single-use connection which will be closed by the thread that
opens it.

- Ben