1

I'm running this code:

<?php
$dbport         = 1578;
$dbhost         = "localhost";
$dbname         = "dbname";
$dbuser         = "dbuser";
$dbpswd         = "dbpswd";
$MySQLLink = mysqli_connect($dbhost, $dbuser, $dbpswd, $dbname, $dbport);
$query = "SELECT * FROM assets_advanced_records LIMIT 100000";
echo "running query ".$query."<br>";
$result = mysqli_query($MySQLLink, $query) or die("Query failed (".$query."): ".mysqli_error($MySQLLink));
echo "query succeeded<br>";
mysqli_close($MySQLLink);
?>

Execution stops at mysqli_query() immediately; the page is no longer loading.

Output looks like this:

running query SELECT * FROM assets_advanced_records LIMIT 100000

If I reduce LIMIT to 10000 I get the correct output:

running query SELECT * FROM assets_advanced_records LIMIT 10000
query succeeded

What could be causing this?

MySQL version: 5.7.12-0ubuntu1.1

PHP version: 7.0.8-0ubuntu0.16.04.3

I get this in the console when execution stops:

Failed to load resource: the server responded with a status of 500 (Internal Server Error)

UPDATE:

Checking /var/log/apache2/error.log I'm getting errors like:

[Fri Dec 30 15:13:23.983920 2016] [:error] [pid 1907] [client 10.10.6.116:54978] FastCGI: server "/usr/lib/cgi-bin/php7-fcgi" stderr: PHP message: PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 806912 bytes) in /var/www/html/debug_tools2.php on line 491, referer: http://10.10.6.24/debug_tools2.php
ChelseaB
  • 13
  • 4
  • 1
    are you trying to print all 100000 records in single page? – Vikas Umrao Dec 30 '16 at 03:47
  • if so , you can use pagination for this – Vikas Umrao Dec 30 '16 at 03:48
  • No, I'm just a bit worried that the query doesn't fail or timeout, but simply stops execution (which doesn't seem like normal behavior). The code above was written specifically to test this issue. I don't actually need to run that query, but I need to know why it isn't working in case it comes up in the future. – ChelseaB Dec 30 '16 at 03:53
  • I think that we are talking about max_execution_time defined in php.ini http://php.net/manual/en/function.set-time-limit.php – Yeikel Dec 30 '16 at 07:21
  • Pretty sure it isn't max_execution_time; I get the error immediately, not after the 300 seconds my max_execution_time is set to. – ChelseaB Dec 30 '16 at 22:18

1 Answers1

1

The error log was the key:

PHP Fatal error: Allowed memory size of 134217728 bytes exhausted

You ran out of memory. You had 128MB to work with, and your result set was bigger than that, so the script crapped out.

When you run mysqli_query it loads the entire result set into memory, so it makes sense that a large enough result set would be too large for the amount of memory php has been allocated. There are a few solutions:

• Chunking, just grab ~10000 records at a time

for ($offset=0;$offset<10;$offset++) {
$query = "SELECT * FROM assets_advanced_records LIMIT ".10000*$offset.", 10000";
//do stuff...
}

• Increase php's memory limit. If you have the permissions to do so: You are currently at 128M, jack it up with

ini_set('memory_limit','1G');

at the top of your script, or just throw caution to the wind and max out with 

ini_set('memory_limit','-1')

• Use an unbuffered query. That won't load the entire resultset into memory. You can read about mysqli_real_query with mysqli_use_result to achieve that. Further reading: https://dev.mysql.com/doc/apis-php/en/apis-php-mysqlinfo.concepts.buffering.html, unbuffered query with MySQLi?, http://php.net/manual/en/function.mysql-unbuffered-query.php

Community
  • 1
  • 1
chiliNUT
  • 18,989
  • 14
  • 66
  • 106
  • 1
    Yep, that did it. Looks like the query was taking about 268MB. I just edited /etc/php/7.0/fpm/php.ini and increased memory_limit. Thanks for answering! – ChelseaB Dec 31 '16 at 00:49