Fixing MySQL Invalid Keys

Corrupted MYI key files in MySQL can cause queries to fail with “Invalid key” errors, and the usual mysqlcheck repair sometimes isn’t enough. The script below walks through every database owned by the current user, runs a full repair, flags any tables that still report errors, and then forces a rebuild with ALTER TABLE ... USE_FRM.

When to Use This

You’ll hit this situation when:

• mysqlcheck reports a table as corrupted but the repair step does not fully fix it.
• Queries against a specific table return ERROR 126 (HY000): Record has been deleted or Invalid key messages.
• The .MYI index file is out of sync with the .MYD data file.

The Repair Script

Run this from the directory that contains the user’s database folders (typically /var/lib/mysql):

user=$(pwd | cut -d'/' -f3)

for db in $(
  for i in $(ls /var/lib/mysql | grep "$user_" | grep -v back | cut -d'/' -f1); do
    echo "$i"
    mysqlcheck -rso "$i"
    myisamchk -r "/var/lib/mysql/$i/"*.MYI
  done | grep -i "Error" | grep "$user_"
); do
  realdb=$(echo "$db" | awk -F. '{print $1}')
  table=$(echo "$db" | awk -F. '{print $2}')
  echo "ALTER TABLE $table USE_FRM;" | mysql "$realdb"
done

What Each Part Does

1. Identify the user prefix. The script assumes the working directory path encodes the user name as the third path segment. Adjust the cut field if your layout differs.
2. List candidate databases. It filters /var/lib/mysql for directories matching the user prefix and excludes backup folders.
3. Repair in place. mysqlcheck -rso runs a full repair with verbose output, and myisamchk -r rebuilds the MYI index file directly.
4. Capture failures. Only lines containing “Error” that also match the user prefix are passed to the second loop.
5. Force a rebuild. ALTER TABLE ... USE_FRM tells MySQL to reconstruct the table from the .FRM definition file, effectively discarding the stale index.

Things to Watch

• The script assumes MyISAM tables (it targets .MYI files). For InnoDB tables, mysqlcheck and REPAIR TABLE work differently, and USE_FRM is not applicable.
• If the grep -i "Error" filter is too broad, you may end up running ALTER TABLE on tables that are actually fine. Review the output of mysqlcheck first if you want a safer pass.
• Run the script as the mysql system user or a user with sufficient privileges to access /var/lib/mysql.

Verifying the Fix

After the script completes, re-run a quick health check:

mysqlcheck -s --all-databases

All tables should report OK. If any still show errors, investigate the specific table with SHOW TABLE STATUS and consider a full mysqldump / DROP / RESTORE cycle for that table.

• Technology
• How-To

• Bash
• SysAdmin