共计 5975 个字符,预计需要花费 15 分钟才能阅读完成。
监控到一台 MySQL 实例在早上发生过 Crash,上去看了一下,已经被 mysqld_safe 成功拉起。
上去检查一下错误日志,发现错误日志如下(已对表名,库名,路径做脱敏处理):
- ……………………………………(大量相同的报错)…………………………………………
- 2017–08–31T11:11:04.291424Z 32394522 [ERROR] InnoDB: Record in index `t_idx` of table `$db_name`.`$tb_name` was not found on update: TUPLE (info_bits=0, 9 fields): {[12]121098369601(0x010201000908030609060001),[9] (0x000000000000010000),[4]KOWA(0x0B0F0701),[4]AYNA(0x01090E01),[6]STAT44(0x030401040404),[4]AYNA(0x01090E01),[1]0(0x00),[1]0(0x00),[32]8f2a39b44fe74cd781527d856342d834(0x0806020103090204040605070403040708010502070408050603040204080304)} at: COMPACT RECORD(info_bits=0, 9 fields): {[12]121098369601(0x010201000908030609060001),[9] (0x000000000000010000),[4]KOWA(0x0B0F0701),[4]AYNA(0x01090E01),NULL,NULL,[1]0(0x00),[1]0(0x00),[32]8f2a39b44fe74cd781527d856342d834(0x0806020103090204040605070403040708010502070408050603040204080304)}
- 2017–08–31T03:11:04.291454Z 32394522 [Note] InnoDB: GIS MBR INFO: 1.31506e–47 and 1.02964e–71, 2.8816e–306, 1.93059e+53
- 2017–08–31 03:11:04 0x7fcaf04be700 InnoDB: Assertion failure in thread 140509591627520 in file row0ins.cc line 282
- InnoDB: Failing assertion: !cursor–>index–>is_committed()
- 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/5.7/en/forcing–innodb–recovery.html
- InnoDB: about forcing recovery.
- 03:11:04 UTC – mysqld got signal 6 ;
- This could be because you hit a bug. It is also possible that this binary
- or 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 information
- collection process might fail.
- …………………………………………………………………………………………………………
- Trying to get some variables.
- Some pointers may be invalid and cause the dump to abort.
- Query (7fca7c0dbaa0): is an invalid pointer
- Connection ID (thread ID): 32394522
- 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.
- …………………………………………………………………………………………………………
- (重启中)
- …………………………………………………………………………………………………………
- 2017–08–31T03:11:08.925622Z 0 [Note] $basedir/bin/mysqld: ready for connections.
- Version: ‘5.7.12-log’ socket: ‘$datadir/mysqld.sock’ port: 3306 Source distribution
- 2017–08–31T03:31:10.232145Z 1704 [ERROR] InnoDB: Record in index `t_idx` of table `$db_name`.`$tb_name` was not found on update: TUPLE (info_bits=0, 9 fields): {[12]198051077411(0x010908000501000707040101),[9] 7 (0x000000000000020700),[4]AOGA(0x010F0701),[4]AQGA(0x01010701),[6]STAT44(0x030401040404),NULL,[1]0(0x00),[1]0(0x00),[32]c6f98a358ace4897a11a27d689bb6884(0x0306060908010305080103050408090701010101020704060809020206080804)} at: COMPACT RECORD(info_bits=0, 9 fields): {[12]198051077411(0x010908000501000707040101),[9] 7 (0x000000000000020700),[4]AOGA(0x010F0701),[4]AQGA(0x01010701),NULL,NULL,[1]0(0x00),[1]0(0x00),[32]c6f98a358ace4897a11a27d689bb6884(0x0306060908010305080103050408090701010101020704060809020206080804)}
- 2017–08–31T03:31:10.232168Z 1704 [Note] InnoDB: GIS MBR INFO: 7.26084e–43 and 1.08604e–42, 2.8823e–306, 132832
- 2017–08–31T03:35:51.201716Z 2208 [ERROR] InnoDB: Flagged corruption of `t_idx` in table `$db_name`.`$tb_name` in CHECK TABLE; Wrong count
初步确定为因为名为 t_idx 的索引损坏导致的大量报错,并在处理 update 语句时导致 crash。
检查 binlog 发现的确有很多对该表的 update 操作。
执行一下 check table,发现的确有问题:
- mysql> CHECK TABLE `$db_name`.`$tb_name`;
- +––––––––––––––––––––+–––––––+––––––––––+–––––––––––––––––––––––––––––––––––––––––––––––––––––––+
- | Table | Op | Msg_type | Msg_text |
- +––––––––––––––––––––+–––––––+––––––––––+–––––––––––––––––––––––––––––––––––––––––––––––––––––––+
- | $db_name.$tb_name | check | Warning | InnoDB: Index t_idx is marked as corrupted |
- | $db_name.$tb_name | check | error | Corrupt |
- +––––––––––––––––––––+–––––––+––––––––––+–––––––––––––––––––––––––––––––––––––––––––––––––––––––+
- 2 rows in set (0.83 sec)
因该库为高可用主库,检查到备库状态正常,准备先手动做 failover,再对该表进行修复。
因为表小,也比较幸运,修复过程十分顺利:
- mysql> OPTIMIZE TABLE `$db_name`.`$tb_name`;
- +––––––––––––––––––––+––––––––––+––––––––––+–––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––+
- | Table | Op | Msg_type | Msg_text |
- +––––––––––––––––––––+––––––––––+––––––––––+–––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––+
- | $db_name.$tb_name | optimize | note | Table does not support optimize, doing recreate + analyze instead |
- | $db_name.$tb_name | optimize | status | OK |
- +––––––––––––––––––––+––––––––––+––––––––––+–––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––+
- 2 rows in set (3.42 sec)
- mysql> ALTER TABLE `$db_name`.`$tb_name` ENGINE=INNODB;
- Query OK, 0 rows affected (3.09 sec)
- Records: 0 Duplicates: 0 Warnings: 0
- mysql> ANALYZE TABLE `$db_name`.`$tb_name`;
- +––––––––––––––––––––+–––––––––+––––––––––+––––––––––+
- | Table | Op | Msg_type | Msg_text |
- +––––––––––––––––––––+–––––––––+––––––––––+––––––––––+
- | $db_name.$tb_name | analyze | status | OK |
- +––––––––––––––––––––+–––––––––+––––––––––+––––––––––+
- 1 row in set (0.00 sec)
- mysql> CHECK TABLE `$db_name`.`$tb_name`;
- +––––––––––––––––––––+–––––––+––––––––––+––––––––––+
- | Table | Op | Msg_type | Msg_text |
- +––––––––––––––––––––+–––––––+––––––––––+––––––––––+
- | $db_name.$tb_name | check | status | OK |
- +––––––––––––––––––––+–––––––+––––––––––+––––––––––+
- 1 row in set (0.98 sec)
〇 参考文档:
关于 mysqlcheck 与 check/analyze/optimize table 等命令:
http://www.linuxidc.com/Linux/2017-08/146647.htm
比较类似的一个 case 被提到了 bug 库:
https://bugs.mysql.com/bug.php?id=82997
问题描述节选:
- With some random DML running I managed to hit a problem on 5.7.13.
- Next step for me is to test current version and make a suitable testcase.
- Version: ‘5.7.13’ socket: ” port: 3306 MySQL Community Server (GPL)
- [ERROR] InnoDB: Record in index `ed` of table `test`.`users` was n
- [Note] InnoDB: GIS MBR INFO: 1.20768e-153 and 4.76881e-038, 7.0436
- InnoDB: Assertion failure in thread 2384 in file row0ins.cc line 282
- InnoDB: Failing assertion: !cursor->index->is_committed()
本文永久更新链接地址 :http://www.linuxidc.com/Linux/2017-09/146646.htm
正文完
星哥玩云-微信公众号