Debian Codegraph @

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

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