1# -----------------------------------------------------------------------
2# Tests for the performance schema nested stored program instrumentation.
3# -----------------------------------------------------------------------
4
5--source include/not_embedded.inc
6--source include/have_perfschema.inc
7
8TRUNCATE TABLE performance_schema.events_statements_summary_by_program;
9TRUNCATE TABLE performance_schema.events_statements_history_long;
10
11--echo #################################################
12--echo # Quering PS statement summary and history_long #
13--echo #################################################
14
15--source suite/perfschema/include/program_nested_setup.inc
16--source suite/perfschema/include/program_nested_execution.inc
17
18SELECT EVENT_NAME, SQL_TEXT, CURRENT_SCHEMA, OBJECT_TYPE, OBJECT_SCHEMA,
19       OBJECT_NAME, NESTING_EVENT_TYPE, NESTING_EVENT_LEVEL
20       FROM performance_schema.events_statements_history_long WHERE
21       CURRENT_SCHEMA='nested_sp' ORDER BY
22       OBJECT_NAME,NESTING_EVENT_LEVEL,SQL_TEXT,EVENT_NAME;
23
24SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_STATEMENTS
25       FROM performance_schema.events_statements_summary_by_program
26       WHERE OBJECT_SCHEMA='nested_sp' ORDER BY OBJECT_NAME;
27
28--echo # clean-up
29TRUNCATE TABLE performance_schema.events_statements_summary_by_program;
30TRUNCATE TABLE performance_schema.events_statements_history_long;
31
32# After truncate the statictics collected will are reset
33SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_STATEMENTS
34       FROM performance_schema.events_statements_summary_by_program
35       WHERE OBJECT_SCHEMA='nested_sp' ORDER BY OBJECT_NAME;
36
37--source suite/perfschema/include/program_nested_cleanup.inc
38
39# After clean-up the stored programs are removed from PS tables
40SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_STATEMENTS
41       FROM performance_schema.events_statements_summary_by_program
42       WHERE OBJECT_SCHEMA='nested_sp' ORDER BY OBJECT_NAME;
43
44
45# Check the intrumentation of stored programs
46# when statement/sp/% instruments not timed
47
48--source suite/perfschema/include/program_nested_setup.inc
49
50update performance_schema.setup_instruments set enabled='YES', timed='NO'
51  where name like "statement/sp/%" order by name;
52
53TRUNCATE TABLE performance_schema.events_statements_summary_by_program;
54TRUNCATE TABLE performance_schema.events_statements_history_long;
55
56--source suite/perfschema/include/program_nested_execution.inc
57
58# check instrumentation
59
60SELECT EVENT_NAME, TIMER_START, TIMER_END, TIMER_WAIT FROM
61       performance_schema.events_statements_history_long WHERE
62       CURRENT_SCHEMA='nested_sp' AND EVENT_NAME like "statement/sp/%";
63
64# clean-up
65
66# Restore the setup
67update performance_schema.setup_instruments set enabled='YES', timed='YES'
68  where name like "statement/sp/%" order by name;
69
70TRUNCATE TABLE performance_schema.events_statements_summary_by_program;
71TRUNCATE TABLE performance_schema.events_statements_history_long;
72
73--source suite/perfschema/include/program_nested_cleanup.inc
74