how to escape mysql_connect() DNS caching?

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Hello people,

There is a problem is that mysql_connect() somehow caches last sessions
IP and is not using the one which you put into host place.

Has anyone made mysql_connect() from php to multiple SQL servers so
that script tries to connect to resolved IPs until it finds one that

I have set up DNS so that on request it returns two
IPs (rotating on each request).
When I see output of script below I see that php function
gethostbyname() does DNS requests in for() cycle and does return sql
nodes IP in rotated manner, but from tcpdump I see, that
mysql_connect() is using just one IP to connect to port 3306.

Is there some way to flush the mysql_connect() dns cache?

    echo $ip."</br>";
    $db =mysql_connect($ip, "user", "pwd",1);
die("Error connecting to the Server");

I think this is the problem in mysql library used by php, so I tried to
drill down there and found comment beolw in developers page. I am not
wery well in C so I am
not sure where to put this piece of code so that recompiled mysql and
php(or just php)
would contain correct mysql_connect() function that does connect to IP
which is given.

This problem is very strange mostly because right now mysql is
advertising its cluster - so
connection to multiple SQLs is just natural to be really "no single
point of failure".

Can anyone suggest a solloution to this?
Txh in advance!

OS=FreeBSD 6.1
mysql=5.0.24(from source)
php=5.1.5 (from source)

Posted by Andrew Donkin on March 4 2005 2:23am    [Delete] [Edit]

If you have configured your DNS to return a list of MySQL servers, and
you want your client
to work through that list until it finds one that works,
you can put real_connect inside a getaddrinfo() loop.
I wish the client library did it by itself:

struct addrinfo *addr, *addrlist;
struct addrinfo hints = {0, PF_UNSPEC, SOCK_STREAM,
0, 0, 0, 0, 0};

int gai_result = getaddrinfo(host, 0, &hints, &addrlist);
if (gai_result) bomb(Unknown host);

for (addr = addrlist; addr; addr = addr->ai_next) {
#define IPNAMELEN 20
char ipnamebuff[IPNAMELEN];
const char *ipname = inet_ntop(((struct sockaddr_in *) addr->ai_addr)
(void *)&((struct sockaddr_in *) addr->ai_addr) ->sin_addr,
ipnamebuff, IPNAMELEN-1);

mysql_real_connect(mysql, ipname, user, pass, database, ...);

Re: how to escape mysql_connect() DNS caching?


found the problem:
1)PHP didn't read correct config file - so mysql.connect_timeout was
not correct
2)as I use FreeBSD jails - tcpdump didn't show packets between jails on
same machine because they do not cross interface..

Problem was between screen and chair..:)

Site Timeline