[SOLVED] mysql_connect(): Too many connections

[patd]

frineds I have built a fully dynamic site with PHP & MySQL.

I have hosted this site on a Linux Corporate Server.

Now After every week or some time after only 2 or 3 days the site is down by showing this error

Warning: mysql_connect(): Too many connections

As I reported this problem to the server admins they are tell me that this is because there is too many open sql connection, I should optimie my database.

I already use mysql_close(); after each open connection.

Now I am very confused why this is happening, Should I change the server, will this continue to hapen after i chnage the server.

how can I fix this problem.

[wickning1]

This is the kind of problem that is difficult to troubleshoot from a distance. Usually it happens when you have badly written queries on your site, but it can also happen if PHP threads crash or are hanging open.

MySQL's maximum number of connections is 100, so if you have a lot of users and a few slow queries it's not unusual to run into this problem. You can alleviate the problem by increasing the maximum number of connections in your MySQL configuration file, identifying your slowest queries and fixing them, or decreasing the default timeout for MySQL connections in your php.ini file.

You should also make sure that your PHP application opens one and only one database connection per page load.

[patd]

thanks for ur reply, but still i am not quite sure about how 2 solve the problem.

as i dont have access 2 the php.ini I can't change the config.

what else can I do form My end befour i chage the server.

[wickning1]

On your end you can make sure your queries are running quickly. Look through your pages, find the queries you're making, and run them through phpMyAdmin, it will tell you how long the query took. Anything over a half a second probably needs immediate attention. You can also run queries with EXPLAIN in front. That will tell you how many rows are involved, whether it's copying to a temp table, etc.

You can also make sure that your application is only opening one PHP connection at a time. This is just program logic.. does it run mysql_connect() twice during a page load?

[husyn]

Dear fellows
I have install an invision powerboard 2.1.5 forum, and it some times generate the same error of too many connections. I have no idea with using PHP and mysql, it was working fine in start but now generate this error after a day or 2 and forum again start working fine after 4-5 hours.

I have only 29 registered users so far and highest user online is only 6, i have set options like guest can't view posts before registering, no anonymous member can view post, while registration u have to enter security code.

I have seen many webmaster facing the same problem, so it may not be becuase of server, there should be some adjustement in coding i think.

I will be thankful to you, if some1 come up with an appropriate solution.
my forum link is www.zonepk.com/forum

Thanks in advance

[patd]

thanks for ur help.

after checking the code I fix the problem. I found that lots of php connections are left open.

but I was wondering that if a user click the stop button befour the connection close itself ???.

[hadoob024]

Is there a way to see if a connection is open? Like after I open a connection, if I encounter an error, before exiting out of the script, I send myself an email with the error message and append any error message to my error_log. But I also wanted to check and see if there're any open db connections, and if so, close them out before exiting. Is this possible? I'm looking at the mysql functions right now, and can't tell if any of them can help me with this.

[wickning1]

you can use the MySQL command "SHOW processlist" and then kill threads, but I wouldn't recommend it. Connections do close when PHP exits, unless you are using mysql_pconnect() or whatever the persistent connection function is. Also, PHP will not process for more than 30 seconds (or the setting in php.ini) before it exits as well. It is highly unlikely to have hanging threads these days, unless you specifically instruct them to hang.

If you are getting "too many connections" errors, the most likely possibility is that you are opening more than one connection per page load, or some of your queries take too long, and so a few page loads at once throw you over the limit.

[patd]

Thanks friends, after fixing the code, its been one year and after receiving hevy traffic I have not faced any prolems yet.