MySQLi query results: When do I close, free, or both?

Question

I have some questions about using MySQLi queries, and related memory management.

Suppose I have something like this:

$db = new mysqli($dbhost, $un, $ps, $dbname);

$query = "SELECT field1, field2  FROM table1 ";
$results = $db->query($query);

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

$query = "SELECT field1, field2 FROM table2 ";
// 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 the link above). And in contrast, my normal PHP reference text uses free (PHP and MySQL Web Development, Fourth Edition, Welling and Thomson).

Answer 1

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.

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 really no need to free the result set.

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.

This 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.

Answer 2

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 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 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.