How I can close the connection between PHP & Mysql immediately after accessing

Question

May wabsite has faced max_user_connections error After contacting the website hosting provider they said I have to close the connection immediately after accessing the database using mysql_close()

My question is that how I can close the connection if my php file is :

*/

if (!defined('QA_VERSION')) { // don't allow this page to be requested directly from browser
    header('Location: ../');
    exit;
}


/**
 * Indicates to the Q2A database layer that database connections are permitted fro this point forwards
 * (before this point, some plugins may not have had a chance to override some database access functions).
 */
function qa_db_allow_connect()
{
    if (qa_to_override(__FUNCTION__)) { $args=func_get_args(); return qa_call_override(__FUNCTION__, $args); }

    global $qa_db_allow_connect;

    $qa_db_allow_connect=true;
}


/**
 * Connect to the Q2A database, select the right database, optionally install the $failhandler (and call it if necessary).
 * Uses mysqli as of Q2A 1.7.
 */
function qa_db_connect($failhandler=null)
{
    if (qa_to_override(__FUNCTION__)) { $args=func_get_args(); return qa_call_override(__FUNCTION__, $args); }

    global $qa_db_connection, $qa_db_fail_handler, $qa_db_allow_connect;

    if (!$qa_db_allow_connect)
        qa_fatal_error('It appears that a plugin is trying to access the database, but this is not allowed until Q2A initialization is complete.');

    if (isset($failhandler))
        $qa_db_fail_handler = $failhandler; // set this even if connection already opened

    if ($qa_db_connection instanceof mysqli)
        return;

    // in mysqli we connect and select database in constructor
    if (QA_PERSISTENT_CONN_DB)
        $db = new mysqli('p:'.QA_FINAL_MYSQL_HOSTNAME, QA_FINAL_MYSQL_USERNAME, QA_FINAL_MYSQL_PASSWORD, QA_FINAL_MYSQL_DATABASE);
    else
        $db = new mysqli(QA_FINAL_MYSQL_HOSTNAME, QA_FINAL_MYSQL_USERNAME, QA_FINAL_MYSQL_PASSWORD, QA_FINAL_MYSQL_DATABASE);

    // must use procedural `mysqli_connect_error` here prior to 5.2.9
    $conn_error = mysqli_connect_error();
    if ($conn_error)
        qa_db_fail_error('connect', $db->connect_errno, $conn_error);

    // From Q2A 1.5, we explicitly set the character encoding of the MySQL connection, instead of using lots of "SELECT BINARY col"-style queries.
    // Testing showed that overhead is minimal, so this seems worth trading off against the benefit of more straightforward queries, especially
    // for plugin developers.
    if (!$db->set_charset('utf8'))
        qa_db_fail_error('set_charset', $db->errno, $db->error);

    qa_report_process_stage('db_connected');

    $qa_db_connection=$db;
}


/**
 * If a DB error occurs, call the installed fail handler (if any) otherwise report error and exit immediately.
 */
function qa_db_fail_error($type, $errno=null, $error=null, $query=null)
{
    if (qa_to_override(__FUNCTION__)) { $args=func_get_args(); return qa_call_override(__FUNCTION__, $args); }

    global $qa_db_fail_handler;

    @error_log('PHP Question2Answer MySQL '.$type.' error '.$errno.': '.$error.(isset($query) ? (' - Query: '.$query) : ''));

    if (function_exists($qa_db_fail_handler))
        $qa_db_fail_handler($type, $errno, $error, $query);

    else {
        echo '<hr><font color="red">Database '.htmlspecialchars($type.' error '.$errno).'<p>'.nl2br(htmlspecialchars($error."\n\n".$query));
        qa_exit('error');
    }
}


/**
 * Return the current connection to the Q2A database, connecting if necessary and $connect is true.
 */
