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

[28 Feb 2014 12:53] Daniël van Eeden
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.
[28 Feb 2014 13:38] Daniël van Eeden
Maybe there could be a mysqlbinlogadm tool be created for MySQL Utilities which helps to move the binlogs and could fix the index file?
[6 Mar 2014 12:56] Umesh Shastry
Hello Daniel,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh
[6 Mar 2014 13:00] Umesh Shastry
// 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)
[14 Nov 2016 8:51] OCA Admin
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.