mysql_fetch_array returning false when pointer is at exactly 1/2 of mysql_num_rows

Question

The table is about 1.2M rows (its actively going up, so total rows grows slightly between iterations of this test, but that doesn't change the outcome).

mysql_num_rows is displayed - which is accurate, tested with select count(*) - after the pull query in order to show how large the result set is.

Then, a loop assigns the row to an array with mysql_fetch_array(). Iteration counter is incremented for every round, and you would expect it go get to mysql_num_rows and then break out of the while loop. Pretty standard stuff, been done a million times over.

What actually happens, on the other hand, is quite odd. It gets to exactly 1/2 way (floor(number_of_rows/2)) and then mysql_fetch_array() returns false. No matter how you limit the result set...

$iteration = 0;
$result = mysql_query("select `file_id`, `size` from `files`", $dbconn); // get all records
echo "\nDone. Found " . mysql_num_rows($result) . " rows."; // Done. Found 1291595 rows.
if ($result){
    while ($line = mysql_fetch_array($result) !== false){
         $iteration++;
    }
    echo "\ngot to $iteration before mysql_fetch_array was false."; // got to 642679 before mysql_fetch_array was false.
}

Sometimes $line is an empty array, sometimes mysql_fetch_array triggers false.

It gets mysql_num_rows that i'd expect and continues for 1/2 the records of the total result set size, then it stops...

If I put now, limit = 967356,1000000, I get:

Done. Found 324963 rows.
got to 162482 before mysql_fetch_array was false.

This is 1 off from being exactly 1/2 way.

limit = 1000000, 1000000:

Done. Found 292606 rows.
got to 146303 before mysql_fetch_array was false.

What in the world could this be?

php info:

[Please, don't use mysql_* functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) in new code. They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about prepared statements instead, and use [pdo](https://wiki.php.net/rfc/mysql_deprecation) or [mysqli](http://stackoverflow.com/questions/tagged/mysqli). — zessx, Oct 18 '13 at 21:05
Right before the while loop. see php info re: depreciated... its still live in php5.4 — tweak2, Oct 18 '13 at 21:12

Asaph · Accepted Answer · 2013-10-18T21:30:14.480

1

In order to avoid early termination of the while loop in situations where php will interpret the value of $line as false, change the line

while ($line = mysql_fetch_array($result)){

to

while (($line = mysql_fetch_array($result)) !== FALSE){

Update:

Another possibility is that you're running out of memory on very large result sets. Try using mysql_unbuffered_query instead of mysql_query().

Another Update:

Yet another possibility is that you have multiple database connections open in this script and mysql_query() isn't using the one you think it's using. Pass the optional 2nd argument to be sure. Change

mysql_fetch_array($result)

to

mysql_fetch_array($result, $dbconn)

edited Oct 18 '13 at 21:30

answered Oct 18 '13 at 21:19

Asaph

159,146
25
197
199

changed, same effect... with limit 0,20: Done. Found 20 rows. got to 10 before mysql_fetch_array was false – tweak2 Oct 18 '13 at 21:24
1

@tweak2: Is there more code that you can show. It's possible that your bug lies somewhere else. – Asaph Oct 18 '13 at 21:26
memory wouldn't really make much sense since it's exactly 1/2... if it was memory I would think it would run out at about the same amount of results every time, not exactly half. – tweak2 Oct 18 '13 at 21:26

score 1 · Answer 2 · answered Oct 18 '13 at 21:31

1

Sorry guys, I'm pretty dumb. I had a second $line=mysql_fetch_array($result) in the while loop... causing it to grab 2 rows for every loop. Appologies.

answered Oct 18 '13 at 21:31

tweak2

646
5
15

mysql_fetch_array returning false when pointer is at exactly 1/2 of mysql_num_rows

2 Answers2

Update:

Another Update: