0

I am having mysql 8.0 installation with replication between 2 servers in master-master configuration. While data is being replicated from server A to server B the replication is stalled from hours. Below is the snippet for show slave status \G on server B. I am not sure on what waiting for handler commit means and whether its the cause of my Replication stalling

        Master_SSL_Cipher:
       Master_SSL_Key:
Seconds_Behind_Master: 49808
        Last_IO_Errno: 0
        Last_IO_Error:
       Last_SQL_Errno: 0
       Last_SQL_Error:Replicate_Ignore_Server_Ids:
     Master_Server_Id: 1
     Master_Info_File: mysql.slave_master_info
            SQL_Delay: 0
  SQL_Remaining_Delay: NULL
  Slave_SQL_Running_State: waiting for handler commit
       Master_Retry_Count: 86400
              Master_Bind:
  Last_IO_Error_Timestamp:
 Last_SQL_Error_Timestamp:
           Master_SSL_Crl:

After executing show processlist; i am getting a process that is waiting from a long time , having a wait time of 50071 and increasing

system user     |                               | NULL | Query       | 50071 | waiting for handler commit

What steps shall i take to bring my replication on track?

3
  • SELECT @@VERSION; on both servers, please. – Michael - sqlbot Apr 23 '19 at 2:27
  • Both servers are running on version 8.0.13 – AJAY Apr 23 '19 at 6:56
  • Did you ever get this resolved? I'm having the same problem, absolutely no error indication but Seconds_Behind_Master keeps rising steadily and the Slave_SQL_Running_State is most of the time in waiting for handler commit – Aviad P. Jul 12 '20 at 17:18
1

That's new with 8.0.13:

A new Performance Schema stage, waiting for handler commit, is available to detect threads going through transaction commit. Thanks to Facebook for the patch. (Bug #27855592, Bug #90417)

https://bugs.mysql.com/bug.php?id=90417

Suggest you file a comment with that bug report; could be that it caused a regression.

0

Had the same problem. Adding:

   innodb_flush_log_at_trx_commit = 2

Solved the problem for me on MySQL 8.0.13 installation. Read here: https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

And see also: mysql 8.0, updates are sometimes stalling

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.