关于编译:故障分析-MySQL-57-使用临时表导致数据库-Crash

作者：雷文霆

爱可生华东交付服务部 DBA 成员，次要负责Mysql故障解决及相干技术支持。喜好看书，电影。座右铭，每一个未曾起舞的日子，都是对生命的辜负。

本文起源：原创投稿

*爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

目录

• 背景信息
• 故障剖析
• 问题复现
• 测试日志
• 测试论断
• 参考链接

背景信息

在 MySQL5.7.30 主从读写拆散环境下，从库在某天呈现了 MySQL crash.

零碎侧: 监控显示该从库主机的内存和CPU资源使用率在故障前后均失常，磁盘IO有2%的iowait（读写200M/s），阐明故障前磁盘存在压力。

服务侧：slow-log 中记录了服务重启前，存在应用了长期表和文件排序的慢 SQL 语句。

Error-log 中记录了服务调用到 btr0btr.cc 文件 的 L2165 行，呈现了 err == DB_SUCCESS 报错。

0x7f2dd49d0700  InnoDB: Assertion failure in thread 139834817316608 in file btr0btr.cc line 2165InnoDB: Failing assertion: err == DB_SUCCESSInnoDB: We intentionally generate a memory trap.InnoDB: Submit a detailed bug report to http://bugs.mysql.com.

故障剖析

在零碎侧排除了磁盘空间有余和内存不足的因素，服务侧狐疑是慢查问和BUG的起因，之后通过" btr0btr.cc"关键字查找到了一个相似的 BUG 。链接如下:

https://bugs.mysql.com/bug.ph...

报告的意思是，MySQL 在执行 btr_insert_on_non_leaf_level_func()函数时，写入长期表会导致带有断言的服务解体。

通过查看 btr0btr.cc 文件结尾的正文理解到的起因是：

此文件的调用机制是：对b树行操作或记录所做的所有更改。

L2165 行操作内容是：在解决插入到非叶级别的内容时，会查看每个级别的可用空间(需保留2倍索引数高度的页空间)，如果在操作之前，叶决裂曾经开始，就很难撤销，只能通过解体进行前滚。该 BUG 只会在 MySQL5.7 呈现

代码查问：https://github.com/mysql/mysq... (通过 Tags 标签抉择对应版本)

代码内容：https://github.com/mysql/mysq...

nserts a data tuple to a tree on a non-leaf level. It is assumedthat mtr holds an x-latch on the tree. */voidbtr_insert_on_non_leaf_level_func()    ---    ut_a(err == DB_SUCCESS);    ---        其中ut_a()https://dev.mysql.com/doc/dev/mysql-server/latest/ut0dbg_8h.html#ae7aed983dfe98ac872b5a9915fa778fa:

查看数据库对于长期表的参数：

innodb_temp_data_file_path ibtmp1:12M:autoextend:max:20G

tmp_table_size 64M 和 max_heap_table_size 64M

正文：理论限度取二者中的较小者。会话级别的参数，对于 innodb_buffer_pool_size 不大且没有用到长期大数据量查问的状况，不倡议设置的过大，可能会导致内存溢出的状况。连接数800+，64M为推荐值

internal_tmp_disk_storage_engine InnoDB

正文：用于定义磁盘长期表的存储引擎。超出 InnoDB 行或列限度的磁盘外部长期表的查问会返回 Row size too large 或 Too many columns 谬误。解决办法是设置 internal_tmp_disk_storage_engine 为 MYISAM ，咱们的 error-log 中无相干报错。初步排查阶段不倡议批改

created_tmp_disk_tables 2987733

created_tmp_tables 11049848

正文：当在内存或磁盘上创立外部长期表时，服务器会减少该 Created_tmp_tables值。在磁盘上创立外部长期表时，服务器会减少该 Created_tmp_disk_tables值。如果在磁盘上创立了太多外部长期表，请思考减少tmp_table_size和max_heap_table_size设置。从早上10点36分到17点产生较多长期表，联合业务忙碌状况，属于失常景象

小结: 通过下面的剖析，联合利用架构(无奈降级到 MySQL8.0 )。初步阶段是倡议先优化 SQL 语句，缩小对长期表的应用，升高再次发生的概率。

问题复现

上文提到的 BUG 报告中，有个应用 MySQL Test Run 简称MTR(MySQL官网的自动化测试框架) 的测试用例。

下文将援用此测试用例，进行复现测试。

参数解释：

innodb_limit_optimistic_insert_debug参数能够限度每个B树页面的记录数，在 MySQL 运行过程中动静设置此参数能够导致页决裂。

