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
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
|
DROP TABLE IF EXISTS table_11733 ;
grant CREATE, SELECT, DROP on *.* to test@localhost;
set global read_only=0;
create table table_11733 (a int) engine=InnoDb;
BEGIN;
insert into table_11733 values(11733);
set global read_only=1;
select @@global.read_only;
@@global.read_only
1
select * from table_11733 ;
a
11733
COMMIT;
ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement
set global read_only=0;
drop table table_11733 ;
drop user test@localhost;
GRANT CREATE, SELECT, DROP ON *.* TO test@localhost;
CREATE TABLE t1(a INT) ENGINE=INNODB;
INSERT INTO t1 VALUES (0), (1);
SET GLOBAL read_only=1;
SELECT * FROM t1;
a
0
1
BEGIN;
SELECT * FROM t1;
a
0
1
COMMIT;
SET GLOBAL read_only=0;
FLUSH TABLES WITH READ LOCK;
SELECT * FROM t1;
a
0
1
BEGIN;
SELECT * FROM t1;
a
0
1
COMMIT;
UNLOCK TABLES;
connection con1;
lock table t1 read;
connection default;
set global read_only=1;
connection con1;
unlock tables;
connection default;
SET GLOBAL read_only=0;
UNLOCK TABLES;
DROP TABLE t1;
DROP USER test@localhost;
echo End of 5.1 tests
#
# Bug#33669: Transactional temporary tables do not work under --read-only
#
DROP DATABASE IF EXISTS db1;
# Setup user and tables
CREATE USER bug33669@localhost;
CREATE DATABASE db1;
CREATE TABLE db1.t1 (a INT) ENGINE=INNODB;
CREATE TABLE db1.t2 (a INT) ENGINE=INNODB;
INSERT INTO db1.t1 VALUES (1);
INSERT INTO db1.t2 VALUES (2);
GRANT CREATE TEMPORARY TABLES, DROP, INSERT, DELETE, UPDATE,
SELECT, LOCK TABLES ON db1.* TO bug33669@localhost;
SET GLOBAL READ_ONLY = ON;
# Connection con1 (user bug33669):
# Create, insert and drop temporary table:
CREATE TEMPORARY TABLE temp (a INT) ENGINE=INNODB;
INSERT INTO temp VALUES (1);
DROP TABLE temp;
# Lock base tables and use temporary table:
CREATE TEMPORARY TABLE temp (a INT) ENGINE=INNODB;
LOCK TABLES t1 READ, t2 READ;
SELECT * FROM t1;
a
1
INSERT INTO temp values (1);
SELECT * FROM t2;
a
2
UNLOCK TABLES;
DROP TABLE temp;
# Transaction
BEGIN;
SELECT * FROM t1;
a
1
CREATE TEMPORARY TABLE temp (a INT) ENGINE=INNODB;
INSERT INTO t1 VALUES (1);
ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement
INSERT INTO temp VALUES (1);
SELECT * FROM t2;
a
2
ROLLBACK;
SELECT * FROM temp;
a
DROP TABLE temp;
# Lock base table as READ and temporary table as WRITE:
CREATE TEMPORARY TABLE temp (a INT) ENGINE=INNODB;
LOCK TABLES t1 READ, temp WRITE;
SELECT * FROM t1;
a
1
SELECT * FROM temp;
a
INSERT INTO t1 VALUES (1);
ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement
INSERT INTO temp VALUES (1);
DROP TABLE temp;
UNLOCK TABLES;
# Lock temporary table that shadows a base table:
CREATE TEMPORARY TABLE t1 (a INT) ENGINE=INNODB;
LOCK TABLES t1 WRITE;
DROP TABLE t1;
SELECT * FROM t1;
ERROR HY000: Table 't1' was not locked with LOCK TABLES
# INSERT SELECT from base table into temporary table:
CREATE TEMPORARY TABLE temp1 (a INT) ENGINE=INNODB;
CREATE TEMPORARY TABLE temp2 LIKE temp1;
BEGIN;
INSERT INTO temp1 VALUES (10);
INSERT INTO temp2 VALUES (10);
INSERT INTO temp1 SELECT * FROM t1;
INSERT INTO temp2 SELECT * FROM t2;
SELECT * FROM temp1 ORDER BY a;
a
1
10
SELECT * FROM temp2 ORDER BY a;
a
2
10
ROLLBACK;
SELECT * FROM temp1,temp2;
a a
LOCK TABLES t1 READ, t2 READ;
INSERT INTO temp1 VALUES (10);
INSERT INTO temp2 VALUES (10);
INSERT INTO temp1 SELECT * FROM t1;
INSERT INTO temp2 SELECT * FROM t2;
SELECT * FROM temp1 ORDER BY a;
a
1
10
SELECT * FROM temp2 ORDER BY a;
a
2
10
UNLOCK TABLES;
DELETE temp1, temp2 FROM temp1, temp2;
INSERT INTO temp1 VALUES (10);
INSERT INTO temp2 VALUES (10);
INSERT INTO temp1 SELECT * FROM t1;
INSERT INTO temp2 SELECT * FROM t2;
SELECT * FROM temp1 ORDER BY a;
a
1
10
SELECT * FROM temp2 ORDER BY a;
a
2
10
DROP TABLE temp1, temp2;
# INSERT and INSERT SELECT that uses subqueries:
CREATE TEMPORARY TABLE temp1 (a INT) ENGINE=INNODB;
CREATE TEMPORARY TABLE temp2 LIKE temp1;
INSERT INTO temp1 (a) VALUES ((SELECT MAX(a) FROM t1));
LOCK TABLES t2 READ;
INSERT INTO temp2 (a) VALUES ((SELECT MAX(a) FROM t2));
UNLOCK TABLES;
LOCK TABLES t1 READ, t2 READ;
INSERT INTO temp1 SELECT * FROM t1 WHERE a < (SELECT MAX(a) FROM t2);
INSERT INTO temp2 SELECT * FROM t2 WHERE a > (SELECT MAX(a) FROM t1);
UNLOCK TABLES;
INSERT INTO temp1 SELECT * FROM t1 WHERE a < (SELECT MAX(a) FROM t2);
INSERT INTO temp2 SELECT * FROM t2 WHERE a > (SELECT MAX(a) FROM t1);
SELECT * FROM temp1 ORDER BY a;
a
1
1
1
SELECT * FROM temp2 ORDER BY a;
a
2
2
2
DROP TABLE temp1, temp2;
# Multiple table update:
CREATE TEMPORARY TABLE temp1 (a INT) ENGINE=INNODB;
CREATE TEMPORARY TABLE temp2 LIKE temp1;
INSERT INTO temp1 VALUES (1),(2);
INSERT INTO temp2 VALUES (3),(4);
UPDATE temp1,temp2 SET temp1.a = 5, temp2.a = 10;
SELECT * FROM temp1, temp2;
a a
5 10
5 10
5 10
5 10
DROP TABLE temp1, temp2;
# Disconnect and cleanup
SET GLOBAL READ_ONLY = OFF;
DROP USER bug33669@localhost;
DROP DATABASE db1;
|
