mysql 执行查询导致退出求助

[seenu]

macos 下装的 mysql，本地运行一个项目，在操作过程中发现会使本地的 mysql 停止，经排查后发现是一条 sql 导致的
SELECT COUNT(*)
FROM (
SELECT t.*
FROM (
SELECT *
FROM A
WHERE aa <> bb
) t
) t2

然后这条语句我在命令行执行也会导致 mysql 退出，如果不用 COUNT 则不会，但是在同事的电脑上是没有问题的，不知道是我 mysql 哪里配置的问题，mysql 版本是 5.6.47

错误日志如下：
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=4
max_threads=151
thread_count=4
connection_count=4
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 68210 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7fc2421aaa00
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 700007d8eeb0 thread_stack 0x40000

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fc2421ff630): is an invalid pointer
Connection ID (thread ID): 175
Status: NOT_KILLED

我在条件语句加上 limit 后能正常执行，内存也调大了，还是没能找出问题所在，我把 count 改成*也是能正常执行

[mitu9527]

这个问题别人不好重现，找找 mysql 的日志，看看有没有有用的提示，贴出来，好让大家帮你看看是咋回事。

[louettagfh]

这条 log 上面还有一部分 Log 贴上来才知道哪里 crash 了啊

[opengps]

所以我习惯性给各种逻辑都加上 try cache 记录日志，而不是让程序崩溃

[mitu9527]

网上搜了一下，可能是 mysql 的一个 bug ；可以考虑升级一下 mysql 版本，也可以考虑优化一下 SQL 语句，避开这个问题，上面的语句应该可以减少一层甚至两层子查询，说不定就避开了。

这是题外话：如 2 楼所说，你把错误日志最重要的信息给漏掉了，这么提问久了，你身边的人可能会觉得你在故意耍他，虽然你可能是无意的。

[seenu]

@mitu9527 谢谢，减少层数确实可以避开，不过我想知道具体原因而已，先试试升级版本看看能不能解决，日志缺少的部分因为字数限制没贴出来，以后会注意的了
stack_bottom = 700006668eb0 thread_stack 0x40000
0 mysqld 0x00000001056b074d my_print_stacktrace + 61
1 mysqld 0x00000001056117f0 handle_fatal_signal + 688
2 libsystem_platform.dylib 0x00007fff65ebcb5d _sigtramp + 29
3 ??? 0x0000000000000002 0x0 + 2
4 mysqld 0x00000001053d25de _ZN24Item_singlerow_subselect8val_realEv + 30
5 mysqld 0x0000000104e34581 _ZN20Item_direct_view_ref8val_realEv + 65
6 mysqld 0x0000000104e4863c _ZN16Item_func_ifnull7real_opEv + 28
7 mysqld 0x0000000104e7be8c _ZN19Item_func_numhybrid8val_realEv + 108
8 mysqld 0x0000000104e445ab _ZN14Arg_comparator12compare_realEv + 27
9 mysqld 0x0000000104e468ad _ZN12Item_func_ne7val_intEv + 45
10 mysqld 0x00000001054da3e7 _ZL20evaluate_join_recordP4JOINP7QEP_TAB + 71
11 mysqld 0x00000001054da04d _Z10sub_selectP4JOINP7QEP_TABb + 621
12 mysqld 0x00000001054da6c8 _ZL20evaluate_join_recordP4JOINP7QEP_TAB + 808
13 mysqld 0x00000001054da04d _Z10sub_selectP4JOINP7QEP_TABb + 621
14 mysqld 0x00000001054d7641 _ZN4JOIN4execEv + 769
15 mysqld 0x0000000105554bfa _Z12handle_queryP3THDP3LEXP12Query_resultyy + 554
16 mysqld 0x0000000105520fd6 _ZL21execute_sqlcom_selectP3THDP10TABLE_LIST + 950
17 mysqld 0x000000010551af71 _Z21mysql_execute_commandP3THDb + 2689
18 mysqld 0x0000000105519b20 _Z11mysql_parseP3THDP12Parser_state + 848
19 mysqld 0x00000001055189f6 _Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command + 4310
20 mysqld 0x00000001055195aa _Z10do_commandP3THD + 490
21 mysqld 0x00000001055f531c handle_connection + 460
22 mysqld 0x0000000105996011 pfs_spawn_thread + 305
23 libsystem_pthread.dylib 0x00007fff65ec52eb _pthread_body + 126
24 libsystem_pthread.dylib 0x00007fff65ec8249 _pthread_start + 66
25 libsystem_pthread.dylib 0x00007fff65ec440d thread_start + 13

[zhangysh1995]

SELECT COUNT(*)
FROM (
SELECT t.*
FROM (
SELECT *
FROM A
WHERE aa <> bb
) t
) t2

简化 => SELECT COUNT(*) FROM A WHERE aa <> bb;

[zhangysh1995]

@seenu 考虑给 MySQL 报个 bug 吧。。都 crash 有点严重