Memory overflow with mysqli_query

Asked

Viewed 167 times

5

The following instruction stopped 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 combines.php on line 57

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

inserir a descrição da imagem aqui

I think that mysqli_query should not load all table contents in memory, right?

So why the memory burst?

Below the code: 

$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 mysqli windows xampp memory-leaks

  • Query inside loop is rarely good solution. It needs to go through the relevant chunk of code (preferably with the mentioned loop), otherwise it can’t be analyzed. And if you need data partially, it usually fits a LIMIT to split into batches. Other than that, there is "use result" and "save result", each with a behavior. As it stands, the question is not clear enough for a conclusion that does not depend on "kicking". It would be the case of [Edit] and put the relevant code in the post (with due care to hide passwords and server, of course).

  • It is independent of being in a loop. Putting the instruction right at the beginning also gives the same problem. I edited the question.

  • Still worth the above considerations about USE vs SAVE and the application of LIMIT. There are also some factors that are affected by php.ini (or equivalent) in libs configuration.

  • Could demonstrate how to do that in the code?

  • SELECT * FROM base LIMIT 1,10000 for example. Remembering that anyway, you will end up needing to run PHP in command line probably if it is some time consuming task, not to crash the page server and not have timeout problems

1 answer

3


Based on @Bacco’s comment, I researched and realized that actually the command mysqli_query loads ALL records into 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 (that will activate the query without uploading all records to RAM, mysqli_use_result (that will return the resulting object unsbufferized) and finally mysqli_fetch_row that will read the result line by line.

In this way the solution to the problem presented 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)) ...

Browser other questions tagged php mysqli windows xampp memory-leaks

You are not signed in. Login or sign up in order to post.