Bug #87804 Error running query, slave SQL thread aborted.
Submitted: 19 Sep 2017 10:35 Modified: 19 Sep 2017 12:33
Reporter: Nikhil Kumar Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version: 5.6.27-enterprise-commercial-advanced-l OS:Linux (Linux 3.10.0-693.1.1.el7.x86_64)
Assigned to: CPU Architecture:Any

[19 Sep 2017 10:35] Nikhil Kumar
Description:
Hi Team,

We have a Master -Slave setup for our MySQL database replication for our Jira application.
It seems that the replication has stopped working long back which i came to know recently after joining current company. Now i am in a process to resume back to work.

When checked in the mysql-error.log, i can seebelow error:

2017-09-08 07:31:30 2829 [Note] Slave I/O thread: connected to master 'replication@blvmts2l112.snetbl.com:3306',replication started in log 'mysql-bin.000193' at position 282101980
2017-09-08 07:31:30 2829 [ERROR] Error in Log_event::read_log_event(): 'Event too small', data_len: 0, event_type: 0
2017-09-08 07:31:30 2829 [ERROR] Error reading relay log event: slave SQL thread aborted because of I/O error
2017-09-08 07:31:30 2829 [ERROR] Slave SQL: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 1594
2017-09-08 07:31:30 2829 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000073' position 1062927814

This is the output of SHOW SLAVE STATUS\G:

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: blvmts2l112.snetbl.com
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000202
          Read_Master_Log_Pos: 965962969
               Relay_Log_File: relay-bin.000208
                Relay_Log_Pos: 908725578
        Relay_Master_Log_File: mysql-bin.000073
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1594
                   Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1062927814
              Relay_Log_Space: 84979178204
              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: 0
                Last_IO_Error:
               Last_SQL_Errno: 1594
               Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 68d28144-70c8-11e5-acf6-0050560f03a1
             Master_Info_File: /mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 170908 07:31:30
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

There is a lot of difference between Master and Slave's binary log with which i can confirm that the replication has stopped very long back.

These are the binary logs available in Master:

-rw-rw----  1 mysql mysql 1073756513 Sep 14 01:38 mysql-bin.000197
-rw-rw----  1 mysql mysql 1073742291 Sep 14 23:57 mysql-bin.000198
-rw-rw----  1 mysql mysql 1073741956 Sep 16 07:01 mysql-bin.000199
-rw-rw----  1 mysql mysql 1073742046 Sep 17 06:49 mysql-bin.000200
-rw-rw----  1 mysql mysql 1073741883 Sep 18 08:53 mysql-bin.000201
-rw-rw----  1 mysql mysql  968307424 Sep 19 06:23 mysql-bin.000202

And these are the available in Slave:

mysql> show binary logs\G                                                                                                                                                                    *************************** 1. row ***************************
 Log_name: mysql-bin.000086
File_size: 143
*************************** 2. row ***************************
 Log_name: mysql-bin.000087
File_size: 120
2 rows in set (0.00 sec)

mysql-bin.000073 is no more available in Master/Slave, please help me in restoring the replication.
 

How to repeat:
May be by restoring SQL thread which activates the replication
[19 Sep 2017 12:05] MySQL Verification Team
Hello Nikhil,

Thank you for taking the time to report a problem.  Unfortunately you are not using a current version of the product you reported a problem with -- the problem might already be fixed. You are using 5.6.27, since then many bugs fixed. Please use latest GA, and if you are still seeing  then report us back with exact reproducible steps/test case.

In your case, most likely master was crashed/outage etc, later when it came back online slave was pointed to a wrong position and/or relay log is corrupted. You may have to identify the corrupted block, start the replication from the right position. Please note that support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/ 

Thanks,
Umesh
[19 Sep 2017 12:33] Nikhil Kumar
Hi,

Thank you so much for your reply.
We do not have any plans of upgrading to newer/latest version in near future.

Could you please provide workarounds for the solution provided?
Like how to identify the corrupted block and how to get back to original state?
As mentioned, the binary log where it stopped doesn't exist anymore (I guess).
So, please provide some solutions.
Expecting your support.

Nikhil