mysql8启动报错错误代码[ERROR] [MY-013183] [InnoDB] Assertion failure: mtr0log.cc:652:total > (ulint)(log_ptr - log_start) thread 8360

mysql8启动报错错误代码[ERROR] [MY-013183] [InnoDB] Assertion failure: mtr0log.cc:652:total > (ulint)(log_ptr - log_start) thread 8360

现象：

在mysql8突然连接不上之后，对mysql8进行排查。

步骤：

1、找到错误日志（.err为后缀的文件）。对日志进行追踪排查。

发现如下代码

2024-03-06T06:17:20.415739Z 131 [ERROR] [MY-013183] [InnoDB] Assertion failure: mtr0log.cc:652:total > (ulint)(log_ptr - log_start) thread 14288
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.4/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
2024-03-06T06:17: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: 0x1ca449d1490
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...
7ff6be098258    mysqld.exe!?my_print_stacktrace@@YAXPEBEK@Z()
7ff6bd25a731    mysqld.exe!?my_server_abort@@YAXXZ()
7ff6bd25a473    mysqld.exe!?my_server_abort@@YAXXZ()
7ff6be082b4a    mysqld.exe!?my_abort@@YAXXZ()
7ff6be253339    mysqld.exe!?zstd_mem_res_free@Zstd_dec@compression@event@binlog@mysql@@CAXPEAX0@Z()
7ff6be352e71    mysqld.exe!?zstd_mem_res_free@Zstd_dec@compression@event@binlog@mysql@@CAXPEAX0@Z()
7ff6be352b8e    mysqld.exe!?zstd_mem_res_free@Zstd_dec@compression@event@binlog@mysql@@CAXPEAX0@Z()
7ff6be353954    mysqld.exe!?zstd_mem_res_free@Zstd_dec@compression@event@binlog@mysql@@CAXPEAX0@Z()
7ff6be3bd8a5    mysqld.exe!?zstd_mem_res_free@Zstd_dec@compression@event@binlog@mysql@@CAXPEAX0@Z()
7ff6be37ba59    mysqld.exe!?zstd_mem_res_free@Zstd_dec@compression@event@binlog@mysql@@CAXPEAX0@Z()
7ff6be37e4e9    mysqld.exe!?zstd_mem_res_free@Zstd_dec@compression@event@binlog@mysql@@CAXPEAX0@Z()
7ff6be2abef0    mysqld.exe!?zstd_mem_res_free@Zstd_dec@compression@event@binlog@mysql@@CAXPEAX0@Z()
7ff6be2b56b2    mysqld.exe!?zstd_mem_res_free@Zstd_dec@compression@event@binlog@mysql@@CAXPEAX0@Z()
7ff6be361e5b    mysqld.exe!?zstd_mem_res_free@Zstd_dec@compression@event@binlog@mysql@@CAXPEAX0@Z()
7ff6be361645    mysqld.exe!?zstd_mem_res_free@Zstd_dec@compression@event@binlog@mysql@@CAXPEAX0@Z()
7ff6be36419d    mysqld.exe!?zstd_mem_res_free@Zstd_dec@compression@event@binlog@mysql@@CAXPEAX0@Z()
7ff6be35fcfc    mysqld.exe!?zstd_mem_res_free@Zstd_dec@compression@event@binlog@mysql@@CAXPEAX0@Z()
7ff6be365da6    mysqld.exe!?zstd_mem_res_free@Zstd_dec@compression@event@binlog@mysql@@CAXPEAX0@Z()
7ff6be1472f0    mysqld.exe!?zstd_mem_res_free@Zstd_dec@compression@event@binlog@mysql@@CAXPEAX0@Z()
7ff6be0fdc48    mysqld.exe!?zstd_mem_res_free@Zstd_dec@compression@event@binlog@mysql@@CAXPEAX0@Z()
7ff6bd0ce323    mysqld.exe!?ha_write_row@handler@@QEAAHPEAE@Z()
7ff6bd5c41ac    mysqld.exe!?write_record@@YA_NPEAVTHD@@PEAUTABLE@@PEAVCOPY_INFO@@2@Z()
7ff6bd5c0c1e    mysqld.exe!?execute_inner@Sql_cmd_insert_values@@MEAA_NPEAVTHD@@@Z()
7ff6bd3eddce    mysqld.exe!?execute@Sql_cmd_dml@@UEAA_NPEAVTHD@@@Z()
7ff6bd2b2aaf    mysqld.exe!?mysql_execute_command@@YAHPEAVTHD@@_N@Z()
7ff6bd2ad5e3    mysqld.exe!?dispatch_sql_command@@YAXPEAVTHD@@PEAVParser_state@@@Z()
7ff6bd2acacc    mysqld.exe!?dispatch_command@@YA_NPEAVTHD@@PEBTCOM_DATA@@W4enum_server_command@@@Z()
7ff6bd2ad9e4    mysqld.exe!?do_command@@YA_NPEAVTHD@@@Z()
7ff6bd0c3638    mysqld.exe!?thread_id@THD@@QEBAIXZ()
7ff6be63b1f9    mysqld.exe!?init_state_maps@@YA_NPEAVMY_CHARSET_LOADER@@PEAUCHARSET_INFO@@@Z()
7ff6be08c39c    mysqld.exe!?my_thread_self_setname@@YAXPEBD@Z()
7ffed9371bb2    ucrtbase.dll!_configthreadlocale()
7ffed9517614    KERNEL32.DLL!BaseThreadInitThunk()
7ffedb4e26a1    ntdll.dll!RtlUserThreadStart()

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (1ca69c70630): INSERT INTO t_prop_con_cmd  ( id,
code,
msg_id,
valid,

conn_cmd,
rud_cmd,
acc_cmd,


buc_cmd,
create_time,

create_user )  VALUES  ( '247111092181286919',
2,
0,
1,

0,
1000,
1000,


0,
'2024-03-06 06:17:20.364',

'admin' )
Connection ID (thread ID): 131
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.
2024-03-06T08:21:21.291398Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2024-03-06T08:21:21.377231Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.4\bin\mysqld.exe (mysqld 8.4.0) starting as process 5444
2024-03-06T08:21:21.417922Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-03-06T08:21:22.608914Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-03-06T08:21:23.135017Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
2024-03-06T08:21:23.140023Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
2024-03-06T08:21:23.144108Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: mtr0log.cc:652:total > (ulint)(log_ptr - log_start) thread 10040
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.4/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
2024-03-06T08:21:23Z UTC - mysqld got exception 0x16 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x209f2adf470
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...
7ff6be098258    mysqld.exe!?my_print_stacktrace@@YAXPEBEK@Z()
7ff6bd25a731    mysqld.exe!?my_server_abort@@YAXXZ()
7ff6bd25a473    mysqld.exe!?my_server_abort@@YAXXZ()
7ff6be082b4a    mysqld.exe!?my_abort@@YAXXZ()
7ff6be253339    mysqld.exe!?zstd_mem_res_free@Zstd_dec@compression@event@binlog@mysql@@CAXPEAX0@Z()

