Problem of high number of unauthenticated users in MySQL

Problem Description:
In March 2009, we faced a problem with MySQL server. The server was creating large number of connections and it was crossing the maximum number of connections for the server.  At that time we were using MySQL server version 4.1.10 on RHEL 3.0 operating system.
Solution:
When I monitored the connections I saw lots of unauthenticated user connections to the database.
mysql> show processlist;
| 150607 | unauthenticated user | connecting host       |        | Connect     |

The unauthenticated user refers to a thread that has become associated with a client connection but for which authentication of the client user has not yet been done.
After doing some searching I found that this problem was submitted as a bug http://bugs.mysql.com/bug.php?id=8945 for MySQL version 4.1
The problem was related to the DNS. But DNS resolving was working fine on the server (we were using an external DNS server).
Now we are going to see how MySQL uses DNS
When a new client connects to MySQL Server, MySQL spawns a new thread to handle the request. This thread first checks whether the host name is in the host name cache. If not, the thread attempts to resolve the host name:

• The thread takes the IP address and resolves it to a host name (using gethostbyaddr()). It then takes that host name and resolves it back to the IP address (using gethostbyname()) and compares to ensure it is the original IP address.
• If the operating system supports the thread-safe gethostbyaddr_r() and gethostbyname_r() calls, the thread uses them to perform host name resolution.
• If the operating system does not support the thread-safe calls, the thread locks a mutex and calls gethostbyaddr() and gethostbyname() instead. In this case, no other thread can resolve host names that are not in the host name cache until the first thread unlocks the mutex.

In our case the new threads were unable to resolve the host names.
One solution for this problem was to disable DNS hostname lookups by starting MySQL with the option –skip-name-resolve. If we disable DNS hostname lookup then we can only use IP address in the MySQL grant tables. But in our case which was a tough task as many of the grants were using hostname.
So I added all the connecting hosts in /etc/hosts file and that solved our problem.

জয় আই অসম,

প্রণব শর্মা