MySQL Count() Problem on OS X

I've been running Drupal on OS X server and it works well. However I've had a problem that has been reported on the Drupal site as well.
SELECT mode, COUNT(*) FROM users GROUP BY mode; causes problems. The count returned is way out of bounds.
It returns a count of 864691128455135232. While a
SELECT COUNT(uid) FROM users ;
returns 12 - the correct number. The problem is that there are some places (in the import module for example) where there is a COUNT() instead of a COUNT(DISTINCT). For example in the import module the following change will resolve the issue. Change:

db_query("SELECT f.*, COUNT(i.iid)

to:

db_query("SELECT f.*, COUNT(DISTINCT i.iid)

Category: 

2 Comments

My solution

Hi. I posted this issue on drupal.org, but forgot to add my solution after I finally found one (corrected now). But I ended up installing the 4.0.17 package provided on mysql.com. That solved the incredible COUNT () problem.

If you download MySQL 4.0.17, be sure to grab the Installer _package_ and _read_ the included ReadMe.txt carefully.

After installing the package you may still find some incredible read-counts appearing on your Drupal site. Do not despair. This is because Drupal has stored the count results in the field totalcount in the table statistics. I've solved this by resetting the read-counts in statistics to zero (using CocoaMySQL or PHPMyAdmin or something else that allows you to edit the MySQL tables directly).

Good luck
Lieven

thank you, thank you, thank you

oh god, you are a lifesaver. this applies to OTRS too.

chris combs
http://chriscombs.net