MySQLi query results: Best approach, do you close,

Question

I have some questions about using MySQLi, queries, and related memory management. The code here is just to clarify my questions, so don't dump on it for error checking, etc. I know that needs to be done :)

Suppose I have something like this:

@ $db = new mysqli($dbhost, $un, $ps, $dbname);
$query = "SELECT field1, field2 ".
         "FROM table1 ".
         "WHERE field1={$some_value}";
$results = $db->query($query);

while ($result = $results->fetch_object()) {
    // Do something with the results
}

$query = "SELECT field1, field2 ".
         "FROM table2 ".
         "WHERE field1={$some_value2}";
// question 1
$results = $db->query($query);

while ($result = $results->fetch_object()) {
    // Do something with the second set of results
}

// Tidy up, question 2
if ($results) {
    $results->free();
}
if ($db) {
    $db->close();
}

// Question 3, a general one

So, based on the comments in the code above, here are my questions:

1. When I assign the results of the second query to $results, what happens to the memory associated with the previous results? Should I be freeing that result before assigning the new one?

2. Related to 1, when I do clean up at the end, is cleaning up just the last results enough?

3. When I do try to clean up a result, should I be freeing it as above, should I be closing it, or both?

I ask question 3 because the PHP documentation for mysqli::query has an example that uses close, even though close is not part of mysqli_result (see example 1 in mysqli::query). And in contrast, my normal PHP reference text uses free (PHP and MySQL Web Development, Fourth Edition, Welling and Thomson).

Answer 1

Thanks for all the answers, I'd also like to add my experiences of running multiple MySQL queries in one script. Mysqli gave “Commands out of sync” errors after running a query after a MySql procedures with multiple queries. To solve this I had to free all the open result sets using rizwan-mirza's solution to mysqli_free_result() any mysqli_more_results() https://stackoverflow.com/a/25907704/462781

Answer 2

The general PHP way is not to close any opened resource. Everything will be automatically closed at the script end. The only case where you have to take care of manual close is if you have long heavy code to run, which is not very common for PHP.

Answer 3

As rare as they are, in my opinion memory leaks are a nightmare to find and correct. I go out of my way to avoid them. Below is the pattern I use, based on the code you supplied:

$db = NULL;
try {
    $dbPool = "p:$dbhost"; // question 3: use pooling
    $db = new mysqli($dbPool, $un, $ps, $dbname);
    if ($db->connect_errno) {
        throw new Exception('' . $db->connect_error . ' ' . $db->connect_errno 
                . "\n" . $un . '@' . $dbhost . ' ' . $dbname);
        // NOTE: It's commonly considered a security 
        // risk to output connection information e.g.
        // host, user and database names.
    }

    $query = "SELECT field1, field2 ".
             "FROM table1 ".
             "WHERE field1={$some_value}";

    $results = NULL;
    try {

        if (!$results = $db->query($query)) {
            throw new Exception($db->error . " " . $db->errno 
                    . "\n" . $query);
            // NOTE: It's commonly considered a security 
            // risk to output SQL ($query).
        }
        while ($result = $results->fetch_object()) {
            // Do something with the results
        }

    } catch (Exception $ex) {
        // log, report, or otherwise handle the error
    }
    if ($results) {
        $results->free(); // question 1: why risk it?
    }

    $query = "SELECT field1, field2 ".
             "FROM table2 ".
             "WHERE field1={$some_value2}";

    $results = NULL; 
    try {

        if (!$results = $db->query($query)) {
            throw new Exception($db->error . " " . $db->errno 
                    . "\n" . $query);
            // NOTE: It's commonly considered a security 
            // risk to output SQL ($query).
        }            
        while ($result = $results->fetch_object()) {
            // Do something with the second set of results
        }

    } catch (Exception $ex) {
        // log, report, or otherwise handle the error
    }
    if ($results) {
        $results->free(); // question 2: again, why risk it?
    }

} catch (Exception $ex) {
    // log, report, or otherwise handle the error
}
if ($db) {
    $db->close();
}

In my opinion, connection pooling increases the chances for a memory leak, but according to the manual, the connection pooling libraries do a lot of cleanup for you automatically:

The persistent connection of the mysqli extension however provides built-in cleanup handling code. The cleanup carried out by mysqli includes:

Rollback active transactions

Close and drop temporary tables

Unlock tables

Reset session variables

Close prepared statements (always happens with PHP)

Close handler

Release locks acquired with GET_LOCK()

This ensures that persistent connections are in a clean state on return from the connection pool, before the client process uses them.

source: http://php.net/manual/en/mysqli.persistconns.php

I also agree with Pascal MARTIN that it's a good idea to open your connection at the beginning of your script and close it at the end. I think connection pooling makes that less important, but still a good idea.

Answer 4

The answers already provided are good, but I wanted to add one point and clarify another.

First, the clarification. Regarding the use of the close() method, it's important to note that the OP was referencing the close() method of the mysqli_result class, not the mysqli class. In the result class, the close() method is simply an alias to the free() method, as shown in the documentation, while in the mysqli class, it closes the connection. Thus, it's okay to use close() on the result in place of free() if desired.

Second, the additional point. As has already been pointed out, PHP's execution pattern means that everything will eventually be cleaned up behind you, and thus, you don't necessarily need to worry about releasing memory. However, if you're allocating a lot of result objects, or if you're allocating particularly big result objects (e.g., fetching a large amount of data), then you should probably free the memory when you're done to prevent problems further down the path of execution. This becomes especially important as your application starts to get more traffic, where the total amount of memory tied up across sessions can quickly become significant.

Answer 5

When I assign the results of the second query to $results, what happens to the memory associated with the previous results?

When you execute this:

$results = $db->query($query);

If there was something in $results before, this old content cannot be accessed anymore, as there is no reference left to it.

In such a case, PHP will mark the old content of the variable as "not needed anymore" -- and it will be removed from memory when PHP needs some memory.

This, at least, is true for general PHP variables; in the case of results from an SQL query, though, some data may be kept in memory on the driver-level -- over which PHP doesn't have much control.

Should I be freeing that result before assigning the new one?

I never do that -- but, quoting the manual page of mysqli_result::free:

Note: You should always free your result with mysqli_free_result(), when your result object is not needed anymore

It probably doesn't matter for a small script... And the only way to be sure would be to test, using memory_get_usage before and after calling that method, to see whether there is a difference or not.

Related to 1, when I do clean up at the end, is cleaning up just the last results enough?

When the scripts end:

• The connection to the database will be closed -- which means any memory that might be used by the driver should be freed
• All variables used by the PHP script will be destroyed -- which means the memory they were using should be freed.

So, at the end of the script, there is probably really no need to free the resultset.

When I do try to clean up a result, should I be freeing it as above, should I be closing it, or both?

If you close the connection to the database (using mysqli::close like you proposed), this will disconnect you from the database.

Which means you'll have to re-connect if you want to do another query! Which is not good at all (takes some time, resources, ... )

Generally speaking, I would not close the connection to the database until I am really sure that I won't need it anymore -- which means I would not disconnect before the end of the script.

And as "end of the script" means "the connection will be closed" even if you don't specify it; I almost never close the connection myself.