function qa_db_connection($connect=true)
{
    if (qa_to_override(__FUNCTION__)) { $args=func_get_args(); return qa_call_override(__FUNCTION__, $args); }

    global $qa_db_connection;

    if ($connect && !($qa_db_connection instanceof mysqli)) {
        qa_db_connect();

        if (!($qa_db_connection instanceof mysqli))
            qa_fatal_error('Failed to connect to database');
    }

    return $qa_db_connection;
}


/**
 * Disconnect from the Q2A database.
 */
function qa_db_disconnect()
{
    if (qa_to_override(__FUNCTION__)) { $args=func_get_args(); return qa_call_override(__FUNCTION__, $args); }

    global $qa_db_connection;

    if ($qa_db_connection instanceof mysqli) {
        qa_report_process_stage('db_disconnect');

        if (!QA_PERSISTENT_CONN_DB) {
            if (!$qa_db_connection->close())
                qa_fatal_error('Database disconnect failed');
        }

        $qa_db_connection=null;
    }
}


/**
 * Run the raw $query, call the global failure handler if necessary, otherwise return the result resource.
 * If appropriate, also track the resources used by database queries, and the queries themselves, for performance debugging.
 */
function qa_db_query_raw($query)
{
    if (qa_to_override(__FUNCTION__)) { $args=func_get_args(); return qa_call_override(__FUNCTION__, $args); }

    if (QA_DEBUG_PERFORMANCE) {
        global $qa_usage;

        // time the query
        $oldtime = array_sum(explode(' ', microtime()));
        $result = qa_db_query_execute($query);
        $usedtime = array_sum(explode(' ', microtime())) - $oldtime;

        // fetch counts
        $gotrows = $gotcolumns = null;
        if ($result instanceof mysqli_result) {
            $gotrows = $result->num_rows;
            $gotcolumns = $result->field_count;
        }

        $qa_usage->logDatabaseQuery($query, $usedtime, $gotrows, $gotcolumns);
    }
    else
        $result = qa_db_query_execute($query);

//  @error_log('Question2Answer MySQL query: '.$query);

    if ($result === false) {
        $db = qa_db_connection();
        qa_db_fail_error('query', $db->errno, $db->error, $query);
    }

    return $result;
}


/**
 * Lower-level function to execute a query, which automatically retries if there is a MySQL deadlock error.
 */
function qa_db_query_execute($query)
{
    if (qa_to_override(__FUNCTION__)) { $args=func_get_args(); return qa_call_override(__FUNCTION__, $args); }

    $db = qa_db_connection();

    for ($attempt = 0; $attempt < 100; $attempt++) {
        $result = $db->query($query);

        if ($result === false && $db->errno == 1213)
            usleep(10000); // deal with InnoDB deadlock errors by waiting 0.01s then retrying
        else
            break;
    }

    return $result;
}


/**
 * Return $string escaped for use in queries to the Q2A database (to which a connection must have been made).
 */
function qa_db_escape_string($string)
{
    if (qa_to_override(__FUNCTION__)) { $args=func_get_args(); return qa_call_override(__FUNCTION__, $args); }

    $db = qa_db_connection();
    return $db->real_escape_string($string);
}


/**
 * Return $argument escaped for MySQL. Add quotes around it if $alwaysquote is true or it's not numeric.
 * If $argument is an array, return a comma-separated list of escaped elements, with or without $arraybrackets.
 */
function qa_db_argument_to_mysql($argument, $alwaysquote, $arraybrackets=false)
{
    if (is_array($argument)) {
        $parts=array();

        foreach ($argument as $subargument)
            $parts[] = qa_db_argument_to_mysql($subargument, $alwaysquote, true);

        if ($arraybrackets)
            $result = '('.implode(',', $parts).')';
        else
            $result = implode(',', $parts);

    }
    elseif (isset($argument)) {
        if ($alwaysquote || !is_numeric($argument))
            $result = "'".qa_db_escape_string($argument)."'";
        else
            $result = qa_db_escape_string($argument);
    }
    else
        $result = 'NULL';

    return $result;
}


/**
 * Return the full name (with prefix) of database table $rawname, usually if it used after a ^ symbol.
 */
