Debian Codegraph @

mysql-test/suite/perfschema/r/idx_compare_metadata_locks.result

raw
1 # Prevent background threads to acquire mdl locks, which
2 # the result unstable
3 SET GLOBAL innodb_fast_shutdown=0;
4 # restart: --debug=d,do_not_meta_lock_in_background
5 TABLE: "metadata_locks"
6 INDEXES: PK (OBJECT_INSTANCE_BEGIN), KEY (OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME), KEY (OWNER_THREAD_ID, OWNER_EVENT_ID)
7
8 ===== SETUP =====
9 ## Connection Default
10 ## Disable events from the default connection
11
12 ## Create user1, user2, user3
13 CREATE USER user1@localhost;
14 CREATE USER user2@localhost;
15 CREATE USER user3@localhost;
16 GRANT ALL ON *.* to 'user1'@localhost;
17 GRANT ALL ON *.* to 'user2'@localhost;
18 GRANT ALL ON *.* to 'user3'@localhost;
19
20 ## Setup test connection 1
21 ## Setup test connection 2
22 ## Setup test connection 3
23
24 ## Default connection
25
26 ## Create data tables
27 CREATE TABLE test.t1(a INT, b INT, c INT, d INT DEFAULT 0, PRIMARY KEY(a), INDEX index_b(b), INDEX index_cb(c, b));
28 CREATE TABLE test.t2 LIKE test.t1;
29 CREATE TABLE test.t3 LIKE test.t1;
30
31 ## Generate event data from 3 connections
32 ## Connection 1
33 ## Connection 2
34 ## Connection 3
35 ## Connection 2
36 ## Create prepared statements on connection 2
37 PREPARE st1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
38 PREPARE st3 FROM 'INSERT INTO test.t1 SELECT * FROM test.t1 WHERE a<=?';
39 PREPARE st4 FROM '(SELECT a FROM test.t1) UNION (SELECT a+10 FROM test.t1) ORDER BY RAND()*0+a';
40 ## Create stored procedure connection 2
41 CREATE PROCEDURE SampleProc1()
42 BEGIN
43 SET @table = 'test.t1';
44 SET @s = CONCAT('SELECT * FROM ', @table);
45 PREPARE st2 FROM @s;
46 END|
47
48 Call SampleProc1() to prepare a statement
49 CALL SampleProc1();
50 ## Connection default
51
52 ## Use Connection 2 for default target thread id
53
54 ## Create index_test() procedure
55
56 ## Test OBJECT_INSTANCE_BEGIN
57
58 ===== BEGIN TEST =====
59 ========
60 SELECT * FROM performance_schema.metadata_locks WHERE object_instance_begin is null
61
62 OK: Handler_read_key
63
64 OK: Result set: Empty Expected: Empty
65
66 OK: Extra rows: 0 Missing rows: 0
67 ========
68 SELECT * FROM performance_schema.metadata_locks WHERE object_instance_begin is not null
69
70 OK: Handler_read_key
71
72 OK: Result set: Non-empty Expected: Non-empty
73
74 OK: Extra rows: 0 Missing rows: 0
75 ========
76 SELECT * FROM performance_schema.metadata_locks WHERE object_instance_begin = @object_instance_begin
77
78 OK: Handler_read_key
79
80 OK: Result set: Non-empty Expected: Non-empty
81
82 OK: Extra rows: 0 Missing rows: 0
83 ========
84 SELECT * FROM performance_schema.metadata_locks WHERE object_instance_begin != @object_instance_begin
85
86 OK: Handler_read_key
87
88 OK: Result set: Non-empty Expected: Non-empty
89
90 OK: Extra rows: 0 Missing rows: 0
91 ========
92 SELECT * FROM performance_schema.metadata_locks WHERE object_instance_begin = '9999999999'
93
94 OK: Handler_read_key
95
96 OK: Result set: Empty Expected: Empty
97
98 OK: Extra rows: 0 Missing rows: 0
99
100 ## Test OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME
101
102 ===== BEGIN TEST =====
103 ========
104 SELECT * FROM performance_schema.metadata_locks WHERE object_type is null
105
106 OK: Handler_read_key
107
108 OK: Result set: Empty Expected: Empty
109
110 OK: Extra rows: 0 Missing rows: 0
111 ========
112 SELECT * FROM performance_schema.metadata_locks WHERE object_type is not null
113
114 OK: Handler_read_key
115
116 OK: Result set: Non-empty Expected: Non-empty
117
118 OK: Extra rows: 0 Missing rows: 0
119 ========
120 SELECT * FROM performance_schema.metadata_locks WHERE object_type = 'TABLE' and object_schema is null
121
122 OK: Handler_read_key
123
124 OK: Result set: Empty Expected: Empty
125
126 OK: Extra rows: 0 Missing rows: 0
127 ========
128 SELECT * FROM performance_schema.metadata_locks WHERE object_type = 'TABLE' and object_schema is not null
129
130 OK: Handler_read_key
131
132 OK: Result set: Non-empty Expected: Non-empty
133
134 OK: Extra rows: 0 Missing rows: 0
135 ========
136 SELECT * FROM performance_schema.metadata_locks WHERE object_type = 'TABLE' and object_schema = 'test' and object_name is null
137
138 OK: Handler_read_key
139
140 OK: Result set: Empty Expected: Empty
141
142 OK: Extra rows: 0 Missing rows: 0
143 ========
144 SELECT * FROM performance_schema.metadata_locks WHERE object_type = 'TABLE' and object_schema = 'test' and object_name is not null
145
146 OK: Handler_read_key
147
148 OK: Result set: Non-empty Expected: Non-empty
149
150 OK: Extra rows: 0 Missing rows: 0
151 ========
152 SELECT * FROM performance_schema.metadata_locks WHERE object_type = 'TABLE'
153
154 OK: Handler_read_key
155
156 OK: Result set: Non-empty Expected: Non-empty
157
158 OK: Extra rows: 0 Missing rows: 0
159 ========
160 SELECT * FROM performance_schema.metadata_locks WHERE object_type = 'TABLE' and object_schema = 'test'
161
162 OK: Handler_read_key
163
164 OK: Result set: Non-empty Expected: Non-empty
165
166 OK: Extra rows: 0 Missing rows: 0
167 ========
168 SELECT * FROM performance_schema.metadata_locks WHERE object_type = 'TABLE' AND object_schema = 'test' AND object_name = 't1'
169
170 OK: Handler_read_key
171
172 OK: Result set: Non-empty Expected: Non-empty
173
174 OK: Extra rows: 0 Missing rows: 0
175 ========
176 SELECT * FROM performance_schema.metadata_locks WHERE object_type = 'impossible' AND object_schema = 'test' AND object_name = 't1'
177
178 OK: Handler_read_key
179
180 OK: Result set: Empty Expected: Empty
181
182 OK: Extra rows: 0 Missing rows: 0
183 ========
184 SELECT * FROM performance_schema.metadata_locks WHERE object_type = 'impossible'
185
186 OK: Handler_read_key
187
188 OK: Result set: Empty Expected: Empty
189
190 OK: Extra rows: 0 Missing rows: 0
191 ========
192 SELECT * FROM performance_schema.metadata_locks WHERE object_type = 'TABLE' AND object_schema = 'impossible'
193
194 OK: Handler_read_key
195
196 OK: Result set: Empty Expected: Empty
197
198 OK: Extra rows: 0 Missing rows: 0
199 ========
200 SELECT * FROM performance_schema.metadata_locks WHERE object_type = 'TABLE' AND object_schema = 'test' AND object_name = 'impossible'
201
202 OK: Handler_read_key
203
204 OK: Result set: Empty Expected: Empty
205
206 OK: Extra rows: 0 Missing rows: 0
207
208 ## Test OWNER_THREAD_ID, OWNER_EVENT_ID
209
210 ===== BEGIN TEST =====
211 ========
212 SELECT * FROM performance_schema.metadata_locks WHERE owner_thread_id is null
213
214 OK: Handler_read_key
215
216 OK: Result set: Empty Expected: Empty
217
218 OK: Extra rows: 0 Missing rows: 0
219 ========
220 SELECT * FROM performance_schema.metadata_locks WHERE owner_thread_id is not null
221
222 OK: Handler_read_key
223
224 OK: Result set: Non-empty Expected: Non-empty
225
226 OK: Extra rows: 0 Missing rows: 0
227 ========
228 SELECT * FROM performance_schema.metadata_locks WHERE owner_thread_id = @target_thread_id
229
230 OK: Handler_read_key
231
232 OK: Result set: Non-empty Expected: Non-empty
233
234 OK: Extra rows: 0 Missing rows: 0
235 ========
236 SELECT * FROM performance_schema.metadata_locks WHERE owner_thread_id = @target_thread_id AND owner_event_id = @target_event_id
237
238 OK: Handler_read_key
239
240 OK: Result set: Non-empty Expected: Non-empty
241
242 OK: Extra rows: 0 Missing rows: 0
243 ========
244 SELECT * FROM performance_schema.metadata_locks WHERE owner_event_id != 9999999
245
246 OK: Handler_read_key
247
248 OK: Result set: Non-empty Expected: Non-empty
249
250 OK: Extra rows: 0 Missing rows: 0
251 ========
252 SELECT * FROM performance_schema.metadata_locks WHERE owner_thread_id = @target_thread_id ORDER BY owner_thread_id
253
254 OK: Handler_read_key
255
256 OK: Result set: Non-empty Expected: Non-empty
257
258 OK: Extra rows: 0 Missing rows: 0
259 ========
260 SELECT * FROM performance_schema.metadata_locks WHERE owner_thread_id = @target_thread_id ORDER BY owner_thread_id DESC
261
262 OK: Handler_read_key
263
264 OK: Result set: Non-empty Expected: Non-empty
265
266 OK: Extra rows: 0 Missing rows: 0
267 ========
268 SELECT * FROM performance_schema.metadata_locks WHERE owner_thread_id = @target_thread_id AND owner_event_id is null
269
270 OK: Handler_read_key
271
272 OK: Result set: Empty Expected: Empty
273
274 OK: Extra rows: 0 Missing rows: 0
275 ========
276 SELECT * FROM performance_schema.metadata_locks WHERE owner_thread_id = @target_thread_id AND owner_event_id is not null
277
278 OK: Handler_read_key
279
280 OK: Result set: Non-empty Expected: Non-empty
281
282 OK: Extra rows: 0 Missing rows: 0
283 ========
284 SELECT * FROM performance_schema.metadata_locks WHERE owner_thread_id = @target_thread_id AND owner_event_id != 99999999
285
286 OK: Handler_read_key
287
288 OK: Result set: Non-empty Expected: Non-empty
289
290 OK: Extra rows: 0 Missing rows: 0
291 ========
292 SELECT * FROM performance_schema.metadata_locks WHERE owner_thread_id = @target_thread_id AND owner_event_id = 99999999
293
294 OK: Handler_read_key
295
296 OK: Result set: Empty Expected: Empty
297
298 OK: Extra rows: 0 Missing rows: 0
299 ========
300 SELECT * FROM performance_schema.metadata_locks WHERE owner_thread_id >= @target_thread_id
301
302 OK: Handler_read_key
303
304 OK: Result set: Non-empty Expected: Non-empty
305
306 OK: Extra rows: 0 Missing rows: 0
307 ========
308 SELECT * FROM performance_schema.metadata_locks WHERE owner_thread_id <= @target_thread_id
309
310 OK: Handler_read_key
311
312 OK: Result set: Non-empty Expected: Non-empty
313
314 OK: Extra rows: 0 Missing rows: 0
315 ========
316 SELECT * FROM performance_schema.metadata_locks WHERE owner_thread_id != 9999999 AND owner_event_id = @target_event_id
317
318 OK: Handler_read_key
319
320 OK: Result set: Non-empty Expected: Non-empty
321
322 OK: Extra rows: 0 Missing rows: 0
323 ========
324 SELECT * FROM performance_schema.metadata_locks WHERE owner_thread_id = 9999999 ORDER BY owner_thread_id
325
326 OK: Handler_read_key
327
328 OK: Result set: Empty Expected: Empty
329
330 OK: Extra rows: 0 Missing rows: 0
331 UNLOCK TABLES;
332 UNLOCK TABLES;
333
334 ===== CLEAN UP =====
335 ## Default connection
336 DEALLOCATE PREPARE st1;
337 DEALLOCATE PREPARE st2;
338 DEALLOCATE PREPARE st3;
339 DEALLOCATE PREPARE st4;
340 DROP PROCEDURE SampleProc1;
341 DROP PROCEDURE index_test;
342 DROP TABLE IF EXISTS test.indexes_off;
343 DROP TABLE IF EXISTS test.indexes_on;
344 DROP TABLE test.t1;
345 DROP TABLE test.t2;
346 DROP TABLE test.t3;
347 REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost;
348 REVOKE ALL PRIVILEGES, GRANT OPTION FROM user2@localhost;
349 REVOKE ALL PRIVILEGES, GRANT OPTION FROM user3@localhost;
350 DROP USER user1@localhost;
351 DROP USER user2@localhost;
352 DROP USER user3@localhost;
353 UPDATE performance_schema.threads SET INSTRUMENTED = 'YES';
354 # restart