1220 database data_error ai_generated true

ERROR 1220 (HY000): Error when executing command 'PURGE BINARY LOGS': Could not find target log file mentioned in the file index

ID: database/mysql-binlog-purge-failure

Also available as: JSON · Markdown · 中文
85%Fix Rate
85%Confidence
1Evidence
2023-08-20First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
MySQL 5.7 active — — —
MySQL 8.0 active — — —
MySQL 8.4 active — — —
MariaDB 10.5 active — — —
MariaDB 10.6 active — — —

Root Cause

The binary log file specified in the PURGE BINARY LOGS command does not exist in the binary log index file, often because it was manually deleted or the index file is corrupted.

generic

中文

PURGE BINARY LOGS 命令中指定的二进制日志文件在二进制日志索引文件中不存在，通常是由于该文件被手动删除或索引文件损坏。

Official Documentation

https://dev.mysql.com/doc/refman/8.0/en/purge-binary-logs.html

Workarounds

  1. 85% success Rebuild the binary log index file by listing all existing binlog files: `ls -1 /var/lib/mysql/mysql-bin.* > /var/lib/mysql/mysql-bin.index` and ensure the file permissions are correct. Then retry PURGE BINARY LOGS. 
    Rebuild the binary log index file by listing all existing binlog files: `ls -1 /var/lib/mysql/mysql-bin.* > /var/lib/mysql/mysql-bin.index` and ensure the file permissions are correct. Then retry PURGE BINARY LOGS.
  2. 90% success Use RESET MASTER (carefully, as it removes all binlogs) to clean the index and start fresh. Only do this if you don't need point-in-time recovery: `RESET MASTER;` 
    Use RESET MASTER (carefully, as it removes all binlogs) to clean the index and start fresh. Only do this if you don't need point-in-time recovery: `RESET MASTER;`
  3. 75% success If a specific binlog file is missing, identify the gap and use FLUSH LOGS to create a new log, then purge up to that new log: `FLUSH LOGS; PURGE BINARY LOGS TO 'mysql-bin.000new';` 
    If a specific binlog file is missing, identify the gap and use FLUSH LOGS to create a new log, then purge up to that new log: `FLUSH LOGS; PURGE BINARY LOGS TO 'mysql-bin.000new';`

中文步骤

  1. 通过列出所有现有 binlog 文件来重建二进制日志索引文件：`ls -1 /var/lib/mysql/mysql-bin.* > /var/lib/mysql/mysql-bin.index` 并确保文件权限正确。然后重试 PURGE BINARY LOGS。
  2. 谨慎使用 RESET MASTER（它会删除所有 binlog）来清理索引并重新开始。仅在不需要时间点恢复时执行：`RESET MASTER;`
  3. 如果特定 binlog 文件缺失，识别间隙并使用 FLUSH LOGS 创建新日志，然后清除到该新日志：`FLUSH LOGS; PURGE BINARY LOGS TO 'mysql-bin.000new';`

Dead Ends

Common approaches that don't work:

  1. Run PURGE BINARY LOGS TO 'mysql-bin.000999' again with a different filename 90% fail

    If the index file is corrupted, any filename will fail with the same error until the index is rebuilt

  2. Delete the binary log index file manually and restart MySQL 95% fail

    Deleting the index file without rebuilding it will cause MySQL to fail to start or lose track of all binlogs

  3. Set expire_logs_days to 0 to disable purging 70% fail

    This only stops automatic purging; the manual PURGE command will still fail due to the index issue