MySQL Bugs: #82943: query cache crashes MySQL service

Bug #82943	query cache crashes MySQL service
Submitted:	12 Sep 2016 6:44	Modified:	6 Jan 2017 15:35
Reporter:	Matthias Igel	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Query Cache	Severity:	S2 (Serious)
Version:	5.7.14	OS:	Windows (2008 R2)
Assigned to:		CPU Architecture:	Any
Tags:	query cache

Description:
We've seen ongoing MySQL service crashes with version 5.7.12. Digging deeper into it we found a hint about a possible memory leak in that version so we updated to version 5.7.14 - to no use however.

In the logs there are traces like the following:

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

Thread pointer: 0xbb5cc930
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...
14016e0d0    mysqld.exe!my_hash_sort_bin()
13fdf9b81    mysqld.exe!_my_hash_init()
13fdfa982    mysqld.exe!my_hash_update()
13fdf9e4a    mysqld.exe!my_hash_delete()
13f7516ba    mysqld.exe!?unlink_table@Query_cache@@IEAAXPEAUQuery_cache_block_table@@@Z()
13f74d400    mysqld.exe!?free_query_internal@Query_cache@@AEAAXPEAUQuery_cache_block@@@Z()
13f74d33f    mysqld.exe!?free_old_query@Query_cache@@IEAADXZ()
13f74c6a5    mysqld.exe!?allocate_block@Query_cache@@IEAAPEAUQuery_cache_block@@_KD0@Z()
13f74c7ba    mysqld.exe!?allocate_data_chain@Query_cache@@IEAADPEAPEAUQuery_cache_block@@KPEAU2@D@Z()
13f75198b    mysqld.exe!?write_result_data@Query_cache@@IEAADPEAPEAUQuery_cache_block@@KPEAEPEAU2@W4block_type@2@@Z()
13f74ca59    mysqld.exe!?append_result_data@Query_cache@@IEAADPEAPEAUQuery_cache_block@@KPEAEPEAU2@@Z()
13f74e2f3    mysqld.exe!?insert@Query_cache@@QEAAXPEAUQuery_cache_tls@@PEBDKI@Z()
13f750055    mysqld.exe!?query_cache_insert@@YAXPEBDKI@Z()
13f6bf8d6    mysqld.exe!net_write_packet()
13f6bf062    mysqld.exe!?sort_buffer@Filesort_buffer@@QEAAXPEBVSort_param@@I@Z()
13f6bf5f4    mysqld.exe!my_net_write()
13f85846c    mysqld.exe!?end_row@Protocol_classic@@UEAA_NXZ()
13fc5569b    mysqld.exe!?do_sj_dups_weedout@@YAHPEAVTHD@@PEAVSJ_TMP_TABLE@@@Z()
13fc5661d    mysqld.exe!?end_write_group@@YA?AW4enum_nested_loop_state@@PEAVJOIN@@PEAVQEP_TAB@@_N@Z()
13fc5a147    mysqld.exe!?sub_select@@YA?AW4enum_nested_loop_state@@PEAVJOIN@@PEAVQEP_TAB@@_N@Z()
13fc55311    mysqld.exe!?create_intermediate_table@JOIN@@AEAA_NPEAVQEP_TAB@@PEAV?$List@VItem@@@@AEAVORDER_with_src@1@_N@Z()
13fc56ccb    mysqld.exe!?exec@JOIN@@QEAAXXZ()
13f86eb11    mysqld.exe!?handle_query@@YA_NPEAVTHD@@PEAULEX@@PEAVQuery_result@@_K3@Z()
13f6e3c17    mysqld.exe!?execute_init_command@@YAXPEAVTHD@@PEAUst_mysql_lex_string@@PEAUst_mysql_rwlock@@@Z()
13f6e5b06    mysqld.exe!?mysql_execute_command@@YAHPEAVTHD@@_N@Z()
13f6e94b3    mysqld.exe!?mysql_parse@@YAXPEAVTHD@@PEAVParser_state@@@Z()
13f6e267d    mysqld.exe!?dispatch_command@@YA_NPEAVTHD@@PEBTCOM_DATA@@W4enum_server_command@@@Z()
13f6e368a    mysqld.exe!?do_command@@YA_NPEAVTHD@@@Z()
13f6a82bc    mysqld.exe!handle_connection()
1400d5122    mysqld.exe!?reserve@?$vector@EV?$allocator@E@std@@@std@@QEAAX_K@Z()
13fdeb93b    mysqld.exe!my_thread_once()
1401973ef    mysqld.exe!my_wildcmp_mb()
14019763a    mysqld.exe!my_wildcmp_mb()
77a459bd    kernel32.dll!BaseThreadInitThunk()
77c7a2e1    ntdll.dll!RtlUserThreadStart()

