Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8294

Inconsistent behavior of slave parallel threads at runtime

        Details

          Description

          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

            Gliffy Diagrams

              Attachments

                Issue Links

                relates to

                Bug - A problem which impairs or prevents the functions of the product. MDEV-8291 Parallel replication causes slave threads to not pick up new global config after restart

                • Major - Major loss of function.
                • Closed

                Task - A task that needs to be done. MDEV-8292 group_concat_max_len should be stored in the binary log

                • Major - Major loss of function.
                • Open

                  Activity

                  Ascending order - Click to sort in descending order
                  Hide
                  Permalink
                  knielsen Kristian Nielsen added a comment -

                  Pushed to 10.0.20:

                  http://lists.askmonty.org/pipermail/commits/2015-June/008031.html

                  Show
                  knielsen Kristian Nielsen added a comment - Pushed to 10.0.20: http://lists.askmonty.org/pipermail/commits/2015-June/008031.html
                  Hide
                  Permalink
                  knielsen Kristian Nielsen added a comment -

                  Also pushed to 10.1.6

                  Show
                  knielsen Kristian Nielsen added a comment - Also pushed to 10.1.6

                    People

                    • Assignee:
                      knielsen Kristian Nielsen
                      Reporter:
                      elenst Elena Stepanova
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      1 Start watching this issue

                      Dates

                      • Created:
                        Updated:
                        Resolved: