Shulou(Shulou.com)05/31 Report--

This article introduces the MySQL table index damage caused by Crash and repair process is how, the content is very detailed, interested friends can refer to, hope to be helpful to you.

It has been monitored that a MySQL instance has experienced Crash in the morning. After going up and taking a look, it has been successfully pulled up by mysqld_safe.

Go up and check the error log and find that the error log is as follows (desensitized table name, library name, path):

... A large number of the same errors were reported.

2017-08-31T11:11:04.291424Z 32394522 [ERROR] InnoDB: Record in index `tidx` 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.

.

(restarting)

.

2017-08-31T03:11:08.925622Z 0 [Note] $basedir/bin/mysqld: ready for connections.

Version: '5.7.12 socket:' $datadir/mysqld.sock' port: 3306 Source distribution

2017-08-31T03:31:10.232145Z 1704 [ERROR] InnoDB: Record in index `tidx` 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

Initially identified as a large number of errors due to a corrupted index named t_idx and causing crash when processing update statements.

Checking the binlog shows that there are indeed a lot of update operations on the table.

Perform a check table and find that there is a problem:

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)

Because the library is a highly available main library, check that the status of the standby database is normal, prepare to do failover manually, and then repair the table.

Because the watch is small and lucky, the repair process is very smooth:

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)

About the MySQL table index damage caused by Crash and repair process is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.