Bug #74344 RESET MASTER fail to reset all binary logs when binlog index file is out of sync

• Submitted: 12 Oct 2014 22:00
• Modified: 21 Aug 2015 12:25
• Reporter: Muhammad Irfan
• Status: Can't repeat
• Category: MySQL Server: Replication
• Severity: S2 (Serious)
• Version: 5.6.21
• OS: Linux
• CPU Architecture: Any

[12 Oct 2014 22:00] Muhammad Irfan

Description:
RESET MASTER command fails to reset all binary logs when binary log index file is out of sync from on disk binary logs. RESET MASTER only reset till the first value in binary log index file.

How to repeat:
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.6.21                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.21-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | linux-glibc2.5               |
+-------------------------+------------------------------+

# ls -la 
total 180936
drwx------ 24 root root     4096 Oct 13 02:48 .
drwxr-xr-x  4 root root     4096 Oct 13 02:42 ..
-rw-rw----  1 root root       56 Oct 13 02:34 auto.cnf
-rw-rw----  1 root root   243695 Oct 13 02:43 bin.000001
-rw-rw----  1 root root   243532 Oct 13 02:43 bin.000002
-rw-rw----  1 root root   244435 Oct 13 02:44 bin.000003
-rw-rw----  1 root root   243532 Oct 13 02:44 bin.000004
-rw-rw----  1 root root   243532 Oct 13 02:44 bin.000005
-rw-rw----  1 root root   243532 Oct 13 02:45 bin.000006
-rw-rw----  1 root root   243532 Oct 13 02:45 bin.000007
-rw-rw----  1 root root   243532 Oct 13 02:45 bin.000008
-rw-rw----  1 root root   243532 Oct 13 02:45 bin.000009
-rw-rw----  1 root root   243532 Oct 13 02:46 bin.000010
-rw-rw----  1 root root   243566 Oct 13 02:46 bin.000011
-rw-rw----  1 root root   244081 Oct 13 02:46 bin.000012
-rw-rw----  1 root root   243566 Oct 13 02:47 bin.000013
-rw-rw----  1 root root   243566 Oct 13 02:47 bin.000014
-rw-rw----  1 root root   243566 Oct 13 02:47 bin.000015
-rw-rw----  1 root root   243566 Oct 13 02:47 bin.000016
-rw-rw----  1 root root   243875 Oct 13 02:48 bin.000017
-rw-rw----  1 root root   243566 Oct 13 02:48 bin.000018
-rw-rw----  1 root root   243566 Oct 13 02:48 bin.000019
-rw-rw----  1 root root      120 Oct 13 02:48 bin.000020
-rw-rw----  1 root root      260 Oct 13 02:48 bin.index

I intentionally deleted entries from binary index file from bin.000001-bin.000014

# cat bin.index
./bin.000015
./bin.000016
./bin.000017
./bin.000018
./bin.000019
./bin.000020

# mysql -e "SHOW MASTER STATUS;"
+------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| bin.000020 |      120 |              |                  |                   |
+------------+----------+--------------+------------------+-------------------+

# mysql -e "SHOW MASTER STATUS; RESET MASTER; SHOW MASTER STATUS;"
+------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| bin.000020 |      120 |              |                  |                   |
+------------+----------+--------------+------------------+-------------------+
+------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| bin.000015 |      120 |              |                  |                   |
+------------+----------+--------------+------------------+-------------------+

# ls -la
-rw-rw---- 1 root root       56 Oct 13 02:34 auto.cnf
-rw-rw---- 1 root root   243695 Oct 13 02:43 bin.000001
-rw-rw---- 1 root root   243532 Oct 13 02:43 bin.000002
-rw-rw---- 1 root root   244435 Oct 13 02:44 bin.000003
-rw-rw---- 1 root root   243532 Oct 13 02:44 bin.000004
-rw-rw---- 1 root root   243532 Oct 13 02:44 bin.000005
-rw-rw---- 1 root root   243532 Oct 13 02:45 bin.000006
-rw-rw---- 1 root root   243532 Oct 13 02:45 bin.000007
-rw-rw---- 1 root root   243532 Oct 13 02:45 bin.000008
-rw-rw---- 1 root root   243532 Oct 13 02:45 bin.000009
-rw-rw---- 1 root root   243532 Oct 13 02:46 bin.000010
-rw-rw---- 1 root root   243566 Oct 13 02:46 bin.000011
-rw-rw---- 1 root root   244081 Oct 13 02:46 bin.000012
-rw-rw---- 1 root root   243566 Oct 13 02:47 bin.000013
-rw-rw---- 1 root root   243566 Oct 13 02:47 bin.000014
-rw-rw---- 1 root root      120 Oct 13 02:50 bin.000015

