MySQL Bugs: #26112: Connections initiated too fast might result in "Cannot assign requested address"

Bug #26112	Connections initiated too fast might result in "Cannot assign requested address"
Submitted:	6 Feb 2007 11:24	Modified:	15 Feb 2013 16:30
Reporter:	Csongor Fagyal	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	Connectors: DBD::mysql ( Perl )	Severity:	S4 (Feature request)
Version:	4.1.11	OS:	Linux (FC6 (kernel 2.8.19))
Assigned to:		CPU Architecture:	Any

Description:
I get periodical errors saying:

Can't connect to MySQL server on '192.168.0.200' (99)

Happens if I try to connect to the server very frequently (in a loop). 

Tracing the program gives:

restart_syscall(<... resuming interrupted call ...>) = 0
time(NULL)                              = 1170758829
socket(PF_INET, SOCK_STREAM, IPPROTO_IP) = 3
fcntl64(3, F_SETFL, O_RDONLY)           = 0
fcntl64(3, F_GETFL)                     = 0x2 (flags O_RDWR)
connect(3, {sa_family=AF_INET, sin_port=htons(3306), sin_addr=inet_addr("192.168.0.200")}, 16) = -1 EADDRNOTAVAIL (Cannot assign requested address)
shutdown(3, 2 /* send and receive */)   = -1 ENOTCONN (Transport endpoint is not connected)
close(3)                                = 0
write(1, "Doesn\'t work. Reconnecting in 1 "..., 51) = 51
time(NULL)                              = 1170758829
rt_sigprocmask(SIG_BLOCK, [CHLD], [], 8) = 0
rt_sigaction(SIGCHLD, NULL, {SIG_DFL}, 8) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
nanosleep({1, 0}, {1, 0})               = 0
time(NULL)                              = 1170758830
socket(PF_INET, SOCK_STREAM, IPPROTO_IP) = 3
fcntl64(3, F_SETFL, O_RDONLY)           = 0
fcntl64(3, F_GETFL)                     = 0x2 (flags O_RDWR)
connect(3, {sa_family=AF_INET, sin_port=htons(3306), sin_addr=inet_addr("192.168.0.200")}, 16) = -1 EADDRNOTAVAIL (Cannot assign requested address)
shutdown(3, 2 /* send and receive */)   = -1 ENOTCONN (Transport endpoint is not connected) 

How to repeat:
use DBI;
use strict;

my $dsn = "DBI:mysql:database=test:host=192.168.0.200";
my $dbh;
my $user = 'test';
my $password = 'test';

for (1..50000) {
    $dbh = DBI->connect($dsn, $user, $password, {'RaiseError' => 1} );
    my $sth = $dbh->prepare('SELECT * FROM users');
    $sth= undef;
    $dbh->disconnect;
    $dbh= undef;
} 

Suggested fix:
I am thinking whether I run out of ports because of the high number of connects... I see a lot of "TIME_WAIT" TCP connections when I do netstat -n (somewhere around 15000). Also, if at a failure I wait a few seconds, I am able to connect again.

I have found this (a similar situation?):
http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=4294599

Or could it be related to bug # 20629 ?

Actually I am pretty sure now that I just simply run out of available TCP ports, so I have changed severity to "feature request".

However, I would like to see DBD::mysql to handle this situation in a more transparent way (some sort of a "retry connect" feature).

Thank you for the report.

We don't work on DBD::mysql bugs anymore. All its bugs should go to CPAN: https://rt.cpan.org/Public/Dist/Display.html?Name=DBD-mysql

I re-submitted your report to https://rt.cpan.org/Public/Bug/Display.html?id=83346
Please subscribe to the new report on CPAN and work with DBD::mysql developers in case if they need additional details.