Replication Error: Seconds_Behind_Master always 0

[David Murphy]

If I reboot out slave machine, the Seconds_Behind_Master is always 0 on restart and Exec_Master_Log_Pos is not incriminting.

However the io and sql threads are running.

This corrects itself if i , stop slave; reset slave; change master .... ;

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Queueing master event to the relay log
                  Master_Host: db2
                  Master_User: rgdb3_repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log-bin.002629
          Read_Master_Log_Pos: 444668227
               Relay_Log_File: DB3-relay-bin.000004
                Relay_Log_Pos: 939458230
        Relay_Master_Log_File: log-bin.002578
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: lead_genius
           Replicate_Do_Table:
       Replicate_Ignore_Table: esp_v1.engine_injector_recipients,esp_common.ResponseMasterLog,esp_v1.engine_email_log_sent_old,esp_v1.engine_delivery_queue,esp_v1.engine_email_log_queue
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table: esp_v1.dnr_%,esp_v1.tmp_%
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 939458087
              Relay_Log_Space: 60328545845
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

What more data would you need to validate this bug for correction, as its replication its VERY critical.

[David Murphy (dmurphy)]

BTW this is for Percona-XtraDB-server-51.x86_64 5.1.45-10.rhel5 installed

[Raghavendra D Prabhu (raghavendra-prabhu)]

@David, can you check if the issue is the one reported here http://bugs.mysql.com/bug.php?id=61260

It looks like it could either be

       a) A disk space issue on server causing the hang - http://serverfault.com/questions/268112/mysql-stopped-working-full-disk/268411#268411 or a long 'load data infile' being queued to a file.

    b) Replication filters: The events that may getting queued to the log (as indicated in Slave_IO_State) may belong to the ignored table.

You will need to provide error log for futher diagnosing this or steps to reproduce this. Also, let us know if this is fixed in latest versions.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1139