mysql_fetch_assoc() stops after 100 rows

...following mysql_unbuffered_query()

[dmorison]

Does anybody know of a PHP or my.ini config setting that would cause mysql_fetch_assoc to fetch a maximum of 100 rows following an unbuffered query?

I am sure that there are more rows in the result set; as I have checked the value of COUNT(*) using the same where clause; for example, if I execute the query:

SELECT COUNT(*) FROM widgets WHERE color='blue';

this shows the value of COUNT(*) as 482.

However, if I then execute the following statement using mysql_buffered_query():

SELECT * FROM widgets WHERE color='blue';

...and then use the following code:

while($row = mysql_fetch_assoc($resourceID))
{
// rest of code
}

it always stops after 100 rows exactly.

If I make a normal mysql_query() instead of using the unbuffered version; then mysql_fetch_assoc returns all 482 rows as expected.

Can't find anything in the PHP or MySQL docs about this...!

[jatar_k]

I am assuming you bolded function should be mysql_unbuffered_query

is it possible something is interrupting it?

[dmorison]

Ah - yes, I meant unbuffered :o

I am only assuming that nothing is interrupting it because it always stops after exactly 100 records...

[jatar_k]

as an aside

how come you need to use unbuffered? That isn't really that big of a set.

[dmorison]

Ah - the numbers were just for example purposes. The actual application can be processing in the order of 100,000 records in some cases.

[coopster]

If memory suits me correctly we are dealing with the mysq_use_result [dev.mysql.com] API. You may be exceeding the

max_allowed_packet

bytes. Have you checked your error log [dev.mysql.com]?

[dmorison]

Thanks for the pointers guys. I have now identified the problem and fixed it; however this issue has only come about after a server move and significant changes to the PHP / MySQL interface.

What I don't understand is why it worked before; because having studied the relavent PHP and MySQL documentation in more detail, it shouldn't have! :o

My unbuffered query was initialising a result set of many thousands of rows, which is why I am using unbuffered because I was having memory issues before. Now, because this is a lengthy process; I have a mechanism in place to report back progress to the user.

Every 100 records ;) a call is made to an update progress function; which; you guessed it; makes another mysql connect, select and query. This function does not use force the creation of a new link; so the one that was handling my unbuffered query is used. In order to do this; it ditches the unbuffered query that was in progress.

So I don't understand why it worked before. The "significant" change is that I have moved from bog standard MySQL/PHP to a whacky isntallation running mysqld-max (with InnoDB table engine for another app) and invocation via mysql_multi; because I am running 2 servers on the same machine.

Previously, I was connecting using:
mysql_connect("localhost",$username,$password);

On the new server, I am using:
mysql_connect(":/var/lib/mysql/mysql2.sock",$username,$password);

Thanks again for the pointers!

[coopster]

Every 100 records ;) a call is made to an update progress function;

hehehe, you better wink after that one, pal ;) That should have set flags off right away. LOL. Glad you got it sorted. As far as why it worked before ...? Good question. Perhaps you didn't have that "every 100 record" extra-sql step in there ;)

[jatar_k]

interesting, glad you found it, not totally sure that the connection method should make a difference but it seemed to, it shouldn't really have worked either way.

the seperate link is the key.