MySQL Database Connection Management In PDO

Question

I'm very new to PHP/MySQL and I'm learning things as I go. One of the newer things I've learned is that there is a maximum number of connections that can be made to a database for a given username. When I first started building my website on Wordpress, I was using the old mysql_query() commands. I never had to make a connection to the MySQL database because Wordpress keeps an active connection just by being logged in on the website. When I decided to switch all my MySQL queries over to the PDO extension, I could no longer take advantage of Wordpress' active connection, and had to start my own database connections. I did this in a separate PHP config file that I included in every page that ran a script. The PHP code looks like this:

try {
    $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
}
catch(PDOException $e) {  
    echo "Select DB Error: " .$e->getMessage(). "</br>";
}

Unfortunately now, I constantly get the following error:

" SQLSTATE[42000] [1203] User already has more than 'max_user_connections' active connections"

Based on some online research, I tried various different methods to fix this. First, I tried to set the database connection to null at the end of each script (though this should be PHP PDO's default?). Then I tried to set each statement handle to null after each query of the database (this was a hopeless endeavor). And finally, I tried using a persistent connection by changing the following line in my config file:

$DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass, array(PDO::ATTR_PERSISTENT => true));

None of this seems to work and I still get the same exact error. The site, when ready, needs to be able to handle 100-200 people per day entering a myriad of different data. Even if I can figure out how to take advantage of Wordpress' active connection using PDO, that would be a good start. I'd appreciate any help I can get.

UPDATE:

I'm running SHOW FULL PROCESSLIST as a MySQL query in one of my codes and I'm getting very strange behavior. This is the first part of my script (not including the SHOW FULL PROCESSLIST query):

<?php

include 'config.php';

if(isset($_POST['submit']))
{
//Get Current User Login
global $current_user;
$current_user = wp_get_current_user();
$ulog = $current_user->user_login;
$tablename_cc = "cc_".$ulog;
$tablename_db = "db_".$ulog;
$tablename_misc = "misc_".$ulog;
$tablename_cash = "cash_".$ulog;

try {
    $DBH->exec("CREATE TABLE IF NOT EXISTS " .$tablename_cc. " (ID bigint(100) NOT NULL AUTO_INCREMENT, PRIMARY KEY(ID), accnt TEXT(20), cc_num TEXT(4), cc_amnt DECIMAL(8,2), cc_app TEXT(20), cc_date VARCHAR(10), cc_time VARCHAR(10))");            
    $DBH->exec("CREATE TABLE IF NOT EXISTS " .$tablename_db. " (ID bigint(100) NOT NULL AUTO_INCREMENT, PRIMARY KEY(ID), accnt TEXT(20), db_num TEXT(20), db_amnt DECIMAL(8,2), db_date VARCHAR(10), db_time VARCHAR(10))");        
    $DBH->exec("CREATE TABLE IF NOT EXISTS " .$tablename_misc. " (ID bigint(100) NOT NULL AUTO_INCREMENT, PRIMARY KEY(ID), accnt TEXT(20), misc_item TEXT(1000), misc_amnt DECIMAL(8,2), misc_date VARCHAR(10), misc_time VARCHAR(10))");       
    $DBH->exec("CREATE TABLE IF NOT EXISTS " .$tablename_cash. " (ID bigint(100) NOT NULL AUTO_INCREMENT, PRIMARY KEY(ID), accnt TEXT(20), cash_amnt DECIMAL(8,2), cash_time VARCHAR(10))");
}
catch(PDOException $e) {  
    echo "Create Tables Error: " .$e->getMessage();
}
} ?>

If I try to run the SHOW PROCESS LIST query outside of the if statement, I get a result stating that I have one active connection; however, if I try to run the SHOW FULL PROCESSLIST query inside the if statement, I get the error that I have exceeded my number of connections. Based on this result, I figured perhaps my include line on the very top of the page may be causing an issue when the user submits their form (essentially trying to connect to the database twice), so I moved it inside the if statement, but that made no difference either. I'm at a loss as to why this happening.

UPDATE/ANSWER (still some questions):

I figured out what my issue was. Upon completion of one of my scripts, there is a javascript command to pop up a window where another script will print out an invoice for them. That second script also tries to make a connection to the database using the config file. The form the user is working with is dynamic (they can put in as little or as many data sets as they wish), so when the user inserts a small number of data, the PDO queries are performed very fast and there is no conflict between the number of connections; however, if the user inputs a lot of data then it takes a significant more amount of time, and the maximum number of connections is reached by the server. The issue really resides in a combination of poor server environment and inefficient queries on my part (still learning PHP). The only solution I can think of at this point is to put a longer sleep on my second script (already using a 3 second sleep), to let the other script catch up, but at some point, this will just take too long. I'm not sure if you guys have any better suggestions?

Answer 1

If you have another tool that can give you a connection to the database you might want to see how many connections you're actually making. In a pinch, phpmyadmin can do this.

Usually you can see all your active queries and connections with:

SHOW PROCESSLIST

If you have made a mistake in your PDO connection routine you may be making a connection for each query you're trying to run rather than one for the duration of the request. You may also need to check that each request is not creating a large pool of connections for you that aren't closed properly. You probably need to experiment with connection options.

Answer 2

Using include_once('config.php') or require_once('config.php') in the files instead of include 'config.php' may be your solution to avoid duplicate database connections.

Answer 3

What you need is called a PDO "singleton" class. Here is an example:

http://tonylandis.com/php/php5-pdo-singleton-class/

It is not the best example because the constructor takes too many arguments. Just hardcode the $dsn,.., args into the constructor ie. _construct(){...} and you can use new sdb() wherever you would have used PDO(). No need for any include or require statements except once for the class.

The $pdo object is stored as a static variable inside the class and so is always accessible.