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
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
|
# Test of the READ_ONLY global variable:
# check that it blocks updates unless they are only on temporary tables.
# should work with embedded server after mysqltest is fixed
-- source include/not_embedded.inc
--disable_warnings
DROP TABLE IF EXISTS t1,t2,t3;
--enable_warnings
# READ_ONLY does nothing to SUPER users
# so we use a non-SUPER one:
grant CREATE, SELECT, DROP on *.* to test@localhost;
connect (con1,localhost,test,,test);
connection default;
set global read_only=0;
connection con1;
create table t1 (a int);
insert into t1 values(1);
create table t2 select * from t1;
connection default;
set global read_only=1;
# We check that SUPER can:
create table t3 (a int);
drop table t3;
connection con1;
select @@global.read_only;
--error 1290
create table t3 (a int);
--error 1290
insert into t1 values(1);
# if a statement, after parse stage, looks like it will update a
# non-temp table, it will be rejected, even if at execution it would
# have turned out that 0 rows would be updated
--error 1290
update t1 set a=1 where 1=0;
# multi-update is special (see sql_parse.cc) so we test it
--error 1290
update t1,t2 set t1.a=t2.a+1 where t1.a=t2.a;
# check multi-delete to be sure
--error 1290
delete t1,t2 from t1,t2 where t1.a=t2.a;
# With temp tables updates should be accepted:
create temporary table t3 (a int);
create temporary table t4 (a int) select * from t3;
insert into t3 values(1);
insert into t4 select * from t3;
# a non-temp table updated:
--error 1290
update t1,t3 set t1.a=t3.a+1 where t1.a=t3.a;
# no non-temp table updated (just swapped):
update t1,t3 set t3.a=t1.a+1 where t1.a=t3.a;
update t4,t3 set t4.a=t3.a+1 where t4.a=t3.a;
--error 1290
delete t1 from t1,t3 where t1.a=t3.a;
delete t3 from t1,t3 where t1.a=t3.a;
delete t4 from t3,t4 where t4.a=t3.a;
# and even homonymous ones
create temporary table t1 (a int);
insert into t1 values(1);
update t1,t3 set t1.a=t3.a+1 where t1.a=t3.a;
delete t1 from t1,t3 where t1.a=t3.a;
drop table t1;
--error 1290
insert into t1 values(1);
#
# BUG #22077 "DROP TEMPORARY TABLE fails with wrong error if read_only is set"
#
# check if DROP TEMPORARY on a non-existing temporary table returns the right
# error
--error ER_BAD_TABLE_ERROR
drop temporary table ttt;
# check if DROP TEMPORARY TABLE IF EXISTS produces a warning with read_only set
drop temporary table if exists ttt;
connection default;
drop table t1,t2;
drop user test@localhost;
--echo #
--echo # Bug #27440 read_only allows create and drop database
--echo #
--disable_warnings
drop database if exists mysqltest_db1;
drop database if exists mysqltest_db2;
--enable_warnings
delete from mysql.user where User like 'mysqltest_%';
delete from mysql.db where User like 'mysqltest_%';
delete from mysql.tables_priv where User like 'mysqltest_%';
delete from mysql.columns_priv where User like 'mysqltest_%';
flush privileges;
grant all on mysqltest_db2.* to `mysqltest_u1`@`%`;
create database mysqltest_db1;
grant all on mysqltest_db1.* to `mysqltest_u1`@`%`;
flush privileges;
connect (con_bug27440,127.0.0.1,mysqltest_u1,,test,$MASTER_MYPORT,);
connection con_bug27440;
--error ER_OPTION_PREVENTS_STATEMENT
create database mysqltest_db2;
show databases like '%mysqltest_db2%';
--error ER_OPTION_PREVENTS_STATEMENT
drop database mysqltest_db1;
disconnect con_bug27440;
connection default;
delete from mysql.user where User like 'mysqltest_%';
delete from mysql.db where User like 'mysqltest_%';
delete from mysql.tables_priv where User like 'mysqltest_%';
delete from mysql.columns_priv where User like 'mysqltest_%';
flush privileges;
drop database mysqltest_db1;
set global read_only=0;
|
