The network setup is as follows: - 1 5.5.28 mysqld server that queries only 1 DNS server at a time - 2 DNS servers with different values for 192.168.56.102 (which is where the mysql client test connection will come from) MySQL Server details ============= - 192.168.56.101 - Running 5.5.28-enterprise-commercial-advanced on x86_64 (also tested 5.5.16) - CentOS 6.3 (Final) - GLIBC version is: shell> /lib64/libc.so.6 GNU C Library stable release version 2.12, by Roland McGrath et al. Copyright (C) 2010 Free Software Foundation, Inc. This is free software; see the source for copying conditions. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. Compiled by GNU CC version 4.4.6 20120305 (Red Hat 4.4.6-4). Compiled on a Linux 2.6.32 system on 2012-08-27. Available extensions: The C stubs add-on version 2.1.2. crypt add-on version 2.1 by Michael Glad and others GNU Libidn by Simon Josefsson Native POSIX Threads Library by Ulrich Drepper et al BIND-8.2.3-T5B RT using linux kernel aio libc ABIs: UNIQUE IFUNC For bug reporting instructions, please see: . - to reproduce the bug, thread_cache_size must be enabled and greater than 0 DNS server 1 ============ - 192.168.56.102 - PTR -> ns1.example.com when querying DNS server 1 - PTR -> ns11.example.com when querying DNS server 2 - DNS Zones: shell> cat /var/named/example.com.db example.com. IN SOA ns1.example.com. admin.example.com. ( 2006081401 28800 3600 604800 38400 ) example.com. IN NS ns1.example.com. ns1 IN A 192.168.56.102 ns2 IN A 192.168.56.103 shell> cat /var/named/rev.56.168.192.in-addr.arpa @ IN SOA ns1.example.com. admin.example.com. ( 2006081401; 28800; 604800; 604800; 86400 ) 56.168.192.in-addr.arpa. IN NS ns1.example.com. 102 IN PTR ns1.example.com. 103 IN PTR ns2.example.com. DNS server 2 ============ - 192.168.56.103 - PTR -> ns2.example.com when querying DNS server 1 - PTR -> ns22.example.com when querying DNS server 2 - DNS Zones: shell> cat /var/named/example.com.db example.com. IN SOA ns22.example.com. admin.example.com. ( 2006081401 28800 3600 604800 38400 ) example.com. IN NS ns22.example.com. ns11 IN A 192.168.56.102 ns22 IN A 192.168.56.103 shell> cat /var/named/rev.56.168.192.in-addr.arpa @ IN SOA ns22.example.com. admin.example.com. ( 2006081401; 28800; 604800; 604800; 86400 ) 56.168.192.in-addr.arpa. IN NS ns22.example.com. 102 IN PTR ns11.example.com. 103 IN PTR ns22.example.com. ========= TEST CASE ========= 1) First we check the DNS values returned via the shell on the mysqld server are different depending on which DNS server we query shell> cat /etc/resolv.conf search example.com nameserver 192.168.56.102 #nameserver 192.168.56.103 shell> dig ns1.example.com ; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.10.rc1.el6_3.5 <<>> ns1.example.com ;; global options: +cmd ;; Got answer: ;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 44423 ;; flags: qr aa rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 1, ADDITIONAL: 0 ;; QUESTION SECTION: ;ns1.example.com. IN A ;; ANSWER SECTION: ns1.example.com. 38400 IN A 192.168.56.102 ;; AUTHORITY SECTION: example.com. 38400 IN NS ns1.example.com. ;; Query time: 21 msec ;; SERVER: 192.168.56.102#53(192.168.56.102) ;; WHEN: Tue Oct 30 10:23:03 2012 ;; MSG SIZE rcvd: 63 shell> dig ns11.example.com ; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.10.rc1.el6_3.5 <<>> ns11.example.com ;; global options: +cmd ;; Got answer: ;; ->>HEADER<<- opcode: QUERY, status: NXDOMAIN, id: 15996 ;; flags: qr aa rd ra; QUERY: 1, ANSWER: 0, AUTHORITY: 1, ADDITIONAL: 0 ;; QUESTION SECTION: ;ns11.example.com. IN A ;; AUTHORITY SECTION: example.com. 38400 IN SOA ns1.example.com. admin.example.com. 2006081401 28800 3600 604800 38400 ;; Query time: 3 msec ;; SERVER: 192.168.56.102#53(192.168.56.102) ;; WHEN: Tue Oct 30 10:23:14 2012 ;; MSG SIZE rcvd: 80 shell> dig ns1.example.com @192.168.53.103 ; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.10.rc1.el6_3.5 <<>> ns1.example.com @192.168.56.103 ;; global options: +cmd ;; Got answer: ;; ->>HEADER<<- opcode: QUERY, status: NXDOMAIN, id: 19157 ;; flags: qr aa rd; QUERY: 1, ANSWER: 0, AUTHORITY: 1, ADDITIONAL: 0 ;; WARNING: recursion requested but not available ;; QUESTION SECTION: ;ns1.example.com. IN A ;; AUTHORITY SECTION: example.com. 38400 IN SOA ns22.example.com. admin.example.com. 2006081401 28800 3600 604800 38400 ;; Query time: 4 msec ;; SERVER: 192.168.56.103#53(192.168.56.103) ;; WHEN: Tue Oct 30 10:23:46 2012 ;; MSG SIZE rcvd: 80 shell>dig ns11.example.com @192.168.56.103 ; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.10.rc1.el6_3.5 <<>> ns11.example.com @192.168.56.103 ;; global options: +cmd ;; Got answer: ;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 27456 ;; flags: qr aa rd; QUERY: 1, ANSWER: 1, AUTHORITY: 1, ADDITIONAL: 1 ;; WARNING: recursion requested but not available ;; QUESTION SECTION: ;ns11.example.com. IN A ;; ANSWER SECTION: ns11.example.com. 38400 IN A 192.168.56.102 ;; AUTHORITY SECTION: example.com. 38400 IN NS ns22.example.com. ;; ADDITIONAL SECTION: ns22.example.com. 38400 IN A 192.168.56.103 ;; Query time: 3 msec ;; SERVER: 192.168.56.103#53(192.168.56.103) ;; WHEN: Tue Oct 30 10:23:51 2012 ;; MSG SIZE rcvd: 85 2) Now we check the same for reverse DNS: shell>dig -x 192.168.56.102 ; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.10.rc1.el6_3.5 <<>> -x 192.168.56.102 ;; global options: +cmd ;; Got answer: ;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 36211 ;; flags: qr aa rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 1, ADDITIONAL: 1 ;; QUESTION SECTION: ;102.56.168.192.in-addr.arpa. IN PTR ;; ANSWER SECTION: 102.56.168.192.in-addr.arpa. 86400 IN PTR ns1.example.com. ;; AUTHORITY SECTION: 56.168.192.in-addr.arpa. 86400 IN NS ns1.example.com. ;; ADDITIONAL SECTION: ns1.example.com. 38400 IN A 192.168.56.102 ;; Query time: 2 msec ;; SERVER: 192.168.56.102#53(192.168.56.102) ;; WHEN: Tue Oct 30 10:24:03 2012 ;; MSG SIZE rcvd: 104 shell> dig -x 192.168.56.102 @192.168.56.103 ; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.10.rc1.el6_3.5 <<>> -x 192.168.56.102 @192.168.56.103 ;; global options: +cmd ;; Got answer: ;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 7203 ;; flags: qr aa rd; QUERY: 1, ANSWER: 1, AUTHORITY: 1, ADDITIONAL: 1 ;; WARNING: recursion requested but not available ;; QUESTION SECTION: ;102.56.168.192.in-addr.arpa. IN PTR ;; ANSWER SECTION: 102.56.168.192.in-addr.arpa. 86400 IN PTR ns11.example.com. ;; AUTHORITY SECTION: 56.168.192.in-addr.arpa. 86400 IN NS ns22.example.com. ;; ADDITIONAL SECTION: ns22.example.com. 38400 IN A 192.168.56.103 ;; Query time: 3 msec ;; SERVER: 192.168.56.103#53(192.168.56.103) ;; WHEN: Tue Oct 30 10:24:18 2012 ;; MSG SIZE rcvd: 110 3) Startup mysqld and check if it queries a new DNS server after changing resolv.conf shell> cat /etc/resolv.conf search example.com nameserver 192.168.56.102 mysql [localhost] {msandbox} ((none)) > SHOW GLOBAL VARIABLES LIKE "thread_cache_size"; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | thread_cache_size | 0 | +-------------------+-------+ 1 row in set (0.00 sec) mysql [localhost] {msandbox} ((none)) > SHOW PROCESSLIST; +----+----------+-----------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+----------+-----------+------+---------+------+-------+------------------+ | 6 | msandbox | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST | +----+----------+-----------+------+---------+------+-------+------------------+ 1 row in set (0.00 sec) Now connect a client coming from IP 192.168.56.102 and check how mysqld resolved it: shell@192.168.56.102> mysql -h192.168.56.101 -P5528 -umsandbox -pmsandbox Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.5.28-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) mysql [localhost] {msandbox} ((none)) > SHOW PROCESSLIST; +----+----------+-----------------------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+----------+-----------------------+------+---------+------+-------+------------------+ | 7 | msandbox | ns1.example.com:47739 | NULL | Sleep | 48 | | NULL | | 8 | msandbox | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST | +----+----------+-----------------------+------+---------+------+-------+------------------+ 2 rows in set (0.00 sec) We see that mysqld queries DNS1 and receives ns1.example.com as the hostname of the mysql client. Now let's change resolv.conf, flush the host cache and reconnect the client: shell> cat /etc/resolv.conf search example.com nameserver 192.168.56.103 mysql [localhost] {msandbox} ((none)) > FLUSH HOSTS; Query OK, 0 rows affected (0.00 sec) mysql [localhost] {msandbox} ((none)) > SHOW PROCESSLIST; +----+----------+-----------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+----------+-----------+------+---------+------+-------+------------------+ | 8 | msandbox | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST | +----+----------+-----------+------+---------+------+-------+------------------+ 1 row in set (0.00 sec) shell@192.168.56.102> mysql -h192.168.56.101 -P5528 -umsandbox -pmsandbox Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.5.28-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) mysql [localhost] {msandbox} ((none)) > SHOW PROCESSLIST; +----+----------+------------------------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+----------+------------------------+------+---------+------+-------+------------------+ | 8 | msandbox | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST | | 9 | msandbox | ns11.example.com:47740 | NULL | Sleep | 2 | | NULL | +----+----------+------------------------+------+---------+------+-------+------------------+ 2 rows in set (0.00 sec) 4) We can see that mysqld correctly queries DNS2 and receives back ns11.example.com instead of ns1.example.com. However, if we set thread_cache_size >0, then the new ns11.example.com value from DNS2 is never received as mysqld keeps using DNS1. Lets do the same test as above, this time with thread_cache_size=1; shell> cat /etc/resolv.conf search example.com nameserver 192.168.56.102 mysql [localhost] {msandbox} ((none)) > SET @@global.thread_cache_size=1; Query OK, 0 rows affected (0.00 sec) mysql [localhost] {msandbox} ((none)) > SHOW GLOBAL VARIABLES LIKE "thread_cache_size"; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | thread_cache_size | 1 | +-------------------+-------+ 1 row in set (0.01 sec) mysql [localhost] {msandbox} ((none)) > SHOW PROCESSLIST; +----+----------+-----------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+----------+-----------+------+---------+------+-------+------------------+ | 1 | msandbox | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST | +----+----------+-----------+------+---------+------+-------+------------------+ 1 row in set (0.00 sec) shell@192.168.56.102> mysql -h192.168.56.101 -P5528 -umsandbox -pmsandbox Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.5.28-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) mysql [localhost] {msandbox} ((none)) > SHOW PROCESSLIST; +----+----------+-----------------------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+----------+-----------------------+------+---------+------+-------+------------------+ | 1 | msandbox | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST | | 10 | msandbox | ns1.example.com:47749 | NULL | Sleep | 33 | | NULL | +----+----------+-----------------------+------+---------+------+-------+------------------+ 2 rows in set (0.00 sec) Time to change resolv.conf, flush the host cache and connect the client again: shell> cat /etc/resolv.conf search example.com nameserver 192.168.56.103 mysql [localhost] {msandbox} ((none)) > FLUSH HOSTS; Query OK, 0 rows affected (0.00 sec) mysql [localhost] {msandbox} ((none)) > SHOW PROCESSLIST; +----+----------+-----------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+----------+-----------+------+---------+------+-------+------------------+ | 11 | msandbox | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST | +----+----------+-----------+------+---------+------+-------+------------------+ 1 row in set (0.00 sec) shell@192.168.56.102> mysql -h192.168.56.101 -P5528 -umsandbox -pmsandbox Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 5.5.28-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) mysql [localhost] {msandbox} ((none)) > SHOW PROCESSLIST; +----+----------+-----------------------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+----------+-----------------------+------+---------+------+-------+------------------+ | 11 | msandbox | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST | | 12 | msandbox | ns1.example.com:47750 | NULL | Sleep | 4 | | NULL | +----+----------+-----------------------+------+---------+------+-------+------------------+ 2 rows in set (0.00 sec) mysql [localhost] {msandbox} ((none)) > ^DBye We still get back ns1.example.com instead of the expected ns11.example.com. shell> dig -x 192.168.56.102 ; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.10.rc1.el6_3.5 <<>> -x 192.168.56.102 ;; global options: +cmd ;; Got answer: ;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 28005 ;; flags: qr aa rd; QUERY: 1, ANSWER: 1, AUTHORITY: 1, ADDITIONAL: 1 ;; WARNING: recursion requested but not available ;; QUESTION SECTION: ;102.56.168.192.in-addr.arpa. IN PTR ;; ANSWER SECTION: 102.56.168.192.in-addr.arpa. 86400 IN PTR ns11.example.com. ;; AUTHORITY SECTION: 56.168.192.in-addr.arpa. 86400 IN NS ns22.example.com. ;; ADDITIONAL SECTION: ns22.example.com. 38400 IN A 192.168.56.103 ;; Query time: 6 msec ;; SERVER: 192.168.56.103#53(192.168.56.103) ;; WHEN: Tue Oct 30 10:51:26 2012 ;; MSG SIZE rcvd: 110 As we can see, with thread_cache_size=1, mysqld fails to query the new DNS2 server and keeps using the old DNS1 server. My tests show the same behaviou with thread_cache_size={10,500,1000}.