Solution for Mysql "too many database connections" error?

Question

Following are my system specifications
- Apache 2.x
- PHP 5.2.x
- Mysql 5.x

We are not using persistent mysql connections. We have a lot of unclosed connections in our script and also have a few unnecessary mysql_connects. Does that matter? What could be a permanent fix to the problem.
- Increasing the max number of allowed connections?
- If it is on a shared server, checking the max number of connections per user and reducing that if it is more? But won't that affect users of my site also because all will be using the same db connection params?
-

Also please answer this questions of mine also :-
1. I know that php automatically closes all the existing connections at the end of a script execution and mysql terminates all idle connections after some timeout. If php itself closes then Mysql need not close in this case or some connections survive php termination ?

Answer 1

It is a bad habit to leave a connection opened!

In each call, you must have:

$link = dbconnect() // the function that connects to the database

mysql_free_result($res) // to FREE the ressource you are using in a "select" type of query

and mysql_close($link);



Having many short connections will not decrease your efficiency, while too many opened at once WILL.





Not freeing the result will consume a fair amount of memory in your system, so free them once you are done with it. Too many "results" (ressources) left over WILL clutter the DB connections.



The only permanent solution to your problem is a review of the code and addition of these "mysql_close" (don't just hope that the system or time-out will do the job for you: YOU are in charge, NOT Php!

Answer 2

Did you relatively create the database you attempt to attach with? If the database does no longer yet exist you isn't waiting to attach with it (I unexpectedly met this myself different day mutually as i replaced into installation phpBB and MySQL, I forgot to create the database ).

Answer 3

1. Tune the parameters in the my.cfg file, to increase the max number of connections.

2. Implement the connection pooling in your application , so that application can use the existing database connection if available instead of creating a new connection.