MySQL Bugs: #67202: GTID replication fails when first binlog file is purged

Bug #67202	GTID replication fails when first binlog file is purged
Submitted:	12 Oct 2012 9:42	Modified:	29 Oct 2012 12:46
Reporter:	Petr Medonos	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.6.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	GTID, purge, replication

Description:
When first binlog file is purged and replication is restarted, following last IO error message appears:

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.'
GTID replication fails when first binlog file is purged. 

This can be fixed by CHANGE MASTER TO MASTER_HOST = 'xxx', MASTER_PORT = 3306, MASTER_USER = 'repl', MASTER_PASSWORD = 'xxx', MASTER_AUTO_POSITION = 1;, but after next stop/start slave this message appears again.

How to repeat:
On master:
mysql> CREATE TABLE `item` (`id` int(11) NOT NULL AUTO_INCREMENT,`item` varchar(10), PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

mysql> insert into item(item) values ('test1') ;
mysql> flush logs;
mysql> insert into item(item) values ('test2') ;
mysql> flush logs;
mysql> insert into item(item) values ('test3') ;
mysql> flush logs;
mysql> insert into item(item) values ('test4') ;
mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000004
         Position: 448
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 2B2B0657-13DD-11E2-BF0D-0201B2EE23F3:1-7
1 row in set (0.00 sec)

mysql> PURGE BINARY LOGS TO 'mysql-bin.000002';
mysql> insert into item(item) values ('test5') ;
mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000004
         Position: 705
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 2B2B0657-13DD-11E2-BF0D-0201B2EE23F3:1-8

On slave:
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: xxx
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 705
               Relay_Log_File: xxy-relay-bin.000008
                Relay_Log_Pos: 915
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 705
              Relay_Log_Space: 1215
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 2b2b0657-13dd-11e2-bf0d-0201b2ee23f3
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 2B2B0657-13DD-11E2-BF0D-0201B2EE23F3:1-8
            Executed_Gtid_Set: 2B2B0657-13DD-11E2-BF0D-0201B2EE23F3:1-8

mysql> stop slave;
mysql> start slave;

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: xxx
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 705
               Relay_Log_File: xxy-relay-bin.000008
                Relay_Log_Pos: 915
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 705
              Relay_Log_Space: 1215
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1236
                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.'
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 2b2b0657-13dd-11e2-bf0d-0201b2ee23f3
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 121012 11:38:49
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 2B2B0657-13DD-11E2-BF0D-0201B2EE23F3:1-8
            Executed_Gtid_Set: 2B2B0657-13DD-11E2-BF0D-0201B2EE23F3:1-8

Slave configuration:
...
server-id=2
log_bin                 = /var/log/mysql/mysql-bin.log
log-slave-updates = 1
disable-gtid-unsafe-statements = 1
expire_logs_days        = 2
max_binlog_size         = 100M
gtid-mode = ON
binlog-format = row
master_info_repository = TABLE
relay_log_info_repository = TABLE
sync_binlog = 1
slave_parallel_workers = 8
...

master configuration:
...
server-id
log_bin                 = /var/log/mysql/mysql-bin.log
log-slave-updates = 1
disable-gtid-unsafe-statements = 1
expire_logs_days        = 2
max_binlog_size         = 100M
gtid-mode = ON
binlog-format = row
# multithreaded replication
#slave_parallel_workers = 8
sync_binlog = 1
#sync_master_info = 1
#sync_relay_log = 1
#sync_relay_log_info = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE
#master_verify_checksum = ON

Thank you for the report.

I can not repeat described behavior. Please provide your configuration files for both master and slave.

Master config file

Attachment: master-my.cnf (application/octet-stream, text), 1.96 KiB.

Slave config file

Attachment: slave-my.cnf (application/octet-stream, text), 1.92 KiB.

Only workaround I currently found is to stop master and slave, clear all binary logs and index files and then run CHANGE MASTER TO ... MASTER_AUTO_POSITION = 1; start slave.

Thank you for the feedback.

Verified as described.

test case for MTR

Attachment: rpl_bug67202.test (application/octet-stream, text), 759 bytes.

master option file

Attachment: rpl_bug67202-master.opt (application/octet-stream, text), 78 bytes.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Fixed in 5.6+. Fix should appear in 5.6.9.

mysql 5.6.15 GTID replication 'Got fatal error 1236 from master when reading data from binary log'

I Checked SHOW SLAVE STATUS error is "'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.''

i am suspecting it happens because of master old binary log (7 days before) deleted form master as we are using expire_logs_days=7 option and slave still looking for this old binary log for some GTID reference to replicate with master.

I tried following link solution ,

http://www.mysqlperformanceblog.com/2013/02/08/how-to-createrestore-a-slave-using-gtid-rep...

but it still not working.

Note :using mysql 5.6.15

Now only option for me to re-create slave by taking mysqldump from master.

Is there any other option if we can start replication without recreating new slave.

Thanks in Advance