function qa_db_add_table_prefix($rawname)
{
    if (qa_to_override(__FUNCTION__)) { $args=func_get_args(); return qa_call_override(__FUNCTION__, $args); }

    $prefix = QA_MYSQL_TABLE_PREFIX;

    if (defined('QA_MYSQL_USERS_PREFIX')) {
        switch (strtolower($rawname)) {
            case 'users':
            case 'userlogins':
            case 'userprofile':
            case 'userfields':
            case 'messages':
            case 'cookies':
            case 'blobs':
            case 'cache':
            case 'userlogins_ibfk_1': // also special cases for constraint names
            case 'userprofile_ibfk_1':
                $prefix = QA_MYSQL_USERS_PREFIX;
                break;
        }
    }

    return $prefix.$rawname;
}


/**
 * Callback function to add table prefixes, as used in qa_db_apply_sub().
 */
function qa_db_prefix_callback($matches)
{
    return qa_db_add_table_prefix($matches[1]);
}


/**
 * Substitute ^, $ and # symbols in $query. ^ symbols are replaced with the table prefix set in qa-config.php.
 * $ and # symbols are replaced in order by the corresponding element in $arguments (if the element is an array,
 * it is converted recursively into comma-separated list). Each element in $arguments is escaped.
 * $ is replaced by the argument in quotes (even if it's a number), # only adds quotes if the argument is non-numeric.
 * It's important to use $ when matching a textual column since MySQL won't use indexes to compare text against numbers.
 */
function qa_db_apply_sub($query, $arguments)
{
    $query = preg_replace_callback('/\^([A-Za-z_0-9]+)/', 'qa_db_prefix_callback', $query);

    if (!is_array($arguments))
        return $query;

    $countargs = count($arguments);
    $offset = 0;

    for ($argument = 0; $argument < $countargs; $argument++) {
        $stringpos = strpos($query, '$', $offset);
        $numberpos = strpos($query, '#', $offset);

        if ($stringpos === false || ($numberpos !== false && $numberpos < $stringpos)) {
            $alwaysquote = false;
            $position = $numberpos;
        }
        else {
            $alwaysquote = true;
            $position = $stringpos;
        }

        if (!is_numeric($position))
            qa_fatal_error('Insufficient parameters in query: '.$query);

        $value = qa_db_argument_to_mysql($arguments[$argument], $alwaysquote);
        $query = substr_replace($query, $value, $position, 1);
        $offset = $position + strlen($value); // allows inserting strings which contain #/$ character
    }

    return $query;
}


/**
 * Run $query after substituting ^, # and $ symbols, and return the result resource (or call fail handler).
 */
function qa_db_query_sub($query) // arguments for substitution retrieved using func_get_args()
{
    $funcargs=func_get_args();

    return qa_db_query_raw(qa_db_apply_sub($query, array_slice($funcargs, 1)));
}


/**
 * Return the number of rows in $result. (Simple wrapper for mysqli_result::num_rows.)
 */
function qa_db_num_rows($result)
{
    if ($result instanceof mysqli_result)
        return $result->num_rows;

    return 0;
}


/**
 * Return the value of the auto-increment column for the last inserted row.
 */
function qa_db_last_insert_id()
{
    $db = qa_db_connection();
    return $db->insert_id;
}


/**
 * Return the number of rows affected by the last query.
 */
function qa_db_affected_rows()
{
    $db = qa_db_connection();
    return $db->affected_rows;
}


/**
 * For the previous INSERT ... ON DUPLICATE KEY UPDATE query, return whether an insert operation took place.
 */
function qa_db_insert_on_duplicate_inserted()
{
    return (qa_db_affected_rows() == 1);
}


/**
 * Return a random integer (as a string) for use in a BIGINT column.
 * Actual limit is 18,446,744,073,709,551,615 - we aim for 18,446,743,999,999,999,999.
 */
