ERROR 1712 (HY000)
Got this error when using mysqldump for backing up a database. At this moment, we can't do any operations but DDL.
mysqldump: Error 1712: Index table_name is corrupted when dumping table `table_name` at row: 0
Or met this error when queried the table.
ERROR 1712 (HY000): Index table_name is corrupted
In the log, you can also see the errors.
[root@test ~]# less /var/log/mysqld.log
...
2015-11-18 21:32:27 3185 [ERROR] Got error 180 when reading table './database_name/table_name'
2015-11-18 21:32:54 3185 [ERROR] /usr/sbin/mysqld: Sort aborted: Index table_name is corrupted
...
This error explicitly said the error came from one of the indexes, not the data. It could be easier than Error 2013 to debug. Chances are, it might recover itself at times by restarting the server. If the error continues appearing, you may consider the following solutions.
Now, allow me to conclude the solution straightly then I will explain the details later in this post.
Recreate the problematic index.
The recreation includes two actions in fact, dropping and creating index. Sometimes, it may cost you to drop some columns. But now the key questions we should ask ourselves first are:
- Which one is the problematic index?
- Why could an index be corrupted?
- What kind of situations can make it corrupted?
The questions are almost able to answer themselves: Constraints. Only constraints can stop indexes going further. So, we should look for the following indexes in sequence.
- Primary index.
- Unique index.
- Foreign index.
In my case, only the auto incremental sequence in the primary key can not be controlled by business logic. I suspect the primary key has duplicate values causing the index corrupted, so I just recreate the primary key by issuing one statement of DDL to solve the problem. You may refer the following post for the statement. How to Reassign Sequential Numbers on the Auto Incremental Column.
As you can see, I drop the auto increment column and the primary key as well, then add them back to the table. But if the column is quite critical for you, we need some other ways to solve it.
If all the above approaches failed, you may drop all indexes, remove the problematic rows, then add all the indexes back. This can be the last resort.