At some point you will encounter “Too many connections” errors – given that the MySQL database server has three different connection limits, it’s important to know which limit you’ve hit, and what you can do about it.

Reasons for “Too many connections” errors

Connections may fail with the “Too many connections” message for three reasons:

1. The server has too many open connections already. A MySQL server can only handle a specific number of open connections before refusing to allow any more, and this limit is shared amongst all users of the server. It’s usually set quite high, although it’s easily possible for someone to effectively DoS a MySQL server by making lots of connections (but this can be prevented – see below)
2. Your user account has a limited number of connections allowed per hour – any further connections within that hour would be rejected. This is set on a per-user basis.
3. Your user account has a limited number of allowable open connections – any further connections would be rejected. This is set on a per-user basis.

It’s always important to read the error message you get returned on the connection attempt, as in most cases this will pinpoint the exact reason for failure.

Per-user connection limit on number of open connections

If your account has a maximum number of connections limit (scenario #3), the error would be:

ERROR 1226 (42000): User 'mysqldba' has exceeded the 'max_user_connections' resource (current value: 1)

Where ‘mysqldba’ would be your username, and the ‘current value’ is the maximum number of open connections allowed from this user. In this case, you need to reduce the number of database connections you’re making (persistent or pooled connections may help, as might using a singleton pattern for your database connections) or speak to the DBA in charge of that server and ask that your per-user connection limit is raised.

Per-user connection limit on number of connections per hour

If your account has a maximum number of connections per hour limit (scenario #2), the error would be:

ERROR 1226 (42000): User 'mysqldba' has exceeded the 'max_connections_per_hour' resource (current value: 1)

Where, once again, ‘mysqldba’ would be your username, and the ‘current value’ is the maximum number of connections per hour allowed for this user. Once again, your options are to change your code to reduce the frequency of connections (as opposed to the total number – the same system/coding advice above still applies, but in this case you need to prevent the connections from being closed by increasing the pooled connection lifetime and/or overloading the disconnect function to become a null operation. You will also need to look at the idle connection timeout on the server – this will automatically close your connections and may have been reduced to conserve system resources).

Server-wide connection limit on number of open connections

The final limit is the total number of open connections the server will accept, from all users. The error message you would get in these cases is:

ERROR 1040 (08004): Too many connections

What can you do about it? If you don’t have superuser privileges on this server then you can’t do anything apart from complain to the DBA responsible for that server. They might increase the maximum number of connections allowed, which could solve the problem short-term, but if someone else using the server is creating a stupid number of database connections the slots would just fill up again.

If you are the DBA for the server, you could increase the total number of connections allowed by setting the global ‘max_connections’ variable (don’t forget to set it in the configuration file as well, otherwise the next time the server starts your modification would be lost). You will need to bear in mind that these extra connections will take up system resources, so setting the figure too high can have an extremely negative impact (like the server being pushed into swap, or even worse on Linux systems, triggering the OOM killer).

The best way of handling database connections is to enforce a per-user maximum open connection limit – this would have no impact on light users but would stop the heavy users effectively performing a DoS on the server. In a shared-server situation, this makes the most sense – ‘power users’ would/should have their own server, or could/should pay to increase their maximum open connections.