【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)
〇 参考文档:
关于mysqlcheck与check/analyze/optimize table等命令:
http://blog.itpub.net/29773961/viewspace-1815688/
比较类似的一个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()
正文到此结束