Bug #90928 MySQL Replication SLAVE Error
Submitted: 18 May 2018 7:11 Modified: 18 May 2018 7:35
Reporter: Young Ju You Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Replication Severity:S1 (Critical)
Version:5.7.18 OS:CentOS (7.0)
Assigned to: CPU Architecture:Any

[18 May 2018 7:11] Young Ju You 
Description:
MySQL Community 5.7.18

Master/Slave Replication

Slave Error

mysql.err

2018-05-17T15:22:09.120832Z 11 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2018-05-17T15:22:09.122876Z 12 [ERROR] Slave SQL for channel '': Error 'XAER_NOTA: Unknown XID' on query. Default database: 'test0002'. Query: 'XA ROLLBACK X'3138322e3139332e342e323437000000022bbe110a0001c416',X'3138322e3139332e342e323437000000022bbf1d140001c44d',1114926712', Error_code: 1397
2018-05-17T15:22:09.122909Z 12 [Warning] Slave: XAER_NOTA: Unknown XID Error_code: 1397
2018-05-17T15:22:09.122921Z 12 [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.000068' position 286288826
2018-05-18T00:47:56.792946Z 185 [ERROR] Slave SQL for channel '': Error 'XAER_NOTA: Unknown XID' on query. Default database: 'test0002'. Query: 'XA ROLLBACK X'3138322e3139332e342e323437000000022bbe110a0001c416',X'3138322e3139332e342e323437000000022bbf1d140001c44d',1114926712', Error_code: 1397
2018-05-18T00:47:56.792973Z 185 [Warning] Slave: XAER_NOTA: Unknown XID Error_code: 1397
2018-05-18T00:47:56.792980Z 185 [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.000068' position 286288826

How to repeat:
              Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.30.11
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 5
              Master_Log_File: mysql-bin.000085
          Read_Master_Log_Pos: 649855925
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 5885440
        Relay_Master_Log_File: mysql-bin.000068
             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: 1397
                   Last_Error: Error 'XAER_NOTA: Unknown XID' on query. Default database: 'test0002'. Query: 'XA ROLLBACK X'3138322e3139332e342e323437000000022bbe110a0001c416',X'3138322e3139332e342e323437000000022bbf1d140001c44d',1114926712'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 286288826
              Relay_Log_Space: 18623287304
              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: 1397
               Last_SQL_Error: Error 'XAER_NOTA: Unknown XID' on query. Default database: 'test0002'. Query: 'XA ROLLBACK X'3138322e3139332e342e323437000000022bbe110a0001c416',X'3138322e3139332e342e323437000000022bbf1d140001c44d',1114926712'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 3202
                  Master_UUID: 0ebc3dc7-41e4-11e8-93ab-005056932d6a
             Master_Info_File: mysql.slave_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: 180518 00:47:56
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
[18 May 2018 7:35] MySQL Verification Team 
Hello Eun Byul Joe,

Thank you for the report.
This is duplicate of Bug #88358 (which in turned later marked as duplicate of Bug #87393). Per Bug #87393 fix - Changelog entry added for MySQL 8.0.4 and 5.7.21: XA ROLLBACK statements that failed because an incorrect transaction ID was given, could be recorded in the binary log with the correct transaction ID, and could therefore be actioned by replication slaves. A check is now made for the error situation before binary logging takes place, and failed XA ROLLBACK statements are not logged.
 
Please upgrade to latest 5.7 build and inform to us if you are seeing the issue along with exact reproducible test case.

Thanks,
Umesh