这篇文章给大家介绍MySQL表索引损坏致Crash及修复过程是怎样的，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。
监控到一台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)
关于MySQL表索引损坏致Crash及修复过程是怎样的就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
原创文章，作者：kirin，如若转载，请注明出处：https://blog.ytso.com/204160.html