MySQL – Preventing Index Corruption

I'm having a recurring issue with a MySQL database where one index in particular has been getting corrupted. Originally I saw a corruption event about every 2-3 weeks and this week I've seen it happen twice. The corruption also only happens on one index and the data itself seems fine.

The composite index in question spans two BigInt foreign key ID Columns on a table with 5M rows. I'm using InnoDB for all tables and running MySQL 5.6.23 on Amazon RDS. I looked to see if I could find anything in mysql-error but haven't found any entries in it.

To verify that the index corrupted, I run the following EXPLAIN:

mysql> explain SELECT * FROM  student INNER JOIN school ON school.id = student.school_id WHERE student.student_status_id IN (3, 4, 7) ORDER BY student.id desc LIMIT 0, 25;

+----+-------------+------------+------+-----------------------------------------------------------------+---------------------------+---------+-----------------------+------+---------------------------------+
| id | select_type | table      | type | possible_keys                                                   | key                       | key_len | ref                   | rows | Extra                           |
+----+-------------+------------+------+-----------------------------------------------------------------+---------------------------+---------+-----------------------+------+---------------------------------+
|  1 | SIMPLE      | school     | ALL  | PRIMARY                                                         | NULL                      | NULL    | NULL                  | 3690 | Using temporary; Using filesort |
|  1 | SIMPLE      | student    | ref  | student_status_id_idx,school_id_idx,schoolStatusIndex_idx       | schoolStatusIndex_idx     | 8       | school.id             |  178 | Using index condition           |
+----+-------------+------------+------+-----------------------------------------------------------------+---------------------------+---------+-----------------------+------+————————————————+

When it’s “fixed” the rows are roughly 48/1 instead of 3690/178

To fix it, it requires some combination of these 3 steps:

1. alter table student drop KEY schoolStatusIndex_idx; alter table student add key schoolStatusIndex_idx (school_id,student_status_id);
2. alter table school engine=innodb; (rebuilds all indexes)
3. alter table student engine=innodb;

Still, this has me baffled. What could be causing this kind of index corruption and how can I prevent it from happening moving forward?

Here's the trimmed output of SHOW CREATE TABLE for student, student_status, and school:

CREATE TABLE `student_status` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `code` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;


CREATE TABLE `student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `school_id` bigint(20) NOT NULL,
  `phone` varchar(10) NOT NULL,
  `student_status_id` bigint(20) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `student_status_id_idx` (`student_status_id`),
  KEY `school_id_idx` (`school_id`),
  KEY `schoolStatusIndex_idx` (`school_id`,`student_status_id`),
  CONSTRAINT `student_student_status_id_student_status_id` FOREIGN KEY (`student_status_id`) REFERENCES `student_status` (`id`),
  CONSTRAINT `student_school_id_school_id` FOREIGN KEY (`school_id`) REFERENCES `school` (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=2582686 DEFAULT CHARSET=latin1;


CREATE TABLE `school` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=4350 DEFAULT CHARSET=latin1;

And some additional output that might be helpful:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 5753536512 |
+-------------------------+------------+