HOW TO FIX User 'user_name' has exceeded the 'max_user_connections' resource (current value: 30)?

Question

function getDB() {            
    $server_name = "localhost";
    $dbname = "db_name";
    $user_name = "root";
    $password = "12345678";

    $dbConnection = new PDO("mysql:host=$server_name;dbname=$dbname", 
    $user_name, $password); 
    $dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $dbConnection->exec('SET NAMES utf8');

    return $dbConnection;
}

some function are there

function one(){
    $db = getDB();

    // after some mysql query

    $db = null;
}

function two(){ 
    $db = getDB(); 

    // after some mysql query

    $db = null;
}

there are 50 + functions call as per requirements

Answer 1

mysql DB, table user see max_user_connections

Answer 2

There are two possibilities:

• only open 1 connection with your application. Those errors are showing multiple opened connection. 1 process should only open 1 connection, and reuse it.

• alter mysql config file, specifically https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_user_connections

Answer 3

Everytime you call the function getDB that function will setup a new connection to the database. That adds up quickly. In 99,999% of the use-cases it is better to reuse that connection. One way to achieve that is by using a static variable.

function getDB() {   
    static $dbConnection = null; // $dbConnection will be 'remembered', only first time it will be null

    if ($dbConnection === null) {         
        $server_name = "localhost";
        $dbname = "db_name";
        $user_name = "root";
        $password = "12345678";

        $dbConnection = new PDO("mysql:host=$server_name;dbname=$dbname", 
        $user_name, $password); 
        $dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $dbConnection->exec('SET NAMES utf8');
    }

    return $dbConnection;
}