I'm still confused on how to solve 'max_user_connections' problem

Hosting Support
1

Username (e.g. epiz_XXX) or Website URL

Username: epiz_33045611
The website url: http://gladdigital.rf.gd/

Error Message

SQLSTATE[HY000] [1203] User epiz_33045611 already has more than ‘max_user_connections’ active connections

Other Information

Hi, everyone. I am a total newbie when it comes to hosting. I have built a php pdo with mvc model website. The first problem I ran into is redirecting the user to a subfolder called public but I have solved it with a solution from this forum. After that, I added the database through the vpanel, I ran into this error.

I have read this post about max_user_connection.

  • If you’ve written the code yourself, make sure that you’re only creating one database connection for every request. I’ve seen scripts which create a new database connection for every database query, and never close connections, which makes it easy to have a lot of connections open at the same time.
  • Especially if you have slow running code, it might be a good idea to manually close connections and reopen connections after your queries. This should also help reduce the number of active connections.

I have coded the php scripts myself and I’m not sure if I have written a good code or not. After googling the error message, I found a stackoverflow post that says I need to set the PDO::ATTR_PERSISTENT to false. In my code, I have the persistent connection set to true because I was following a tutorial on youtube. When I change it to false, I get ERR_TOO_MANY_REDIRECTS error message, so I changed it back to true. I still don’t what to do. What do I need to change in my code?

I have the database connection in Database.php file. Here is the construct method.

class Database
{
    private $host = DB_HOST;
    private $db_name = DB_NAME;
    private $user = DB_USER;
    private $pass = DB_PASS;

    private $dbh;
    private $stmt;

    public function __construct()
    {
        $dsn = "mysql:host=" . $this->host . ";dbname=" . $this->db_name;
        $option = [
            PDO::ATTR_PERSISTENT => true,
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        ];

        try {
            $this->dbh = new PDO($dsn, $this->user, $this->pass, $option);
        } catch (PDOException $e) {
            die($e->getMessage());
        }
    }
    ...

And in the model files, I have this constructor method:

class UsersModel
{
    private $table = "users";
    private $db;

    public function __construct()
    {
        $this->db = new Database();
    }

    public function getUserData($username)
        ...
    {

I assume this is not a good way to make connections because this produces so many connections. I also don’t close the connection (I don’t know if I should close the connection after every query or not).

So, if someone can help me and tell me what I need to do or what part in my code I need to fix, I would appreciate it so much. Thank you so much in advance.

1 Like
2

Thank you for the elaborate post! All the information you’ve provided really helps paint a clear picture for me as to what’s going on. Also, you seem to have a pretty good understanding of what’s going on, which helps a lot.

I think I agree with the StackOverflow post you found. I didn’t know about the persistent option before, but it seems like it keeps the database connection alive even after the request ends.

If I understand correctly how this option is implemented and works with Apache (connections are held by Apache worker processes), I don’t think this option works well at all on our hosting, and may result in a large number of lingering database connections that never get used (but will still use your max_user_connections allocation).

So I think you should definitely turn the persistent connection option off. The ERR_TOO_MANY_REDIRECTS is odd, but it may be unrelated and I think we’ll be able to figure it out.

Yes, I agree that this is not a particularly efficient way to create handle connections, because every instance of UserModel will establish a new database connection.

There are many different ways in which you can try to fix this problem. Using a Service Container is fancy but hard to implement. A simpler approach is to use the Singleton Pattern. Some consider it to be an antipattern, but I think it’s an appropriate amount of complexity for your use case.

You can create a Singleton like this:

If you turn your Database class into a singleton, you could just replace the $this->db = new Database() with $this->db = Database::getInstance(), and easily reuse the same database connection for all models.

7 Likes
3

Hi, thank you very much for the answer. I will try your to change my code to use Singleton Pattern as soon as I can.

1 Like
4

Hello, I have tried your solution and it works. Thank you very much. :pray: I was still getting the ERR_TOO_MANY_REDIRECTS but I have found the problem and solved it.

1 Like
5

It will be good if you can elaborate on how you solve this

This may benefit subsequent IF users with this issue in the future

2 Likes
6

Sure. The reason I was getting that error is because of the way I set up my routing.

Here is my App.php with path src/app/core/App.php that controls the routing.

class App
{
    protected $controller = "Home";
    protected $method = "index";
    protected $params = [];

    public function __construct()
    {
        // public/{controller}/{method}/{params1}/{params2}/..
        $url = $this->parseURL();

        if ($url == null) {
            $url = [$this->controller];
        }

        // controller
        if (file_exists("../src/app/controllers/" . $url[0] . ".php")) {
            $this->controller = $url[0];
            unset($url[0]);
        }

        require_once "../src/app/controllers/" . $this->controller . ".php";
        $this->controller = new $this->controller();

        // method
        if (isset($url[1])) {
            if (method_exists($this->controller, $url[1])) {
                $this->method = $url[1];
                unset($url[1]);
            }
        }

        // params
        if (!empty($url)) {
            $this->params = array_values($url);
        }

        // run the controller & method & send params if it exists
        call_user_func_array([$this->controller, $this->method], $url);
    }

    public function parseURL()
    {
        if (isset($_GET["url"])) {
            $url = rtrim($_GET["url"], "/");
            $url = filter_var($url, FILTER_SANITIZE_URL);
            $url = explode("/", $url);
            return $url;
        }
    }
}

The constructor will call the parseURL() method. The parseURL() method will take the url and put anything after the the word public/ and put it inside an array called $url. After that, take the value of the first index from $url array and check whether a file with the same name as $url[0] exsits inside the /src/app/controllers/ directory. The default controller is the Home controller and the default method is index. If the file doesn’t exist or there is no character after the word public/ in the url, then it will use the default controller and method.

So now, if the url is http:{sitename}/public/home → this should take me to the home page because it uses the Home controller that calls the Home view.
If the url is http:{sitename}/public/login → this should take me to the login page because it uses the Login controller that calls the Login view.

I also check if the user has logged in or not with php session. If the user is trying to access a page before logging in, then I would redirect the user with header() function to the login page.

The problem is the file_exists() function always return false, even though I typed http:{sitename}/public/login. This, in addition with the header() function that keeps redirecting the user to login page creates an infinite loop that redirect to the current url. (Because the file_exists() return false, so the controller that’s used is actually the Home controller not the Login controller. This means the I am redirecting the user from the home page to the home page). That’s why I get the ERR_TOO_MANY_REDIRECTS error message.

I just learned from this stackoverflow post, that I need to use the $_SERVER["DOCUMENT_ROOT"].

So I changed the the code to this:

if (file_exists($_SERVER["DOCUMENT_ROOT"] ."/src/app/controllers/" . ucfirst($url[0]) . ".php")) {
    ...
}

I also use ucfirst so it matches the name of the controller file, because the case sensitivity depends on the filesystem of the OS (I also learned from this stackoverflow post).

After that, the ERR_TOO_MANY_REDIRECTS problem is solved. (Sorry for any grammar mistake, English is not my first language)

7 Likes
7

Thank you for this wonderful contribution!!

3 Likes
8

No problem, anytime

1 Like
9

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.