MySQL Bugs: #76263: GTID replication fails with master has purged binary logs, but log does exist

Bug #76263	GTID replication fails with master has purged binary logs, but log does exist
Submitted:	11 Mar 2015 15:08	Modified:	15 May 2019 15:49
Reporter:	Naheem Munir	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S1 (Critical)
Version:	5.6.18	OS:	Linux
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
Hi

We had this issue on 19 Aug 2014

and I posted it in Bug #70048 (which in turn say its duplicate of bug #71376)

and 71376 states this should not happen in 5.6.18.

So please do not mark this duplicate or close it.

This is effecting our production system.

Please check bug Bug #70048 for info related to 19 Aug 2014.

-----------------------------
Hi just hit same problem again on Production Slave,

It was restored from mysqldump backups and

when tried to run 

CHANGE MASTER TO
MASTER_HOST='master server',
MASTER_USER ='replication user',
MASTER_PASSWORD ='password',
MASTER_AUTO_POSITION=1;

We have encountered 

 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'
WE need urgent help this problem.

MySQL version is

Server version: 5.6.18-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Regards
Naheem

How to repeat:
N/A

Suggested fix:
There is none.

I have tried the following work around but it failed.

I ran on slave

mysql> select * from slave_master_info \G
*************************** 1. row ***************************
       Number_of_lines: 23
       Master_log_name: huso-slvdb97-log-bin.001052
        Master_log_pos: 82089755
                  Host: XXXXXXXX
             User_name: XXXXXXX
         User_password: XXXXXXXXXXX
                  Port: XXXXXX
         Connect_retry: 60
           Enabled_ssl: 0
                Ssl_ca:
            Ssl_capath:
              Ssl_cert:
            Ssl_cipher:
               Ssl_key:
Ssl_verify_server_cert: 0
             Heartbeat: 1800
                  Bind:
    Ignored_server_ids: 0
                  Uuid: XXXXXXXXXXXXXXXXXXXXXXXX
           Retry_count: 86400
               Ssl_crl:
           Ssl_crlpath:
 Enabled_auto_position: 0
1 row in set (0.00 sec)

to get the binlog and last position at time of backup.

which is confirmed in mysqld.log after the backup the point slave started.

2015-03-06 18:03:05 26214 [Note] Slave I/O thread: connected to master 'xxxxxx@xxxxxxxxx:xxxx',replication started in log 'huso-slvdb97-log-bin.001052' at position 82089755

I disabled GTID in my.cnf and restarted the salve

and ran 

reset master;
reset slave;
CHANGE MASTER TO
  MASTER_HOST='master server',
  MASTER_USER ='replication user', 
  MASTER_PASSWORD ='password',
  MASTER_LOG_FILE='huso-slvdb97-log-bin.001052',
  MASTER_LOG_POS=82089755;

It moaned about Master having GTID, Put GTID back in and restarted mysql.

It failed with a unique key constraint violation. the mysqld.log

2015-03-11 11:28:07 29244 [Note] Slave I/O thread: connected to master 'xxxxx@xxxxxx:xxxxx',replication started in log 

'huso-slvdb97-log-bin.001052' at position 82089755
2015-03-11 11:28:07 29244 [Note] Event Scheduler: Loaded 0 events
2015-03-11 11:28:07 29244 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.18-enterprise-commercial-advanced-log'  socket: '/xxxxxx/ mysql.sock'  port: xxxxx  MySQL Enterprise 

Server - Advanced Edition (Commercial)
2015-03-11 11:28:08 29244 [ERROR] Slave SQL: Error 'Duplicate entry '122699859' for key 'PRIMARY'' on query. Default 

database: xxxxxxxxxxxxxxxxxxxxxxxxx', Error_code: 1062
2015-03-11 11:28:08 29244 [Warning] Slave: Duplicate entry '122699859' for key 'PRIMARY' Error_code: 1062
2015-03-11 11:28:08 29244 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the 

slave SQL thread with "SLAVE START". We stopped at log 'huso-slvdb97-log-bin.0

Hi,

I cannot reproduce this on any of the moderl 5.6.44, 5.7.26 nor 8.0.16

can you confirm you have issues with any of the modern releases?

thanks