MySQL5.7被一條排序SQL弄當機

深圳gg發表於2018-06-22
MySql排序

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就好了。

相關文章