# Replication stuck: Relay\_Log\_Pos not increasing **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [November 26, 2022, 1:19pm UTC](https://forums.percona.com/t/replication-stuck-relay-log-pos-not-increasing/18776 "2022-11-26T13:19:38Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![larryschul](https://avatars.discourse-cdn.com/v4/letter/l/eada6e/32.png) [@larryschul](https://forums.percona.com/u/larryschul)\ **Post date:** [November 26, 2022, 1:19pm UTC](https://forums.percona.com/t/replication-stuck-relay-log-pos-not-increasing/18776/1 "2022-11-26T13:19:38Z")
about 2 days ago relay\_log\_pos stopped increasing. i have 2 read replicas and both stopped at the same place… Relay\_Log\_Pos: 826986907 looking at a show replica status i am seeing this ``` Source_Log_File: binlog.009267 Read_Source_Log_Pos: 842701137 Relay_Log_File: mysql-prod-replica-02-relay-bin.007426 Relay_Log_Pos: 826986907 Relay_Source_Log_File: binlog.009135 Replica_IO_Running: Yes Replica_SQL_Running: Yes ``` so i dumped mysql-prod-replica-02-relay-bin.007426 — and pasted an excerpt around “826986907” i am not sure where to go from here to fix this… thanks for any ideas people can help with ```auto # at 826986732 #221124 18:06:25 server id 1 end_log_pos 826986667 CRC32 0x4c28e931 Update_rows: table id 86 flags: STMT_END_F BINLOG ' obJ/YxMBAAAASgAAABvQSjEAAFYAAAAAAAEABXNsaWNlAA1lbWFpbF9kb21haW5zAAUIDw8PEgf6 APoA+gAAHgEBgAIBCB0CNsc= obJ/Yx8BAAAAkAAAAKvQSjEAAFYAAAAAAAEAAgAF//8AnGgXUgAAAAAIem9oby5jb20Pc210cGlu LnpvaG8uY29tDjEzNi4xNDMuMTkxLjIzma5xISMAnGgXUgAAAAAIem9oby5jb20Pc210cGluLnpv aG8uY29tDjEzNi4xNDMuMTkxLjIzma5xIZkx6ShM '/*!*/; # at 826986876 #221124 18:06:25 server id 1 end_log_pos 826986698 CRC32 0x66eb27ad Xid = 31457734691 COMMIT/*!*/; # at 826986907 #221124 18:05:51 server id 1 end_log_pos 826986783 CRC32 0x16ea18b7 GTID last_committed=136821 sequence_number=136822 rbr_only=yes original_committed_timestamp=16693131852318 13 immediate_commit_timestamp=1669313185231813 transaction_length=1518829485 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; # original_commit_timestamp=1669313185231813 (2022-11-24 18:06:25.231813 GMT) # immediate_commit_timestamp=1669313185231813 (2022-11-24 18:06:25.231813 GMT) /*!80001 SET @@session.original_commit_timestamp=1669313185231813*//*!*/; /*!80014 SET @@session.original_server_version=80026*//*!*/; /*!80014 SET @@session.immediate_server_version=80026*//*!*/; SET @@SESSION.GTID_NEXT= '42053cc7-d6d9-11ec-a377-0200170e2487:5152282533'/*!*/; # at 826986992 #221124 18:05:51 server id 1 end_log_pos 826986863 CRC32 0x0567319e Query thread_id=1713651 exec_time=0 error_code=0 SET TIMESTAMP=1669313151/*!*/; BEGIN /*!*/; # at 826987072 #221124 18:05:51 server id 1 end_log_pos 826987124 CRC32 0x716e1e1d Table_map: `scrles`.`trade_algo_ta_append_finalcsv_ec0ya` mapped to number 44173693 # at 826987333 #221124 18:05:51 server id 1 end_log_pos 826994442 CRC32 0x504a2c01 Update_rows: table id 44173693 # at 826994651 #221124 18:05:51 server id 1 end_log_pos 827002162 CRC32 0x39ca7d06 Update_rows: table id 44173693 # at 827002371 #221124 18:05:51 server id 1 end_log_pos 827009634 CRC32 0xf413dc95 Update_rows: table id 44173693 ``` ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [November 28, 2022, 4:33am UTC](https://forums.percona.com/t/replication-stuck-relay-log-pos-not-increasing/18776/2 "2022-11-28T04:33:58Z")
Can you add `-v -v --base64-output=decode-rows` to your mysqlbinlog output? Also, include the event before and after. ---
**Author:** ![larryschul](https://avatars.discourse-cdn.com/v4/letter/l/eada6e/32.png) [@larryschul](https://forums.percona.com/u/larryschul)\ **Post date:** [November 28, 2022, 1:27pm UTC](https://forums.percona.com/t/replication-stuck-relay-log-pos-not-increasing/18776/3 "2022-11-28T13:27:02Z")
thanks for the reply… here is the binlog with the 20 lines before and after… ```auto > # at 826986658 > #221124 18:06:25 server id 1 end_log_pos 826986523 CRC32 0xc736021d Table_map: `xxxxx`.`exxxx_dxxxxxs` mapped to number 86 > # at 826986732 > #221124 18:06:25 server id 1 end_log_pos 826986667 CRC32 0x4c28e931 Update_rows: table id 86 flags: STMT_END_F > ### UPDATE `xxxxx`.`exxxx_dxxxxxs` > ### WHERE > ### @1=1377265820 /* LONGINT meta=0 nullable=0 is_null=0 */ > ### @2='zxxxo.com' /* VARSTRING(250) meta=250 nullable=1 is_null=0 */ > ### @3='smtpin.zxxxo.com' /* VARSTRING(250) meta=250 nullable=1 is_null=0 */ > ### @4='136.xxx.xxx.23' /* VARSTRING(250) meta=250 nullable=1 is_null=0 */ > ### @5='2022-11-24 18:04:35' /* DATETIME(0) meta=0 nullable=1 is_null=0 */ > ### SET > ### @1=1377265820 /* LONGINT meta=0 nullable=0 is_null=0 */ > ### @2='zxxxo.com' /* VARSTRING(250) meta=250 nullable=1 is_null=0 */ > ### @3='smtpin.zxxxo.com' /* VARSTRING(250) meta=250 nullable=1 is_null=0 */ > ### @4='136.xxx.xxx.23' /* VARSTRING(250) meta=250 nullable=1 is_null=0 */ > ### @5='2022-11-24 18:06:25' /* DATETIME(0) meta=0 nullable=1 is_null=0 */ > # at 826986876 > #221124 18:06:25 server id 1 end_log_pos 826986698 CRC32 0x66eb27ad Xid = 31457734691 > COMMIT/*!*/; > # at 826986907 > #221124 18:05:51 server id 1 end_log_pos 826986783 CRC32 0x16ea18b7 GTID last_committed=136821 sequence_number=136822 rbr_only=yes original_committed_timestamp=1669313185231813 immediate_commit_timestamp=1669313185231813 transaction_length=1518829485 > /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; > # original_commit_timestamp=1669313185231813 (2022-11-24 18:06:25.231813 GMT) > # immediate_commit_timestamp=1669313185231813 (2022-11-24 18:06:25.231813 GMT) > /*!80001 SET @@session.original_commit_timestamp=1669313185231813*//*!*/; > /*!80014 SET @@session.original_server_version=80026*//*!*/; > /*!80014 SET @@session.immediate_server_version=80026*//*!*/; > SET @@SESSION.GTID_NEXT= '42053cc7-d6d9-11ec-a377-0200170e2487:5152282533'/*!*/; > # at 826986992 > #221124 18:05:51 server id 1 end_log_pos 826986863 CRC32 0x0567319e Query thread_id=1713651 exec_time=0 error_code=0 > SET TIMESTAMP=1669313151/*!*/; > BEGIN > /*!*/; > # at 826987072 > #221124 18:05:51 server id 1 end_log_pos 826987124 CRC32 0x716e1e1d Table_map: `scrub_tables`.`trade_algo_ta_append_finalcsv_ec0ya` mapped to number 44173693 > # at 826987333 > #221124 18:05:51 server id 1 end_log_pos 826994442 CRC32 0x504a2c01 Update_rows: table id 44173693 > # at 826994651 > #221124 18:05:51 server id 1 end_log_pos 827002162 CRC32 0x39ca7d06 Update_rows: table id 44173693 > # at 827002371 ``` ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [November 28, 2022, 4:31pm UTC](https://forums.percona.com/t/replication-stuck-relay-log-pos-not-increasing/18776/4 "2022-11-28T16:31:44Z")
Are your relay logs still growing, or has growth also halted? If growth of relay logs has also halted, then there’s something on the source side that isn’t sending to the replica. This seems quite likely since _both_ of your replicas have halted. /_!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED_//_!_/; This line concerns me. I’m not sure why this would be present in the replication stream. Are you using GTID AUTO\_POSITION=1? If so, I would skip to the next position and see if replication resumes. ---
**Author:** ![larryschul](https://avatars.discourse-cdn.com/v4/letter/l/eada6e/32.png) [@larryschul](https://forums.percona.com/u/larryschul)\ **Post date:** [November 28, 2022, 5:00pm UTC](https://forums.percona.com/t/replication-stuck-relay-log-pos-not-increasing/18776/5 "2022-11-28T17:00:50Z")
this is UTC ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/3/3dc2437368918b140a6e4161df26080ae216fbd8.png) it appears that is working fine… these numbers are incrementing Source\_Log\_File: binlog.009408 Read\_Source\_Log\_Pos: 576323813 these are staying the same Relay\_Log\_File: mysql-rithcrm-prod-replica-02-relay-bin.007426 Relay\_Log\_Pos: 826986907 Relay\_Source\_Log\_File: binlog.009135 when i set up the slave i did execute MASTER\_AUTO\_POSITION = 1; how would i skip to the next position – never did that before thanks larry ---
**Author:** ![larryschul](https://avatars.discourse-cdn.com/v4/letter/l/eada6e/32.png) [@larryschul](https://forums.percona.com/u/larryschul)\ **Post date:** [November 28, 2022, 8:11pm UTC](https://forums.percona.com/t/replication-stuck-relay-log-pos-not-increasing/18776/6 "2022-11-28T20:11:36Z")
> [@larryschul](#): > > `SET @@SESSION.GTID_NEXT= '42053cc7-d6d9-11ec-a377-0200170e2487:5152282533'` > BEGIN; > COMMIT; > SET GTID\_NEXT=‘AUTOMATIC’; i was trying to execute the above – but the ititial command seemed to hang… so i ctrl-c’d out of it – figuring maybe i should stop the replica first… so i tried a stop replica… now i am seeing this… ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/8/85e9cd38c9d3822d9b87eac1234712b73ac70f0d.png) ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [November 28, 2022, 8:58pm UTC](https://forums.percona.com/t/replication-stuck-relay-log-pos-not-increasing/18776/7 "2022-11-28T20:58:26Z")
Woah woah, hold on. What is that query that’s been running for 353,026 seconds?!!! That’s 4 days! You have some MASSIVE update query which is running and **that** is the reason replication is not progressing. That’s also the reason why your STOP SLAVE appeared hung. Use the master’s binlog and exec\_master\_pos to find that query in the master and see what that is. Whatever that query is that is updating so many rows is locking up your replicas. ---
**Author:** ![larryschul](https://avatars.discourse-cdn.com/v4/letter/l/eada6e/32.png) [@larryschul](https://forums.percona.com/u/larryschul)\ **Post date:** [November 28, 2022, 9:43pm UTC](https://forums.percona.com/t/replication-stuck-relay-log-pos-not-increasing/18776/8 "2022-11-28T21:43:08Z")
> [@matthewb](#): > > exec\_master\_pos am i correct that “Exec\_Source\_Log\_Pos” is the mysql8 version of exec\_master\_pos? and i will use mysqlbinlog – to dump Relay\_Source\_Log\_File: binlog.009135 (from show replica status) i am doing this from the master ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [November 28, 2022, 9:51pm UTC](https://forums.percona.com/t/replication-stuck-relay-log-pos-not-increasing/18776/9 "2022-11-28T21:51:54Z")
Yes, you can look at the masters binlog ---
**Author:** ![larryschul](https://avatars.discourse-cdn.com/v4/letter/l/eada6e/32.png) [@larryschul](https://forums.percona.com/u/larryschul)\ **Post date:** [November 28, 2022, 10:23pm UTC](https://forums.percona.com/t/replication-stuck-relay-log-pos-not-increasing/18776/10 "2022-11-28T22:23:24Z")
i seem to see the same thing as a saw in the relay log when i looked at the replica… grep 826986698 /mnt/mysqldata/bin009135.log -B 20 -A 20 > BEGIN > /_!_/; > > # at 826986449 > > #221124 18:06:25 server id 1 end\_log\_pos 826986523 CRC32 0xc736021d Table\_map: `slice`.`email_domains` mapped to number 86 > > # at 826986523 > > #221124 18:06:25 server id 1 end\_log\_pos 826986667 CRC32 0x4c28e931 Update\_rows: table id 86 flags: STMT\_END\_F > > ### UPDATE `xxx`.`xxxx` > > ### WHERE > > ### @1=1377265820 /\* LONGINT meta=0 nullable=0 is\_null=0 \*/ > > ### @2=‘[zoho.com](http://zoho.com)’ /\* VARSTRING(250) meta=250 nullable=1 is\_null=0 \*/ > > ### @3=‘[smtpin.zoho.com](http://smtpin.zoho.com)’ /\* VARSTRING(250) meta=250 nullable=1 is\_null=0 \*/ > > ### @4=‘136.143.191.23’ /\* VARSTRING(250) meta=250 nullable=1 is\_null=0 \*/ > > ### @5=‘2022-11-24 18:04:35’ /\* DATETIME(0) meta=0 nullable=1 is\_null=0 \*/ > > ### SET > > ### @1=1377265820 /\* LONGINT meta=0 nullable=0 is\_null=0 \*/ > > ### @2=‘[zoho.com](http://zoho.com)’ /\* VARSTRING(250) meta=250 nullable=1 is\_null=0 \*/ > > ### @3=‘[smtpin.zoho.com](http://smtpin.zoho.com)’ /\* VARSTRING(250) meta=250 nullable=1 is\_null=0 \*/ > > ### @4=‘136.143.191.23’ /\* VARSTRING(250) meta=250 nullable=1 is\_null=0 \*/ > > ### @5=‘2022-11-24 18:06:25’ /\* DATETIME(0) meta=0 nullable=1 is\_null=0 \*/ > > # at 826986667 > > #221124 18:06:25 server id 1 end\_log\_pos 826986698 CRC32 0x66eb27ad Xid = 31457734691 > COMMIT/_!_/; > > # at 826986698 > > #221124 18:05:51 server id 1 end\_log\_pos 826986783 CRC32 0x16ea18b7 GTID last\_committed=136821 sequence\_number=136822 rbr\_only=yes original\_committed\_timestamp=1669313185231813 immediate\_commit\_timestamp=1669313185231813 transaction\_length=1518829485 > /_!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED_//_!_/; > > # original\_commit\_timestamp=1669313185231813 (2022-11-24 18:06:25.231813 GMT) > > # immediate\_commit\_timestamp=1669313185231813 (2022-11-24 18:06:25.231813 GMT) > > /_!80001 SET @@session.original\_commit\_timestamp=1669313185231813_//_!_/; > /_!80014 SET @@session.original\_server\_version=80026_//_!_/; > /_!80014 SET @@session.immediate\_server\_version=80026_//_!_/; > SET @@SESSION.GTID\_NEXT= ‘42053cc7-d6d9-11ec-a377-0200170e2487:5152282533’/_!_/; > > # at 826986783 > > #221124 18:05:51 server id 1 end\_log\_pos 826986863 CRC32 0x0567319e Query thread\_id=1713651 exec\_time=0 error\_code=0 > SET TIMESTAMP=1669313151/_!_/; > BEGIN > /_!_/; > > # at 826986863 > > #221124 18:05:51 server id 1 end\_log\_pos 826987124 CRC32 0x716e1e1d Table\_map: `scrub_tables`.`trade_algo_ta_append_finalcsv_ec0ya` mapped to number 44173693 > > # at 826987124 > > #221124 18:05:51 server id 1 end\_log\_pos 826994442 CRC32 0x504a2c01 Update\_rows: table id 44173693 > > # at 826994442 > > #221124 18:05:51 server id 1