Debug Your Infrastructure
Get Instant Solutions for Kubernetes, Databases, Docker and more
MySQL 1200: Index is corrupted.
- Identify the Corrupted Table: First, try to identify which table is corrupted. Run the MySQL command to check all tables in the database:
CHECK TABLE tablename;
Replace `tablename` with the name of your table. If you have multiple tables, you might need to run this command for each.
- Repair the Table: If the `CHECK TABLE` command indicates corruption, use the `REPAIR TABLE` command to attempt a repair:
REPAIR TABLE tablename;
This command attempts to repair the corrupted table. Note that it works for MyISAM and ARCHIVE tables. For InnoDB, the approach is different (see step 3).
- InnoDB Tables: If the corrupted table is an InnoDB table, MySQL's automatic recovery process should kick in, but if it doesn't, you might need to restore from a backup. If no backup is available or you want to try to recover without restoring:
- Check the MySQL error log (often named `mysql_error.log`) for any InnoDB-specific messages that might indicate the nature of the corruption.
- Use `innodbforcerecovery` option in the `my.cnf` (or `my.ini` on Windows) configuration file. Start with a low level (1) and increase as necessary up to 6. This option allows the MySQL server to start so you can dump your tables:[mysqld]
innodbforcerecovery = 1
After setting this option, restart the MySQL server and dump your databases:mysqldump -u root -p --all-databases > all_databases.sql
Then, stop the server, remove the `innodbforcerecovery` line (or comment it out), and restart the server. Drop the corrupted database(s), and restore from the dump:mysql -u root -p < all_databases.sql
- Check Hardware and Filesystem: Sometimes, corruption can be due to underlying hardware or filesystem issues. Run a filesystem check (e.g., `fsck` on Linux) and check your hardware (e.g., HDD/SSD for errors).
- Check for Disk Space: Ensure you have enough disk space on the server, as a full disk can cause corruption and other errors. Use `df -h` to check disk space on Linux.
- Review MySQL Logs: Look into the MySQL error log for any indications of what caused the corruption or other related errors. This can provide clues for preventive measures in the future.
Remember, if you are not comfortable performing these actions or if the data is critical, consider consulting a professional.
Master
MySQL
debugging in Minutes
— Grab the Ultimate Cheatsheet
(Perfect for DevOps & SREs)
MySQL
Cheatsheet
(Perfect for DevOps & SREs)
MORE ISSUES
Backed by
