mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-10-25 00:48:31 +02:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/suite/rpl/r/rpl_start_alter_7.result
Kristian Nielsen 0a68328673 MDEV-34705: Binlog-in-engine: Protect against concurrent RESET MASTER and dump threads 
This is actually an existing problem in the old binlog implementation, and
this patch is applicable to old binlog also. The problem is that RESET
MASTER can run concurrently with binlog dump threads / connected slaves.
This will remove the binlog from under the feet of the reader, which can
cause all sorts of strange behaviour.

This patch fixes the problem by disallowing to run RESET MASTER when dump
threads (or other RESET MASTER or SHOW BINARY LOGS) are running. An error is
thrown in this case, user must stop slaves and/or kill dump threads to make
the RESET MASTER go through. A slave that connects in the middle of RESET
MASTER will wait for it to complete.

Fix a lot of test cases to kill any lingering dump threads before doing
RESET MASTER, mostly just by sourcing include/kill_binlog_dump_threads.inc.

Signed-off-by: Kristian Nielsen <knielsen@knielsen-hq.org>
2025-06-11 11:32:10 +02:00

374 lines
12 KiB
Text
Raw Permalink Blame History

connect server_1,127.0.0.1,root,,,$SERVER_MYPORT_1;
connect server_2,127.0.0.1,root,,,$SERVER_MYPORT_2;
connect server_3,127.0.0.1,root,,,$SERVER_MYPORT_3;
connection server_1;
set global binlog_alter_two_phase = ON;
set binlog_alter_two_phase = ON;
connection server_2;
stop slave;
Warnings:
Note 1255 Slave already has been stopped
set global binlog_alter_two_phase=true;
connection server_3;
SET STATEMENT sql_log_bin=0 FOR
CALL mtr.add_suppression("The table mysql.gtid_slave_pos was removed. This change will not take full effect until all SQL threads have been restarted");
SET STATEMENT sql_log_bin=0 FOR
ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB;
SET GLOBAL slave_parallel_threads=8;
set global slave_parallel_mode=optimistic;
set global gtid_strict_mode=1;
connection server_1;
set gtid_domain_id= 11;
create database s1;
use s1;
connect master_node,127.0.0.1,root,,$db_name, $M_port;
set gtid_domain_id= 11;;
connect slave_node,127.0.0.1,root,,test, $S_port;
set gtid_domain_id= 11;;
# myisam
connection master_node;
create table t1(a int, b int) engine=myisam;;
insert into t1 values(1,1);
insert into t1 values(2,2);
# Normal Alter
alter table t1 add column c int;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
# Failed Alter
insert into t1 values(1,1, NULL);
alter table t1 change a a int unique;
ERROR 23000: Duplicate entry '1' for key 'a'
set @@session.binlog_alter_two_phase = 0;
alter table t1 change a a int;
set @@session.binlog_alter_two_phase = 1;
alter table t1 change a a int;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
connection master_node;
drop table t1;
# innodb
connection master_node;
create table t1(a int, b int) engine=innodb;;
insert into t1 values(1,1);
insert into t1 values(2,2);
# Normal Alter
alter table t1 add column c int;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
# Failed Alter
insert into t1 values(1,1, NULL);
alter table t1 change a a int unique;
ERROR 23000: Duplicate entry '1' for key 'a'
set @@session.binlog_alter_two_phase = 0;
alter table t1 change a a int;
set @@session.binlog_alter_two_phase = 1;
alter table t1 change a a int;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
connection master_node;
drop table t1;
# aria
connection master_node;
create table t1(a int, b int) engine=aria;;
insert into t1 values(1,1);
insert into t1 values(2,2);
# Normal Alter
alter table t1 add column c int;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL
) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1
# Failed Alter
insert into t1 values(1,1, NULL);
alter table t1 change a a int unique;
ERROR 23000: Duplicate entry '1' for key 'a'
set @@session.binlog_alter_two_phase = 0;
alter table t1 change a a int;
set @@session.binlog_alter_two_phase = 1;
alter table t1 change a a int;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL
) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1
connection master_node;
drop table t1;
# concurrent alter Myisam
# Concurrent DML
# Rollback tests
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
# concurrent alter Aria
# Concurrent DML
# Rollback tests
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
# concurrent alter Innodb copy
# Concurrent DML
# Rollback tests
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
# concurrent alter Innodb Inplace
# Concurrent DML
# Rollback tests
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
connection server_1;
drop database s1;
select @@gtid_binlog_pos;
@@gtid_binlog_pos
11-1-412
connection server_2;
set gtid_domain_id= 12;
create database s2;
use s2;
connect master_node,127.0.0.1,root,,$db_name, $M_port;
set gtid_domain_id= 12;;
connect slave_node,127.0.0.1,root,,test, $S_port;
set gtid_domain_id= 12;;
# myisam
connection master_node;
create table t1(a int, b int) engine=myisam;;
insert into t1 values(1,1);
insert into t1 values(2,2);
# Normal Alter
alter table t1 add column c int;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
# Failed Alter
insert into t1 values(1,1, NULL);
alter table t1 change a a int unique;
ERROR 23000: Duplicate entry '1' for key 'a'
set @@session.binlog_alter_two_phase = 0;
alter table t1 change a a int;
set @@session.binlog_alter_two_phase = 1;
alter table t1 change a a int;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
connection master_node;
drop table t1;
# innodb
connection master_node;
create table t1(a int, b int) engine=innodb;;
insert into t1 values(1,1);
insert into t1 values(2,2);
# Normal Alter
alter table t1 add column c int;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
# Failed Alter
insert into t1 values(1,1, NULL);
alter table t1 change a a int unique;
ERROR 23000: Duplicate entry '1' for key 'a'
set @@session.binlog_alter_two_phase = 0;
alter table t1 change a a int;
set @@session.binlog_alter_two_phase = 1;
alter table t1 change a a int;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
connection master_node;
drop table t1;
# aria
connection master_node;
create table t1(a int, b int) engine=aria;;
insert into t1 values(1,1);
insert into t1 values(2,2);
# Normal Alter
alter table t1 add column c int;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL
) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1
# Failed Alter
insert into t1 values(1,1, NULL);
alter table t1 change a a int unique;
ERROR 23000: Duplicate entry '1' for key 'a'
set @@session.binlog_alter_two_phase = 0;
alter table t1 change a a int;
set @@session.binlog_alter_two_phase = 1;
alter table t1 change a a int;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL
) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1
connection master_node;
drop table t1;
# concurrent alter Myisam
# Concurrent DML
# Rollback tests
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
# concurrent alter Aria
# Concurrent DML
# Rollback tests
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
# concurrent alter Innodb copy
# Concurrent DML
# Rollback tests
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
# concurrent alter Innodb Inplace
# Concurrent DML
# Rollback tests
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
connection server_2;
drop database s2;
select @@gtid_binlog_pos;
@@gtid_binlog_pos
12-2-412
connection server_3;
start all slaves;
Warnings:
Note 1937 SLAVE 'm2' started
Note 1937 SLAVE 'm1' started
set default_master_connection = 'm1';
include/wait_for_slave_to_start.inc
set default_master_connection = 'm2';
include/wait_for_slave_to_start.inc
set default_master_connection = 'm1';
include/sync_with_master_gtid.inc
set default_master_connection = 'm2';
include/sync_with_master_gtid.inc
# cleanup
connection server_3;
set default_master_connection = 'm1';
include/stop_slave.inc
set default_master_connection = 'm2';
include/stop_slave.inc
set global slave_parallel_threads = 0;;
set global slave_parallel_mode = optimistic;;
set global gtid_strict_mode = 0;;
set global gtid_domain_id= 0;
reset master;
RESET SLAVE ALL;
SET GLOBAL gtid_slave_pos= '';
connection server_1;
set global binlog_alter_two_phase=0;;
set global gtid_domain_id= 0;
include/kill_binlog_dump_threads.inc
reset master;
connection server_2;
set global gtid_domain_id= 0;
set global binlog_alter_two_phase=0;
include/kill_binlog_dump_threads.inc
reset master;
disconnect server_1;
disconnect server_2;
disconnect server_3;
Reference in a new issue View git blame Copy permalink