mysql自动化错误怎么办（故障分析一条）

逗爷 2023-06-18 16:52:20 991

mysql自动化错误怎么办（故障分析一条）1、查看 OS 日志以及系统资源使用情况：2、数据库发出的信号为 signal 11 ，即是 MySQL 访问到了一个错误的内存地址。一般来讲，只要数据库崩了，那么错误日志一定会留下线索的，先来看下具体的报错：06:08:23 UTC - mySQLd got signal 11 ; MOSt likely you have hit a bug but this error can also be caused by malfunctioning hardware. Thread pointer: 0x7f55ac0008c0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this somet

作者：刘开洋

爱可生交付服务团队北京 DBA，对数据库及周边技术有浓厚的学习兴趣，喜欢看书，追求技术。

本文来源：原创投稿

*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

在很多疑难问题的排查中，小编最近又遇到了一个 select 语句执行就会导致 MySQL 崩溃的问题，特来分享给大家。

先看下报错

一般来讲，只要数据库崩了，那么错误日志一定会留下线索的，先来看下具体的报错：

06:08:23 UTC - mySQLd got signal 11 ; MOSt likely you have hit a bug but this error can also be caused by malfunctioning hardware. Thread pointer: 0x7f55ac0008c0 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 = 7f56f4074d80 thread_stack 0x46000 /usr/local/mysql/bin/mysqld(my_print_stacktrace(unsigned char const* unsigned long) 0x2e) [0x1f1b71e] /usr/local/mysql/bin/mysqld(handle_fatal_signal 0x323) [0xfcfac3] /lib64/libpthread.so.0( 0xf630) [0x7f5c28c85630] /usr/local/mysql/bin/mysqld(actual_key_parts(KEY const*) 0xa) [0xef55ca] /usr/local/mysql/bin/mysqld(calculate_key_len(TABLE* unsigned int unsigned long) 0x28) [0x10da428] /usr/local/mysql/bin/mysqld(handler::ha_index_read_map(unsigned char* unsigned char const* unsigned long ha_rkey_function) 0x261) [0x10dac51] /usr/local/mysql/bin/mysqld(check_unique_constraint(TABLE*) 0xa3) [0xe620e3] /usr/local/mysql/bin/mysqld(do_sj_dups_weedout(THD* SJ_TMP_TABLE*) 0x111) [0xe62361] /usr/local/mysql/bin/mysqld(WeedoutIterator::Read() 0xa9) [0x1084cd9] /usr/local/mysql/bin/mysqld(MaterializeIterator::MaterializeQueryBlock(MaterializeIterator::QueryBlock const& unsigned long long*) 0x17c) [0x10898bc] /usr/local/mysql/bin/mysqld(MaterializeIterator::Init() 0x1e1) [0x108a021] /usr/local/mysql/bin/mysqld(SELECT_LEX_UNIT::ExecuteIteratorQuery(THD*) 0x251) [0xf5d241] /usr/local/mysql/bin/mysqld(SELECT_LEX_UNIT::execute(THD*) 0xf9) [0xf5f3f9] /usr/local/mysql/bin/mysqld(Sql_cmd_dml::execute_inner(THD*) 0x20b) [0xeedf8b] /usr/local/mysql/bin/mysqld(Sql_cmd_dml::execute(THD*) 0x3e8) [0xef7418] /usr/local/mysql/bin/mysqld(mysql_execute_command(THD* bool) 0x39c9) [0xeab3a9] /usr/local/mysql/bin/mysqld(mysql_parse(THD* Parser_state*) 0x31c) [0xead0cc] /usr/local/mysql/bin/mysqld(dispatch_command(THD* COM_DATA const* enum_server_command) 0x156b) [0xeaeb6b] /usr/local/mysql/bin/mysqld(do_command(THD*) 0x174) [0xeb0104] /usr/local/mysql/bin/mysqld() [0xfc1a08] /usr/local/mysql/bin/mysqld() [0x23ffdec] /lib64/libpthread.so.0( 0x7ea5) [0x7f5c28c7dea5] /lib64/libc.so.6(clone 0x6d) [0x7f5c26db9b0d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (7f55ac0ca298): SELECT DISTINCT T.CUST_NO FROM testDB.TABLE_TRANSACTION T WHERE EXISTS (SELECT 1 FROM testDB.Table1 T1 WHERE T.CUST_NO = T1.CUST_NO ) AND T.AGENT_CERT_NO IS NOT NULL Connection ID (thread ID): 65 Status: NOT_KILLED

从上述错误日志的输出中可以找到较为明显的几处信息：

1、导致崩溃的 SQL 语句为：SELECT DISTINCT T.CUST_NO FROM testDB.TABLE_TRANSACTION T WHERE EXISTS (SELECT 1 FROM testDB.Table1 T1 WHERE T.CUST_NO = T1.CUST_NO ) AND T.AGENT_CERT_NO IS NOT NULL

2、数据库发出的信号为 signal 11 ，即是 MySQL 访问到了一个错误的内存地址。

分析过程

1、查看 OS 日志以及系统资源使用情况：

OS 日志的输出对排查方向没有影响，无 Mysql OOM 的现象。

查看监控在 MySQL 崩溃时间段没有任何异常输出，且任何时候都可以在环境中执行 select 触发数据库 crash 。

2、从业务一侧获取完整的 SQL 以及表结构信息。

# 完整的SQL语句： SELECT 'testPA' AS INDIC_KEY A.CUST_NO AS OBJ_KEY CASE WHEN B.CUST_NO IS NULL THEN 1 ELSE END AS INDICVAL1 '2222-06-06' AS GRADING_DATE FROM testDB.Table1 A LEFT JOIN ( SELECT DISTINCT T.CUST_NO FROM testDB.TABLE_TRANSACTION T WHERE EXISTS (SELECT 1 FROM testDB.Table1 T1 WHERE T.CUST_NO = T1.CUST_NO) AND T.AGENT_CERT_NO IS NOT NULL ) B ON A.CUST_NO = B.CUST_NO; # 表结构 CREATE TABLE `TABLE_TRANSACTION` ( `cert_key` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL `cust_no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL ··· CREATE TABLE `Table1` ( `CUST_NO` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ···

3、查看该 select 语句的执行计划

mysql自动化错误怎么办（故障分析一条）(1)