Debian Codegraph @

mysql-test/suite/innodb/r/monitor.result

raw
1 select name, if(enabled,'enabled','disabled') status
2 from information_schema.innodb_metrics;
3 name status
4 metadata_table_handles_opened disabled
5 lock_deadlocks enabled
6 lock_timeouts enabled
7 lock_rec_lock_waits disabled
8 lock_table_lock_waits disabled
9 lock_rec_lock_requests disabled
10 lock_rec_lock_created disabled
11 lock_rec_lock_removed disabled
12 lock_rec_locks disabled
13 lock_table_lock_created disabled
14 lock_table_lock_removed disabled
15 lock_table_locks disabled
16 lock_row_lock_current_waits enabled
17 lock_row_lock_time enabled
18 lock_row_lock_time_max enabled
19 lock_row_lock_waits enabled
20 lock_row_lock_time_avg enabled
21 buffer_pool_size enabled
22 buffer_pool_reads enabled
23 buffer_pool_read_requests enabled
24 buffer_pool_write_requests enabled
25 buffer_pool_wait_free enabled
26 buffer_pool_read_ahead enabled
27 buffer_pool_read_ahead_evicted enabled
28 buffer_pool_pages_total enabled
29 buffer_pool_pages_misc enabled
30 buffer_pool_pages_data enabled
31 buffer_pool_bytes_data enabled
32 buffer_pool_pages_dirty enabled
33 buffer_pool_bytes_dirty enabled
34 buffer_pool_pages_free enabled
35 buffer_pages_created enabled
36 buffer_pages_written enabled
37 buffer_pages_read enabled
38 buffer_data_reads enabled
39 buffer_data_written enabled
40 buffer_flush_batch_scanned disabled
41 buffer_flush_batch_num_scan disabled
42 buffer_flush_batch_scanned_per_call disabled
43 buffer_flush_batch_total_pages disabled
44 buffer_flush_batches disabled
45 buffer_flush_batch_pages disabled
46 buffer_flush_neighbor_total_pages disabled
47 buffer_flush_neighbor disabled
48 buffer_flush_neighbor_pages disabled
49 buffer_flush_n_to_flush_requested disabled
50 buffer_flush_n_to_flush_by_age disabled
51 buffer_flush_adaptive_avg_time disabled
52 buffer_flush_adaptive_avg_pass disabled
53 buffer_LRU_get_free_loops disabled
54 buffer_flush_avg_page_rate disabled
55 buffer_flush_lsn_avg_rate disabled
56 buffer_flush_pct_for_dirty disabled
57 buffer_flush_pct_for_lsn disabled
58 buffer_flush_sync_waits disabled
59 buffer_flush_adaptive_total_pages disabled
60 buffer_flush_adaptive disabled
61 buffer_flush_adaptive_pages disabled
62 buffer_flush_sync_total_pages disabled
63 buffer_flush_sync disabled
64 buffer_flush_sync_pages disabled
65 buffer_flush_background_total_pages disabled
66 buffer_flush_background disabled
67 buffer_flush_background_pages disabled
68 buffer_LRU_batch_scanned disabled
69 buffer_LRU_batch_num_scan disabled
70 buffer_LRU_batch_scanned_per_call disabled
71 buffer_LRU_batch_flush_total_pages enabled
72 buffer_LRU_batch_evict_total_pages enabled
73 buffer_LRU_get_free_search disabled
74 buffer_LRU_search_scanned disabled
75 buffer_LRU_search_num_scan disabled
76 buffer_LRU_search_scanned_per_call disabled
77 buffer_LRU_unzip_search_scanned disabled
78 buffer_LRU_unzip_search_num_scan disabled
79 buffer_LRU_unzip_search_scanned_per_call disabled
80 buffer_page_read_index_leaf disabled
81 buffer_page_read_index_non_leaf disabled
82 buffer_page_read_undo_log disabled
83 buffer_page_read_index_inode disabled
84 buffer_page_read_system_page disabled
85 buffer_page_read_trx_system disabled
86 buffer_page_read_fsp_hdr disabled
87 buffer_page_read_xdes disabled
88 buffer_page_read_blob disabled
89 buffer_page_read_zblob disabled
90 buffer_page_read_zblob2 disabled
91 buffer_page_read_other disabled
92 buffer_page_written_index_leaf disabled
93 buffer_page_written_index_non_leaf disabled
94 buffer_page_written_undo_log disabled
95 buffer_page_written_index_inode disabled
96 buffer_page_written_system_page disabled
97 buffer_page_written_trx_system disabled
98 buffer_page_written_fsp_hdr disabled
99 buffer_page_written_xdes disabled
100 buffer_page_written_blob disabled
101 buffer_page_written_zblob disabled
102 buffer_page_written_zblob2 disabled
103 buffer_page_written_other disabled
104 os_data_reads enabled
105 os_data_writes enabled
106 os_data_fsyncs enabled
107 os_pending_reads enabled
108 os_pending_writes enabled
109 os_log_bytes_written enabled
110 trx_rw_commits disabled
111 trx_ro_commits disabled
112 trx_nl_ro_commits disabled
113 trx_commits_insert_update disabled
114 trx_rollbacks disabled
115 trx_rollbacks_savepoint disabled
116 trx_rseg_history_len enabled
117 trx_undo_slots_used disabled
118 trx_undo_slots_cached enabled
119 trx_rseg_current_size disabled
120 purge_del_mark_records disabled
121 purge_upd_exist_or_extern_records disabled
122 purge_invoked disabled
123 purge_undo_log_pages disabled
124 purge_dml_delay_usec disabled
125 purge_stop_count disabled
126 purge_resume_count disabled
127 log_checkpoints disabled
128 log_lsn_last_flush disabled
129 log_lsn_last_checkpoint disabled
130 log_lsn_current disabled
131 log_lsn_checkpoint_age disabled
132 log_lsn_buf_pool_oldest disabled
133 log_max_modified_age_async disabled
134 log_waits enabled
135 log_write_requests enabled
136 log_writes enabled
137 compress_pages_compressed disabled
138 compress_pages_decompressed disabled
139 compression_pad_increments disabled
140 compression_pad_decrements disabled
141 compress_saved disabled
142 compress_pages_page_compressed disabled
143 compress_page_compressed_trim_op disabled
144 compress_pages_page_decompressed disabled
145 compress_pages_page_compression_error disabled
146 compress_pages_encrypted disabled
147 compress_pages_decrypted disabled
148 index_page_splits disabled
149 index_page_merge_attempts disabled
150 index_page_merge_successful disabled
151 index_page_reorg_attempts disabled
152 index_page_reorg_successful disabled
153 index_page_discards disabled
154 adaptive_hash_searches enabled
155 adaptive_hash_searches_btree enabled
156 adaptive_hash_pages_added disabled
157 adaptive_hash_pages_removed disabled
158 adaptive_hash_rows_added disabled
159 adaptive_hash_rows_removed disabled
160 adaptive_hash_rows_deleted_no_hash_entry disabled
161 adaptive_hash_rows_updated disabled
162 file_num_open_files enabled
163 innodb_master_thread_sleeps disabled
164 innodb_activity_count enabled
165 innodb_master_active_loops disabled
166 innodb_master_idle_loops disabled
167 innodb_log_flush_usec disabled
168 innodb_dict_lru_usec disabled
169 innodb_dict_lru_count_active disabled
170 innodb_dict_lru_count_idle disabled
171 innodb_dblwr_writes enabled
172 innodb_dblwr_pages_written enabled
173 innodb_page_size enabled
174 ddl_background_drop_indexes disabled
175 ddl_online_create_index disabled
176 ddl_pending_alter_table disabled
177 ddl_sort_file_alter_table disabled
178 ddl_log_file_alter_table disabled
179 icp_attempts disabled
180 icp_no_match disabled
181 icp_out_of_range disabled
182 icp_match disabled
183 create temporary table orig_innodb_metrics as select name, enabled from information_schema.innodb_metrics;
184 set global innodb_monitor_disable = All;
185 select name from information_schema.innodb_metrics where enabled;
186 name
187 set global innodb_monitor_enable = all;
188 select name from information_schema.innodb_metrics where not enabled;
189 name
190 set global innodb_monitor_enable = aaa;
191 ERROR 42000: Variable 'innodb_monitor_enable' can't be set to the value of 'aaa'
192 set global innodb_monitor_disable = All;
193 select name from information_schema.innodb_metrics where enabled;
194 name
195 set global innodb_monitor_reset_all = all;
196 select name from information_schema.innodb_metrics where count!=0;
197 name
198 set global innodb_monitor_enable = "%lock%";
199 select name from information_schema.innodb_metrics
200 where enabled != (name like "%lock%");
201 name
202 set global innodb_monitor_disable = "%lock%";
203 select name, if(enabled,'enabled','disabled') status
204 from information_schema.innodb_metrics
205 where name like "%lock%";
206 name status
207 lock_deadlocks disabled
208 lock_timeouts disabled
209 lock_rec_lock_waits disabled
210 lock_table_lock_waits disabled
211 lock_rec_lock_requests disabled
212 lock_rec_lock_created disabled
213 lock_rec_lock_removed disabled
214 lock_rec_locks disabled
215 lock_table_lock_created disabled
216 lock_table_lock_removed disabled
217 lock_table_locks disabled
218 lock_row_lock_current_waits disabled
219 lock_row_lock_time disabled
220 lock_row_lock_time_max disabled
221 lock_row_lock_waits disabled
222 lock_row_lock_time_avg disabled
223 set global innodb_monitor_enable = "%lock*";
224 ERROR 42000: Variable 'innodb_monitor_enable' can't be set to the value of '%lock*'
225 set global innodb_monitor_enable="%%%%%%%%%%%%%%%%%%%%%%%%%%%";
226 select name from information_schema.innodb_metrics where not enabled;
227 name
228 set global innodb_monitor_disable="%%%%%";
229 select name from information_schema.innodb_metrics where enabled;
230 name
231 set global innodb_monitor_enable="%";
232 select name from information_schema.innodb_metrics where not enabled;
233 name
234 set global innodb_monitor_disable="%_%";
235 select name from information_schema.innodb_metrics where enabled;
236 name
237 set global innodb_monitor_enable="log%%%%";
238 select name from information_schema.innodb_metrics
239 where enabled != (name like "log%");
240 name
241 set global innodb_monitor_enable="os_%a_fs_ncs";
242 set global innodb_monitor_enable="os%pending%";
243 select name, if(enabled,'enabled','disabled') status
244 from information_schema.innodb_metrics
245 where name like "os%";
246 name status
247 os_data_reads disabled
248 os_data_writes disabled
249 os_data_fsyncs enabled
250 os_pending_reads enabled
251 os_pending_writes enabled
252 os_log_bytes_written disabled
253 set global innodb_monitor_enable="";
254 ERROR 42000: Variable 'innodb_monitor_enable' can't be set to the value of ''
255 set global innodb_monitor_enable="_";
256 ERROR 42000: Variable 'innodb_monitor_enable' can't be set to the value of '_'
257 SET global innodb_monitor_disable = module_metadata;
258 SET global innodb_monitor_reset_all = module_metadata;
259 set global innodb_monitor_enable = metadata_table_handles_opened;
260 create table monitor_test(col int) engine = innodb;
261 select * from monitor_test;
262 col
263 select name, max_count, min_count, count,
264 max_count_reset, min_count_reset, count_reset,
265 if(enabled,'enabled','disabled') status
266 from information_schema.innodb_metrics
267 where name = "metadata_table_handles_opened";
268 name max_count min_count count max_count_reset min_count_reset count_reset status
269 metadata_table_handles_opened 1 NULL 1 1 NULL 1 enabled
270 set global innodb_monitor_reset = metadata_table_handles_opened;
271 select name, max_count, min_count, count,
272 max_count_reset, min_count_reset, count_reset,
273 if(enabled,'enabled','disabled') status
274 from information_schema.innodb_metrics
275 where name = "metadata_table_handles_opened";
276 name max_count min_count count max_count_reset min_count_reset count_reset status
277 metadata_table_handles_opened 1 NULL 1 NULL NULL 0 enabled
278 drop table monitor_test;
279 create table monitor_test(col int) engine = innodb;
280 select * from monitor_test;
281 col
282 select name, max_count, min_count, count,
283 max_count_reset, min_count_reset, count_reset,
284 if(enabled,'enabled','disabled') status
285 from information_schema.innodb_metrics
286 where name = "metadata_table_handles_opened";
287 name max_count min_count count max_count_reset min_count_reset count_reset status
288 metadata_table_handles_opened 2 NULL 2 1 NULL 1 enabled
289 set global innodb_monitor_reset_all = metadata_table_handles_opened;
290 select name, max_count, min_count, count,
291 max_count_reset, min_count_reset, count_reset,
292 if(enabled,'enabled','disabled') status
293 from information_schema.innodb_metrics
294 where name = "metadata_table_handles_opened";
295 name max_count min_count count max_count_reset min_count_reset count_reset status
296 metadata_table_handles_opened 2 NULL 2 1 NULL 1 enabled
297 set global innodb_monitor_disable = metadata_table_handles_opened;
298 set global innodb_monitor_reset = metadata_table_handles_opened;
299 select name, max_count, min_count, count,
300 max_count_reset, min_count_reset, count_reset,
301 if(enabled,'enabled','disabled') status
302 from information_schema.innodb_metrics
303 where name = "metadata_table_handles_opened";
304 name max_count min_count count max_count_reset min_count_reset count_reset status
305 metadata_table_handles_opened 2 NULL 2 NULL NULL 0 disabled
306 drop table monitor_test;
307 create table monitor_test(col int) engine = innodb;
308 select * from monitor_test;
309 col
310 select name, max_count, min_count, count,
311 max_count_reset, min_count_reset, count_reset,
312 if(enabled,'enabled','disabled') status
313 from information_schema.innodb_metrics
314 where name = "metadata_table_handles_opened";
315 name max_count min_count count max_count_reset min_count_reset count_reset status
316 metadata_table_handles_opened 2 NULL 2 NULL NULL 0 disabled
317 set global innodb_monitor_reset_all = metadata_table_handles_opened;
318 select name, max_count, min_count, count,
319 max_count_reset, min_count_reset, count_reset,
320 if(enabled,'enabled','disabled') status
321 from information_schema.innodb_metrics
322 where name = "metadata_table_handles_opened";
323 name max_count min_count count max_count_reset min_count_reset count_reset status
324 metadata_table_handles_opened NULL NULL 0 NULL NULL 0 disabled
325 set global innodb_monitor_enable = metadata_table_handles_opened;
326 drop table monitor_test;
327 create table monitor_test(col int) engine = innodb stats_persistent=0;
328 select * from monitor_test;
329 col
330 select name, max_count, min_count, count,
331 max_count_reset, min_count_reset, count_reset,
332 if(enabled,'enabled','disabled') status
333 from information_schema.innodb_metrics
334 where name like "metadata%";
335 name max_count min_count count max_count_reset min_count_reset count_reset status
336 metadata_table_handles_opened 1 NULL 1 1 NULL 1 enabled
337 set global innodb_monitor_disable = module_metadata;
338 set global innodb_monitor_reset = module_metadata;
339 select name, max_count, min_count, count,
340 max_count_reset, min_count_reset, count_reset,
341 if(enabled,'enabled','disabled') status
342 from information_schema.innodb_metrics
343 where name like "metadata%";
344 name max_count min_count count max_count_reset min_count_reset count_reset status
345 metadata_table_handles_opened 1 NULL 1 NULL NULL 0 disabled
346 set global innodb_monitor_reset_all = module_metadata;
347 select name, max_count, min_count, count,
348 max_count_reset, min_count_reset, count_reset,
349 if(enabled,'enabled','disabled') status
350 from information_schema.innodb_metrics
351 where name like "metadata%";
352 name max_count min_count count max_count_reset min_count_reset count_reset status
353 metadata_table_handles_opened NULL NULL 0 NULL NULL 0 disabled
354 set global innodb_monitor_enable = module_trx;
355 begin;
356 insert into monitor_test values(9);
357 commit;
358 begin;
359 insert into monitor_test values(9);
360 rollback;
361 select name, max_count, min_count, count,
362 max_count_reset, min_count_reset, count_reset,
363 if(enabled,'enabled','disabled') status
364 from information_schema.innodb_metrics
365 where name='trx_rollbacks';
366 name max_count min_count count max_count_reset min_count_reset count_reset status
367 trx_rollbacks 1 NULL 1 1 NULL 1 enabled
368 set global innodb_monitor_disable = module_trx;
369 drop table monitor_test;
370 set global innodb_monitor_enable = file_num_open_files;
371 select name, max_count, min_count, count,
372 max_count_reset, min_count_reset, count_reset,
373 if(enabled,'enabled','disabled') status
374 from information_schema.innodb_metrics
375 where name like "file_num_open_files";
376 name max_count min_count count max_count_reset min_count_reset count_reset status
377 file_num_open_files # # # # # # enabled
378 set global innodb_monitor_disable = file_num_open_files;
379 set global innodb_monitor_enable = "icp%";
380 create table monitor_test(a char(3), b int, c char(2),
381 primary key (a(1), c(1)), key(b)) engine = innodb;
382 insert into monitor_test values("13", 2, "aa");
383 select a from monitor_test where b < 1 for update;
384 a
385 select name, count from information_schema.innodb_metrics
386 where name like "icp%";
387 name count
388 icp_attempts 1
389 icp_no_match 0
390 icp_out_of_range 1
391 icp_match 0
392 select a from monitor_test where b < 3 for update;
393 a
394 13
395 select name, count from information_schema.innodb_metrics
396 where name like "icp%";
397 name count
398 icp_attempts 2
399 icp_no_match 0
400 icp_out_of_range 1
401 icp_match 1
402 drop table monitor_test;
403 set global innodb_monitor_disable = All;
404 set global innodb_monitor_reset_all = all;
405 select 1 from `information_schema`.`INNODB_METRICS`
406 where case (1) when (1) then (AVG_COUNT_RESET) else (1) end;
407 1
408 set global innodb_monitor_enable = default;
409 set global innodb_monitor_disable = default;
410 set global innodb_monitor_reset = default;
411 set global innodb_monitor_reset_all = default;
412 #
413 # Bug#22576241 SETTING INNODB_MONITOR_ENABLE TO ALL DOES NOT ENABLE ALL
414 # MONITORS
415 #
416 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB STATS_PERSISTENT=0;
417 SELECT /*1*/ NAME, COUNT > 0 FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
418 LIKE 'buffer_page_written_index_leaf';
419 NAME COUNT > 0
420 buffer_page_written_index_leaf 0
421 SET GLOBAL innodb_monitor_enable='module_buffer_page';
422 INSERT INTO t1 VALUES (1), (2), (3), (4);
423 FLUSH TABLES t1 FOR EXPORT;
424 UNLOCK TABLES;
425 SELECT /*2*/ NAME, COUNT > 0 FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
426 LIKE 'buffer_page_written_index_leaf';
427 NAME COUNT > 0
428 buffer_page_written_index_leaf 1
429 SET GLOBAL innodb_monitor_disable='module_buffer_page';
430 SET GLOBAL innodb_monitor_reset_all='module_buffer_page';
431 SELECT /*3*/ NAME, COUNT > 0 FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
432 LIKE 'buffer_page_written_index_leaf';
433 NAME COUNT > 0
434 buffer_page_written_index_leaf 0
435 SET GLOBAL innodb_monitor_enable='%';
436 SET GLOBAL innodb_monitor_reset_all= '%', innodb_compression_algorithm= foo;
437 ERROR 42000: Variable 'innodb_compression_algorithm' can't be set to the value of 'foo'
438 INSERT INTO t1 VALUES (5), (6), (7), (8);
439 FLUSH TABLES t1 FOR EXPORT;
440 UNLOCK TABLES;
441 SELECT /*4*/ NAME, COUNT > 0 FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
442 LIKE 'buffer_page_written_index_leaf';
443 NAME COUNT > 0
444 buffer_page_written_index_leaf 1
445 SET GLOBAL innodb_monitor_disable='%';
446 SET GLOBAL innodb_monitor_reset_all='%';
447 SELECT /*5*/ NAME, COUNT > 0 FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
448 LIKE 'buffer_page_written_index_leaf';
449 NAME COUNT > 0
450 buffer_page_written_index_leaf 0
451 SET GLOBAL innodb_monitor_enable='ALL';
452 INSERT INTO t1 VALUES (9), (10), (11), (12);
453 FLUSH TABLES t1 FOR EXPORT;
454 UNLOCK TABLES;
455 SELECT /*6*/ NAME, COUNT > 0 FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
456 LIKE 'buffer_page_written_index_leaf';
457 NAME COUNT > 0
458 buffer_page_written_index_leaf 1
459 DROP TABLE t1;
460 CREATE TABLE fl0 (
461 id INT NOT NULL PRIMARY KEY
462 ) ENGINE = InnoDB;
463 CREATE TABLE fl1 (
464 id INT NOT NULL PRIMARY KEY,
465 fl0_id INT,
466 CONSTRAINT `fkl0`
467 FOREIGN KEY (fl0_id) REFERENCES fl0 (id)
468 ON DELETE CASCADE
469 ON UPDATE RESTRICT
470 ) ENGINE = InnoDB;
471 CREATE TABLE fl2 (
472 id INT NOT NULL PRIMARY KEY,
473 fl1_id INT,
474 CONSTRAINT `fkl1`
475 FOREIGN KEY (fl1_id) REFERENCES fl1 (id)
476 ON DELETE CASCADE
477 ON UPDATE SET NULL
478 ) ENGINE = InnoDB;
479 INSERT INTO fl0 VALUES (1000);
480 INSERT INTO fl1 VALUES (500, 1000), (1500, 1000);
481 INSERT INTO fl2 VALUES (200, 500), (800, 500), (1200, 1500), (1800, 1500);
482 CREATE TABLE t1(id INT PRIMARY KEY, a INT, b CHAR(1), UNIQUE KEY u(a,b))
483 ENGINE=InnoDB;
484 SET @start = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
485 = 'lock_rec_lock_created');
486 BEGIN;
487 INSERT INTO t1 VALUES(1,1,'a'),(2,9999,'b'),(3,10000,'c'),(4,4,'d');
488 DELETE FROM t1 WHERE a = 9999 AND b='b';
489 COMMIT;
490 SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
491 = 'lock_rec_lock_created');
492 SELECT @end - @start;
493 @end - @start
494 1
495 # Records must not be S/X-locked if a table is X-locked.
496 SET @start = @end;
497 SET autocommit = 0;
498 BEGIN;
499 LOCK TABLE t1 WRITE;
500 SELECT * FROM t1;
501 id a b
502 1 1 a
503 4 4 d
504 3 10000 c
505 SELECT * FROM t1 WHERE a>=10000;
506 id a b
507 3 10000 c
508 SELECT * FROM t1 FOR UPDATE;
509 id a b
510 1 1 a
511 4 4 d
512 3 10000 c
513 SELECT * FROM t1 WHERE a>=10000 FOR UPDATE;
514 id a b
515 3 10000 c
516 UPDATE t1 SET b = 'b' WHERE id = 4;
517 UPDATE t1 SET b = 'b' WHERE a = 10000;
518 REPLACE INTO t1 VALUES (4,3,'a');
519 INSERT INTO t1 VALUES (3,1,'e') ON DUPLICATE KEY UPDATE b = 'b';
520 INSERT INTO t1 VALUES (5,5,'e');
521 DELETE FROM t1 WHERE a = 1 AND b='a';
522 DELETE FROM t1;
523 COMMIT;
524 UNLOCK TABLES;
525 SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_rec_lock_created');
526 SELECT @end - @start;
527 @end - @start
528 0
529 # Records must not be S-locked if a table is S-locked.
530 SET @start = @end;
531 BEGIN;
532 LOCK TABLE t1 WRITE;
533 INSERT INTO t1 VALUES(1,1,'a'),(2,9999,'b'),(3,10000,'c'),(4,4,'d');
534 DELETE FROM t1 WHERE a = 9999 AND b='b';
535 COMMIT;
536 UNLOCK TABLES;
537 BEGIN;
538 LOCK TABLE t1 READ;
539 SELECT * FROM t1 LOCK IN SHARE MODE;
540 id a b
541 1 1 a
542 4 4 d
543 3 10000 c
544 SELECT * FROM t1 WHERE a>=10000 LOCK IN SHARE MODE;
545 id a b
546 3 10000 c
547 COMMIT;
548 UNLOCK TABLES;
549 SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_rec_lock_created');
550 SELECT @end - @start;
551 @end - @start
552 0
553 # Records must not be S-locked for foreign keys enforcement
554 SET @start = @end;
555 BEGIN;
556 LOCK TABLE fl0 READ, fl1 READ, fl2 WRITE;
557 INSERT INTO fl2 VALUES (300, 500), (700, 500), (1300, 1500), (1700, 1500);
558 SELECT * FROM fl1 LOCK IN SHARE MODE;
559 id fl0_id
560 500 1000
561 1500 1000
562 COMMIT;
563 UNLOCK TABLES;
564 SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_rec_lock_created');
565 SELECT @end - @start;
566 @end - @start
567 0
568 # Records must not be X-locked for foreign keys cascade
569 SET @start = @end;
570 BEGIN;
571 LOCK TABLE fl0 READ, fl1 WRITE, fl2 WRITE;
572 DELETE FROM fl1 WHERE id = 1500;
573 UPDATE fl1 SET id = 2500 WHERE id = 500;
574 COMMIT;
575 UNLOCK TABLES;
576 SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_rec_lock_created');
577 SELECT @end - @start;
578 @end - @start
579 0
580 SET autocommit = default;
581 DROP TABLE t1;
582 DROP TABLE fl2;
583 DROP TABLE fl1;
584 DROP TABLE fl0;
585 set global innodb_monitor_disable = 'adaptive\\_hash\\_p%';
586 set global innodb_monitor_disable = 'adaptive\\_hash\\_r%';
587 set global innodb_monitor_disable = 'buffer\\_LRU\\_batch\\_n%';
588 set global innodb_monitor_disable = 'buffer\\_LRU\\_batch\\_s%';
589 set global innodb_monitor_disable = 'buffer\\_LRU\\_g%';
590 set global innodb_monitor_disable = 'buffer\\_LRU\\_s%';
591 set global innodb_monitor_disable = 'buffer\\_LRU\\_u%';
592 set global innodb_monitor_disable = 'buffer\\_f%';
593 set global innodb_monitor_disable = 'buffer\\_page\\_%';
594 set global innodb_monitor_disable = 'c%';
595 set global innodb_monitor_disable = 'ddl%';
596 set global innodb_monitor_disable = 'icp%';
597 set global innodb_monitor_disable = 'index\\_p%';
598 set global innodb_monitor_disable = 'innodb\\_di%';
599 set global innodb_monitor_disable = 'innodb\\_l%';
600 set global innodb_monitor_disable = 'innodb\\_m%';
601 set global innodb_monitor_disable = 'lock\\_re%';
602 set global innodb_monitor_disable = 'lock\\_ta%';
603 set global innodb_monitor_disable = 'log%';
604 set global innodb_monitor_disable = 'm%';
605 set global innodb_monitor_disable = 'p%';
606 set global innodb_monitor_disable = 't%';
607 set global innodb_monitor_enable = 'log\\_w%';
608 set global innodb_monitor_enable = 'trx_rseg_history_len';
609 set global innodb_monitor_enable = 'trx_undo_slots_cached';
610 set global innodb_monitor_enable=default;
611 Warnings:
612 Warning 1230 Default value is not defined for this set option. Please specify correct counter or module name.
613 set global innodb_monitor_disable=default;
614 Warnings:
615 Warning 1230 Default value is not defined for this set option. Please specify correct counter or module name.
616 set global innodb_monitor_reset_all=default;
617 Warnings:
618 Warning 1230 Default value is not defined for this set option. Please specify correct counter or module name.
619 select name, orig.enabled, new.enabled from
620 orig_innodb_metrics orig join information_schema.innodb_metrics new using(name)
621 where orig.enabled != new.enabled;
622 name enabled enabled