innodb_limit_optimistic_insert_debug限度每个 B 树页面的记录数。默认值 0 示意不施加限度。仅当应用 CMake选项编译调试反对时，需开启DEBUG选项。# 依赖yum install -y gcc gcc-c++ cmake ncurses ncurses-devel bison openssl openssl-develtar -xvf mysql-boost-5.7.30.tar.gz--编译装置MySQL，因为须要设置innodb_limit_optimistic_insert_debug参数-------tar -xvf mysql-boost-5.7.30.tar.gz# 非BOOST版本的Mysql源码包，须要指定-DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/boostcd mysql-5.7.30cmake . -DCMAKE_INSTALL_PREFIX=/tools/mysql-test5.7.30 -DMYSQL_DATADIR=/tools/mysql-test5.7.30/data -DMYSQL_UNIX_ADDR=/tools/mysql-test5.7.30/mysql5.7.30.sock -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_general_ci -DEXTRA_CHARSETS=all -DENABLED_LOCAL_INFILE=1 -DWITH_SSL=system  -DWITH_BOOST=boost -DWITH_DEBUG=1 makemake install# Cmake 编译之后会在DCMAKE_INSTALL_PREFIX目录中生成mysql-test测试框架目录/tools/mysql-test5.7.30/mysql-test/tvim my0420.test cat my0420.test --source include/have_innodb.inc--let $restart_parameters = "restart: --innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:1G --big-tables=1"--source include/restart_mysqld.incSELECT @@innodb_temp_data_file_path;drop function if exists func1;delimiter |;create function func1(x int) returns int deterministic        begin                declare z1, z2 int;                set z1 = x;                set z2 = z1 + 2;                return z2;        end|delimiter ;|create table t1 (a int, b varchar(20));insert into t1 values(1, 'a'), (2, 'b'), (3, 'c'), (3, 'c'), (4, 'c');SET GLOBAL innodb_limit_optimistic_insert_debug=4;--let $i=1while ($i <= 15) {INSERT INTO t1 SELECT * FROM t1;--inc $i}SELECT COUNT(*) FROM t1;SET GLOBAL innodb_limit_optimistic_insert_debug=2;select * from t1 order by func1(a);进行测试,/tools/mysql-test5.7.30/mysql-test./mtr my0420.test其中 select * from t1 order by func1(a); 会应用Using temporary; Using filesort 和 业务SQL的执行打算统一

将 my0420.test 第二行新增--internal_tmp_disk_storage_engine=MYISAM参数后，服务不解体。

--let $restart_parameters = "restart: --innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:1G --internal_tmp_disk_storage_engine=MYISAM --big-tables=1"

将 my0420.test 第二行增大为---innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:6G参数后，服务不解体。

--let $restart_parameters = "restart: --innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:6G --big-tables=1"

big_tables如果启用，服务器将所有长期表存储在磁盘上而不是内存中。这能够避免须要大型长期表的操作的大多数谬误，但也会减慢内存表就足够的查问。most The table *tbl_name* is full errors forSELECToperations，如果error-log中呈现此报错，阐明select 操作应用了大的磁盘长期表，不举荐启用。

