Debian Codegraph @

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

raw
1 # -----------------------------------------------------------------------
2 # Tests for the performance schema stored program instrumentation.
3 # -----------------------------------------------------------------------
4
5 --source include/not_embedded.inc
6 --source include/have_perfschema.inc
7
8 TRUNCATE TABLE performance_schema.events_statements_summary_by_program;
9 TRUNCATE 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_setup.inc
16 --source suite/perfschema/include/program_execution.inc
17
18 SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_STATEMENTS
19 FROM performance_schema.events_statements_summary_by_program
20 WHERE OBJECT_SCHEMA='stored_programs' ORDER BY OBJECT_NAME;
21
22 SELECT EVENT_NAME, SQL_TEXT, CURRENT_SCHEMA, OBJECT_TYPE, OBJECT_SCHEMA,
23 OBJECT_NAME, NESTING_EVENT_TYPE, NESTING_EVENT_LEVEL FROM
24 performance_schema.events_statements_history_long WHERE
25 CURRENT_SCHEMA='stored_programs' AND
26 (SQL_TEXT not like '%count(*) = %' OR SQL_TEXT IS NULL)
27 ORDER BY OBJECT_NAME, NESTING_EVENT_LEVEL, SQL_TEXT;
28
29
30 --echo # clean -up
31
32 TRUNCATE TABLE performance_schema.events_statements_summary_by_program;
33 TRUNCATE TABLE performance_schema.events_statements_history_long;
34
35 # After truncate the statictics collected will are reset
36 SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_STATEMENTS
37 FROM performance_schema.events_statements_summary_by_program
38 WHERE OBJECT_SCHEMA='stored_programs' ORDER BY OBJECT_NAME;
39
40 --source suite/perfschema/include/program_cleanup.inc
41
42 # After clean-up the stored programs are removed from PS tables
43
44 SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_STATEMENTS
45 FROM performance_schema.events_statements_summary_by_program
46 WHERE OBJECT_SCHEMA='stored_programs' ORDER BY OBJECT_NAME;
47
48 # Check the intrumentation of stored programs
49 # when statement/sp/% instruments not timed
50
51 --source suite/perfschema/include/program_setup.inc
52
53 update performance_schema.setup_instruments set enabled='YES', timed='NO'
54 where name like "statement/sp/%" order by name;
55
56 TRUNCATE TABLE performance_schema.events_statements_summary_by_program;
57 TRUNCATE TABLE performance_schema.events_statements_history_long;
58
59 --source suite/perfschema/include/program_execution.inc
60
61 # check instrumentation
62
63 SELECT EVENT_NAME, TIMER_START, TIMER_END, TIMER_WAIT FROM
64 performance_schema.events_statements_history_long WHERE
65 CURRENT_SCHEMA='stored_programs' AND EVENT_NAME like "statement/sp/%";
66
67 # clean-up
68
69 # Restore the setup
70 update performance_schema.setup_instruments set enabled='YES', timed='YES'
71 where name like "statement/sp/%" order by name;
72
73 TRUNCATE TABLE performance_schema.events_statements_summary_by_program;
74 TRUNCATE TABLE performance_schema.events_statements_history_long;
75
76 --source suite/perfschema/include/program_cleanup.inc
77
78 #----------------------------------------------------------------
79 # The statistics of a stored program are not collected
80 # if its execution fails
81 #----------------------------------------------------------------
82
83 --echo # set-up
84 CREATE DATABASE sp;
85 USE sp;
86
87 CREATE TABLE t1(
88 a INT,
89 b INT
90 );
91
92 --echo # let the creation of the following stored programs fail
93 --error 1064
94 CREATE PROCEDURE fail1(IN a INT OUT x CHAR(16))
95 SET a=1;
96
97 --error 1064
98 CREATE FUNCTION fail2(a INT , b INT) RETURNS INT
99 x=SELECT COUNT(*) FROM t;
100
101 --error 1064
102 CREATE EVENT fail3 SCHEDULE EVERY MICROSECOND DO
103 DROP TABLE t;
104
105 --echo # the below query on PS table doesn't show any rows
106 --echo # as the creation of stored programs failed
107 SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_STATEMENTS
108 FROM performance_schema.events_statements_summary_by_program
109 WHERE OBJECT_SCHEMA='sp';
110
111 --echo # create few stored programs
112 DELIMITER |;
113 CREATE PROCEDURE p(x1 INT, x2 INT)
114 BEGIN
115 INSERT INTO t1 VALUES (x1, x2);
116 END|
117 DELIMITER ;|
118
119 CREATE FUNCTION f(y1 INT, y2 INT) RETURNS INT
120 RETURN y1+y2;
121
122 CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW
123 SET @neg=-1;
124
125 --echo # execute the created stored programs such that they fail.
126 --error 1318
127 CALL p(7);
128 --error 1318
129 SELECT f("add",1,3);
130 --error 1064
131 INSERT INTO t1;
132
133 --echo # the below query on PS table doesn't expose any statistics as
134 --echo # execution of the created stored porgrams failed.
135 SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_STATEMENTS
136 FROM performance_schema.events_statements_summary_by_program
137 WHERE OBJECT_SCHEMA='sp';
138
139 --echo #clean-up
140 DROP PROCEDURE p;
141 DROP FUNCTION f;
142 DROP TRIGGER trg;
143 DROP TABLE t1;
144 DROP DATABASE sp;