mysqldb issue

[fedor]

Hi all,

I have a problem with mysql connections. After about 28000-29000
connections, I get a "Can't connect to MySQL server on '127.0.0.1'" error.

I have made a small program which generates the error

"""
import MySQLdb

for i in range(30000):
if not i % 100:
print i
db = MySQLdb.connect(host='127.0.0.1', user='me',passwd='mypassword')
c = db.cursor()
c.close()
db.close()
"""
This is the error after making about 28200 connections:
'''
Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "/usr/tmp/python-12448vuu", line 7, in ?
db = MySQLdb.connect(host='127.0.0.1', user='me', passwd='mypassword')
File "/usr/local/lib/python2.3/site-packages/MySQLdb/__init__.py", line
64, in Connect
return apply(Connection, args, kwargs)
File "/usr/local/lib/python2.3/site-packages/MySQLdb/connections.py",
line
116, 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)
_mysql_exceptions.OperationalError: (2003, "Can't connect to MySQL server
on
'127.0.0.1' (99)")
'''

Does anybody know how to solve this issue?
System: Suse 8.1, mysql 4.0.14, mysqldb 1.0.1, python2.3
Thanks very much.....

Fedor

[Dennis Lee Bieber]

On Thu, 10 Mar 2005 14:07:44 +0100, fedor <f.*****@gmail.com> declaimed
the following in comp.lang.python:

I have a problem with mysql connections. After about 28000-29000
connections, I get a "Can't connect to MySQL server on '127.0.0.1'" error.
<snip>
This is the error after making about 28200 connections:
'''
Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "/usr/tmp/python-12448vuu", line 7, in ?
db = MySQLdb.connect(host='127.0.0.1', user='me', passwd='mypassword')
File "/usr/local/lib/python2.3/site-packages/MySQLdb/__init__.py", line
64, in Connect
return apply(Connection, args, kwargs)
File "/usr/local/lib/python2.3/site-packages/MySQLdb/connections.py",
line
116, 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)
_mysql_exceptions.OperationalError: (2003, "Can't connect to MySQL server
on
'127.0.0.1' (99)")
'''

Does anybody know how to solve this issue?
Off hand, I suspect you've run out of unused TCP sockets. Each
connect results in your task allocating a port, and possibly MySQL
allocating a fresh socket to handle the connection that it finds on its
assigned port.

I seem to recall reading somewhere that sockets normally have a
delay factor on reuse, which may be all the way up to two minutes before
a freed socket can be reused.

Check documentation on REUSEADDR options...

-- ================================================== ============ <
wl*****@ix.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG <
wu******@dm.net | Bestiaria Support Staff <
================================================== ============ <
Home Page: <http://www.dm.net/~wulfraed/> <
Overflow Page: <http://wlfraed.home.netcom.com/> <

[Steve Holden]

fedor wrote:

Hi all,

I have a problem with mysql connections. After about 28000-29000
connections, I get a "Can't connect to MySQL server on '127.0.0.1'" error.

I have made a small program which generates the error

"""
import MySQLdb

for i in range(30000):
if not i % 100:
print i
db = MySQLdb.connect(host='127.0.0.1', user='me',passwd='mypassword')
c = db.cursor()
c.close()
db.close()
"""
This is the error after making about 28200 connections:
'''
Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "/usr/tmp/python-12448vuu", line 7, in ?
db = MySQLdb.connect(host='127.0.0.1', user='me', passwd='mypassword')
File "/usr/local/lib/python2.3/site-packages/MySQLdb/__init__.py", line
64, in Connect
return apply(Connection, args, kwargs)
File "/usr/local/lib/python2.3/site-packages/MySQLdb/connections.py",
line
116, 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)
_mysql_exceptions.OperationalError: (2003, "Can't connect to MySQL server
on
'127.0.0.1' (99)")
'''

Does anybody know how to solve this issue?

I'm not sure this is something you'll see in Real Life (tm). Try running
an overnight test to see whether sleeping for 100 milliseconds between
connections makes the problem go away. If it does, then you are just
running our of available TCP ports.

There's a delay period after a TCP connection is closed and before the
same port number can be re-used by another local process.

If you run your test as it is currently written and after it fails run

netstat -an

you should see a large number of connections in the TIME_WAIT state. If
so then you probably have nothing much to worry about.

regards
Steve

[fedor]

'127.0.0.1' (99)")
'''

Does anybody know how to solve this issue?

Off hand, I suspect you've run out of unused TCP sockets. Each
connect results in your task allocating a port, and possibly MySQL
allocating a fresh socket to handle the connection that it finds on its
assigned port.

That was it.
Also changing '127.0.0.1' to 'localhost' solves the problem.

Thanks.

Fedor

[Dennis Lee Bieber]

On Fri, 11 Mar 2005 21:23:08 +0100, fedor <no****@here.com> declaimed
the following in comp.lang.python:

Also changing '127.0.0.1' to 'localhost' solves the problem.
Heh... Maybe the name lookup is delaying the rest of the
function just enough for some ports to be recycled <G>

-- ================================================== ============ <
wl*****@ix.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG <
wu******@dm.net | Bestiaria Support Staff <
================================================== ============ <
Home Page: <http://www.dm.net/~wulfraed/> <
Overflow Page: <http://wlfraed.home.netcom.com/> <