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_dist 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)
