MySQL有时会错误地为count（*）返回0 [英] MySQL sometimes erroneously returns 0 for count(*)

问题描述

我维护论坛帖子。要确定线程有多少页，我执行查询

  SELECT COUNT（*）AS`numrows`
 FROM`forum_posts` 
 WHERE`thread_id` ='3004'
 AND`deleted` = 0; 
  

然后得到结果，除以每页的帖子数，并向上取整。上面的查询偶尔返回0的结果没有理由我可以fathom，这导致分页。通常问题魔法修复自己在几分钟内，所以这是一个有趣的旅程，甚至诊断这远。或者，它可以继续几个小时，但似乎在我登录后，几分钟后，试图看到发生了什么（虽然这可能是我的想象力）。

当问题显示所有这样的查询返回0为 numrows ，当它突然解决自己，上述查询开始返回正确的值。

可能是什么原因导致此问题？

解决方案

包含了很多猜测，原来的问题没有包含足够的信息来确定这些。

上面的查询偶尔会返回0的结果，没有理由我不能理解，

您可以获取0条记录（或COUNT（*）查询中的0条记录）

1. 没有行满足WHERE条件（例如在论坛主题中没有帖子）。


2. /异常之间的似乎是不好的查询和代码点你发现的错误。 （例如客户端无法连接到数据库服务器，并且错误被忽略）。

当问题表明所有这样的查询返回0为numrows，并且当它突然解决自己，上述查询开始返回正确的值。

在这种特殊情况下，我的赌注是：到达mysql中的最大连接计数。
数据库访问层默默地忽略错误，这导致一个结果可以转换为数值0.我们知道，除以0除以0，等于0。

这里是一个例子（复制粘贴：不要使用此代码！）

  {
 / ** 
仅注释OO语法：如果连接失败，仍然返回一个对象。 
要检查连接是否失败，请使用
 mysqli_connect_error（）函数或
 mysqli-> connect_error属性，如前面的示例所示。 
 * / 
 $ 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）; 
  

当上述示例中发生异常或未处理连接错误时：

• $ itemCount未定义


• $ itemCount将被视为NULL


• NULL将被强制转换为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

1. No rows are satisfying the WHERE conditions (e.g. no posts in a forum thread).
2. 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.

这篇关于MySQL有时会错误地为count（*）返回0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！