Memory overflow with mysqli_query

Question

Memory overflow with mysqli_query

Navigation

#1 by (3 votes)

5

The following statement caught the program: $result = mysqli_query($db, 'SELECT * FROM base');

And returned the following error:

PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 4194312 bytes) in C: \ xampp \ htdocs \ combina.php on line 57

The base table was just under 3 million records , this table only has 3 fields int(10) .

Ithinkmysqli_queryshouldnotloadthewholecontentsofthetableintomemory,right?

So,whythememoryoverflow?

Belowthecode:

$servername="localhost";
$username = "root";
$password = "";
$dbname = "comb";

$db = mysqli_connect($servername, $username, $password, $dbname);
if (!$db) {
    die("Falha na conexão: " . mysqli_connect_error());
}
$result = mysqli_query($db, 'SELECT * FROM base');

php memory-leaks windows xampp mysqli

asked by anonymous 27.07.2018 / 04:31

1 answer

Reversing each line of a file in C Automatically identify points of influence in a regression

score 3 · Accepted Answer

Based on the @Bacco comment, I researched and realized that the mysqli_query command actually loads ALL records in RAM. That is, for large tables, it will actually generate a memory limitation.

The solution I found was to use the combination of mysqli_real_query (which will enable the query without loading all the records in RAM , mysqli_use_result (which will return the resulting object un-buffered ) and finally mysqli_fetch_row that will read the result line by line.

In this way the solution to the presented problem would be more or less:

$db = mysqli_connect($servername, $username, $password, $dbname);
mysqli_real_query($db, 'SELECT * FROM base');
$result = mysqli_use_result($db);
while ($row = mysqli_fetch_row($result)) ...