#61 MySQLdb.Connection is never deleted (freed)

MySQLdb-1.1
closed
MySQLdb (285)
5
2012-09-19
2003-07-14
Jiri Barton
No

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

Discussion

  • Andy Dustman

    Andy Dustman - 2003-07-15

    Logged In: YES
    user_id=71372

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

     
  • Andy Dustman

    Andy Dustman - 2003-09-07

    Logged In: YES
    user_id=71372

    Have you tested your suggested change?

     
  • Jiri Barton

    Jiri Barton - 2003-09-08

    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

    Andy Dustman - 2003-11-24

    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

    Andy Dustman - 2003-12-13

    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

    Michael C. Neel - 2004-03-25

    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

    Andy Dustman - 2004-06-22

    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

    Andy Dustman - 2005-01-13

    Logged In: YES
    user_id=71372

    Is this bug reproducable with 1.1.8?

     
  • Jiri Barton

    Jiri Barton - 2005-01-13

    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

    Andy Dustman - 2005-01-23

    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

    Andy Dustman - 2005-02-02

    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

    Jiri Barton - 2005-02-04

    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

    Jiri Barton - 2005-02-04

    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

    estrand - 2005-03-23

    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

    Andy Dustman - 2005-03-23

    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

    estrand - 2005-03-23

    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

    Jiri Barton - 2005-03-24

    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

     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks