MySQL Bugs: #109342: Randomly crashes and stops mysql server

Bug #109342	Randomly crashes and stops mysql server
Submitted:	12 Dec 2022 14:13	Modified:	14 Dec 2022 23:10
Reporter:	Juan Prado	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.31	OS:	Windows (Server 2016 Standard)
Assigned to:		CPU Architecture:	Other (Xeon CPU E5-2670 (2 processors))
Tags:	Ram 192 GB

Description:
Randomly mysql server crashes and ends execution, it may spend several days working fine and then mysql server crashes again.

I have gone through the configuration parameters and applied some adjustments depending on the server load, but the mysql server is still having problems.

As a background, this started to happen with the latest updates of the mysql server, before the operation was continuous, and only stopped when some maintenance activity had to be carried out on the windows server.

How to repeat:
Timestamp, Thread, Type, Details
2022-12-12 08:42:20.946433, 34, [ERROR] [MY-013183] [InnoDB]

Assertion failure: que0que.cc:503 thread 34656
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
2022-12-12T11:42:20Z UTC - mysqld got exception 0x16 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x197165b4160
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...
7ff66120fa68    mysqld.exe!?my_print_stacktrace@@YAXPEBEK@Z()
7ff660399411    mysqld.exe!?print_fatal_signal@@YAXH@Z()
7ff660399153    mysqld.exe!?my_server_abort@@YAXXZ()
7ff6611f3a7a    mysqld.exe!?my_abort@@YAXXZ()
7ff66144c7f9    mysqld.exe!?deallocate@?$allocator@V?$sub_match@V?$_String_const_iterator@V?$_String_val@U?$_Simple_types@D@std@@@std@@@std@@@std@@@std@@QEAAXQEAV?$sub_match@V?$_String_const_iterator@V?$_String_val@U?$_Simple_types@D@std@@@std@@@std@@@2@_K@Z() [truncated, 260 bytes total]
7ff6615485a3    mysqld.exe!?deallocate@?$allocator@V?$sub_match@V?$_String_const_iterator@V?$_String_val@U?$_Simple_types@D@std@@@std@@@std@@@std@@@std@@QEAAXQEAV?$sub_match@V?$_String_const_iterator@V?$_String_val@U?$_Simple_types@D@std@@@std@@@std@@@2@_K@Z() [truncated, 260 bytes total]
7ff66154835f    mysqld.exe!?deallocate@?$allocator@V?$sub_match@V?$_String_const_iterator@V?$_String_val@U?$_Simple_types@D@std@@@std@@@std@@@std@@@std@@QEAAXQEAV?$sub_match@V?$_String_const_iterator@V?$_String_val@U?$_Simple_types@D@std@@@std@@@std@@@2@_K@Z() [truncated, 260 bytes total]
7ff6613be3c8    mysqld.exe!?deallocate@?$allocator@V?$sub_match@V?$_String_const_iterator@V?$_String_val@U?$_Simple_types@D@std@@@std@@@std@@@std@@@std@@QEAAXQEAV?$sub_match@V?$_String_const_iterator@V?$_String_val@U?$_Simple_types@D@std@@@std@@@std@@@2@_K@Z() [truncated, 260 bytes total]
7ff6612d0244    mysqld.exe!?set_compression_level@Zstd_comp@compression@transaction@binary_log@@UEAAXI@Z()
7ff660205fe5    mysqld.exe!?closefrm@@YAHPEAUTABLE@@_N@Z()
7ff66031f5c3    mysqld.exe!?intern_close_table@@YAXPEAUTABLE@@@Z()
7ff66031d59d    mysqld.exe!?free_unused_tables_if_necessary@Table_cache@@AEAAXPEAVTHD@@@Z()
7ff660317e5e    mysqld.exe!?add_used_table@Table_cache@@QEAA_NPEAVTHD@@PEAUTABLE@@@Z()
7ff6603236fe    mysqld.exe!?open_table@@YA_NPEAVTHD@@PEAUTABLE_LIST@@PEAVOpen_table_context@@@Z()
7ff660321ee7    mysqld.exe!?open_and_lock_tables@@YA_NPEAVTHD@@PEAUTABLE_LIST@@IPEAVPrelocking_strategy@@@Z()
7ff660324595    mysqld.exe!?open_tables@@YA_NPEAVTHD@@PEAPEAUTABLE_LIST@@PEAIIPEAVPrelocking_strategy@@@Z()
7ff660324a9a    mysqld.exe!?open_tables_for_query@@YA_NPEAVTHD@@PEAUTABLE_LIST@@I@Z()
7ff6604581f1    mysqld.exe!?prepare@Sql_cmd_dml@@UEAA_NPEAVTHD@@@Z()
7ff660454d57    mysqld.exe!?execute@Sql_cmd_dml@@UEAA_NPEAVTHD@@@Z()
7ff66035276a    mysqld.exe!?mysql_execute_command@@YAHPEAVTHD@@_N@Z()
7ff66034d581    mysqld.exe!?dispatch_sql_command@@YAXPEAVTHD@@PEAVParser_state@@@Z()
7ff66034c2b5    mysqld.exe!?dispatch_command@@YA_NPEAVTHD@@PEBTCOM_DATA@@W4enum_server_command@@@Z()
7ff66034d946    mysqld.exe!?do_command@@YA_NPEAVTHD@@@Z()
7ff660179178    mysqld.exe!?thread_id@THD@@QEBAIXZ()
7ff661805b79    mysqld.exe!?my_init_dynamic_array@@YA_NPEAUDYNAMIC_ARRAY@@IIPEAXII@Z()
7ff6612005ac    mysqld.exe!?my_thread_self_setname@@YAXPEBD@Z()
7fffff8dfb80    ucrtbase.dll!_o__realloc_base()
7ff8023c84d4    KERNEL32.DLL!BaseThreadInitThunk()
7ff8024c1791    ntdll.dll!RtlUserThreadStart()
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (1bc504b0570): SELECT distinct date_format(od.dt_tracker,'%Y-%m-%d %H:%i:%s') dt_tracker,  od.lat, od.lng, od.speed, od.angle, od.altitude,  DATE_FORMAT(od.dt_server, '%Y-%m-%d %H:%i:%s') dt_server_tracker,  od.dt_tracker dt_tracker_position,  od.dt_server dt_server_position,  if(json_valid(`od`.`params`),COALESCE(json_unquote(json_extract(`od`.`params`, '$.io1')),'0'), '0') AS ignicion1,  if(json_valid(`od`.`params`),COALESCE(json_unquote(json_extract(`od`.`params`, '$.acc')),'0'), '0') AS ignicion2,  if(json_valid(`od`.`params`),COALESCE(json_unquote(json_extract(`od`.`params`, '$.gpslev')),'-1'), '-1') AS gpslev  FROM gs.gs_object_data_352093088718365 od INNER JOIN is.objects_wisetrack_centinela o ON ('352093088718365' = o.imei)  AND (od.dt_server > o.update_date)  AND (od.dt_tracker != '0000-00-00 00:00:00')  WHERE (o.state = 'A') and od.speed <=120 order by od.dt_tracker [truncated, 894 bytes total]
Connection ID (thread ID): 34
Status: NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

Suggested fix:
A workaround, I have applied these two actions (in the following order):

1.- start the mysql server again
2.- restart the windows server (when the first step fails)

Hi Mr. Prado,

Thank you for your bug report.

We have already received a number of identical reports.

It is clear, from the stacktrace, that a bug occurs in Microsoft's STL library. Hence, you should report this to Microsoft.  It is a problem due to the very frequent usage of the recursion in their library.

A workaround is to increase thread_stack , which solves a problem.

We are not able to repeat it and you have not provided a fully repeatable test case.

Hi Mysql 

I have changed the thread_stack parameter:

Before:
thread_stack = 1048576

Now:
thread_stack = 4194304

The mysql server has been up for a few days, but today it went down again.

What could be the problem? I will upload the my.ini file on the page

The error log is as follows:

Timestamp, Thread, Type, Details
2022-12-14 14:28:27.804632, 0, [Warning] [MY-010055] [Server], IP address '45.227.254.53' could not be resolved: This is usually a temporary error during hostname resolution and means that the local server did not receive a response from an authoritative server.
2022-12-14 14:28:27.804631, 0, [Warning] [MY-010055] [Server], IP address '45.227.254.53' could not be resolved: This is usually a temporary error during hostname resolution and means that the local server did not receive a response from an authoritative server.
2022-12-14 14:28:32.812328, 0, [Warning] [MY-010055] [Server], IP address '45.227.254.53' could not be resolved: This is usually a temporary error during hostname resolution and means that the local server did not receive a response from an authoritative server.
, , , 2022-12-14T22:47:27Z UTC - mysqld got exception 0xc0000005 ;
, , , Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
, , , Thread pointer: 0x0
, , , 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...
, , , 7ffd0ac5b496    jemalloc.dll!???
, , , 7ffd0ac5b918    jemalloc.dll!???
, , , 7ffd0ac5ef2e    jemalloc.dll!je_realloc()
, , , 7ffd0bc36cdd    libcrypto-1_1-x64.dll!CRYPTO_memcmp()
, , , 7ffd0bc7abd2    libcrypto-1_1-x64.dll!CRYPTO_memcmp()
, , , 7ffd0bc7d6e3    libcrypto-1_1-x64.dll!CRYPTO_memcmp()
, , , 7ffd0bc4846d    libcrypto-1_1-x64.dll!CRYPTO_memcmp()
, , , 7ffd0bb512ec    libcrypto-1_1-x64.dll!CRYPTO_memcmp()
, , , 7ffd0bcf827f    libcrypto-1_1-x64.dll!CRYPTO_memcmp()
, , , 7ffd18a785af    ntdll.dll!RtlDeactivateActivationContextUnsafeFast()
, , , 7ffd18a75c41    ntdll.dll!LdrShutdownThread()
, , , 7ffd18ab238e    ntdll.dll!RtlExitUserThread()
, , , 7ffd1595af93    KERNELBASE.dll!FreeLibraryAndExitThread()
, , , 7ffd15d7fd04    ucrtbase.dll!_o__realloc_base()
, , , 7ffd15dd0259    ucrtbase.dll!_endthreadex()
, , , 7ff788e59245    mysqld.exe!?thread_id@THD@@QEBAIXZ()
, , , 7ff78a4e5b79    mysqld.exe!?my_init_dynamic_array@@YA_NPEAUDYNAMIC_ARRAY@@IIPEAXII@Z()
, , , 7ff789ee05ac    mysqld.exe!?my_thread_self_setname@@YAXPEBD@Z()
, , , 7ffd15d7fb80    ucrtbase.dll!_o__realloc_base()
, , , 7ffd161384d4    KERNEL32.DLL!BaseThreadInitThunk()
, , , 7ffd18ab1791    ntdll.dll!RtlUserThreadStart()
, , , The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
, , , information that should help you find out what is causing the crash.

Hi Mr. Prado,

Yes, that is yet another very well known Windows bug.

You have to rename jemalloc library and restart MySQL server .....

You should report both bugs to Microsoft.