RESET MASTER command purged/reset binary logs till the first entry of bin.index file .i.e. bin.000015 not all binary logs.

[21 Aug 2015 12:25] MySQL Verification Team

Hello Irfan,

Thank you for the report.
I'm not seeing this issue with 5.6.26 builds and most likely it is fixed. Could you please confirm this from your end as well?

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.6.26                                                  |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.6.26-enterprise-commercial-advanced-log               |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | linux-glibc2.5                                          |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> \! ls -l log/master-bin.[0-9]*

-rw-rw---- 1 umshastr common   69411 Aug 21 11:22 log/master-bin.000001
-rw-rw---- 1 umshastr common 1361011 Aug 21 11:22 log/master-bin.000002
-rw-rw---- 1 umshastr common    1072 Aug 21 13:54 log/master-bin.000003
-rw-rw---- 1 umshastr common     168 Aug 21 14:00 log/master-bin.000004
-rw-rw---- 1 umshastr common     168 Aug 21 14:00 log/master-bin.000005
-rw-rw---- 1 umshastr common     168 Aug 21 14:00 log/master-bin.000006
-rw-rw---- 1 umshastr common     168 Aug 21 14:00 log/master-bin.000007
-rw-rw---- 1 umshastr common     168 Aug 21 14:00 log/master-bin.000008
-rw-rw---- 1 umshastr common     168 Aug 21 14:00 log/master-bin.000009
-rw-rw---- 1 umshastr common     168 Aug 21 14:00 log/master-bin.000010
-rw-rw---- 1 umshastr common     168 Aug 21 14:00 log/master-bin.000011
-rw-rw---- 1 umshastr common     168 Aug 21 14:00 log/master-bin.000012
-rw-rw---- 1 umshastr common     168 Aug 21 14:00 log/master-bin.000013
-rw-rw---- 1 umshastr common     168 Aug 21 14:00 log/master-bin.000014
-rw-rw---- 1 umshastr common     168 Aug 21 14:00 log/master-bin.000015
-rw-rw---- 1 umshastr common     168 Aug 21 14:00 log/master-bin.000016
-rw-rw---- 1 umshastr common     168 Aug 21 14:00 log/master-bin.000017
-rw-rw---- 1 umshastr common     168 Aug 21 14:00 log/master-bin.000018
-rw-rw---- 1 umshastr common     168 Aug 21 14:00 log/master-bin.000019
-rw-rw---- 1 umshastr common     120 Aug 21 14:00 log/master-bin.000020
mysql>
mysql>
mysql> -- deleted entries from binary index file from master-bin.000001-master-bin.000014
mysql> \! cat log/master-bin.index
/export/umesh/server/binaries/mysql-advanced-5.6.26/log/master-bin.000015
/export/umesh/server/binaries/mysql-advanced-5.6.26/log/master-bin.000016
/export/umesh/server/binaries/mysql-advanced-5.6.26/log/master-bin.000017
/export/umesh/server/binaries/mysql-advanced-5.6.26/log/master-bin.000018
/export/umesh/server/binaries/mysql-advanced-5.6.26/log/master-bin.000019
/export/umesh/server/binaries/mysql-advanced-5.6.26/log/master-bin.000020
mysql>

mysql> SHOW MASTER STATUS; RESET MASTER; SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000020 |      120 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      120 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> \! ls -l log/master-bin.[0-9]*
-rw-rw---- 1 umshastr common 120 Aug 21 14:16 log/master-bin.000001

mysql> \! cat log/master-bin.index
/export/umesh/server/binaries/mysql-advanced-5.6.26/log/master-bin.000001

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thanks,
Umesh