#61 MySQLdb.Connection is never deleted (freed)

[Jiri Barton]

I'm sorry if this bug has been resolved/posted already but it is so
serious. The problem is the same with whatever Python version
and both with MySQLdb 0.9.2 and 0.9.3a

Python 2.3b2+ (#2, Jul 5 2003, 11:28:28)
[GCC 3.3.1 20030626 (Debian prerelease)] on linux2
Type "help", "copyright", "credits" or "license" for more
information.
>>> import MySQLdb, gc
>>> db = MySQLdb.connect (db='withheld', user='withheld',
passwd='withheld')
>>> db.close ()
>>> del db
>>> gc.collect ()
6
>>> gc.garbage
[<_mysql.connection open to 'localhost' at 81b7d5c>]
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Let's get deeper...

>>> gc.get_referrers (gc.garbage[0])
[[<_mysql.connection open to 'localhost' at 81b7d5c>], <built-in
method string_literal of Connection object at 0x81b7d5c>, <bound
method Connection.unicode_literal of <_mysql.connection open to
'localhost' at 81b7d5c>>]
>>> gc.get_referrers (gc.get_referrers (gc.garbage[0])[1])
[{<type 'int'>: <function Thing2Str at 0x4028a4fc>, 1: <type
'int'>, 2: <type 'int'>, 3: <type 'long'>, 4: <type 'float'>, 5: <type
'float'>, 0: <type 'float'>, 7: <function mysql_timestamp_converter
at 0x4028a41c>, 8: <type 'long'>, 9: <type 'int'>, 10: <function
format_DATE at 0x4028a374>, 11: <function format_DATE at
0x4028a374>, 12: <function format_DATE at 0x4028a374>, 13:
<type 'int'>, 248: <function Str2Set at 0x4028a56c>,
'DateTimeType': <function DateTime2literal at 0x4028a3ac>,
<type 'array.array'>: <function array2Str at 0x4029648c>, <type
'list'>: <built-in function escape_sequence>, 'DateTimeDeltaType':
<function DateTimeDelta2literal at 0x4028a3e4>, <type
'NoneType'>: <function None2NULL at 0x402963ac>, <type
'float'>: <function Thing2Str at 0x4028a4fc>, <type 'instance'>:
<function Instance2Str at 0x4029641c>, <type 'tuple'>: <built-in
function escape_sequence>, <type 'object'>: <function
Instance2Str at 0x4029641c>, <type 'str'>: <built-in method
string_literal of Connection object at 0x81b7d5c>, <type
'unicode'>: <bound method Connection.unicode_literal of
<_mysql.connection open to 'localhost' at 81b7d5c>>, <type
'long'>: <function Thing2Str at 0x4028a4fc>, <type 'dict'>:
<built-in function escape_dict>}]

Okay, that's the girl. The built-in method string_literal of
Connection object is referrenced from some dictionary, the key
being <type 'str>. This is actually the converters member of
Connection:

..... (connections.py)
class Connection(ConnectionBase):
.....
def init(self, args, *kwargs):
.....
self.converter[types.StringType] = self.string_literal
if hasattr(types, 'UnicodeType'):
self.converter[types.UnicodeType] = self.unicode_literal
.....

This is it. self.convertes contains a member that points back to a
member of Connection. Now here's the catch. Since Connection
defines its own del method, the built-in garbage collector
cannot resolve this cycle and the object never gets deleted.

Since the Connection.del method is never called, the
connection to the MySQL db stays open because
Connection.del is supposed to call close () on the db.

I found this when I got 'Too many connections' on a standalone
computer (MySQL maxconnections set to 100) - in mod_python
running for several hours. Since mod_python vars are persistent, it
just happened.

So, my suggestion would be
....
self.converter[types.StringType] = Thing2Literal
if hasattr(types, 'UnicodeType'):
self.converter[types.UnicodeType] = Unicode2Str
....
Since Thing2Literal will eventually call string_literal, everything's
going to be as desired The similar with Unicode2Str.

I hope it's not all my misunderstanding.
Jiri Barton

[Andy Dustman]

Logged In: YES
user_id=71372

Interesting, I'll look at the GC in more detail.

[Andy Dustman]

Logged In: YES
user_id=71372

Have you tested your suggested change?

[Jiri Barton]

Logged In: YES
user_id=463603

Yes, I have tested it. In fact, I've been using this change in my own
project since I discovered it - the mod_python script is running all the
time and the pool of mysqld has always the same size.
Plus, it does the very same thing as the original.

[Maximillian Dornseif]

Logged In: YES
user_id=554460

I had the same problem and solved it via weak references. See
Patch 835372 for details. The patch is tested, but converters have
seen no special exercise in the testing. One problem of the patch
is that it only works with recent python versions. So probably one
should build a try: import ... ; except ImportError: dummy proxy
construct around it.

[Maximillian Dornseif]

Logged In: YES
user_id=554460

I suspect the WebWare/MiddleKit People have been running into
the same problem. If you look into their sourse (http://
cvs.sourceforge.net/viewcvs.py/webware/Webware/MiddleKit/Run/
SQLObjectStore.py?rev=1.56&view=auto#) somebody desperately
tried to force "aggressiveGC".

To my understanding the suggestion by Jiri Barton will still lead to
a cyclic reference which can only be freed/closed by the (slow)
cycle detecting garbage collector instead via reference counting.

[Andy Dustman]

Logged In: YES
user_id=71372

The reason the methods are used is because MySQL character
set is per-connection. Without the correct character set,
you can't handle Unicode properlyI think you are on the
right track as far as GC is concerned, though.

[Andy Dustman]

Logged In: YES
user_id=71372

Since it appears the character set cannot be changed by the
client in MySQL<4.1, I'm probably going to fix this by
replacing self.string_literal and self.unicode_literal with
lambdas which include the character set as a default
parameter. I'll let you know when this is done.

[Michael C. Neel]

Logged In: YES
user_id=294560

I'm echoing that this is a very serious bug; we had to apply
this patch recently to stop a high traffic mod_python
webserver from spirling out of control as connections were
never closed.

The weak reference patch mentioned did not work with our
code; we got "weak reference has gone away" messages when
applied. The patch mentioned here however did work, quite well.

[Andy Dustman]

Logged In: YES
user_id=71372

I'll probably apply the weak reference patch to the 1.1
series for inclusion in 1.2, and not try to fix 1.0. I
didn't have much luck with lambda functions.

[Nobody/Anonymous]

Logged In: NO

I understood that connections would be garbage collected
when they went out of scope, but I just had a problem with
too many connections when running my python program :(
Should I be freeing them somehow or is it a definite bug?
I am now about to try adding cursor.close() db.close() at the
end of every function that uses mysql.

[Andy Dustman]

Logged In: YES
user_id=71372

Is this bug reproducable with 1.1.8?

[Jiri Barton]

Logged In: YES
user_id=463603

Unfortunately, yes. I just have downloaded, built, and installed the
version 1.1.8 but the problem is still there:

import MySQLdb, gc
db = MySQLdb.connect(db='test')
del db
gc.collect()

It says 6.

gc.garbage says, <_mysql.connection open to 'localhost' at 6a3f40>]

Now, with the garbage collector freeing all the free objects (forced the
collect), the connection should close but it does not. I have to
investigate yet further what is happenning here.

I have noticed the situation has changed however: using the sequence
of commands posted when opening the bug, there is no leak anymore:

import MySQLdb, gc
db = MySQLdb.connect(db='test')
db.close #closing the connection manually
del db
gc.collect()

And it says, 0. Zero unreachable objects. So, I still have to close the
connection before the connection variable goes out of the scope - so
calling self.close() in the Connection.del still has no effect - I
believe the method del is not called at all - why that is - that is still
to find out.

[Andy Dustman]

Logged In: YES
user_id=71372

With the current CVS version:

andy@tweek MySQLdb $ cat leaktest.py

!/usr/bin/python

import MySQLdb
import sys
import gc

db=MySQLdb.connect(db='test',read_default_file="~/.my.cnf",use_unicode=1)
print db
del db

print "gc.collect() ->", gc.collect()
print "gc.garbage ->", gc.garbage

andy@tweek MySQLdb $ python leaktest.py
<_mysql.connection open to 'localhost' at 80afaec>
gc.collect() -> 0
gc.garbage -> []
andy@tweek MySQLdb $

Give this a try, because I might actually have it fixed.
Make sure you have connections.py rev 1.31. In short, I am
defining a couple local functions in the init which use
a proxy to self as a default parameter.

[Andy Dustman]

Logged In: YES
user_id=71372

Have you tried one of the recent releases (1.1.9 or 1.1.10)?
I'd like to know this is fixed in 1.2. However, I am pretty
sure it is fixed.

[Jiri Barton]

Logged In: YES
user_id=463603

Well well,

I am bringing two messages. The good news is there is no leak
anymore. This is good. In fact, very good for the production
environment.

The other thing is, there is still open mysql connection - unless I
call the close method on the Connection class. I can see it was
called in the del but now since the custom destructor is
gone, it does not close the connection. Here is how to find out:

import MySQLdb, gc
db = MySQLdb.connect(db='test')
del db
gc.collect()

It says 0 - good. However, now, switch to console and see this:

[root@balmora ~]# netstat | grep mysql

unix 3 [ ] STREAM CONNECTED
159671 /var/run/mysqld/mysqld.sock

This is no good. It stays there until I exit python. Of course, if I
invoke the close method, there will be no open connection.

Alternatively, you can see there is no QUIT in tail
-f /var/log/mysql/mysql.log - unless you call Connection.close().

Can you reproduce this?

I'm sorry to bring the bad news again. I checked that with the
versions 1.1.9, 1.1.10, and CVS (two hours ago).

I don't know how to fix this. Am I to use the proxy classes you
were mentioning? Or, can _connection be hooked so that it
calls_mysql_ConnectionObject_close when destroyed?

[Nobody/Anonymous]

Logged In: NO

Try the current CVS or apply this patch:

--- _mysql.c.~1.70.~ 2005-01-31 22:21:49.000000000 -0500
+++ _mysql.c 2005-02-04 11:59:40.636867560 -0500
@@ -640,8 +640,9 @@
_mysql_ConnectionObject self,
PyObject args)
{
- if (!args) return NULL;
- if (!PyArg_ParseTuple(args, "")) return NULL;
+ if (args) {
+ if (!PyArg_ParseTuple(args, "")) return NULL;
+ }
if (self->open) {
Py_BEGIN_ALLOW_THREADS
mysql_close(&(self->connection));

[Jiri Barton]

Logged In: YES
user_id=463603

That's it! It works! The connection is being closed.

This is so great, I can let the garbage collector close the
connections for me, the same as with file().

Long live MySQLdb and Python!
Jiri Barton

[estrand]

Logged In: YES
user_id=1202271

I can confirm that this has been fixed in 1.1.2.

Note however, that if using mod-python's PSP, a connection
is still left open if a psp.redirect() is done. This PSP
code will cause a connection to be left open:

<% import MySQLdb db = MySQLdb.connect(read_default_file="/var/lib/mysql/rlp.cnf") psp.redirect("redir.html") %> This is the end!

I must explicitly close the connection before the redirect.
This PSP code will NOT cause a connection to be left open:

<% import MySQLdb db = MySQLdb.connect(read_default_file="/var/lib/mysql/rlp.cnf") db.close() psp.redirect("redir.html") %> This is the end!

I am not sure that this is a MySQLdb or a PSP issue, but I
want others to know that they cannot necessarily get rid of
all explicit connection closures.

--Eric

[Andy Dustman]

Logged In: YES
user_id=71372

Can you verify this with MySQLdb-1.2.0? 1.1.2 is quite old
from a development standpoint.

[estrand]

Logged In: YES
user_id=1202271

Oops. My apologies; I blew it when typing the version
number in the previous message. I am using version 1.2.0,
and the problem still exists.

--Eric

[Jiri Barton]

Logged In: YES
user_id=463603

Hi estrand,

this is actually no error. The connection closes under
either of the following circumstances:

• you call db.close(), or
• the db object is deleted which will call the close
  method too somewhere in its implementation.

In your situation, the db object persists after the PSP
code is evaluated; remember, mod_python is not
unloaded after the request has been carried out. In
addition, every mod_python instance (there may be
several of them - depending on how Apache has
been configured) keeps its own instance of python
interpreter with all the environment, and above all, the
garbage collector.

So, the garbage collector lasts beyond the span of a
request. (it may not sometimes but I won't go into
details here). Therefore the db object is not collected
immediately after the execution of the PSP code and
so the connection stays open.

In the long run, however, the garbage collector will be
activated eventually and the connection will close.
Either the collector's thresholds will be exceeded or
Apache just decides to free mod_python instance -
both triggering the collecting.

So, calling db.close() is not necessary as long as
you don't care about several open connections - their
number is steady however.

HTH, Jiri