max connections to the database

Preed

Posted on 29 Jan 2010, 16:19:12

Access: Member
Total Posts: 10
Joined: 2010-01-28

Anyone ever encountered a max users connected error to MySQL database? Every so often I get this error:

Warning: mysqli_connect() [function.mysqli-connect]: (42000/1203): User mna_mnanews already has more than 'max_user_connections' active connections in /home/mna/public_html/news/mysqliclass.php on line 56

Warning: mysqli_select_db() expects parameter 1 to be mysqli, boolean given in /home/mna/public_html/news/mysqliclass.php on line 57

Warning: mysqli_prepare() expects parameter 1 to be mysqli, boolean given in /home/mna/public_html/news/mysqliclass.php on line 104
Unable to prepare: SELECT COUNT(uid) AS ratingcount FROM news30_rating WHERE storyid = ?
Arguments: 26

#1760

Chris

Posted on 29 Jan 2010, 16:42:18

Access: Admin
Total Posts: 1395
Joined: 2006-05-19

You could try setting the max connection variable in mysql to a higher value

http://www.electrictoolbox.com/update-max-connections-mysql/

You could also edit the mysqliclass.php file, around line 60~ you'll see an empty function

Code:


    public function close_db_conn(){

    }

Change that to

Code:


    public function close_db_conn(){
        mysqli_close(self::establish_db_conn());
    }

This way each time the script finishes a query it'll close the connection.

#1761

Preed

Posted on 29 Jan 2010, 16:45:44

Access: Member
Total Posts: 10
Joined: 2010-01-28

Thanks for your response. I'll try one at a time and see what works best. :smile2:

#1762

Preed

Posted on 29 Jan 2010, 18:04:47

Access: Member
Total Posts: 10
Joined: 2010-01-28

I don't have access to the max connections in SQL (it's set to the default of 100), and really it shouldn't be a problem because we don't have many users.

I did put in the explicit close. It still happens from time to time. I'm thinking it could be because I'm making changes via the admin and then in a different browser refreshing the site to see the updates. Could be the connections from the admin haven't fully closed when I refresh the page on the site. My understanding is that database connections will usually close once a PHP script finishes executing even without explicitly closing them, but using both the admin and the site at the same time, I can see how maybe doing things in the admin could involve open connections that haven't closed quickly.

Sound reasonable?

I also get it from time to time while using the admin. Just got it while using the admin. I wait a few seconds then refresh and all is fine.

Here was the error while using the admin:

Warning: mysqli_connect() [function.mysqli-connect]: (42000/1203): User mna_mnanews already has more than 'max_user_connections' active connections in /home/mna/public_html/news/mysqliclass.php on line 56

Warning: mysqli_select_db() expects parameter 1 to be mysqli, boolean given in /home/mna/public_html/news/mysqliclass.php on line 57

Warning: mysqli_prepare() expects parameter 1 to be mysqli, boolean given in /home/mna/public_html/news/mysqliclass.php on line 69

Warning: mysqli_connect() [function.mysqli-connect]: (42000/1203): User mna_mnanews already has more than 'max_user_connections' active connections in /home/mna/public_html/news/mysqliclass.php on line 56

Warning: mysqli_select_db() expects parameter 1 to be mysqli, boolean given in /home/mna/public_html/news/mysqliclass.php on line 57

Warning: mysqli_close() expects parameter 1 to be mysqli, boolean given in /home/mna/public_html/news/mysqliclass.php on line 61
Unable to prepare: UPDATE news30_story SET commentcount = ? WHERE id = ?
Arguments: 0,15

#1763

Last edited by Preed at 2010-01-29 18:06:45 Reason:

Chris

Posted on 29 Jan 2010, 20:46:36

Access: Admin
Total Posts: 1395
Joined: 2006-05-19

What you said before is correct about the connection automatically closing at the end of the script execution, so making the above code changes shouldn't really be necessary. This is why it's strange that it's giving you this error message. The only thing I can think is what I mentioned in my previous message, the limit set by your MySQL server needs to be increased.

#1764

Preed

Posted on 30 Jan 2010, 21:43:51

Access: Member
Total Posts: 10
Joined: 2010-01-28

I spoke with length to the tech support at my web host. The account I have for hosting includes unlimited databases, but not very many connections. I was told that anything above 25 simultaneous connections would generate a warning message. They were unwilling to increase the number of max simultaneous connections without upgrading to a virtual dedicated server or dedicated server. Well, not going to do that, it's way too expensive to have a dedicated server.

I've gone back to Cute News for now. Perhaps later I'll find a web host that will give more simultaneous connections.

They did say they tested repeatedly and while they did see from time to time that my home page reached 25 simultaneous connections they closed out fairly quickly.

I have the feeling they didn't try very hard. I also use the same database to handle the login system and an event calendar. I can see how combining the news, the admin, the calendar and the login system I could potentially go over 25 pretty easy, especially since it's a shared server and shared database server. At times it may not close connections as quickly as I'd need them.

No big deal.

I will keep N-13 and maybe use it for my own blog or something like that.

Keep up the good work. Try and make sure your queries are as optimized as possible.

#1777

Chris

Posted on 30 Jan 2010, 22:00:12

Access: Admin
Total Posts: 1395
Joined: 2006-05-19

I don't understand how the script can go over your max connection limit. It should, by default only have 1 active connection at any given time, even with multiple users. For example, if you had 10 visitors on your site, they should all be using the 1 connection as they're using the same connection php has created. I'm almost positive that it's not the script that's causing this, rather something to do with your server config. On my local development server I ran the script and looked the amount of active connections

Code:


mysql> show processlist;
+-----+------+-----------------+------+---------+------+-------+------------------+
| Id  | User | Host            | db   | Command | Time | State | Info             |
+-----+------+-----------------+------+---------+------+-------+------------------+
| 362 | root | localhost:50814 | NULL | Query   |    0 | NULL  | show processlist |
+-----+------+-----------------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

Meaning there is only 1 current connection.

I did the same thing on this server network-13.com is on, which currently has multiple sites hosted on it all using the mysql server, it too, only has 1 active connection

Code:


mysql> show processlist;
+--------+------+-----------+------+---------+------+-------+------------------+
| Id     | User | Host      | db   | Command | Time | State | Info             |
+--------+------+-----------+------+---------+------+-------+------------------+
| 984141 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+--------+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

#1778

Angel

Posted on 05 Feb 2011, 17:49:46

Access: Member
Total Posts: 9
Joined: 2010-02-01

There is solution for this problem?, it spends the same thing, graces to my me.

#3175

Chris

Posted on 12 Feb 2011, 09:50:33

Access: Admin
Total Posts: 1395
Joined: 2006-05-19

No solution really other than to try a different host to see if the problem persists.

#3187