The problem of memory leaking caused by not freeing the result set after using next_result()

When using PHP to manipulate MySQL databases, if you use the mysqli extension for multi-query, you are likely to encounter the next_result() method. And many people ignore an important detail -.

This article will show you the causes, manifestations and how to avoid this problem.

Background: What is next_result()

When you execute multiple SQLs through mysqli::multi_query() , MySQL returns multiple result sets. These result sets can be accessed one by one through mysqli::next_result() , and the content of each result can be obtained in conjunction with mysqli::store_result() .

Sample code:

 $mysqli = new mysqli("localhost", "user", "password", "database");

$sql = "SELECT * FROM users; SELECT * FROM orders;";
if ($mysqli->multi_query($sql)) {
    do {
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_assoc()) {
                // Process each row of data
                echo $row["id"] . "<br>";
            }
            // Release the result set
            $result->free();
        }
    } while ($mysqli->next_result());
}

In this example, get a result set through store_result() each time and release it through $result->free() after use.

Problem: No release of result set

If you omit $result->free() , as follows:

 // ? 未Release the result set的代码示例
if ($result = $mysqli->store_result()) {
    while ($row = $result->fetch_assoc()) {
        echo $row["id"] . "<br>";
    }
    // Ignored $result->free();
}

Then the memory usage in your program will continue to grow, especially when processing large amounts of data or high concurrent requests, this memory leak will be very obvious. Because the mysqli extension caches the result set in client memory, PHP can recycle these resources only by explicitly releasing it.

The consequences of memory leaks

• The memory surges after the program runs for a long time;

• The server response slows down or even crashes;

• PHP reports an Allowed memory size exhausted error;

• Untracked performance issues.

This is especially fatal in high traffic APIs, timing tasks, or daemons.

The correct way to do it

Always remember to call $result->free() after processing the result set. In addition, check whether there are new result sets and process them after each call next_result() .

Complete leak-proof template:

 $mysqli = new mysqli("localhost", "user", "password", "database");

$sql = "CALL complex_procedure(); CALL another_procedure();";
if ($mysqli->multi_query($sql)) {
    do {
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_assoc()) {
                // Process each line
            }
            $result->free(); // ? Release correctly
        }
    } while ($mysqli->more_results() && $mysqli->next_result());
}

How to detect leaks

• Use memory_get_usage() or xdebug tools to monitor memory usage;

• Use Linux commands such as top or htop to monitor process memory;

• Logs the memory usage after each query.

summary

Not releasing the result set when using mysqli::next_result() does lead to a memory leak in PHP scripts. The correct way is to call $result->free() every time the result set is processed. This not only keeps the program robust, but also avoids performance problems caused by excessive memory usage.

This is especially important if you are building complex data interfaces or services that process data in batches.

For more official documentation on mysqli multi-result set processing, you can access:
https://gitbox.net/docs/php/mysqli-multi-query (assumed link)

I hope this article can help you avoid common pitfalls in PHP development.