how to escape mysql_connect() DNS caching?
am 19.08.2006 11:28:10 von hugoHello 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
works?
I have set up DNS so that on request sql.example.com 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?
$dbaze="abc";
for($cnt=1;$cnt<=6;$cnt++){
$ip=gethostbyname('sql.example.com');
echo $ip."";
$db =mysql_connect($ip, "user", "pwd",1);
if($db)break;
}
if(!$db){
die("Error connecting to the Server");
exit();}
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!
versions:
OS=FreeBSD 6.1
mysql=5.0.24(from source)
php=5.1.5 (from source)
----
From: http://dev.mysql.com/doc/refman/5.0/en/mysql-real-connect.ht ml
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)
->sin_family,
(void *)&((struct sockaddr_in *) addr->ai_addr) ->sin_addr,
ipnamebuff, IPNAMELEN-1);
mysql_real_connect(mysql, ipname, user, pass, database, ...);
}
---