# Replication ERROR: Missing transaction in replicas **URL:** \ **Category:** Percona Server for MySQL 8.0\ **Created:** [October 8, 2023, 6:12am UTC](https://forums.percona.com/t/replication-error-missing-transaction-in-replicas/25753 "2023-10-08T06:12:26Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Chanakya](https://avatars.discourse-cdn.com/v4/letter/c/cab0a1/32.png) [@Chanakya](https://forums.percona.com/u/Chanakya)\ **Post date:** [October 8, 2023, 6:12am UTC](https://forums.percona.com/t/replication-error-missing-transaction-in-replicas/25753/1 "2023-10-08T06:12:26Z")
Hi, I have come across an error with replication. LAST\_ERROR\_MESSAGE: Worker 1 failed executing transaction ‘dedc2c0a-de85-11ec-80bb-48df37590a18:2004018060’ at source log ab\_binlog.034612, end\_log\_pos 553967352; Could not execute Update\_rows event on table jamfsoftware.mobile\_device\_app\_deployment\_queue; Can’t find record in ‘mobile\_device\_app\_deployment\_queue’, Error\_code: 1032; Can’t find record in ‘mobile\_device\_app\_deployment\_queue’, Error\_code: 1032; handler error HA\_ERR\_KEY\_NOT\_FOUND; the event’s source log ab\_binlog.034612, end\_log\_pos 553967352 Set up- One Primary and three replicas. All three replicas failed at exact same position/update statement. I looked at binary log of primary, event has been replicated and I see it in relay log of all three replicas. Replicas has log-replica-updates enabled and binary log of replicas has the event (update statement which is missing on replicas that caused replication error) Binary log event where replication failed: #231007 23:49:30 server id 2 end\_log\_pos 553967352 CRC32 0xab6b23c9 Update\_rows: table id 680 flags: STMT\_END\_F ### UPDATE `jamfsoftware`.`mobile_device_app_deployment_queue` ### WHERE ### @1=246625 /\* INT meta=0 nullable=0 is\_null=0 \*/ ### @2=28 /\* INT meta=0 nullable=0 is\_null=0 \*/ ### @3=1675373156420 /\* LONGINT meta=0 nullable=0 is\_null=0 \*/ ### @4=‘Prompting’ /\* VARSTRING(93) meta=93 nullable=0 is\_null=0 \*/ ### @5=1696722308447 /\* LONGINT meta=0 nullable=0 is\_null=0 \*/ ### SET ### @1=246625 /\* INT meta=0 nullable=0 is\_null=0 \*/ ### @2=28 /\* INT meta=0 nullable=0 is\_null=0 \*/ ### @3=1675373156420 /\* LONGINT meta=0 nullable=0 is\_null=0 \*/ ### @4=‘ManagedButUninstalled’ /\* VARSTRING(93) meta=93 nullable=0 is\_null=0 \*/ ### @5=1696722570463 /\* LONGINT meta=0 nullable=0 is\_null=0 \*/ # at 553967352 Applied on Primary: root\> select \* from `jamfsoftware`.`mobile_device_app_deployment_queue` where device\_id=246625 and mobile\_device\_app\_id=28; ±----------±---------------------±--------------------±----------------------±------------------+ | device\_id | mobile\_device\_app\_id | date\_deployed\_epoch | current\_state | last\_update\_epoch | ±----------±---------------------±--------------------±----------------------±------------------+ | 246625 | 28 | 1675373156420 | ManagedButUninstalled | 1696722570463 | ±----------±---------------------±--------------------±----------------------±------------------+ 1 row in set (0.01 sec) On replicas: root\> select \* from `jamfsoftware`.`mobile_device_app_deployment_queue` where device\_id=246625 and mobile\_device\_app\_id=28; ±----------±---------------------±--------------------±----------------------±------------------+ | device\_id | mobile\_device\_app\_id | date\_deployed\_epoch | current\_state | last\_update\_epoch | ±----------±---------------------±--------------------±----------------------±------------------+ | 246625 | 28 | 1675373156420 | ManagedButUninstalled | 1696722297552 | ±----------±---------------------±--------------------±----------------------±------------------+ 1 row in set (0.00 sec) Missing Event on replicas which is in relaylog too: #231007 23:45:08 server id 2 end\_log\_pos 510404732 CRC32 0x010ee88c Update\_rows: table id 680 flags: STMT\_END\_F ### UPDATE `jamfsoftware`.`mobile_device_app_deployment_queue` ### WHERE ### @1=246625 /\* INT meta=0 nullable=0 is\_null=0 \*/ ### @2=28 /\* INT meta=0 nullable=0 is\_null=0 \*/ ### @3=1675373156420 /\* LONGINT meta=0 nullable=0 is\_null=0 \*/ ### @4=‘ManagedButUninstalled’ /\* VARSTRING(93) meta=93 nullable=0 is\_null=0 \*/ ### @5=1696722297552 /\* LONGINT meta=0 nullable=0 is\_null=0 \*/ ### SET ### @1=246625 /\* INT meta=0 nullable=0 is\_null=0 \*/ ### @2=28 /\* INT meta=0 nullable=0 is\_null=0 \*/ ### @3=1675373156420 /\* LONGINT meta=0 nullable=0 is\_null=0 \*/ ### @4=‘Prompting’ /\* VARSTRING(93) meta=93 nullable=0 is\_null=0 \*/ ### @5=1696722308447 /\* LONGINT meta=0 nullable=0 is\_null=0 \*/ # at 510404732 Replica Binary log: #231007 23:45:08 server id 2 end\_log\_pos 792452634 CRC32 0xc43d68b4 Update\_rows: table id 166 flags: STMT\_END\_F ### UPDATE `jamfsoftware`.`mobile_device_app_deployment_queue` ### WHERE ### @1=246625 /\* INT meta=0 nullable=0 is\_null=0 \*/ ### @2=28 /\* INT meta=0 nullable=0 is\_null=0 \*/ ### @3=1675373156420 /\* LONGINT meta=0 nullable=0 is\_null=0 \*/ ### @4=‘ManagedButUninstalled’ /\* VARSTRING(93) meta=93 nullable=0 is\_null=0 \*/ ### @5=1696722297552 /\* LONGINT meta=0 nullable=0 is\_null=0 \*/ ### SET ### @1=246625 /\* INT meta=0 nullable=0 is\_null=0 \*/ ### @2=28 /\* INT meta=0 nullable=0 is\_null=0 \*/ ### @3=1675373156420 /\* LONGINT meta=0 nullable=0 is\_null=0 \*/ ### @4=‘Prompting’ /\* VARSTRING(93) meta=93 nullable=0 is\_null=0 \*/ ### @5=1696722308447 /\* LONGINT meta=0 nullable=0 is\_null=0 \*/ # at 792452634 It is quite weird how this particular SQL update statement is not applied on all three replicas. Replication variables: root\> show variables like ‘%replic%’; ±----------------------------------------------±------------------------+ | Variable\_name | Value | ±----------------------------------------------±------------------------+ | group\_replication\_consistency | EVENTUAL | | init\_replica | | | innodb\_replication\_delay | 0 | | log\_replica\_updates | ON | | log\_slow\_replica\_statements | OFF | | pseudo\_replica\_mode | OFF | | replica\_allow\_batching | ON | | replica\_checkpoint\_group | 512 | | replica\_checkpoint\_period | 300 | | replica\_compressed\_protocol | OFF | | replica\_enable\_event | | | replica\_exec\_mode | STRICT | | replica\_load\_tmpdir | /path/to/data | | replica\_max\_allowed\_packet | 1073741824 | | replica\_net\_timeout | 60 | | replica\_parallel\_type | LOGICAL\_CLOCK | | replica\_parallel\_workers | 1 | | replica\_pending\_jobs\_size\_max | 134217728 | | replica\_preserve\_commit\_order | ON | | replica\_skip\_errors | OFF | | replica\_sql\_verify\_checksum | ON | | replica\_transaction\_retries | 10 | | replica\_type\_conversions | | | replication\_optimize\_for\_static\_plugin\_config | OFF | | replication\_sender\_observe\_commit\_only | OFF | | rpl\_stop\_replica\_timeout | 31536000 | | skip\_replica\_start | OFF | | sql\_replica\_skip\_counter | 0 | ±----------------------------------------------±------------------------+ 28 rows in set (0.00 sec) ---
**Author:** ![Denis\_Subbota](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/denis_subbota/32/23607_2.png) [@Denis\_Subbota](https://forums.percona.com/u/Denis_Subbota)\ **Post date:** [October 9, 2023, 6:56am UTC](https://forums.percona.com/t/replication-error-missing-transaction-in-replicas/25753/2 "2023-10-09T06:56:07Z")
Hello Chanakya, Please share server\_id and UUID of the primary and replicas. Also, are you using GTID replication or general replication? Regards, Denis Subbota. Managed Services, Percona. ---
**Author:** ![Chanakya](https://avatars.discourse-cdn.com/v4/letter/c/cab0a1/32.png) [@Chanakya](https://forums.percona.com/u/Chanakya)\ **Post date:** [October 11, 2023, 5:01am UTC](https://forums.percona.com/t/replication-error-missing-transaction-in-replicas/25753/3 "2023-10-11T05:01:21Z")
Primary: dedc2c0a-de85-11ec-80bb-48df37590a18 Serverid- 2 Replica-1; 6bb05d8c-65b1-11ee-9ec1-48df37152f58 Serverid- 3 Replica-2 61e3deb7-6590-11ee-acea-48df37559ff8 serverid- 1 Replica-3 5fb9b2b1-64e1-11ee-aa44-48df371e1de0 serverid- 4 GTID replication. ---
**Author:** ![Hussain\_Patel](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/hussain_patel/32/17808_2.png) [@Hussain\_Patel](https://forums.percona.com/u/Hussain_Patel)\ **Post date:** [October 30, 2023, 1:44pm UTC](https://forums.percona.com/t/replication-error-missing-transaction-in-replicas/25753/4 "2023-10-30T13:44:33Z")
Hi Team, Please help to check this issue. ---
**Author:** ![Hussain\_Patel](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/hussain_patel/32/17808_2.png) [@Hussain\_Patel](https://forums.percona.com/u/Hussain_Patel)\ **Post date:** [December 18, 2023, 4:26pm UTC](https://forums.percona.com/t/replication-error-missing-transaction-in-replicas/25753/5 "2023-12-18T16:26:20Z")
Hi Team, Please help to identify the issue ---
**Author:** ![smit.arora](https://avatars.discourse-cdn.com/v4/letter/s/aeb1de/32.png) [@smit.arora](https://forums.percona.com/u/smit.arora)\ **Post date:** [January 11, 2024, 12:56am UTC](https://forums.percona.com/t/replication-error-missing-transaction-in-replicas/25753/6 "2024-01-11T00:56:32Z")
Hi Chanakya, As per the binlogs on Primary and records on Replica, it is evident that there is data mismatch in Primary and replicas. MySQL is looking for exact record i.e. every matching column in replicas. The record on Primary before update was: device\_id =246625 mobile\_device\_app\_id =28 date\_deployed\_epoch =1675373156420 current\_state =‘Prompting’ last\_update\_epoch =1696722308447 On replica the record is device\_id =246625 mobile\_device\_app\_id =28 date\_deployed\_epoch =1675373156420 current\_state =‘ ManagedButUninstalled’ last\_update\_epoch = 1696722297552 There could be two causes for this: 1- An update query was executed on Primary some time back with sql\_log\_bin=0 which caused the data discrepancy. 2- An update query was executed on all replicas to change the record. If the update query was executed without setting sql\_log\_bin=0 on replicas, there would be an errant gtid. You can find more about finding it and fixing it on this blog - [https://percona.community/blog/2021/11/08/the-errant-gtid-pt1/](https://percona.community/blog/2021/11/08/the-errant-gtid-pt1/) To make sure that its not repeated again, I would recommend running [pt-table-checksum](https://docs.percona.com/percona-toolkit/pt-table-checksum.html) the cluster to fix the discepancies and then [pt-table-sync](https://docs.percona.com/percona-toolkit/pt-table-sync.html) to fix the discrepancies found. ---
**Author:** ![Hussain\_Patel](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/hussain_patel/32/17808_2.png) [@Hussain\_Patel](https://forums.percona.com/u/Hussain_Patel)\ **Post date:** [March 8, 2024, 5:02pm UTC](https://forums.percona.com/t/replication-error-missing-transaction-in-replicas/25753/7 "2024-03-08T17:02:30Z")
We dont feel there was any transaction that was directly applied on replica nor was there any transaction that was executed with sql\_bin\_log off. The transaction that got skipped on replica was there on the binlog of primary. I feel this has to do with no primary key on the table. Any issue reported by others with similar issue?