[MDEV-15010] Wrong Seconds_Behind_Master when only starting the SQL_Thread. Created: 2018-01-20  Updated: 2023-11-27

Status: Stalled
Project: MariaDB Server
Component/s: Replication
Affects Version/s: 5.5, 10.0, 10.1, 10.2.12, 10.2
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Jean-François Gagné Assignee: Andrei Elkin
Resolution: Unresolved Votes: 0
Labels: seconds-behind-master, upstream


 Description   

Hi,

When I run replication normally, I get a value in "Seconds_Behind_Master" from "SHOW SLAVE STATUS". However, when I run only the SQL Thread (on local relay logs that have been downloaded previously), I have "NULL" in "Seconds_Behind_Master". I would expect to have a numeric value.

Also, note that the manual ([1]) does not document a "NULL" value in "Seconds_Behind_Master", even if "NULL" is shown when the SQL Thread is not running. This might be a different bug thought and is documentation related.

[1]: https://mariadb.com/kb/en/library/show-slave-status/

mysql <<< "select version();"
 
version()
 
10.2.12-MariaDB-log


mysql <<< "start slave; do sleep(1); show slave status\G stop slave" | grep Seconds_Behind_Master
 
        Seconds_Behind_Master: 130422


mysql <<< "start slave sql_thread; do sleep(1); show slave status\G stop slave" | grep Seconds_Behind_Master
 
        Seconds_Behind_Master: NULL


mysql <<< "stop slave; do sleep(1); show slave status\G" | grep Seconds_Behind_Master
 
        Seconds_Behind_Master: NULL

Many thanks for looking into that,

JFG

 Comments   
Comment by Elena Stepanova [ 2018-01-23 ]

It looks like a usual documentation deficiency. MySQL manual actually describes it quite explicitly:

In MySQL 5.6.9 and later, this field is NULL (undefined or unknown) if the slave SQL thread is not running, or if the SQL thread has consumed all of the relay log and the slave I/O thread is not running. Previously, this field was NULL if the slave SQL thread or the slave I/O thread was not running or was not connected to the master.

https://dev.mysql.com/doc/refman/5.6/en/show-slave-status.html

So, both before and after 5.6.9, the field was/is NULL when IO thread was not running.
I'll leave it to Elkin to decide if he wants to change it. In any case, it should be documented much better, since Seconds_Behind_Master is one of the most notoriously confusing status values in MySQL / MariaDB.

--source include/master-slave.inc
 
 
 
create table t1 (i int);
 
drop table t1;
 
 
 
--sync_slave_with_master
 
 
 
--let $status_items= Seconds_Behind_Master
 
--source include/show_slave_status.inc
 
 
 
STOP SLAVE IO_THREAD;
 
--source include/wait_for_slave_io_to_stop.inc
 
 
 
--let $status_items= Seconds_Behind_Master
 
--source include/show_slave_status.inc
 
 
 
START SLAVE IO_THREAD;
 
--source include/wait_for_slave_io_to_start.inc
 
 
 
--let $status_items= Seconds_Behind_Master
 
--source include/show_slave_status.inc
 
 
 
--connection master
 
 
 
create table t1 (i int);
 
drop table t1;
 
 
 
--sync_slave_with_master
 
 
 
--let $status_items= Seconds_Behind_Master
 
--source include/show_slave_status.inc
 
 
 
# Cleanup
 
 
 
--connection master
 
--source include/rpl_end.inc

output

 
include/master-slave.inc
 
[connection master]
 
create table t1 (i int);
 
drop table t1;
 
connection slave;
 
Seconds_Behind_Master = '0'
 
STOP SLAVE IO_THREAD;
 
include/wait_for_slave_io_to_stop.inc
 
Seconds_Behind_Master = 'NULL'
 
START SLAVE IO_THREAD;
 
include/wait_for_slave_io_to_start.inc
 
Seconds_Behind_Master = '0'
 
connection master;
 
create table t1 (i int);
 
drop table t1;
 
connection slave;
 
Seconds_Behind_Master = '0'
 
connection master;
 
include/rpl_end.inc
 
bug.mdev15010 'mix'                      [ pass ]    992

Comment by Andrei Elkin [ 2018-01-24 ]

Thanks elenst for bring some background and showing it with a test!

Indeed Mariadb behaviour in this regard is like mysql before 5.6.9: NULL is displayed
when the IO thread is down, regardless of the SQL thread status.
To display a numeric value when the IO thread is down is reasonable, as well to indicate
about the reached synchronization with NULL rather than 0 of when the IO thread is up.

Comment by Sujatha Sivakumar (Inactive) [ 2019-06-19 ]

Hello Andrei,

Can you please review the fixes for MDEV-15010.

http://buildbot.askmonty.org/buildbot/grid?category=main&branch=bb-5.5-sujatha
https://github.com/MariaDB/server/commit/fb619954867232b3a733213a2703b2a425dff8e9

Thank you

Generated at Thu Feb 08 08:18:00 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.