In every strack trace we could see the query cache involved every time, so we changed the setting from 1mb (default?) to 0. Since that change there hasn't been any subsequent crash. We know that 1mb is not a sufficient setting - to be honest, we didn't care about that setting at all in the first place - but this should not lead to a service crash.

Let me know if you need additional information.

How to repeat:
As the crashes happened irregularly, we cannot provide an easy way to reproduce it for sure. Out server is on heavy load with different applications - perhaps that sufficient to reproduce it.

Please try version 5.7.16. Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I know this is a closed bug, be we just experienced the exact same issue while using 5.7.16 on Windows Server 2008 R2

Version: '5.7.16-log'  socket: ''  port: 3306  MySQL Community Server (GPL)

13:57:24 UTC - mysqld got exception 0xc0000005 ;
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=59
max_threads=151
thread_count=7
connection_count=7
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: 0x5dca3940
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...
14024ebe0    mysqld.exe!my_hash_sort_bin()
13fedc311    mysqld.exe!_my_hash_init()
13fedd112    mysqld.exe!my_hash_update()
13fedc613    mysqld.exe!my_hash_delete()
13f82d48c    mysqld.exe!?free_query@Query_cache@@IEAAXPEAUQuery_cache_block@@@Z()
13f82d45f    mysqld.exe!?free_old_query@Query_cache@@IEAADXZ()
13f82c7c5    mysqld.exe!?allocate_block@Query_cache@@IEAAPEAUQuery_cache_block@@_KD0@Z()
13f82c98e    mysqld.exe!?allocate_data_chain@Query_cache@@IEAADPEAPEAUQuery_cache_block@@KPEAU2@D@Z()
13f831aab    mysqld.exe!?write_result_data@Query_cache@@IEAADPEAPEAUQuery_cache_block@@KPEAEPEAU2@W4block_type@2@@Z()
13f82cade    mysqld.exe!?append_result_data@Query_cache@@IEAADPEAPEAUQuery_cache_block@@KPEAEPEAU2@@Z()
13f82e413    mysqld.exe!?insert@Query_cache@@QEAAXPEAUQuery_cache_tls@@PEBDKI@Z()
13f830175    mysqld.exe!?query_cache_insert@@YAXPEBDKI@Z()
13f79f9a6    mysqld.exe!net_write_packet()
13f79f132    mysqld.exe!?sort_buffer@Filesort_buffer@@QEAAXPEBVSort_param@@I@Z()
13f79f6c4    mysqld.exe!my_net_write()
13f938a2c    mysqld.exe!?end_row@Protocol_classic@@UEAA_NXZ()
13fd3646b    mysqld.exe!?do_sj_dups_weedout@@YAHPEAVTHD@@PEAVSJ_TMP_TABLE@@@Z()
13fd373ed    mysqld.exe!?end_write_group@@YA?AW4enum_nested_loop_state@@PEAVJOIN@@PEAVQEP_TAB@@_N@Z()
13fd3af17    mysqld.exe!?sub_select@@YA?AW4enum_nested_loop_state@@PEAVJOIN@@PEAVQEP_TAB@@_N@Z()
13fd360e1    mysqld.exe!?create_intermediate_table@JOIN@@AEAA_NPEAVQEP_TAB@@PEAV?$List@VItem@@@@AEAVORDER_with_src@1@_N@Z()
13fd37a9b    mysqld.exe!?exec@JOIN@@QEAAXXZ()
13f94f171    mysqld.exe!?handle_query@@YA_NPEAVTHD@@PEAULEX@@PEAVQuery_result@@_K3@Z()
13f7c3ce7    mysqld.exe!?execute_init_command@@YAXPEAVTHD@@PEAUst_mysql_lex_string@@PEAUst_mysql_rwlock@@@Z()
13f7c5bd6    mysqld.exe!?mysql_execute_command@@YAHPEAVTHD@@_N@Z()
13f7c9583    mysqld.exe!?mysql_parse@@YAXPEAVTHD@@PEAVParser_state@@@Z()
13f7c274d    mysqld.exe!?dispatch_command@@YA_NPEAVTHD@@PEBTCOM_DATA@@W4enum_server_command@@@Z()
13f7c375a    mysqld.exe!?do_command@@YA_NPEAVTHD@@@Z()
13f78a10c    mysqld.exe!handle_connection()
1401b59f2    mysqld.exe!?reserve@?$vector@EV?$allocator@E@std@@@std@@QEAAX_K@Z()
13fece0cb    mysqld.exe!my_thread_once()
140277eff    mysqld.exe!my_wildcmp_mb()
14027814a    mysqld.exe!my_wildcmp_mb()
770459cd    kernel32.dll!BaseThreadInitThunk()
7717a561    ntdll.dll!RtlUserThreadStart()

Please provide a repeatable test case. Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

We faced the same issue on MySQL 5.7.16 on Windows Server 2012

Sorry, my previous comment contained a typo.

This is a correct duplicate:

https://bugs.mysql.com/bug.php?id=88170