Debian Codegraph @

mysql-test/suite/binlog/r/read_only_statement.result

raw
1 #
2 # MDEV-17863 DROP TEMPORARY TABLE creates a transaction in
3 # binary log on read only server
4 # MDEV-19074 Improved read_only mode for slaves with
5 # gtid_strict_mode enabled
6 #
7 create user test@localhost;
8 grant CREATE TEMPORARY TABLES, DROP, INSERT, SELECT on *.* to test@localhost;
9 create table t1 (a int) engine=myisam;
10 insert into t1 values (1),(2);
11 reset master;
12 set global read_only=1;
13 # Ensure that optimize and analyze doesn't log to binary log
14 connect con1,localhost,test,,test;
15 insert into t1 values(3);
16 ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement
17 analyze table t1;
18 Table Op Msg_type Msg_text
19 test.t1 analyze status Engine-independent statistics collected
20 test.t1 analyze status OK
21 check table t1;
22 Table Op Msg_type Msg_text
23 test.t1 check status OK
24 repair table t1;
25 Table Op Msg_type Msg_text
26 test.t1 repair Error The MariaDB server is running with the --read-only option so it cannot execute this statement
27 test.t1 repair error Corrupt
28 optimize table t1;
29 ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement
30 # Ensure that using temporary tables is not logged
31 create temporary table tmp1 (a int) engine=myisam;
32 insert into tmp1 values (1),(2);
33 update tmp1 set a=10 where a=2;
34 delete from tmp1 where a=1;
35 create temporary table tmp2 select * from t1;
36 select * from tmp1;
37 a
38 10
39 select * from tmp2;
40 a
41 1
42 2
43 create temporary table tmp3 like t1;
44 create or replace temporary table tmp3 like t1;
45 alter table tmp2 add column (b int);
46 select * from tmp2;
47 a b
48 1 NULL
49 2 NULL
50 insert into t1 select a+100 from tmp2;
51 ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement
52 drop table tmp1,tmp2,tmp3;
53 # Clean up test connection
54 disconnect con1;
55 connection default;
56 # Execute some commands as root that should not be logged
57 optimize table t1;
58 Table Op Msg_type Msg_text
59 test.t1 optimize status Table is already up to date
60 repair table t1;
61 Table Op Msg_type Msg_text
62 test.t1 repair status OK
63 # Changes to temporary tables created under readonly should not
64 # be logged
65 create temporary table tmp4 (a int) engine=myisam;
66 insert into tmp4 values (1),(2);
67 create temporary table tmp5 (a int) engine=myisam;
68 insert into tmp5 select * from tmp4;
69 alter table tmp5 add column (b int);
70 set global read_only=0;
71 insert into tmp4 values (3),(4);
72 insert into tmp5 values (10,3),(11,4);
73 select * from tmp4;
74 a
75 1
76 2
77 3
78 4
79 select * from tmp5;
80 a b
81 1 NULL
82 2 NULL
83 10 3
84 11 4
85 update tmp4 set a=10 where a=2;
86 delete from tmp4 where a=1;
87 create table t2 select * from tmp4;
88 alter table tmp5 add column (c int);
89 insert into tmp5 values (20,5,1),(21,5,2);
90 select * from tmp5;
91 a b c
92 1 NULL NULL
93 2 NULL NULL
94 10 3 NULL
95 11 4 NULL
96 20 5 1
97 21 5 2
98 insert into t1 select a+200 from tmp5;
99 select * from t1;
100 a
101 1
102 2
103 201
104 202
105 210
106 211
107 220
108 221
109 drop table tmp4,tmp5;
110 # Check what is logged. Only last create select and the insert...select's should be
111 # row-logged
112 include/show_binlog_events.inc
113 Log_name Pos Event_type Server_id End_log_pos Info
114 master-bin.000001 # Gtid # # GTID #-#-#
115 master-bin.000001 # Query # # use `test`; analyze table t1
116 master-bin.000001 # Gtid # # GTID #-#-#
117 master-bin.000001 # Query # # use `test`; optimize table t1
118 master-bin.000001 # Gtid # # GTID #-#-#
119 master-bin.000001 # Query # # use `test`; repair table t1
120 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
121 master-bin.000001 # Query # # use `test`; CREATE TABLE `t2` (
122 `a` int(11) DEFAULT NULL
123 )
124 master-bin.000001 # Annotate_rows # # create table t2 select * from tmp4
125 master-bin.000001 # Table_map # # table_id: # (test.t2)
126 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
127 master-bin.000001 # Query # # COMMIT
128 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
129 master-bin.000001 # Annotate_rows # # insert into t1 select a+200 from tmp5
130 master-bin.000001 # Table_map # # table_id: # (test.t1)
131 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
132 master-bin.000001 # Query # # COMMIT
133 # Clean up
134 drop user test@localhost;
135 drop table t1,t2;