MySQL8.0 WITH 导致singal 11 案例分析

在使用MySQL 8.0.26时，遇到了与WITH通用表达式导致mysqld got signal 11频繁崩溃问题。
按照之前分享过的问题排查思路，阶梯式去查找和分析有用的信息。

下面分享分析问题的完整思路和技术内容。

1.查找问题

首先，需要查看MySQL的错误日志。这个日志文件通常包含了关于崩溃的详细信息，如堆栈跟踪、错误代码和导致崩溃的查询语句。

错误记录信息如下所示：

#错误1：
[Warning] [MY-011825] [InnoDB] Cannot add field `_sup_Agreement_Type_280` in table `tmp`.`#sql1b13_25d83b_1e` because after adding it, the row size is 8126 which is greater than maximum allowed size (8126) for a record on index leaf page.
15:16:08 UTC - mysqld got signal 11 ;
。。。
/usr/sbin/mysqld(temptable::Handler::index_init(unsigned int, bool)+0xf) [0x253e98f]
。。。
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f887c428210): select count(0) from ( with tmp1 a...

#错误2：
11:21:10 UTC - mysqld got signal 11 ;
。。。
/usr/sbin/mysqld(TemptableAggregateIterator::Init()+0xde) [0x12b71fe]
。。。
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f241669d340): with pecom as ( ...


从上述错误中有3个关键点信息：
1.signal 11。
2.Temptable相关处理信息。
3.WITH表达式SQL语句。

2.分析过程

问题核心点已经找到，那就需要结合知识点进行进一步的分析。

1）singal 11

信号(signal)是一种软件中断，它提供了一种处理异步事件的方法，也是进程间惟一的异步通信方式。在Linux系统中，根据POSIX标准扩展以后的信号机制，不仅可以用来通知某种程序发生了什么事件，还可以给进程传递数据。

信号的来源可以有很多种试，按照产生条件的不同可以分为硬件和软件两种。
1、硬件方式
当用户在终端上按下某键时，将产生信号。如按下组合键后将产生一个SIGINT信号。硬件异常产生信号：除数据、无效的存储访问等。这些事件通常由硬件(如:CPU)检测到，并将其通知给Linux操作系统内核，然后内核生成相应的信号，并把信号发送给该事件发生时正在进行的程序。
2、软件方式
用户在终端下调用kill命令向进程发送任务信号。进程调用kill或sigqueue函数发送信号。

在Linux中，kill命令用于发送信号给进程。可以使用kill --help来查看关于kill命令的帮助信息。信号列表如下：

shell#> kill -l
[root@schouse kevindba]# kill -l
1) SIGHUP 2) SIGINT 3) SIGQUIT 4) SIGILL 5) SIGTRAP
6) SIGABRT 7) SIGBUS 8) SIGFPE 9) SIGKILL 10) SIGUSR1
11) SIGSEGV 12) SIGUSR2 13) SIGPIPE 14) SIGALRM 15) SIGTERM
16) SIGSTKFLT 17) SIGCHLD 18) SIGCONT 19) SIGSTOP 20) SIGTSTP
21) SIGTTIN 22) SIGTTOU 23) SIGURG 24) SIGXCPU 25) SIGXFSZ
26) SIGVTALRM 27) SIGPROF 28) SIGWINCH 29) SIGIO 30) SIGPWR
31) SIGSYS 34) SIGRTMIN 35) SIGRTMIN+1 36) SIGRTMIN+2 37) SIGRTMIN+3
38) SIGRTMIN+4 39) SIGRTMIN+5 40) SIGRTMIN+6 41) SIGRTMIN+7 42) SIGRTMIN+8
43) SIGRTMIN+9 44) SIGRTMIN+10 45) SIGRTMIN+11 46) SIGRTMIN+12 47) SIGRTMIN+13
48) SIGRTMIN+14 49) SIGRTMIN+15 50) SIGRTMAX-14 51) SIGRTMAX-13 52) SIGRTMAX-12
53) SIGRTMAX-11 54) SIGRTMAX-10 55) SIGRTMAX-9 56) SIGRTMAX-8 57) SIGRTMAX-7
58) SIGRTMAX-6 59) SIGRTMAX-5 60) SIGRTMAX-4 61) SIGRTMAX-3 62) SIGRTMAX-2
63) SIGRTMAX-1 64) SIGRTMAX


