Technical notes from Linux and FreeBSD infrastructure work

SQLSTATE[HY000] [2002] Connection timed out

A-Team Systems investigated a production issue where a PHP application intermittently reported this MySQL connection error:

Unfortunately, this error can mean a lot of different things. You can get it when the database engine is down, when a firewall rule is wrong, when routing is broken, when a cable or switch path is having trouble, or when something in the middle simply does not return the TCP handshake.

Most people who land on this article will probably have a simpler cause than the one described here. That is worth saying up front. This case was unusual because almost everything was working, including almost all of the MySQL connections.

The application was making a very large number of database connections, and only about 1 in every 10,000 new TCP connections failed.

Everything else looked healthy; other application traffic was working, most MySQL traffic was working, and the database was not globally unavailable. The database endpoint path was not showing obvious signs of CPU, memory, swap, or listen-backlog exhaustion.

The environment: FreeBSD PF, CARP, and a high-churn MySQL path

The production path looked roughly like this:

In this case, there was a SQL proxy layer in the database path, and we suspected it for a while. That was a reasonable suspicion. Bypassing that layer stopped the visible application errors.

But the proxy software was not the problem. The same kind of issue could have shown up on a direct-to-database path with enough short-lived connections and the same firewall behavior in the middle.

The pieces that mattered were:

That told us the path mattered. It did not prove the service at the end of the path was failing.

Why the error was misleading

The application error was a MySQL connection timeout:

This was not the same as a slow query, an authentication failure, or a connection that was established and then dropped later.

The failing requests were not getting far enough for the MySQL protocol to matter. They were failing at TCP connect time.

In other words, this was a TCP connect timeout, not a MySQL query problem. So the useful question became very basic:

Did the TCP handshake complete?