Solving MySQL Error 1035: Steps to Repair an Old Key File for Tables

by

When you encounter Error 1035 – SQLSTATE: HY000 (ER_OLD_KEYFILE) in MySQL, it indicates that there is an issue with the key file associated with a table. This error typically means that the key file is outdated or corrupted, and MySQL is unable to use it. In this guide, we will walk through the process of diagnosing and resolving this error to ensure your database tables are functioning correctly.

Understanding Error 1035

Error 1035 alerts you to a problem with the key file for a specific table, which is essential for the table’s indexing. This could be due to a crash, a failed upgrade, or corruption within the file system.

Diagnosing the Issue

  1. Identify the Affected Table:
    The error message will specify which table has the problematic key file. Take note of this table name for the repair process.
  2. Check Table Status:
    Use the CHECK TABLE command to assess the status of the affected table:
   CHECK TABLE tablename;
  1. Review File System:
    Ensure that the data directory and the table’s files are not corrupted and that the file system is intact.
  2. Inspect MySQL Version:
    If you recently upgraded MySQL, the key file format might have changed, and some tables may require repair.

Fixing the Issue

  1. Repair the Table:
    Use the REPAIR TABLE command to fix the key file for the table:
   REPAIR TABLE tablename USE_FRM;

The USE_FRM option forces MySQL to use the table’s .frm file (the table definition file) to regenerate the index file.

  1. Restore from Backup:
    If the repair operation is unsuccessful, restore the table from a recent backup, if available:
   -- Drop the corrupted table if it cannot be repaired
   DROP TABLE tablename;

   -- Restore the table from a backup
   RESTORE TABLE tablename FROM '/path/to/backup';
  1. Recreate Indexes:
    If the .MYI file (the key file for MyISAM tables) is missing or corrupted, you might need to drop and recreate the indexes:
   ALTER TABLE tablename DROP INDEX indexname;
   ALTER TABLE tablename ADD INDEX indexname (column1, column2);
  1. Upgrade Compatibility:
    If the error occurred after a MySQL upgrade, run mysql_upgrade to check and fix any incompatibilities:
   mysql_upgrade -u root -p
  1. Check File Permissions:
    Verify that the MySQL server has the correct permissions to access the table’s files:
   sudo chown -R mysql:mysql /var/lib/mysql/dbname
   sudo chmod -R 660 /var/lib/mysql/dbname/tablename.*
  1. Monitor Disk Space:
    Ensure that there is enough disk space available on the server to accommodate the repair operation.

By carefully following these steps, you should be able to diagnose and correct Error 1035 in MySQL. Always make sure to have up-to-date backups of your database to prevent data loss. If the error continues after attempting these solutions, it may be necessary to seek further assistance from MySQL support forums or a database specialist.

Leave a Comment