SQLSTATE[HY000] [1040] Too many connections

Question

Sometimes when I open my error log file I see this this error

[14-Jun-2014 19:09:55 UTC] PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [1040] Too many connections' in /home/root/products/db.php:2

Stack trace: /home/root/products/db.php(2): PDO->__construct('mysql:host=loca...', 'database', 'password')

/home/root/products/by-brand.php(2): include_once('/home/root/...')

{main} thrown in /home/root/products/db.php on line 2

[14-Jun-2014 19:15:11 UTC] PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1286 Unknown storage engine InnoDB in /home/root/products/detail.php:8

Stack trace: /home/root/products/name.php(8): PDO->prepare('select * from p...')

{main} thrown in /home/root/products/name.php on line 8

I am trying to solve this issue by closing every script by using $db=null; but it doesn't seem to work.

I contacted the service provider. They say everything works fine. You need to check errors at the end. I have just a few users.

Why do I somestimes get this error?

My hosting is unlimited: unlimited disk space. unlimited bandwidth but still I get this error. I don't know how can I solve this issue. I hope to make my website larger but these error make me confuses

My db.php file has this code. Can you check if this fine? Maybe this is causing the problem.

db.php

$db = new PDO('mysql:host=localhost;dbname=mobiles;charset=utf8', 'root', '**somePassword**');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Solution 1

There is nothing wrong with your connection code.

On a shared hosting, "unlimited" means other users are unrestricted with their resource using. Means they can eat up whole pool, while your consumption remains modest.

As you've been told in comments, don't use this host.

Solution 2

This is a limit of database's configuration. If you have permission to edit the configuration file of your database service, you can change max_connections value.

Take a look: http://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html

You can try running this SQL query (take care about this value!)

SET GLOBAL max_connections = 512;

and this to get current value of all variables:

SHOW VARIABLES;

or (for specific variable):

SHOW GLOBAL VARIABLES LIKE '%max_connections%'

By the way, try to set PDO object to null when you don't need it. This will close connection to your database and PHP does not wait for script to finish to close active connections. (Oh, you did it, sorry, I will not edit it to delete. Get it as a note)

Solution 3

In situations of low memory or few resources, first determine the root cause.

Does this problem appear in your development system? Or does it appear in your production system?

If seen in the dev system, it is very likely a severe technical error. If seen in the production system, the problem might be due to system limits (not necessarily system overload).

Most server applications allocate a fixed amount of RAM for a fixed number of incoming connections (due to technical advantages).

As most other network-server applications, MySQL allocates a fixed number of connections too. Each time a client connects, a slot in the connection pool gets assigned. Each time a client disconnects, a slot gets marked as free.

While you can enlarge the pre-allocated connection pool, do this in small steps. Your system administration (hopefully) has chosen the connection pool's size wisely.

If a connection pool gets too large, your application - or the whole server - might get non-responsive: Each computing environment can only process a certain number of requests per unit of time. The number depends of the average cost per request / the distribution of cost over requests.

That said, collect system load and connection amount data over time. 
Figure out, at which situations the error message gets written.

Once you know, you might change MySQL's max_connections setting.

PHP and MySQL

Assigning $db = null is fine as explained here.

Regarding your problem scenario, disk-space and bandwidth most likely aren't relevant.

In general, you might prepare a class conforming to the singleton pattern, which provides the one and only interface talking to your database-server. Other patterns may be applicable too.

All other scripts should require_once this classe's file and do all DB-related stuff using this class.

Correctly implemented and called, the singleton class uses a single MySQL connection per request processed.

The correct place to set the amount of connections is my.cnf, often found in the /etc/ directory. There, you might set a value like so:

[mysqld]
set-variable=max_connections=250

In case of trouble, SHOW processlist might be of help. It provides details of all active connections:

In a top-level catch block that handles PDOException, you may save the outcome of SHOW processlist in a log-file for later analysis. But ensure, that the amount of such log entries is limited per unit of time to not flood your log-file.

Note, that MySQL permits one more connection than max_connections for a client connection of a user having SUPER privileges.

You might use such a user to gather the process list information. Or - in case of a PDOException - you might delay your application for a short period of time and try to connect again and then save the processlist.

Unlimited disk space

Something like unlimited disk space doesn't exist: Due to the simple fact, that the amount of HDDs and SSDs ever produced is a finite number ;-)

While your provider doesn't set a limit, technical limits exist - which quite likely aren't important in your case.

Practically, there's another limit: Write and run an application which allocates more and more disk space. Within a short amount of time, your provider will turn down your contract...

Comments

[Blank EDjok]

Make sure you do not have multiple object instances of PDO within your application, you only need 1.

[developer]

@BlankEDjok you mean i don't need to use same config file with all scripts? or what? explain it because am new in pdo::

[Blank EDjok]

Say you have your db connection in 1 file say: dbconfig.php which you include once and only once on any page that requires a db connection.

[Niet the Dark Absol]

"Unlimited disk space" is a lie. Don't use hosts that lie.

[Blank EDjok]

@Niet I do have Unlimited Disk Space on my godaddy server though, Even though disk space itself does have a finite amount i think they are always expanding their storage capacity.

[Lightness Races in Orbit]

Your host may not be limiting your disk usage but plenty of other factors are.