(小提示，客户环境中时常会收到某张长期表 #sql_tbl_name is full的告警邮件，须要思考是否能够优化SQL了)

测试日志

MTR 的执行逻辑为启动一个长期 MySQL 服务，并执行t目录中 my0420.test 文件的内容，执行后果默认会和r目录中的 result 同名文件(也叫规范执行后果文件，个别会在正确的版本中生成)进行比照，用于判断测试文件是否正确。

在mysql-test目录下：./mtr my0420.test  --执行到以下语句时报错--SET GLOBAL innodb_limit_optimistic_insert_debug=2;[100%] main.my0420                              [ fail ]        Test ended at 2022-04-20 20:05:39CURRENT_TEST: main.my0420mysqltest: At line 32: query 'select * from t1 order by func1(a)' failed: 2013: Lost connection to MySQL server during querysafe_process[7080]: Child process: 7081, exit: 1Server [mysqld.1 - pid: 7089, winpid: 7089, exit: 256] failed during test runServer log from this test:

Lost connection ，MySQL 服务已进行。之后打印了 error 信息。谬误同样呈现在 btr0btr.cc line 2165

Server log from this test:----------SERVER LOG START-----------2022-04-20T12:02:42.082135Z 0 [Note] /tools/mysql-test5.7.30/bin/mysqld (mysqld 5.7.30-debug-log) starting as process 7049 ...2022-04-20T12:02:43.698812Z 0 [Note] /tools/mysql-test5.7.30/bin/mysqld: Shutdown complete2022-04-20T12:02:45.051667Z 0 [Note] /tools/mysql-test5.7.30/bin/mysqld (mysqld 5.7.30-debug-log) starting as process 7090 ...2022-04-20T12:02:45.262573Z 0 [Note] /tools/mysql-test5.7.30/bin/mysqld: ready for connections.Version: '5.7.30-debug-log'  socket: '/tools/mysql-test5.7.30/mysql-test/var/tmp/mysqld.1.sock'  port: 13000  Source distribution2022-04-20 15:05:37 0x7fc298bc4700  InnoDB: Assertion failure in thread 140473762858752 in file btr0btr.cc line 2165InnoDB: Failing assertion: err == DB_SUCCESSInnoDB: 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, evenInnoDB: immediately after the mysqld startup, there may beInnoDB: corruption in the InnoDB tablespace. Please refer toInnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.htmlInnoDB: about forcing recovery.12:05:37 UTC - mysqld got signal 6 ;This could be because you hit a bug. It is also possible that this binaryor one of the libraries it was linked against is corrupt, improperly built,or misconfigured. This error can also be caused by malfunctioning hardware.Attempting to collect some information that could help diagnose the problem.As this is a crash and something is definitely wrong, the informationcollection process might fail.key_buffer_size=1048576read_buffer_size=131072max_used_connections=1max_threads=151thread_count=1connection_count=1It is possible that mysqld could use up tokey_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 61093 K  bytes of memoryHope that's ok; if not, decrease some variables in the equation.Trying to get some variables.Some pointers may be invalid and cause the dump to abort.Query (7fc234005890): select * from t1 order by func1(a)Connection ID (thread ID): 2Status: NOT_KILLED

和客户环境的区别之处:

生产中的报错为 Query(7f3be00479d0) is an invalid pointer. 有效指针，相似磁盘空间有余的报错.

测试中的报错为 Query (7fc234005890): select * from t1 order by func1(a).

测试环境的堆栈信息:

https://github.com/mysql/mysq...

（上图中文是翻译的代码正文，可能会有些偏差谬误）

查看测试日志文件 error-log，默认会在当前目录生成var目录，其中蕴含 my.cnf 文件

/tools/mysql-test5.7.30/mysql-test/var/log/mysqld.1.err

Trying to get some variables.Some pointers may be invalid and cause the dump to abort.Query (7fc234005890): select * from t1 order by func1(a)Connection ID (thread ID): 2Status: NOT_KILLEDThe manual page at http://dev.mysql.com/doc/mysql/en/crashing.html containsinformation that should help you find out what is causing the crash.Writing a core filesafe_process[7089]: Child process: 7090, killed by signal: 6

对于编译装置 MySQL 的补充：

Cmake 编译之后会在 DCMAKE_INSTALL_PREFIX 目录中生成 mysql-test 测试框架目录，

不须要以下步骤（以下步骤在不须要 DEBUG 调试时应用）

1.此包是mysql-test-5.7.30-linux-glibc2.12-x86_64.tar.gz

mv mysql-5.7.30-linux-glibc2.12-x86_64/ /tools/mysql-test

2.将mysql安装包目录下的文件与mtr目录合并，mysql安装包目录下为basedir

cp -r /data/mysql/base/5.7.30 /tools/mysql-test

(插播生存日记，make命令大概须要执行1个小时，刚好18:27，又恰逢隔离，起身洗锅炸厨房了嗷)

测试论断

此 BUG 可能会呈现在 MySQL5.7 版本中

1.测试中验证了数据库参数 innodb_temp_data_file_path 增大max_file_size后不会产生服务解体，如果业务 SQL 无奈进行优化时，能够增大此参数，可升高触发解体的概率。

2.测试中验证了数据库参数 internal_tmp_disk_storage_engine=MYISAM 时不会产生服务解体，默认 INNODB

如果业务无奈降级到 8.0 时，能够动静调整此参数。

咱们倡议的变更程序是:

优化 SQL 语句 -> 增大 innodb_temp_data_file_path 参数的 max_file_size 值 -> 降级到 MySQL 8.0（应用会话长期表空间） -> 批改 internal_tmp_disk_storage_engine 参数。

其中 internal_tmp_disk_storage_engine 参数，集体不是很了解，是否真的要将默认值 INNODB 更改为 MYISAM 。之后求教共事理解到，"外部长期表不会被复制，不会有并发拜访，是能够思考应用 MYISAM 的"

再次感激嗷。

参考链接

BUG报告:

https://bugs.mysql.com/bug.ph...
https://jira.percona.com/brow...

编译装置及参数阐明:

https://blog.csdn.net/iteye_6...
https://baijiahao.baidu.com/s...

https://dev.mysql.com/doc/ref...

MTR 文档:

https://dev.mysql.com/doc/dev...

数据库参数:

https://dev.mysql.com/doc/ref...

https://dev.mysql.com/doc/ref...

https://dev.mysql.com/doc/ref...

https://dev.mysql.com/doc/ref...

https://dev.mysql.com/doc/ref...

https://dev.mysql.com/doc/ref...