Debian Codegraph @

mysql-test/suite/rpl_nogtid/t/rpl_read_only.test

raw
1 # Test case for BUG #11733
2 --source include/not_have_privilege_checks_user.inc
3 -- source include/master-slave.inc
4
5 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
6
7 # Create a test and replicate it to slave
8 connection master;
9 create user test;
10 --source include/sync_slave_sql_with_master.inc
11
12 # Setting the master readonly :
13 # - the variable @@readonly is not replicated on the slave
14
15 connect (master2,127.0.0.1,test,,test,$MASTER_MYPORT,);
16 connect (slave2,127.0.0.1,test,,test,$SLAVE_MYPORT,);
17
18 connection master1;
19
20 create table t1(a int) engine=InnoDB;
21 insert into t1 values(1001);
22
23 connection master;
24 set global read_only=1;
25
26 connection master1;
27 select @@read_only;
28 select * from t1;
29
30 --source include/sync_slave_sql_with_master.inc
31 select @@read_only;
32 select * from t1;
33
34 # - replication of transactions
35 connection master;
36 set global read_only=0;
37
38 connection master1;
39 BEGIN;
40
41 connection master2;
42 BEGIN;
43
44 connection master;
45 select @@read_only;
46 set global read_only=1;
47
48 connection master1;
49 -- echo *** On SUPER USER connection ***
50 insert into t1 values(1002);
51
52 connection master2;
53 -- echo *** On regular USER connection ***
54 --error ER_OPTION_PREVENTS_STATEMENT
55 insert into t1 values(1003);
56
57 connection master1;
58 ## works even with read_only=1, because master1 is root
59 -- echo *** SUPER USER COMMIT (must succeed) ***
60 COMMIT;
61
62 connection master2;
63 -- echo *** regular USER COMMIT (must succeed - nothing to commit) ***
64 COMMIT;
65
66 connection master;
67 select @@read_only;
68 set global read_only=0;
69
70 connection master1;
71 insert into t1 values(1004);
72
73 select * from t1;
74
75 --source include/sync_slave_sql_with_master.inc
76 select * from t1;
77
78 # Setting the slave readonly : replication will pass
79 #
80 connection slave1;
81 set global read_only=1;
82
83 connection slave;
84 select @@read_only;
85 # Make sure the replicated table is also transactional
86 show create table t1;
87
88 connection master;
89 insert into t1 values(1005);
90 select * from t1;
91
92 --source include/sync_slave_sql_with_master.inc
93 connection slave;
94 select * from t1;
95
96 # Non root user can not write on the slave
97 connection slave2;
98 --error ER_OPTION_PREVENTS_STATEMENT
99 insert into t1 values(1006);
100
101 ## Cleanup
102 connection master;
103 drop user test;
104 drop table t1;
105 --source include/sync_slave_sql_with_master.inc
106 set global read_only=0;
107
108
109 --source include/rpl_end.inc