MySQL connection after error 1040: too many connections

And again ERROR 1040 ...

Technical support receives many complaints about this notorious error: ERROR 1040: Too many connections



- too many connections. The problem is obvious: the application or users create more connections than the server allows, that is, the current number of connections exceeds the value of the max_connections



variable.

The situation itself is a problem for end users, but if you still do not have access to the server to diagnose and correct the cause, everything becomes very bad. Typically, you have to complete the instance and restart it in order to recover.

The root user cannot connect either! Why?!

In a properly configured environment, a user with the SUPER



privilege will be able to access the instance and diagnose the cause of error 1040, due to which there are not enough connections. This is described in the manual:

mysqld allows max_connections



+ 1 client connections. An additional connection is reserved for accounts with SUPER



privileges. When these privileges are granted to administrators, and not ordinary users (who do not need them), an administrator who also has the PROCESS



privilege can connect to the server and use SHOW PROCESSLIST



to diagnose problems, even if the maximum number of clients without privileges is connected.

But a lot of people give SUPER



privileges to their users of the application or script - because of the requirements of the application (dangerous!) Or lack of knowledge of the consequences, and then the regular user takes the reserved connection, and the administrative user (usually root



) cannot connect.

How to guarantee access to an instance

You can use the well-known hack with GDB, which Aurimas advised 100 years ago for error 1040, but now there are better solutions. True, they must first be turned on.

With Percona Server 5.5.29 and higher and MySQL 8.0.14 and higher, you can configure another port with an additional number of connections. The application will not use these interfaces. They are only for database administrators and health monitoring and verification agents (see note below).

Configure Percona Server

Starting with Percona Server 5.5.29, you can simply add extra_port



to my.cnf



, and the next time you restart, the port will be available and will expect data on the same bind_address



as normal connections. If you do not configure the extra_port



variable, there will be no additional port by default.

You can also define extra_max_connections



to specify the number of connections this port will handle. The default number is 1.

For an example, I took all connections to the regular users port from the instance where I already configured extra_port



and extra_max_connections



in my.cnf



:

result

By the way, extra_port was removed in Percona Server 8.0.14 and higher, because admin_port with the same functions is implemented in MySQL Community. So edit my.cnf when upgrading to Percona Server 8.0.14 or higher if you already defined extra_port.

Tuning in MySQL Community

As I said, this requires MySQL 8.0.14, where WorkLog 12138 is used .

To enable the admin interface, you need to define admin_addres , which must be the only and unique (without wildcards) IPv4, IPv6, IPv4-mapped address or host name at which the admin interface will wait for data to be transmitted. If this variable is not defined, the interface is not enabled.

You can still define the port, but this is not necessary. By default, this is port 33062



. If this port is free, this value does not need to be configured. If you configure, put both variables in the [mysqld]



section in my.cnf



.

Finally, you can configure create_admin_listener_thread



(disabled by default), which creates a separate thread to handle incoming connections. This may come in handy in some situations.

Another difference is that the Oracle documentation says that:

The number of administrative connections is unlimited.

(And we have the default value of 1). Not sure what that means, but I would be careful not to accidentally establish 1 million connections. They, of course, are not limited, but they consume resources anyway.

Use for monitoring and health checks

Conveniently, not only people can use an additional interface or port in an emergency when we have reached max_connections



. A proxy / load balancer / service discovery monitoring system can connect to it.

Monitoring scripts will be able to retrieve data for diagrams, so that later you figure out where so many connections come from. And the health check scripts will report on the deteriorating state of the server, and certain code may indicate that there are a lot of connections, but the server is coping (that is, it can figure it out by itself and it’s better to wait a bit longer before it fails).

Be sure to install only one connection at a time for monitoring and health checks, so as not to clog extra_max_connections in Percona Server and not create a million threads in MySQL. That is, the scripts should not be connected again if the previous request or connection to the database is still active.

Here is the same example, but with MySQL .

For Percona Server 8.0.14 and above, the process will be the same as for MySQL Community.

Help! I need to go in, but all ports are busy!

If this is the reason you are reading this post, use a crazy hack with GDB (no offense, Aurimas, it just looks risky :-D) or terminate the instance. Fortunately, an instance can almost always be neatly terminated with SIGTERM



(-15) instead of SIGKILL



(-9). So the server will make a clean stop, and the threads will have a chance to shut down normally. Just follow the instructions:

1) Get the PID:

 marcos.albe in ~/ pgrep -x mysqld; 650
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

2) Send SIGTERM to this PID:

 marcos.albe in ~/ kill -15 650;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

3) Check in the error log how the shutdown is performed. It will look something like this:

 2019-07-11T13:43:28.421244Z 0 [Note] Giving 0 client threads a chance to die gracefully 2019-07-11T13:43:28.521238Z 0 [Note] Shutting down slave threads 2019-07-11T13:43:28.521272Z 0 [Note] Forcefully disconnecting 0 remaining clients
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

This marks the beginning of the completion process. The instance will be completed when you see a similar line:

 2019-07-11T13:43:31.292836Z 0 [Note] /opt/percona_server/5.7.26/bin/mysqld: Shutdown complete