PHP Fatal error: Uncaught mysqli_sql_exception: Too many connections in (SOLVED)

Contents

1. Error “Uncaught mysqli_sql_exception: Too many connections”
2. Why does the “Too many connections” error occur
3. How to temporarily increase the maximum number of connections to MySQL / MariaDB
4. How to permanently increase the maximum number of connections to MySQL / MariaDB
5. What is the maximum number of connections that can be established for MySQL and MariaDB

1. Error “Uncaught mysqli_sql_exception: Too many connections”

When running a PHP script that uses a connection to MySQL (or MariaDB) databases, the following error may occur:

PHP Fatal error:  Uncaught mysqli_sql_exception: Too many connections in /home/mial/bin/path/script.php:17
Stack trace:
#0 /home/mial/bin/path/script.php(17): mysqli->__construct('localhost', 'root', Object(SensitiveParameterValue), 'DB_NAME...')
#1 {main}
  thrown in /home/mial/bin/path/script.php on line 17

Fatal error: Uncaught mysqli_sql_exception: Too many connections in /home/mial/bin/path/script.php:17
Stack trace:
#0 /home/mial/bin/path/script.php(17): mysqli->__construct('localhost', 'root', Object(SensitiveParameterValue), 'DB_NAME...')
#1 {main}
  thrown in /home/mial/bin/path/script.php on line 17

In the screenshot above, the error occurred when running a PHP program in the command line, but this problem can also occur for a website.

2. Why does the “Too many connections” error occur

The cause of the problem is quite accurately described in the error text – too many connections to the MySQL or MariaDB DBMS.

There is no limit on the number of connections to databases in PHP. The reason for this error is the limitation in MySQL or MariaDB on the number of simultaneous connections.

So, if your computer or server really has many instances of PHP scripts running (a hundred or more), then this error is quite natural. If the error occurs when running one script or one page of the site, it may mean that there is an error in the source code that leads to an unreasonable increase in the number of simultaneous connections to MySQL / MariaDB.

Please note that an open connection using the code

$mysqli = new mysqli("localhost", $db_user, $db_password, $db_name);

is considered open even if it is not currently being actively used. That is, a connection to the database created in PHP exists not only at the time of query execution, but until the code is executed:

$mysqli->close();

Open but unused connections have the “Sleep” status. You can view all currently existing connections using the SQL query:

SHOW PROCESSLIST;

See also: How to list all current connections to MySQL and MariaDB databases

In addition to the obvious reason, this situation with a lack of connections to the DBMS can be caused by disk problems – if there is no more space on the disk where the databases are located, the DBMS can return the same error.

The fact is that when there is not enough space on the disk, MySQL / MariaDB waits for INSERT or UPDATE queries to complete, which leads to an increase in their number and, eventually, to the exhaustion of the maximum number of connections.

3. How to temporarily increase the maximum number of connections to MySQL / MariaDB

Please note that the method shown in this section to increase the allowed maximum number of connections to databases is temporary – until the next restart of the DBMS server. For a permanent change, see the next section.

To change the maximum number of simultaneous connections, connect to the MySQL / MariaDB database from the command line using one of the following commands:

mariadb -u root -p
# OR
mysql -u root -p

First, you can view the current value of the allowed maximum number of connections by running the following query:

SHOW VARIABLES LIKE "max_connections";

Example output:

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

As you can see, by default, MariaDB sets the maximum number of connections to 151.

To increase the number of maximum connections to databases, run a query like this (instead of 300, specify the desired number of connections):

SET GLOBAL max_connections = 300;

As you can see from the screenshot, the value of the max_connections variable has indeed increased.

Also note that this method of increasing the maximum number of allowed connections does not require a MySQL / MariaDB server reboot (on the contrary, after a reboot, this value will be reset to the original level).

Remember that only superusers, such as root, can set global variable values. To find out which user is allowed to change the values ​​of global variables, run the following query:

SELECT user,host FROM mysql.user WHERE super_priv='Y';

4. How to permanently increase the maximum number of connections to MySQL / MariaDB

In order for the MySQL / MariaDB server to use the new values ​​of the allowed maximum number of simultaneous connections, you must add the appropriate setting to the configuration file. MySQL and MariaD server configuration files are usually /etc/my.cnf or /etc/my.cnf.d/server.cnf.

See also: How to determine the location and name of the MySQL (MariaDB) configuration file. How to find the group name for MySQL and MariaDB configuration files

Open any of these files, for example:

sudo gedit /etc/my.cnf
# OR
sudo gedit /etc/my.cnf.d/server.cnf

And add the following lines to this file:

[mysqld]
max_connections = 300

Note: if the [mysqld] section already exists, then you do not need to create it a second time – just add the desired settings to the existing section.

Note 2: replace the value 300 with the number you need.

These changes will take effect after restarting the DBMS server.

To restart the MySQL server, run the command:

sudo systemctl restart mysqld.service

To restart the MariaDB server, run the command:

sudo systemctl restart mariadb.service

5. What is the maximum number of connections that can be established for MySQL and MariaDB

The default value is 151 connections.

The maximum value of max_connections can be 100000.

And the minimum value is 1.

See also: https://dev.mysql.com/doc/refman/9.2/en/server-system-variables.html#sysvar_max_connections

Remember that each new active connection requires RAM. Therefore, when choosing the maximum number of simultaneous connections allowed, consider the computer's resources.

For example, on a virtual hosting, the max_connections value is set to 1000 – apparently, this is enough to serve thousands of sites.

Optionally, you can also set the value of the max_user_connections variable – it determines how many connections one user can use. By default, the value is 0, which means that each user can use any number of connections (of course, within the value of max_connections).

See also: https://dev.mysql.com/doc/refman/9.2/en/server-system-variables.html#sysvar_max_user_connections