MySQL replication: purge_logs giving error

Question

On a MySQL replication instance I have this error message:

[ERROR] MYSQL_BIN_LOG :: purge_logs Was called Expired with file / db-space-no-connected-cpa / DBMS / dxnc6sla / arch / not listed in the index .

Can you give me an idea about the solution? thank you in advance

Answer 1

ANALYSIS

When log-bin is enabled, MySQL will log completed SQL transactions into binary logs.

Binary Logs are maintained by a text file whose name would be the same as the binary log but have file extension of .index.

For example, say you had log-bin=/mysqllogs/mysql-bin and started mysqld.

There will be files placed in /mysqllogs

mysql-bin.000001
mysql-bin.index

The index file will contain the name of all the binary logs.

In this example, it would contain mysql-bin.000001

Over time, binary logs will accumulate and you will have something like this in /mysqllogs

mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004
mysql-bin.index

and mysql-bin.index will contain this

mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004

Based on the variables expire_logs_days (if it is greater than 0) and max_binlog_size, mysqld will do its own rotation and expiring of binary logs.

If you do the following

cd /mysqllogs
rm -f mysql-bin.000001
rm -f mysql-bin.000002

this will disable mysqld's log rotation mechanism. Why ?

The /mysqllogs folder will have

mysql-bin.000003
mysql-bin.000004
mysql-bin.index

but the mysql-bin.index will still contain this

mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004

mysqld actively uses the index file to verify that every file in /mysqlvar listed in mysql-bin.index exists before doing any rotation or expiration.

Therefore, you should never let the OS delete binary logs. Let mysqld do it.

I wrote about this before

• May 23, 2016 : Error 1236 - "Could not find first log file name in binary log index file"
• Apr 23, 2013 : Is it safe to delete mysql-bin files?

SOLUTION

Shutdown mysqld, edit your .index file so the contents matches the exist binary logs on disk, and start mysqld. I have suggested this before (Mysql binlogs exists, show binary logs is empty)

GIVE IT A TRY !!!