Got fatal error 1236 from master when reading data from binary log

Table of Contents

Problem

Error on slave node replication :

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. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the master's binary log expiration period. The GTID set sent by the slave is '00019832-1111-1111-1111-111111111111:1,
00019832-1111-1111-2222-111111111111:1', and the missing transactions are '00019832-1111-1111-2222-111111111111:10'.'

Once we try to start the replication on slave node we receive the above error. Below is the complete show slave status output.

slave1 [localhost:19833] {msandbox} ((none)) > show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 127.0.0.1
                  Master_User: rsandbox
                  Master_Port: 19832
                Connect_Retry: 60
              Master_Log_File:
          Read_Master_Log_Pos: 4
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 4
        Relay_Master_Log_File:
             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: 0
              Relay_Log_Space: 154
              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: 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. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the master's binary log expiration period. The GTID set sent by the slave is '00019832-1111-1111-1111-111111111111:1,
00019832-1111-1111-2222-111111111111:1', and the missing transactions are '00019832-1111-1111-2222-111111111111:10'.'
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 19832
                  Master_UUID: 00019832-1111-1111-1111-111111111111
             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 more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp: 210423 05:28:31
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set: 00019832-1111-1111-1111-111111111111:1,
00019832-1111-1111-2222-111111111111:1
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

Here we can see that slave node is trying to fetch the GTID from the master node which has already been purged on the master node. To fix this issue, see the solution steps.

Solution

Here based on the show slave status output, we can say the Master_UUID is “00019832-1111-1111-1111-111111111111″ .

Master_UUID: 00019832-1111-1111-1111-111111111111

Executed_Gtid_Set: 00019832-1111-1111-1111-111111111111:1,
00019832-1111-1111-2222-111111111111:1

So we need to make sure that, the GTID for all the UUID which are present on the “Executed_Gtid_Set” are available on the master node.

I.e. The GTIDs for all below UUID :

Executed_Gtid_Set: 00019832-1111-1111-1111-111111111111:1,
00019832-1111-1111-2222-111111111111:1

Once we connected to the master and saw the “show master status” like below, we found that on Master node, GTID is still available for slave’s master UUID “00019832-1111-1111-1111-111111111111:1-2″ i.e. GTID is not purged yet.

master [localhost:19832] {msandbox} ((none)) > select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 00019832-1111-1111-1111-111111111111 |
+--------------------------------------+
1 row in set (0.00 sec)

master [localhost:19832] {msandbox} ((none)) > show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 307
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 00019832-1111-1111-1111-111111111111:1-2,
00019832-1111-1111-2222-111111111111:10
1 row in set (0.00 sec)

But above on master node, we can notice that there is one another UUID’s (00019832-1111-1111-2222-111111111111) which slave is also trying to fetch GTID but has already been purged upto “00019832-1111-1111-2222-111111111111:10“, so due to this reason slave is throwing the error as it can’t find the GTID from “00019832-1111-1111-2222-111111111111:2“.

Once we reviewed about this UUID, found this is errant GTID which is not related to any of the replication nodes UUID.

master [localhost:19832] {msandbox} ((none)) > select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 00019832-1111-1111-1111-111111111111 |
+--------------------------------------+
1 row in set (0.00 sec)

slave1 [localhost:19833] {msandbox} ((none)) > select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 00019833-2222-2222-2222-222222222222 |
+--------------------------------------+
1 row in set (0.00 sec)

To solve this, we will have to tell the slave node that this extra errant unknown UUID’s GTID (00019832-1111-1111-2222-111111111111:10) has already been executed on slave node.

slave1 [localhost:19833] {msandbox} ((none)) > stop slave;
Query OK, 0 rows affected (0.01 sec)

slave1 [localhost:19833] {msandbox} ((none)) > reset slave;
Query OK, 0 rows affected (0.00 sec)

slave1 [localhost:19833] {msandbox} ((none)) > reset master;
Query OK, 0 rows affected (0.01 sec)

slave1 [localhost:19833] {msandbox} ((none)) > set global gtid_purged='00019832-1111-1111-1111-111111111111:1,00019832-1111-1111-2222-111111111111:10';
Query OK, 0 rows affected (0.00 sec)

slave1 [localhost:19833] {msandbox} ((none)) > start slave;
Query OK, 0 rows affected (0.01 sec)

slave1 [localhost:19833] {msandbox} ((none)) > show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: rsandbox
                  Master_Port: 19832
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 307
               Relay_Log_File: mysql-relay.000003
                Relay_Log_Pos: 520
        Relay_Master_Log_File: mysql-bin.000001
             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: 307
              Relay_Log_Space: 723
              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: 19832
                  Master_UUID: 00019832-1111-1111-1111-111111111111
             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 more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 00019832-1111-1111-1111-111111111111:2
            Executed_Gtid_Set: 00019832-1111-1111-1111-111111111111:1-2,
00019832-1111-1111-2222-111111111111:10
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

Leave a Reply