Big problems with mysql_num_rows - PLEASE HELP!

[google@framevillage.com]

MySQL Server is version 4.1.7 (I know I should upgrade, and I will if
that is the problem but it has been working fine for 1.5 years now and
only recently has started to have this problem). Server is Linux
Redhat Enterprise 4. PHP is 4.4.4.

SQL STATEMENT:
==============
SELECT * FROM campaign_contac t cc
LEFT JOIN optins_master co
ON cc.cc_cont_id=c o.optin_id
WHERE cc.cc_camp_id=3 270
AND (co.status_flag IS NULL OR co.status_flag! ='unsub');

When I run the query, I get one of two results.

1. mysql_num_rows( ) returns the number of rows it found and all of the
data associated with it.

2. mysql_num_rows( ) returns 0 rows, but still gives me all of the
data. In the example above (cc.cc_camp_id= 3270) it gives me 2,579
rows.

There doesn't appear to be any rhyme or reason as to whether or not it
returns the appropriate row count or not. The largest database in the
query is the campaign_contac t database, but it only has approximately
15 million records in it. I am using UNSIGNED INT's for the record
id's, so I should have plenty of room to spare. On disk that database
takes up 439MB for the data and just over 1GB for the indexes.

I've shutdown the server and ran checks on everything to fix any index
problems (twice) and I still get the same problem.

Checking the packages I noticed this:

mysql-devel-4.1.7-4.RHEL4.1
mysql-server-4.1.7-4.RHEL4.1
mysql-4.1.7-4.RHEL4.1
mysqlclient10-3.23.58-4.RHEL4.1

^^^ mysqlclient v3.23.58?? I'm not sure if that could be causing the
problem, if it is I don't know why it has worked for 1.5 years now and
then all of the sudden we have issues.

I'm one of those people who don't believe in fixing something that
isn't broken. I'm happy to upgrade to 5.x if I know for sure it will
fix the problem, but I can't afford the time or money to upgrade if
I'll still be in the same boat.

*** UPDATE ***
I have upgraded everything to MySQL v5.0.27 (client/server/
development), and I recompiled PHP v4.4.4 and now it consistantly
returns 0 results (even though I still get all of my results if I were
to enumerate the $results returned from the mysql_query. Even
phpMyAdmin shows 0 results returns, but then below that it displays
all of the matching rows.

I am unbelievably confused, if anyone could shed light on this I'd
greatly appreciate it.

Thanks for your help,
-- Rob

[Colin McKinnon]

Re: Big problems with mysql_num_rows - PLEASE HELP!

google@framevil lage.com wrote:

>
SQL STATEMENT:
==============
SELECT * FROM campaign_contac t cc
LEFT JOIN optins_master co
ON cc.cc_cont_id=c o.optin_id
WHERE cc.cc_camp_id=3 270
AND (co.status_flag IS NULL OR co.status_flag! ='unsub');
>
When I run the query, I get one of two results.
>
1. mysql_num_rows( ) returns the number of rows it found and all of the
data associated with it.
>
2. mysql_num_rows( ) returns 0 rows, but still gives me all of the
data. In the example above (cc.cc_camp_id= 3270) it gives me 2,579
rows.

Taking a wild stab in the dark, the num_rows is only known if the query
result is cached / and/or you iterate to the end of the data set on this or
the previous access.

Try asking on a MySQL list for more educated guesses.

C.