MySQL Bugs: #71879: Wrong binlog size reported after moving binlogs

Bug #71879	Wrong binlog size reported after moving binlogs
Submitted:	28 Feb 2014 12:53	Modified:	6 Mar 2014 12:56
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Logging	Severity:	S3 (Non-critical)
Version:	5.6.16, 5.6.17	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	binlogs

Description:
I had to move the binlog files to another filesystem.

I stopped MySQL, changed log_bin and log_bin_index, moved the binlogs and started MySQL again.

Then SHOW BINARY LOGS showed 0 as file size for the old logs. But "SHOW BINLOG EVENTS IN 'some-binlog' LIMIT 5" did work.

The issue was resolved by editing the index files as it included relative paths (./server-bin.000022) instead of full path names (/some/binlog/location/server-bin.000022).

How to repeat:
Move the binlogs elsewhere w/o editing the index file...

Suggested fix:
SHOW BINARY LOGS should show a warning if it can't find the files. If SHOW BINLOG EVENTS can find the files then SHOW BINARY LOGS should also be able to locate them.

Maybe there could be a mysqlbinlogadm tool be created for MySQL Utilities which helps to move the binlogs and could fix the index file?

Hello Daniel,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh

// How repeat

- Bring down MySQL
- Change log_bin location to diff location
- move existing binlogs to the new location 
- Bring up MySQL again

// Confirm from mysql> prompt.

mysql> show binary logs;
+-------------------------+-----------+
| Log_name                | File_size |
+-------------------------+-----------+
| cluster-repo-bin.000001 |       120 |
| cluster-repo-bin.000002 |         0 |
| cluster-repo-bin.000003 |         0 |
| cluster-repo-bin.000004 |         0 |
| cluster-repo-bin.000005 |         0 |
| cluster-repo-bin.000006 |       120 |
+-------------------------+-----------+
6 rows in set (0.00 sec)

mysql> SHOW BINLOG EVENTS IN 'cluster-repo-bin.000002' LIMIT 5;
+-------------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------+
| Log_name                | Pos | Event_type  | Server_id | End_log_pos | Info                                                                 |
+-------------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------+
| cluster-repo-bin.000002 |   4 | Format_desc |      3306 |         120 | Server ver: 5.6.17-enterprise-commercial-advanced-log, Binlog ver: 4 |
| cluster-repo-bin.000002 | 120 | Query       |      3306 |         201 | BEGIN                                                                |
| cluster-repo-bin.000002 | 201 | Query       |      3306 |         300 | use `mysql`; delete from help_topic;                                 |
| cluster-repo-bin.000002 | 300 | Query       |      3306 |         382 | COMMIT                                                               |
| cluster-repo-bin.000002 | 382 | Query       |      3306 |         463 | BEGIN                                                                |
+-------------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------+
5 rows in set (0.00 sec)

Contribution submitted via Github - Bug #71879 - Wrong binlog size reported after moving binlogs (*) Contribution by Elshad Aghazade (Github elshadaghazade, mysql-server/pull/110#issuecomment-260129105): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_93380468.txt (text/plain), 2.58 KiB.