Bug #18557 Host field of processlist incorrectly cached
Submitted: 27 Mar 2006 21:55 Modified: 31 Mar 2006 21:27
Reporter: Jeffrey Considine Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.18-standard-log OS:MacOS (OS X 10.4.5)
Assigned to: CPU Architecture:Any

[27 Mar 2006 21:55] Jeffrey Considine 
Description:
The Host column of SHOW PROCESSLIST remembers old hostnames for a specified IP address. This persists over multiple days and reboots. This is very inconvenient when clients are using DHCP.

'host' command and 'nslookup' from database server report the correct client hostnames.

How to repeat:
Connect with one client with dynamic DNS name. Disconnect client from network. Connect with second client which receives first client's IP address.

Suggested fix:
Periodically refresh any cached name information.
[30 Mar 2006 14:57] Domas Mituzas 
MySQL server does not have persistent host cache (that would survive reboots). 
host and nslookup commands do not use libc resolver, which relies on nscd.

nscd cache might be the source of this behavior as well as incorrect TTL values on DNS records.
[30 Mar 2006 19:36] Jeffrey Considine 
MySQL is the only program which seems to have this problem. Other programs using the libc resolver do not have this problem.

For instance, netstat will output the following line

   tcp4       0      0  quark.adverplex..mysql trope.adverplex..55659 ESTABLISHED

while 'SHOW PROCESSLIST' says

  | 62307 | adverplex | walker.adverplex.com:55659  | NULL       | Connect     |     4 | Reading from net                                               | NULL                                                                                                 |

And no, there are no other connections with the same port. There is also no ncsd since this is Mac OS X, not Linux.
[31 Mar 2006 13:33] Valeriy Kravchuk 
Then it should be a duplicate of "famous" bug #15756. Please, check your IP-addresses. If it is a duplicate, wait for 4.1.19 to be realeased officially or build from current sources yourself.
[31 Mar 2006 19:47] Jeffrey Considine 
The IP addresses in question are in the same subnet, and are identical except the last octets which have decimal values 222 and 254. The query

  SELECT CHAR(254) = CHAR(222)

returns 1, so it looks like this is the problem.

(One of the guys in the office was actually wondering yesterday if it was a coincidence that the addresses were off by 32.)
[31 Mar 2006 21:27] Jeffrey Considine 
Dup of 15756 as suggested before.