mysql - 使用 group by 子句和 count() 函数时 MySQL 的不一致/奇怪行为

Question

由于 MySQL 中的错误 (?)，COUNT() 函数以及 GROUP BY 子句可能会导致 MySQL 泄漏数据库详细信息，如下所示 -

mysql> select count(*), floor(rand()*2)x from users group by x;

ERROR 1062 (23000): Duplicate entry '1' for key 'group_key'

可以通过精心设计的查询在此处揭示敏感详细信息。这是意外的行为，也许是一个错误？

mysql> select count(*), floor(rand()*2)x from users group by x;

+----------+---+  
| count(*) | x |  
+----------+---+  
| 8        | 0 |  
| 5        | 1 |  
+----------+---+ 

集合中 2 行(0.00 秒) <-- 有时查询运行时没有任何错误(预期行为)

有谁知道到底是什么原因导致MySQL错误。

我正在使用的测试平台是这个优秀的资源 - https://github.com/Audi-1/sqli-labs

Answer 1

这看起来是一个报告的(而且是老的!)错误:http://bugs.mysql.com/bug.php?id=58081

Description: A GROUP BY query returns this error under certain circumstances:

Duplicate entry '107374182410737418241' for key 'group_key'

'group_key' is not a real column name in the table. It looks like a name for the grouping column in the temporary table.

How to repeat: set names latin1; drop table if exists t1; create table t1(a int) engine=myisam; insert into t1 values (0),(0),(1),(0),(0); select count(*) from t1, t1 t2 group by insert('', t2.a, t1.a,(@@global.max_binlog_size));

ERROR 1062 (23000): Duplicate entry '107374182410737418241' for key 'group_key'

评论表明建议的解决方法是增加可用堆和临时表的大小:

The workaround i found is to increase the size of the tmp_table:

SET SESSION max_heap_table_size=536870912; SET SESSION tmp_table_size=536870912;

now my request work !

或者检查您的可用磁盘空间