关键错误有两处：

1、sql插入错误：

这里就是导致断言问题所在

第二处断言问题：

断言导致mysql8 启动不了。

解决办法

从网上查找各种断言处理办法：解决问题步骤

1、使用innodb_force_recovery进行强制恢复启动。

设置参数

[mysqld]
innodb_force_recovery=3  #(这里我使用的是3启动的)

1 (SRV_FORCE_IGNORE_CORRUPT): 忽略检查到的 corrupt 页。尽管检测到了损坏的 page 仍强制服务运行。一般设置为该值即可，然后 dump 出库表进行重建。
2(SRV_FORCE_NO_BACKGROUND): 阻止主线程的运行，如主线程需要执行 full purge 操作，会导致 crash。 阻止 master thread 和任何 purge thread 运行。若 crash 发生在 purge 环节则使用该值。
3(SRV_FORCE_NO_TRX_UNDO): 不执行事务回滚操作。
4(SRV_FORCE_NO_IBUF_MERGE): 不执行插入缓冲的合并操作。如果可能导致崩溃则不要做这些操作。不要进行统计操作。该值可能永久损坏数据文件。若使用了该值，则将来要删除和重建辅助索引。
5(SRV_FORCE_NO_UNDO_LOG_SCAN): 不查看重做日志，InnoDB 存储引擎会将未提交的事务视为已提交。此时 InnoDB 甚至把未完成的事务按照提交处理。该值可能永久性的损坏数据文件。
6(SRV_FORCE_NO_LOG_REDO): 不执行前滚的操作。恢复时不做 redo log roll-forward。使数据库页处于废止状态，继而可能引起 B 树或者其他数据库结构更多的损坏。

2、设置参数之后进行数据库启动

将对应的报错数据表进行导出备份，然后在data文件下将相应的数据文件夹删除。

3、将my.ini 配置文件中innodb_force_recovery=3 注释掉，然后进行启动

4、将导出的数据再进行导入。