MySQL Bugs: #114756: The purge thread cause the server crash

Bug #114756	The purge thread cause the server crash
Submitted:	24 Apr 7:10	Modified:	24 Apr 9:55
Reporter:	xincheng xie	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.27	OS:	CentOS (CentOS Linux release 7.6.1810 (Core) )
Assigned to:		CPU Architecture:	Any (32 cores)
Tags:	compress, purge

Description:

Here is the error-log:

2024-04-23T08:28:23.967325+08:00 3081381 [ERROR] [MY-012869] [InnoDB] Record in index `idx_parent_site_code` of table `site_360`.`zto_mvp_salesman_times` was not found on update: TUPLE (info_bits=0, 2 n_cmp=2, fields): {[5]57101(0x3537313031),[8]    sSG2(0x0000000073534732)} at: COMPACT RECORD(info_bits=0, 1 fields): {[8]infimum (0x696e66696d756d00)}
2024-04-23T08:28:35.120148+08:00 3081458 [Warning] [MY-013745] [Server] Accepted a connection with deprecated protocol 'TLSv1.1' for account `zabbix_dba`@`%` from host `192.168.36.229`. Client supplied username `zabbix_dba`
2024-04-23T08:29:23.982401+08:00 0 [ERROR] [MY-012833] [InnoDB] tried to purge non-delete-marked record in index `idx_parent_site_code` of table `site_360`.`zto_mvp_salesman_times`: tuple: TUPLE (info_bits=0, 2 n_cmp=2, fields): {[5]57101(0x3537313031),[8]    sSG2(0x0000000073534732)}, record: COMPACT RECORD(info_bits=0, 2 fields): {[5]57101(0x3537313031),[8]    sSG2(0x0000000073534732)}
2024-04-23T08:29:24.006414+08:00 0 [ERROR] [MY-011853] [InnoDB] Corruption of an index tree: table `site_360`.`zto_mvp_salesman_times` index `idx_parent_site_code`, father ptr page no 290778, child page no 290783
PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 5; hex 3537313031; asc 57101;;
 1: len 8; hex 0000000073126f95; asc     s o ;;
PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 5; hex 3537313031; asc 57101;;
 1: len 8; hex 0000000070de1890; asc     p   ;;
 2: len 4; hex 00046fda; asc   o ;;
2024-04-23T08:29:24.025134+08:00 0 [ERROR] [MY-011854] [InnoDB] [FATAL] You should dump + drop + reimport the table to fix the corruption. If the crash happens at database startup. Please refer to http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html for information about forcing recovery. Then dump + drop + reimport.
2024-04-23T08:29:24.025745+08:00 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: btr0btr.cc:707:ib::fatal triggered thread 139737450100480
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.
00:29:24 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x7f18c9803000
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...
stack_bottom = 7f1729131330 thread_stack 0x80000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x1fc33ad]
/usr/sbin/mysqld(handle_fatal_signal+0x30b) [0xee902b]
/lib64/libpthread.so.0(+0xf630) [0x7f1b367bf630]
/lib64/libc.so.6(gsignal+0x37) [0x7f1b34d0a387]
/lib64/libc.so.6(abort+0x148) [0x7f1b34d0ba78]
/usr/sbin/mysqld() [0xc3378c]
/usr/sbin/mysqld() [0x22529df]
/usr/sbin/mysqld() [0x227bc60]
/usr/sbin/mysqld(btr_compress(btr_cur_t*, unsigned long, mtr_t*)+0x4fd) [0x227f01d]
/usr/sbin/mysqld(btr_cur_compress_if_useful(btr_cur_t*, unsigned long, mtr_t*)+0x15d) [0x228b6ed]
/usr/sbin/mysqld(btr_cur_pessimistic_delete(dberr_t*, unsigned long, btr_cur_t*, unsigned int, bool, unsigned long, unsigned long, unsigned long, mtr_t*, btr_pcur_t*, purge_node_t*)+0x21b) [0x228cd9b]
/usr/sbin/mysqld() [0x21cbc22]
/usr/sbin/mysqld(row_purge_step(que_thr_t*)+0x84e) [0x21cd93e]
/usr/sbin/mysqld(que_run_threads(que_thr_t*)+0x5d0) [0x21934b0]
/usr/sbin/mysqld(srv_worker_thread()+0x242) [0x2200cb2]
/usr/sbin/mysqld(std::thread::_State_impl<std::thread::_Invoker<std::tuple<Detached_thread, void (*)()> > >::_M_run()+0xc0) [0x2144d30]
/usr/sbin/mysqld() [0x2768100]
/lib64/libpthread.so.0(+0x7ea5) [0x7f1b367b7ea5]
/lib64/libc.so.6(clone+0x6d) [0x7f1b34dd29fd]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): Connection ID (thread ID): 0
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.

How to repeat:
Table structure:
CREATE TABLE `zto_mvp_salesman_times` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `manage_area_code` varchar(10) NOT NULL COMMENT '管理区的唯一编码',
  `manage_area_name` varchar(50) DEFAULT NULL ,
  `center_code` varchar(15) NOT NULL DEFAULT '' COMMENT '中心编码',
  `center_name` varchar(100) NOT NULL DEFAULT '' COMMENT '中心名称',
  `parent_site_code` varchar(50) DEFAULT NULL COMMENT '网点的所属的关联关系，父级网点编码。',
  `site_code` varchar(50) NOT NULL COMMENT '网点编码，全局唯一（如02100)，现有的网点编号规则是：支持3-6位数字、字母混组。',
  `site_name` varchar(45) NOT NULL COMMENT '网点：与中通有品牌加盟合作的服务点',
  `user_code` varchar(32) NOT NULL COMMENT '用户工号',
  `user_name` varchar(64) DEFAULT NULL COMMENT '用户名',
  `stat_date_type` tinyint NOT NULL COMMENT '0,日维度，1周维度',
  `stat_date_str` varchar(32) DEFAULT NULL COMMENT '时间(日-当日，周-当前周周一)',
  `pack_duration` decimal(20,6) DEFAULT NULL COMMENT '交件时长',
  `delivery_rate` decimal(20,6) DEFAULT NULL COMMENT '交件率',
  `disp_duration` decimal(20,6) DEFAULT NULL COMMENT '派件时长',
  `version` int NOT NULL DEFAULT '0' COMMENT '数据版本号',
  `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `gmt_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `creator` varchar(15) NOT NULL DEFAULT '' COMMENT '创建人',
  `modifier` varchar(15) NOT NULL DEFAULT '' COMMENT '修改人',
  `user_id` varchar(50) NOT NULL DEFAULT '' COMMENT '用户id',
  `sub_type` tinyint NOT NULL COMMENT '是否包含下级:0-否,1-是',
  PRIMARY KEY (`id`),
  KEY `idx_site_code` (`site_code`),
  KEY `idx_stat_date_str` (`stat_date_str`),
  KEY `idx_parent_site_code` (`parent_site_code`)
) ENGINE=InnoDB AUTO_INCREMENT=1947961668 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED COMMENT='mvp时效分析业务员数据表' 

When crash happen ,the last transaction is 
delete from zto_mvp_salesman_times where stat_date_type =2 and stat_date_str ='2024-03-01' limit 40000;
and do the same transaction many times

Hi Mr. xie,

Thank you for your bug report.

We have looked at your stacktrace and could not find a single verified bug report with similar stacktrace.

Hence, we can not proceed without getting a full test case. It should consist not only of the CREATE TABLE and DELETE statement, but it should also contain the necessary number of rows from that table that would lead to that stacktrace.

Since we do not have such a test case, we can not proceed.

Can't repeat.