MySQL5.7被一条排序SQL弄宕机

以你之姓@ 2022-05-22 10:36 15阅读 0赞

windows2008上，数据库（5.7.17）被一条排序的SQL搞宕机，每次毕现，表只有一条记录（表定义比较长），且表文件既有几百k。

**错误日志：**

04:58:38 UTC - mysqld got exception 0xc000001d ;

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=65536  
max\_used\_connections=23  
max\_threads=151  
thread\_count=9  
connection\_count=9  
It is possible that mysqld could use up to   
key\_buffer\_size + (read\_buffer\_size + sort\_buffer\_size)\*max\_threads = 58347 K  bytes of memory  
Hope that's ok; if not, decrease some variables in the equation.  
  
Thread pointer: 0xa8c9010  
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...  
1407d3343    mysqld.exe!my\_wildcmp\_mb()  
1402a193b    mysqld.exe!?copy\_min\_to\_min@SEL\_ARG@@QEAAXPEAV1@@Z()  
1402a4d00    mysqld.exe!?free\_tree@SEL\_ARG@@QEAAXXZ()  
1402b14be    mysqld.exe!?test\_quick\_select@@YAHPEAVTHD@@V?$Bitmap@$0EA@@@\_K2\_NW4enum\_order@st\_order@@PEBVQEP\_shared\_owner@@PEAVItem@@PEAV2@PEAPEAVQUICK\_SELECT\_I@@@Z()  
13fe6736e    mysqld.exe!?get\_op\_type@Ft\_hints@@QEAA?AW4ft\_operation@@XZ()  
13fe65dcd    mysqld.exe!?estimate\_rowcount@JOIN@@AEAA\_NXZ()  
13fe68b39    mysqld.exe!?make\_join\_plan@JOIN@@AEAA\_NXZ()  
13fe6a6ab    mysqld.exe!?optimize@JOIN@@QEAAHXZ()  
13feaf179    mysqld.exe!?optimize@st\_select\_lex@@QEAA\_NPEAVTHD@@@Z()  
13fecc1c5    mysqld.exe!?optimize@st\_select\_lex\_unit@@QEAA\_NPEAVTHD@@@Z()  
13ff451bc    mysqld.exe!?optimize\_derived@TABLE\_LIST@@QEAA\_NPEAVTHD@@@Z()  
13fe6a336    mysqld.exe!?optimize@JOIN@@QEAAHXZ()  
13feaf179    mysqld.exe!?optimize@st\_select\_lex@@QEAA\_NPEAVTHD@@@Z()  
13fead19d    mysqld.exe!?handle\_query@@YA\_NPEAVTHD@@PEAULEX@@PEAVQuery\_result@@\_K3@Z()  
13fd24107    mysqld.exe!?execute\_init\_command@@YAXPEAVTHD@@PEAUst\_mysql\_lex\_string@@PEAUst\_mysql\_rwlock@@@Z()  
13fd26036    mysqld.exe!?mysql\_execute\_command@@YAHPEAVTHD@@\_N@Z()  
13fd29a83    mysqld.exe!?mysql\_parse@@YAXPEAVTHD@@PEAVParser\_state@@@Z()  
13fd22b83    mysqld.exe!?dispatch\_command@@YA\_NPEAVTHD@@PEBTCOM\_DATA@@W4enum\_server\_command@@@Z()  
13fd23b7a    mysqld.exe!?do\_command@@YA\_NPEAVTHD@@@Z()  
13fcf981c    mysqld.exe!handle\_connection()  
140717392    mysqld.exe!?reserve@?$vector@EV?$allocator@E@std@@@std@@QEAAX\_K@Z()  
14042fc4b    mysqld.exe!my\_thread\_once()  
1407d9a3f    mysqld.exe!my\_wildcmp\_mb()  
1407d9c8a    mysqld.exe!my\_wildcmp\_mb()  
779bf56d    kernel32.dll!BaseThreadInitThunk()  
77bf3281    ntdll.dll!RtlUserThreadStart()  
  
Trying to get some variables.  
Some pointers may be invalid and cause the dump to abort.  
Query (ef11b40): SELECT COUNT(t.DEVICE\_ID) FROM  
 (SELECT DISTINCT DEVICE\_ID FROM \`energy\_airconditioner\_info\` ) t  
Connection ID (thread ID): 59

Status: NOT\_KILLED

第一次遇到这种问题，看错误提示好像是参数设置的有问题，查看了一下数据库的这几个参数，都是默认的，于是修改如下，然后验证问题解决：

innodb\_buffer\_pool\_size=200M  
key\_buffer\_size=8M  
read\_buffer\_size=1M  
read\_rnd\_buffer\_size=1M

sort\_buffer\_size=1M

过了一会又宕机了，换到5.7.22，还是有问题，安装了windows6.1-KB976932-X64.exe就好了。