Solving MySQL connection problems caused by a dead name server

A client site went down today. This is what happened and how it was fixed.

Symptoms

The immediate symptom is that your application servers can’t connect to your database servers. Attempted connections get an error message:

Can’t connect to MySQL server on ‘10.10.110.11’ (111)

The relevant MySQL process list reveals a long list of attempted connections in state login:

root@server-db1:~ $ mysqladmin processlist
+-----+----------------------+--------------------+----+---------+------+-------+
| Id  | User                 | Host               | db | Command | Time | State |
+-----+----------------------+--------------------+----+---------+------+-------+
...
| 261 | unauthenticated user | 10.20.115.19:43381 |    | Connect |      | login |
| 262 | unauthenticated user | 10.20.115.19:43396 |    | Connect |      | login |
| 263 | unauthenticated user | 10.20.115.19:43420 |    | Connect |      | login |
| 264 | unauthenticated user | 10.20.115.19:43429 |    | Connect |      | login |
| 265 | unauthenticated user | 10.20.115.4:55297  |    | Connect |      | login |
...

Your site is probably down as no request can connect to the database - people are starting to get upset.

Problem

When a client connects to MySQL, the newly spawned thread attempts to resolve the host name (see MySQL’s documentation on DNS). This problem is caused by the first name server in /etc/resolv.conf being down, causing the DNS request to time-out. Hence, every connection to MySQL sits for a minute waiting for the timeout to occur. Within a few seconds, no client will be able to connect.

You can verify this using the dig command to exercise the name servers in /etc/resolv.conf. Here’s the broken one (you’ll have to wait for the time-out):

dig @180.179.39.80 www.google.com

; <<>> DiG 9.7.0-P1 <<>> @180.179.39.80 www.google.com
; (1 server found)
;; global options: +cmd
;; connection timed out; no servers could be reached

A working name server would return something like:

dig @180.179.39.81 www.google.com

; <<>> DiG 9.7.0-P1 <<>> @180.179.39.81 www.google.com
; (1 server found)
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 14538
;; flags: qr rd ra; QUERY: 1, ANSWER: 6, AUTHORITY: 4, ADDITIONAL: 4

;; QUESTION SECTION:
;www.google.com.googleINA

;; ANSWER SECTION:
www.google.com.com      427763  IN  CNAME   www.l.google.com.
www.l.google.com.com    97      IN  A       74.125.236.116
www.l.google.com.com    97      IN  A       74.125.236.112
www.l.google.com.com    97      IN  A       74.125.236.113
www.l.google.com.com    97      IN  A       74.125.236.114
www.l.google.com.com    97      IN  A       74.125.236.115

;; AUTHORITY SECTION:
google.com.com          250791  IN  NS      ns2.google.com.
google.com.com          250791  IN  NS      ns3.google.com.
google.com.com          250791  IN  NS      ns4.google.com.
google.com.com          250791  IN  NS      ns1.google.com.

;; ADDITIONAL SECTION:
ns1.google.com.com      76667   IN  A       216.239.32.10
ns1.google.com.com      76667   IN  A       216.239.34.10
ns1.google.com.com      76667   IN  A       216.239.36.10
ns1.google.com.com      76667   IN  A       216.239.38.10

;; Query time: 13 msec
;; SERVER: 180.179.39.81#53(180.179.39.81)
;; WHEN: Thu Feb  2 16:18:38 2012
;; MSG SIZE  rcvd: 268

Solution

The root solution is to fix the name server, but sometimes that isn’t in your control.

You can work around the borked name server by restarting MySQL with the --skip-name-resolve option. This prevents MySQL trying to resolve the host name for each thread, bypassing the name server problem.

Alternatively, you can remove the broken DNS server from your /etc/resolv.conf.

Discussion

Note that running MySQL with --skip-name-resolve means you can’t use hostnames in your privileges table. Thus, you may have to reconfigure your client users to get your site back up. You can verify this by using the following SQL to inspect your configured users and hosts:

mysql> SELECT user, host FROM mysql.user;

Check to see if the host column uses domain names.

Credits

Thanks to Tangent’s operations team @timbobsteve and @kuramanga for their help in debugging and fixing this.

——————

Something wrong? Suggest an improvement or add a comment (see article history)
Tagged with: mysql
Filed in: tips

Previous: Auto-setting terminal titles for python virtual environments
Next: A Fabric function for git tagging

Copyright © 2005-2024 David Winterbottom
Content licensed under CC BY-NC-SA 4.0.