Index corruption is usually a very rare issue, caused either by bugs in MySQL or hardware problems. Here’s how I handled it.
I knew something was wrong when I got the following in the logs:
|
1 2 3 4 5 6 |
Jun 19 03:51:18 sandy mysqld: 160619 3:51:18 InnoDB: error clustered record for sec rec not found Jun 19 03:51:18 sandy mysqld: InnoDB: index `i_user2` of table `DBNAME`.`photos` Jun 19 03:51:18 sandy mysqld: InnoDB: sec index record PHYSICAL RECORD: n_fields 3; compact format; info bits 0 Jun 19 03:51:18 sandy mysqld: 0: len 4; hex 80037dd5; asc } ;; Jun 19 03:51:18 sandy mysqld: 1: len 1; hex 80; asc ;; Jun 19 03:51:18 sandy mysqld: 2: len 4; hex 85563fe7; asc V? ;; |
At first it seemed to be related to a specific index and only appeared when a query was executed that affected the entire table. Oh well, let’s do a table check in MySQL using “check table photos”, the result was:
|
1 2 3 4 5 |
| dbname.photos | check | Warning | InnoDB: The B-tree of index "PRIMARY" is corrupted. | | dbname.photos | check | Warning | InnoDB: Index 'key_time' contains 21767138 entries, should be 18446744073709551615. | | dbname.photos | check | Warning | InnoDB: Index 'i_user2' contains 21767138 entries, should be 18446744073709551615. | | dbname.photos | check | Warning | InnoDB: Index 'i_album_ord' contains 21767138 entries, should be 18446744073709551615. | | dbname.photos | check | error | Corrupt |
That wasn’t looking very good. Also keep in mind that InnoDB does not offer a REPAIR function for the InnoDB engine, only for MyISAM. Mostly because it’s not supposed to happen with a InnoDB architecture, and the engine should fix everything at startup by replaying the datalogs.
Let’s stop the MySQL server and do a quick snapshot of the data files, it’s always best to have a copy of the data before you try to do any fixing / recovery.
Starting the MySQL server again and running a “check table photos” produced the following:
|
1 2 3 4 5 6 7 8 |
+-----------------+-------+----------+-------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +-----------------+-------+----------+-------------------------------------------------------+ | dbname.photos | check | Warning | InnoDB: Index "key_time" is marked as corrupted | | dbname.photos | check | Warning | InnoDB: Index "i_user2" is marked as corrupted | | dbname.photos | check | Warning | InnoDB: Index "i_album_ord" is marked as corrupted | | dbname.photos | check | error | Corrupt | +-----------------+-------+----------+-------------------------------------------------------+ |
So, there was something wrong with all the indexes. And also at this point any query that used indexes failed, basically the table was unusable. At this point I had 3 options:
1) drop the indexes and re-create them
2) create a table using the same structure and indexes and use “insert … select”, that will basically copy the data
3) rebuild the table using “alter table photos engine = innodb” or “optimize table photos”
Since I had created a backup before, I was safe to try anything.
I didn’t like option 1, I wanted something that would get me a fresh start and not work on the existing table data.
Option 2 seemed safe but slow in my mind.
I went with option 3. Keep in mind that on InnoDB both of those commands do the same, “optimize table” is mapped to “alter table photos engine = innodb”.
For all 3 options you need to have at least enough free space as the table you are working on. And it will take time, my table was 6 GB and it took 1 hour using option 3. During that time I had thousands of lines in the logs:
|
1 2 3 4 |
Jun 23 00:16:22 sandy mysqld: 160623 0:16:22 [ERROR] Got error 179 when reading table './dbname/photos' Jun 23 00:16:22 sandy mysqld: 160623 0:16:22 [ERROR] Got error 179 when reading table './dbname/photos' Jun 23 00:16:23 sandy mysqld: 160623 0:16:23 [ERROR] Got error 179 when reading table './dbname/photos' Jun 23 00:16:23 sandy mysqld: 160623 0:16:23 [ERROR] Got error 179 when reading table './dbname/photos' |
But in the end everything was fine, running a “check table photos” finally produced the “ok” message:
|
1 2 3 4 5 6 |
+-----------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------------+-------+----------+----------+ | dbname.photos | check | status | OK | +-----------------+-------+----------+----------+ 1 row in set (1 min 20.84 sec) |
Whew.
Make sure you have backups and don’t try anything before that!
References:
https://dev.mysql.com/doc/refman/5.7/en/alter-table.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-file-defragmenting.html
http://dba.stackexchange.com/questions/31701/finding-and-fixing-innodb-index-corruption