function qa_db_random_bigint()
{
    return sprintf('%d%06d%06d', mt_rand(1, 18446743), mt_rand(0, 999999), mt_rand(0, 999999));
}


/**
 * Return an array of the names of all tables in the Q2A database, converted to lower case.
 * No longer used by Q2A and shouldn't be needed.
 */
function qa_db_list_tables_lc()
{
    return array_map('strtolower', qa_db_list_tables());
}


/**
 * Return an array of the names of all tables in the Q2A database.
 */
function qa_db_list_tables()
{
    return qa_db_read_all_values(qa_db_query_raw('SHOW TABLES'));
}

/* The selectspec array can contain the elements below. See qa-db-selects.php for lots of examples.

too much code ... Start with a small example and try to put the close statement where you think it is correct. Then ask again and it will be much easier to help you out. — Marged, Jun 28 '15 at 18:19
I do not know which part of code I have to put in the question , Now I have put apart of code which contained "connect" and any one want to answer he can search for code which have to change by pressing Ctrl+F — araz ahmed, Jun 28 '15 at 18:33
Please don't take that personally, but: how do you want to fix the code if you don't know what it is doing ? We might be able to help you out with the parts you show us here, but noone of us will take the responsibility to fix your complete program. — Marged, Jun 28 '15 at 18:37
This isn't really a troubleshooting forum for third-party code that you don't understand the basic bits of. (If you did, you would have quickly spotted the `function qa_db_disconnect()` function.) Regardless. You probably have your setup using persistent connections. The application already provides the closing functionality. See my answer below on how to turn it on. — Markus AO, Jun 28 '15 at 18:58
@arazahmed that is good but can this compensate for the lack of php specific knowledge ? Ask yourself this question honestly. — Marged, Jun 29 '15 at 04:21

score 2 · Accepted Answer · edited May 23 '17 at 11:51

2

I bet you have your code set to use persistent connections for MySQL. This can result in open but idle connections, maxing out whatever your (most likely shared) hosting provider allocates for you.

You have a QA_PERSISTENT_CONN_DB constant in your code that determines your connection mode. You will find it defined as true or false somewhere in a configuration file. (It's not defined in your code above). Ensure it is set to false and see if the problem is resolved. When persistent connections are off, the code above takes care of closing the connections. You don't need to add it in.

If you're interested in knowing more about persistent connections, this answer covers a lot of ground; and there's more in the manual:

Persistent connections are links that do not close when the execution of your script ends.

Next time, make sure you read through your code to see if the functionality is already there (in this case, it's inside function qa_db_disconnect()), and if it's simply a matter of configuration.

edited May 23 '17 at 11:51

Community

1
1

answered Jun 28 '15 at 18:51

Markus AO

4,771
2
18
29

I have found "function qa_db_disconnect()" and about "QA_PERSISTENT_CONN_DB" it is like the following : [code] // in mysqli we connect and select database in constructor if (QA_PERSISTENT_CONN_DB) $db = new mysqli('p:'.QA_FINAL_MYSQL_HOSTNAME, QA_FINAL_MYSQL_USERNAME, QA_FINAL_MYSQL_PASSWORD, QA_FINAL_MYSQL_DATABASE); else $db = new mysqli(QA_FINAL_MYSQL_HOSTNAME, QA_FINAL_MYSQL_USERNAME, QA_FINAL_MYSQL_PASSWORD, QA_FINAL_MYSQL_DATABASE);[code/] – araz ahmed Jun 28 '15 at 21:49
Yeah very good, I also read that in the code you posted above, that `CTRL+F` technique is pretty awesome. Now have you done the change to `QA_PERSISTENT_CONN_DB` in your config file? – Markus AO Jun 29 '15 at 17:12
Yes I have done the change to QA_PERSISTENT_CONN_DB in my config file – araz ahmed Jul 03 '15 at 22:00
And then what? Fixed or not? – Markus AO Jul 04 '15 at 23:31

How I can close the connection between PHP & Mysql immediately after accessing

1 Answers1