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