Debian Codegraph @

mysql-test/suite/perfschema/t/mdl_func.test

raw
1 --source include/not_embedded.inc
2 --source include/have_perfschema.inc
3 # This test needs a fresh restart. The table performance_schema.table_handles
4 # can otherwise contain extra rows left from previous testcases.
5 # For example the test case main.long_unique_delayed, which uses
6 # INSERT DELAYED, will leave extra rows in this table if run just before this
7 # test, causing .result diff failure.
8 --source include/restart_mysqld.inc
9
10 UPDATE performance_schema.setup_instruments SET enabled = 'NO', timed = 'YES';
11
12 UPDATE performance_schema.setup_instruments SET enabled = 'YES'
13 WHERE name in ('wait/io/table/sql/handler',
14 'wait/lock/table/sql/handler',
15 'wait/lock/metadata/sql/mdl');
16
17 set @orig_sql_mode= @@sql_mode;
18 set sql_mode= (select replace(@@sql_mode,'NO_AUTO_CREATE_USER',''));
19 grant ALL on *.* to user1@localhost;
20 grant ALL on *.* to user2@localhost;
21 set sql_mode= @orig_sql_mode;
22
23 insert into performance_schema.setup_objects (object_type, object_schema, object_name, enabled, timed)
24 values ('TABLE', 'mtr', '%', 'NO', 'NO');
25
26 --echo # Switch to (con1, localhost, user1, , )
27 connect (con1, localhost, user1, , );
28
29 let $user1_tid=`select THREAD_ID from performance_schema.threads where PROCESSLIST_ID=connection_id()`;
30
31 --echo # Switch to (con2, localhost, user2, , )
32 connect (con2, localhost, user2, , );
33
34 let $user2_tid=`select THREAD_ID from performance_schema.threads where PROCESSLIST_ID=connection_id()`;
35
36 --connection default
37
38 --disable_query_log
39 --eval set @user1_tid= $user1_tid;
40 --eval set @user2_tid= $user2_tid;
41 --enable_query_log
42
43 --disable_warnings
44 drop function if exists thread_id_name;
45 --enable_warnings
46
47 --delimiter //
48
49 create function thread_id_name(THREAD_ID int)
50 returns varchar(16)
51 begin
52 if (THREAD_ID IS NULL)
53 then
54 return NULL;
55 end if;
56 if (THREAD_ID = @user1_tid)
57 then
58 return "USER1";
59 end if;
60 if (THREAD_ID = @user2_tid)
61 then
62 return "USER2";
63 end if;
64 return "OTHER";
65 end;
66 //
67
68 delimiter ;//
69
70 select thread_id_name(NULL);
71 select thread_id_name(@user1_tid);
72 select thread_id_name(@user2_tid);
73
74 prepare dump_metadata_locks from
75 "select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME,
76 LOCK_TYPE, LOCK_DURATION, LOCK_STATUS,
77 thread_id_name(OWNER_THREAD_ID) as OWNER_THREAD_ID
78 from performance_schema.metadata_locks
79 order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME,
80 LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, OWNER_THREAD_ID;";
81
82 prepare dump_table_handles from
83 "select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME,
84 INTERNAL_LOCK, EXTERNAL_LOCK,
85 thread_id_name(OWNER_THREAD_ID) as OWNER_THREAD_ID
86 from performance_schema.table_handles
87 order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME,
88 INTERNAL_LOCK, EXTERNAL_LOCK, OWNER_THREAD_ID;";
89
90 prepare dump_waits_current from
91 "select thread_id_name(THREAD_ID) as THREAD_ID,
92 EVENT_NAME,
93 TIMER_START is not NULL as TIMER_START_SET,
94 TIMER_END is not NULL as TIMER_END_SET,
95 OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME,
96 OPERATION
97 from performance_schema.events_waits_current
98 where event_name = \'wait/lock/metadata/sql/mdl\';";
99
100 prepare dump_waits_history_long from
101 "select thread_id_name(THREAD_ID) as THREAD_ID,
102 EVENT_NAME,
103 TIMER_START is not NULL as TIMER_START_SET,
104 TIMER_END is not NULL as TIMER_END_SET,
105 OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME,
106 OPERATION
107 from performance_schema.events_waits_history_long
108 where event_name = \'wait/lock/metadata/sql/mdl\';";
109
110 # reset lost counters
111 truncate table performance_schema.events_statements_summary_by_digest;
112 flush global status;
113
114 flush tables;
115
116 --disable_warnings
117 DROP TABLE IF EXISTS t1;
118 --enable_warnings
119
120 CREATE TABLE t1 (id INT PRIMARY KEY, b CHAR(100) DEFAULT 'initial value');
121
122 INSERT INTO t1 (id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
123
124 TRUNCATE TABLE performance_schema.events_waits_history_long;
125 TRUNCATE TABLE performance_schema.events_waits_history;
126 TRUNCATE TABLE performance_schema.events_waits_current;
127
128 --connection con1
129
130 BEGIN;
131
132 # MDL lock granted immediately, no wait.
133 SELECT * from t1 where id=1;
134
135 --connection default
136
137 echo "---- Marker 1 ----";
138
139 --vertical_results
140 execute dump_metadata_locks;
141 execute dump_table_handles;
142 execute dump_waits_current;
143 execute dump_waits_history_long;
144 --horizontal_results
145
146 --connection con1
147
148 # The MDL lock is now released
149 COMMIT;
150
151 --connection default
152
153 echo "---- Marker 2 ----";
154
155 --vertical_results
156 execute dump_metadata_locks;
157 execute dump_table_handles;
158 execute dump_waits_current;
159 execute dump_waits_history_long;
160 --horizontal_results
161
162 --connection con1
163
164 BEGIN;
165
166 # MDL lock is granted immediately, no wait
167 UPDATE t1 set b="new value" where id=2;
168
169 --connection default
170
171 echo "---- Marker 3 ----";
172
173 --vertical_results
174 execute dump_metadata_locks;
175 execute dump_table_handles;
176 execute dump_waits_current;
177 execute dump_waits_history_long;
178 --horizontal_results
179
180 --connection con2
181
182 BEGIN;
183
184 # MDL lock is PENDING : waiting for the update transaction
185 --send DROP TABLE t1;
186
187 --connection default
188
189 # Wait for the DROP TABLE t1 to block
190 let $wait_condition=
191 select count(*) = 1 from performance_schema.events_waits_current
192 where event_name = 'wait/lock/metadata/sql/mdl'
193 and object_name = 't1';
194 --source include/wait_condition.inc
195
196 echo "---- Marker 4 ----";
197
198 --vertical_results
199 execute dump_metadata_locks;
200 execute dump_table_handles;
201 execute dump_waits_current;
202 execute dump_waits_history_long;
203 --horizontal_results
204
205 --connection con1
206
207 # MDL lock on the update is released,
208 # MDL pending lock is granted for the drop table, then released.
209 COMMIT;
210
211 --connection con2
212
213 # complete DROP TABLE t1
214 --reap
215
216 --connection default
217
218 echo "---- Marker 5 ----";
219
220 --vertical_results
221 execute dump_metadata_locks;
222 execute dump_table_handles;
223 execute dump_waits_current;
224 execute dump_waits_history_long;
225 --horizontal_results
226
227 --connection default
228
229 TRUNCATE TABLE performance_schema.events_waits_history_long;
230 TRUNCATE TABLE performance_schema.events_waits_history;
231 TRUNCATE TABLE performance_schema.events_waits_current;
232 create table t1 (a int);
233
234 --connection con1
235
236 # Get MDL read lock
237 LOCK TABLE t1 READ;
238
239 --connection default
240
241 echo "---- Marker 6 ----";
242
243 --vertical_results
244 execute dump_metadata_locks;
245 execute dump_table_handles;
246 execute dump_waits_current;
247 execute dump_waits_history_long;
248 --horizontal_results
249
250 --connection con2
251
252 # User1 has a READ LOCK
253 # User2 waiting for WRITE LOCK
254 --send LOCK TABLE t1 write;
255
256 --connection default
257
258 # Wait for the LOCK TABLE t1 write to block
259 let $wait_condition=
260 select count(*) = 1 from performance_schema.events_waits_current
261 where event_name = 'wait/lock/metadata/sql/mdl'
262 and object_name = 't1';
263 --source include/wait_condition.inc
264
265 echo "---- Marker 7 ----";
266
267 --vertical_results
268 execute dump_metadata_locks;
269 execute dump_table_handles;
270 execute dump_waits_current;
271 execute dump_waits_history_long;
272 --horizontal_results
273
274 --connection con1
275
276 # User1 releases a READ LOCK
277 # User2 granted a WRITE LOCK
278 UNLOCK TABLES;
279
280 --connection con2
281
282 # Complete LOCK TABLE t1 write
283 --reap
284
285 --connection default
286
287 echo "---- Marker 8 ----";
288
289 --vertical_results
290 execute dump_metadata_locks;
291 execute dump_table_handles;
292 execute dump_waits_current;
293 execute dump_waits_history_long;
294 --horizontal_results
295
296 --connection con1
297
298 # User2 granted a WRITE LOCK
299 # User1 waits for a READ LOCK
300 --send LOCK TABLES t1 read;
301
302 --connection default
303
304 # Wait for the LOCK TABLES t1 READ to block
305 let $wait_condition=
306 select count(*) = 1 from performance_schema.events_waits_current
307 where event_name = 'wait/lock/metadata/sql/mdl'
308 and object_name = 't1';
309 --source include/wait_condition.inc
310
311 echo "---- Marker 9 ----";
312
313 --vertical_results
314 execute dump_metadata_locks;
315 execute dump_table_handles;
316 execute dump_waits_current;
317 execute dump_waits_history_long;
318 --horizontal_results
319
320 --connection con2
321
322 UNLOCK TABLES;
323
324 --connection con1
325
326 # Complete LOCK TABLE t1 read
327 --reap
328
329 --connection default
330
331 echo "---- Marker 10 ----";
332
333 --vertical_results
334 execute dump_metadata_locks;
335 execute dump_table_handles;
336 execute dump_waits_current;
337 execute dump_waits_history_long;
338 --horizontal_results
339
340 --connection con1
341
342 UNLOCK TABLES;
343
344 --connection default
345
346 echo "---- Marker 11 ----";
347
348 --vertical_results
349 execute dump_metadata_locks;
350 execute dump_table_handles;
351 execute dump_waits_current;
352 execute dump_waits_history_long;
353 --horizontal_results
354
355
356 TRUNCATE TABLE performance_schema.events_waits_history_long;
357 TRUNCATE TABLE performance_schema.events_waits_history;
358 TRUNCATE TABLE performance_schema.events_waits_current;
359 --connection con1
360
361 BEGIN;
362
363 # MDL lock is granted immediately, no wait
364 UPDATE LOW_PRIORITY t1 SET a=8;
365
366 --connection default
367
368 echo "---- Marker 12 ----";
369
370 --vertical_results
371 execute dump_metadata_locks;
372 execute dump_table_handles;
373 execute dump_waits_current;
374 execute dump_waits_history_long;
375 --horizontal_results
376
377 --connection con1
378 COMMIT;
379
380 --connection default
381
382 TRUNCATE TABLE performance_schema.events_waits_history_long;
383 TRUNCATE TABLE performance_schema.events_waits_history;
384 TRUNCATE TABLE performance_schema.events_waits_current;
385
386 --connection con1
387
388 --disable_ps2_protocol
389 SELECT GET_LOCK('test', 0);
390 --enable_ps2_protocol
391
392 --connection default
393
394 echo "---- Marker 13 ----";
395
396 --vertical_results
397 execute dump_metadata_locks;
398 execute dump_table_handles;
399 execute dump_waits_current;
400 execute dump_waits_history_long;
401 --horizontal_results
402
403 --connection con2
404
405 --disable_ps2_protocol
406 --send SELECT GET_LOCK('test', 120);
407 --enable_ps2_protocol
408
409 --connection default
410
411 let $wait_condition=
412 select count(*) = 1 from performance_schema.events_waits_current
413 where event_name = 'wait/lock/metadata/sql/mdl'
414 and object_name = 'test';
415 --source include/wait_condition.inc
416
417 echo "---- Marker 14 ----";
418
419 --vertical_results
420 execute dump_metadata_locks;
421 execute dump_table_handles;
422 execute dump_waits_current;
423 execute dump_waits_history_long;
424 --horizontal_results
425
426 --connection con1
427 --disable_ps2_protocol
428 SELECT RELEASE_LOCK('test');
429 --enable_ps2_protocol
430
431 --connection con2
432
433 --reap
434
435 --connection default
436
437 echo "---- Marker 15 ----";
438
439 --vertical_results
440 execute dump_metadata_locks;
441 execute dump_table_handles;
442 execute dump_waits_current;
443 execute dump_waits_history_long;
444 --horizontal_results
445
446 --connection con2
447 --disable_ps2_protocol
448 SELECT RELEASE_LOCK('test');
449 --enable_ps2_protocol
450
451 --connection default
452
453 echo "---- Marker 16 ----";
454
455 --vertical_results
456 execute dump_metadata_locks;
457 execute dump_table_handles;
458 execute dump_waits_current;
459 execute dump_waits_history_long;
460 --horizontal_results
461
462 # Cleanup
463
464 --disconnect con1
465 --disconnect con2
466
467 --connection default
468
469 drop table t1;
470
471 UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES';
472
473 revoke all privileges, grant option from user1@localhost;
474 revoke all privileges, grant option from user2@localhost;
475
476 drop user user1@localhost;
477 drop user user2@localhost;
478
479 drop function thread_id_name;
480
481 drop prepare dump_metadata_locks;
482 drop prepare dump_table_handles;
483 drop prepare dump_waits_current;
484 drop prepare dump_waits_history_long;
485
486 delete from performance_schema.setup_objects
487 where object_schema='mtr';
488
489 # In case of failure, will indicate the root cause
490 show global status like "performance_schema%";
491