mysql count 返回0,MySQL有时会错误地为count（*）返回0

I maintain a forum which paginates forum threads. To determine how many pages a thread has, I perform the query

SELECT COUNT(*) AS `numrows`

FROM `forum_posts`

WHERE `thread_id` = '3004'

AND `deleted` = 0;

Then get the result, divide it by the number of posts per page, and round up. The above queries occasionally return a result of 0 for no reason that I can fathom, which results in broken pagination. Usually the problem "magically" fixes itself within a few minutes so it's been an interesting journey even diagnosing it this far. Or rather, it can go on for hours but seems to magically fix itself a few moments after I log in to try to see what's going on (although this may be my imagination).

When the problem manifests all such queries return 0 for numrows, and when it resolves itself suddenly the above queries start returning the correct values again.

What could be causing this issue?

解决方案

This answer contains lots of guessing, the original question did not contain enough info to be sure about these. However, this answer might be useful for further readers.

The above queries occasionally return a result of 0 for no reason that I can fathom, which results in broken pagination.

You can get 0 records (or 0 in a COUNT(*) query) when

No rows are satisfying the WHERE conditions (e.g. no posts in a forum thread).

There is an error/exception somewhere between the 'seems to be bad' query and the code point where you found the error. (For example the client can not connect to the database server and the error is silently ignored).

When the problem manifests all such queries return 0 for numrows, and when it resolves itself suddenly the above queries start returning the correct values again.

In this particular case, my bet is: the max connection count in mysql is reached.

The database access layer silently ignores the error, which leads to a result which can be casted to the numeric value 0. As we know, dividing 0 by anything (except 0) equals to 0.

Here is an example (copy-pasters: do not use this code!)

try {

/**

Note for OO syntax only: If a connection fails an object is still returned.

To check if the connection failed then use either the

mysqli_connect_error() function or the

mysqli->connect_error property as in the preceding examples.

*/

$connection = new \mysqli('localhost', 'user', 'superSecretPass', 'database');

$query = 'SELECT COUNT(*) AS cnt FROM myTable WHERE this_flag IS NULL';

$result = $connection->query($connection, $query);

$itemCount = $result->fetchAssoc()['cnt'];

$result->close();

}

catch (\Exception $e) {

//Do nothing

}

$itemsPerPage = 15;

$sumPages = \ceil($itemCount / $itemsPerPage);

When an exception occurs or a connection error is not handled in the above example:

$itemCount is not defined

$itemCount will be treated as NULL

NULL will be casted to 0

0/15 = 0

If you want to handle errors in a bottom layer (such as database access) class, do it in a way which allows the caller to be notified about an error/exception.