[#MDEV-8294] Inconsistent behavior of slave parallel threads at runtime

The problem first appeared in 10.0.18 (unless it's an intentional part of the new parallel threads' logic which I think was introduced in that version).

If a normally running replication is stopped via STOP SLAVE, it terminates all slave threads: SQL, IO, and slave parallel threads.
But if the replication aborted, further STOP SLAVE leaves slave parallel threads intact. Moreover, neither reducing nor increasing the number of parallel threads has any effect.

Test case to demonstrate the problem
--source include/master-slave.inc
--source include/have_binlog_format_statement.inc

SET SESSION binlog_format=statement;
drop table if exists t1;
set sql_log_bin = 0;
create table t1 (i int) engine=MyISAM;
set sql_log_bin = 1;
insert into t1 values (1);

--connection slave

show processlist;

--let $status_items = Last_SQL_Error,Slave_SQL_Running
sleep 1;

--source include/show_slave_status.inc
show processlist;

--source include/stop_slave.inc
show processlist;
SET GLOBAL slave_parallel_threads=5;
show processlist;
start slave;

sleep 1;
show processlist;

--source include/stop_slave.inc
show processlist;
SET GLOBAL slave_parallel_threads=1;
show processlist;
start slave;

sleep 1;
show processlist;

If the test is run with the first sql_log_bin = 0 (as above), it causes a replication abort. If it's run without that statement, it allows replication to execute normally.

Results with replication abort
set sql_log_bin = 0;
create table t1 (i int) engine=MyISAM;
set sql_log_bin = 1;
insert into t1 values (1);
show processlist;
Id User Host db Command Time State Info Progress
4 root localhost:34846 test Sleep 0 NULL 0.000
5 root localhost:34847 test Sleep 1 NULL 0.000
6 system user NULL Connect 0 Queueing master event to the relay log NULL 0.000
7 system user NULL Connect 0 Reading event from the relay log NULL 0.000
8 root localhost:34853 test Query 0 init show processlist 0.000
9 root localhost:34854 test Sleep 0 NULL 0.000
Last_SQL_Error = 'Error 'Table 'test.t1' doesn't exist' on query. Default database: 'test'. Query: 'insert into t1 values (1)''
Slave_SQL_Running = 'No'
show processlist;
Id User Host db Command Time State Info Progress
4 root localhost:34846 test Sleep 1 NULL 0.000
5 root localhost:34847 test Sleep 2 NULL 0.000
6 system user NULL Connect 1 Waiting for master to send event NULL 0.000
8 root localhost:34853 test Query 0 init show processlist 0.000
9 root localhost:34854 test Sleep 1 NULL 0.000
include/stop_slave.inc
show processlist;
Id User Host db Command Time State Info Progress
4 root localhost:34846 test Sleep 1 NULL 0.000
5 root localhost:34847 test Sleep 2 NULL 0.000
8 root localhost:34853 test Query 0 init show processlist 0.000
9 root localhost:34854 test Sleep 1 NULL 0.000
SET GLOBAL slave_parallel_threads=5;
show processlist;
Id User Host db Command Time State Info Progress
4 root localhost:34846 test Sleep 1 NULL 0.000
5 root localhost:34847 test Sleep 2 NULL 0.000
8 root localhost:34853 test Query 0 init show processlist 0.000
9 root localhost:34854 test Sleep 1 NULL 0.000
start slave;
show processlist;
Id User Host db Command Time State Info Progress
4 root localhost:34846 test Sleep 2 NULL 0.000
5 root localhost:34847 test Sleep 3 NULL 0.000
8 root localhost:34853 test Query 0 init show processlist 0.000
9 root localhost:34854 test Sleep 2 NULL 0.000
10 system user NULL Connect 1 Waiting for master to send event NULL 0.000
11 system user NULL Connect 1 Waiting for work from SQL thread NULL 0.000
12 system user NULL Connect 1 Waiting for work from SQL thread NULL 0.000
13 system user NULL Connect 1 Waiting for work from SQL thread NULL 0.000
14 system user NULL Connect 1 Waiting for work from SQL thread NULL 0.000
15 system user NULL Connect 2 Waiting for work from SQL thread NULL 0.000
include/stop_slave.inc
show processlist;
Id User Host db Command Time State Info Progress
4 root localhost:34846 test Sleep 2 NULL 0.000
5 root localhost:34847 test Sleep 3 NULL 0.000
8 root localhost:34853 test Query 0 init show processlist 0.000
9 root localhost:34854 test Sleep 2 NULL 0.000
11 system user NULL Connect 1 Waiting for work from SQL thread NULL 0.000
12 system user NULL Connect 1 Waiting for work from SQL thread NULL 0.000
13 system user NULL Connect 1 Waiting for work from SQL thread NULL 0.000
14 system user NULL Connect 1 Waiting for work from SQL thread NULL 0.000
15 system user NULL Connect 2 Waiting for work from SQL thread NULL 0.000
SET GLOBAL slave_parallel_threads=1;
show processlist;
Id User Host db Command Time State Info Progress
4 root localhost:34846 test Sleep 2 NULL 0.000
5 root localhost:34847 test Sleep 3 NULL 0.000
8 root localhost:34853 test Query 0 init show processlist 0.000
9 root localhost:34854 test Sleep 2 NULL 0.000
11 system user NULL Connect 1 Waiting for work from SQL thread NULL 0.000
12 system user NULL Connect 1 Waiting for work from SQL thread NULL 0.000
13 system user NULL Connect 1 Waiting for work from SQL thread NULL 0.000
14 system user NULL Connect 1 Waiting for work from SQL thread NULL 0.000
15 system user NULL Connect 2 Waiting for work from SQL thread NULL 0.000
start slave;
show processlist;
Id User Host db Command Time State Info Progress
4 root localhost:34846 test Sleep 3 NULL 0.000
5 root localhost:34847 test Sleep 4 NULL 0.000
8 root localhost:34853 test Query 0 init show processlist 0.000
9 root localhost:34854 test Sleep 3 NULL 0.000
11 system user NULL Connect 2 Waiting for work from SQL thread NULL 0.000
12 system user NULL Connect 2 Waiting for work from SQL thread NULL 0.000
13 system user NULL Connect 2 Waiting for work from SQL thread NULL 0.000
14 system user NULL Connect 2 Waiting for work from SQL thread NULL 0.000
15 system user NULL Connect 3 Waiting for work from SQL thread NULL 0.000
17 system user NULL Connect 1 Waiting for master to send event NULL 0.000

Results with normal execution
set sql_log_bin = 1;
create table t1 (i int) engine=MyISAM;
set sql_log_bin = 1;
insert into t1 values (1);
show processlist;
Id User Host db Command Time State Info Progress
4 root localhost:34871 test Sleep 0 NULL 0.000
5 root localhost:34872 test Sleep 1 NULL 0.000
6 system user NULL Connect 0 Queueing master event to the relay log NULL 0.000
7 system user NULL Connect 0 Reading event from the relay log BEGIN 0.000
8 root localhost:34878 test Query 0 init show processlist 0.000
9 root localhost:34879 test Sleep 0 NULL 0.000
Last_SQL_Error = ''
Slave_SQL_Running = 'Yes'
show processlist;
Id User Host db Command Time State Info Progress
4 root localhost:34871 test Sleep 1 NULL 0.000
5 root localhost:34872 test Sleep 2 NULL 0.000
6 system user NULL Connect 1 Waiting for master to send event NULL 0.000
7 system user NULL Connect 1 Slave has read all relay log; waiting for the slave I/O thread to update it NULL 0.000
8 root localhost:34878 test Query 0 init show processlist 0.000
9 root localhost:34879 test Sleep 1 NULL 0.000
include/stop_slave.inc
show processlist;
Id User Host db Command Time State Info Progress
4 root localhost:34871 test Sleep 1 NULL 0.000
5 root localhost:34872 test Sleep 2 NULL 0.000
8 root localhost:34878 test Query 0 init show processlist 0.000
9 root localhost:34879 test Sleep 1 NULL 0.000
SET GLOBAL slave_parallel_threads=5;
show processlist;
Id User Host db Command Time State Info Progress
4 root localhost:34871 test Sleep 1 NULL 0.000
5 root localhost:34872 test Sleep 2 NULL 0.000
8 root localhost:34878 test Query 0 init show processlist 0.000
9 root localhost:34879 test Sleep 1 NULL 0.000
start slave;
show processlist;
Id User Host db Command Time State Info Progress
4 root localhost:34871 test Sleep 2 NULL 0.000
5 root localhost:34872 test Sleep 3 NULL 0.000
8 root localhost:34878 test Query 0 init show processlist 0.000
9 root localhost:34879 test Sleep 2 NULL 0.000
10 system user NULL Connect 1 Waiting for master to send event NULL 0.000
11 system user NULL Connect 1 Waiting for work from SQL thread NULL 0.000
12 system user NULL Connect 1 Waiting for work from SQL thread NULL 0.000
13 system user NULL Connect 1 Waiting for work from SQL thread NULL 0.000
14 system user NULL Connect 1 Waiting for work from SQL thread NULL 0.000
15 system user NULL Connect 1 Waiting for work from SQL thread NULL 0.000
16 system user NULL Connect 1 Slave has read all relay log; waiting for the slave I/O thread to update it NULL 0.000
include/stop_slave.inc
show processlist;
Id User Host db Command Time State Info Progress
4 root localhost:34871 test Sleep 2 NULL 0.000
5 root localhost:34872 test Sleep 3 NULL 0.000
8 root localhost:34878 test Query 0 init show processlist 0.000
9 root localhost:34879 test Sleep 2 NULL 0.000
SET GLOBAL slave_parallel_threads=1;
show processlist;
Id User Host db Command Time State Info Progress
4 root localhost:34871 test Sleep 2 NULL 0.000
5 root localhost:34872 test Sleep 3 NULL 0.000
8 root localhost:34878 test Query 0 init show processlist 0.000
9 root localhost:34879 test Sleep 2 NULL 0.000
start slave;
show processlist;
Id User Host db Command Time State Info Progress
4 root localhost:34871 test Sleep 3 NULL 0.000
5 root localhost:34872 test Sleep 4 NULL 0.000
8 root localhost:34878 test Query 0 init show processlist 0.000
9 root localhost:34879 test Sleep 3 NULL 0.000
17 system user NULL Connect 1 Waiting for master to send event NULL 0.000
18 system user NULL Connect 1 Waiting for work from SQL thread NULL 0.000
19 system user NULL Connect 1 Slave has read all relay log; waiting for the slave I/O thread to update it NULL 0.000

[MDEV-8294] Inconsistent behavior of slave parallel threads at runtime Created: 2015-06-10 Updated: 2015-06-15 Resolved: 2015-06-10
Status:	Closed
Project:	MariaDB Server
Component/s:	Replication
Affects Version/s:	10.0, 10.1
Fix Version/s:	10.0.20, 10.1.6

[MDEV-8294] Inconsistent behavior of slave parallel threads at runtime Created: 2015-06-10 Updated: 2015-06-15 Resolved: 2015-06-10