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