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

[11 Mar 2015 15:08] Naheem Munir
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.
[11 Mar 2015 15:48] Naheem Munir
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