信号量编号还是挺多的。但在MySQL日常故障中，常出现的信号量有signal 6,7,8，11。这些信号量都会导致数据库服务崩溃。
其中signal 11最常见的。mysqld got signal 11表示MySQL服务器进程（mysqld）接收到了一个 SIGSEGV 信号，这是一个指示内存访问违规的错误。通常，这意味着mysqld尝试访问不属于它的内存区域，或者试图写入只读内存区域。

备注：在Linux中查看信号量命令行是ipcs

2）Temptable相关处理信息

MySQL临时表在很多场景中都会用到，比如用户自己创建的临时表用于保存临时数据，以及MySQL内部在执行复杂SQL时，需要借助临时表进行分组、排序、去重等操作。如图所示：

错误日志中记录对临时表的操作信息如下：

[Warning] [MY-011825] [InnoDB] Cannot add field `_sup_Agreement_Type_280`
in table `tmp`.`#sql1b13_25d83b_1e` because after adding it, the row size is 8126
which is greater than maximum allowed size (8126) for a record on index leaf page.

/usr/sbin/mysqld(TemptableAggregateIterator::Init()+0xde) [0x12b71fe]


• tmp.#sql1b13_25d83b_1e：是对于innodb_temp_tablespaces_dir参数配置的临时文件。
• TempTableAggregateIterator：在MySQL实现中，属于内部迭代器或者执行计划中的一部分，用于处理临时表和聚合操作。这通常表明 MySQL 正在使用一种策略来处理复杂的聚合操作。特别是涉及到 GROUP BY 子句或需要进行聚合运算（如 SUM, COUNT, AVG 等）时，可能需要用到临时表来存储中间结果。
• greater than maximum allowed size (8126)：单行的数据记录，不操过8126字节。在InnoDB引擎下的限制。

3）WITH表达式

MySQL8.0支持公共表表达式common table expression（CTE）。CTE是临时结果集，存在于单个语句的范围内，也可以在该语句中引用。在很多场景下，可以简化复杂的嵌套查询。CTE类似于使用CREATE[TEMPORARY]TABLE创建的表，CTE实际成本也可能受到结果集大小的影响。产生许多行的CTE可能需要一个足够大的内部临时表，以便从内存格式转换为磁盘格式，并且可能会受到性能损失。
从结合WITH语句触发和下面错误日志。可以确定临时结果集处理存在问题。

4） 临时缓存落盘机制

临时缓存落盘机制是 当会话临时表超过设置大小的时，就会落盘。（在MySQL 8.0.15及更早版本中，internal_tmp_disk_storage_engine参数定义了用于磁盘上内部临时表的存储引擎。支持的存储引擎是InnoDB和MyISAM。从MySQL 8.0.16开始，MySQL只对磁盘上的内部临时表使用InnoDB存储引擎。不再支持MYISAM存储引擎用于此目的。）就是说临时表落磁盘，必定是InnoDB临时表引擎。那不想碰到如下问题，就需要保存在内存中。其中internal_tmp_mem_storage_engine决定内存中的引擎类型（MEMORY，INNODB引擎，tmp_table_size 和 max_heap_table_size 控制内存临时表限制。

5） bug

搜索MySQL的官方Bug列表和社区论坛，查看是否有其他人报告了类似的问题。能提供一些分析思路和说明。

分析结论

从上述分析中可以执行得到如下结论。在使用WITH表达式时，因为用到了大量的临时表，导致数据落盘，临时表缓存在内存里使用MEMORY引擎，底层临时表引擎因为是InnoDB引擎，在处理过程中因为受到InnoDB引擎的限制导致崩溃情况发生。
当然解决这方面问题可以采取

• 1.整改WITH 语句，避免使用是临时表。
• 2.同时可以更改临时表参数tmp_table_size，max_heap_table_size大小。

总结

在MySQL运维当中，碰到疑难杂时，需要综合使用多种方法和技术。通过收集和分析崩溃日志、复现和简化问题、检查查询、分析MySQL配置和状态、查找已知问题，以及联系支持或寻求帮助，能够逐步缩小问题范围并最终找到解决方案。
在MySQL的运维中，遇到复杂或棘手的问题时，确实需要综合运用多种方法和技术，逐步缩小问题范围并最终找到解决方案。重要的是保持耐心和细心，不要急于下结论或进行可能导致更多问题的操作。