Debian Codegraph @

mysql-test/suite/sysschema/t/pr_statement_performance_analyzer.test

raw
1 ########### suite/sysschema/t/pr_statement_performance_analyzer.test #############
2 # #
3 # Testing of of the sys.statement_performance_analyzer() procedure #
4 # #
5 # Creation: #
6 # 2015-07-28 jkrogh Implement this test as part of #
7 # WL#7804 REPORT FOR SUPPORT #
8 # #
9 ##################################################################################
10
11 # Performance schema tracks prepared statements separately, and does not
12 # yet have a summary view that we can use for this view.
13 # Until then, we disable this test with --ps-protocol
14 # Test requires: sp-protocol/ps-protocol/view-protocol/cursor-protocol disabled
15 -- source include/no_protocol.inc
16 # Bug #23290879 - For reasons unknown to man this test fails randomly only on Windows
17 -- source include/not_windows.inc
18
19 # Thread stack overrun in debug mode on sparc
20 --source include/not_sparc_debug.inc
21
22 use test;
23 --disable_warnings
24 DROP TABLE IF EXISTS t1;
25 DROP TEMPORARY TABLE IF EXISTS tmp_digests_ini;
26 DROP VIEW IF EXISTS view_digests;
27 --enable_warnings
28
29 # Create a table
30 CREATE TABLE t1 (id INT PRIMARY KEY, val int);
31
32 # Create a new connection to make the actual changes
33 # Create the connection now to ensure queries like "SELECT @@`version_comment` LIMIT 1" are excluded
34 connect (con1,localhost,root,,);
35 connection con1;
36 use test;
37 --let $con1_thread_id= `SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = CONNECTION_ID()`
38
39 # Disable instrumentation for all other threads other than the one that will
40 # my monitored for this test ($con1_thread_id)
41 connection default;
42 --disable_result_log
43 --replace_result $con1_thread_id CON1_THREAD_ID
44 eval UPDATE performance_schema.threads SET INSTRUMENTED = IF(THREAD_ID = $con1_thread_id, 'YES', 'NO');
45 CALL sys.ps_setup_enable_consumer('events_statements_history_long');
46 CALL sys.ps_truncate_all_tables(FALSE);
47 --enable_result_log
48
49 # Start with some initial queries
50 # Don't rely on the digests being constant across MySQL releases and versions, so find the
51 # digest for each of the three queries by getting the last event from performance_schema.events_statements_current
52 # for the con1 connection.
53 connection con1;
54 INSERT INTO t1 VALUES (1, 0);
55 connection default;
56 --let $wait_condition= SELECT SUBSTRING(SQL_TEXT, 1, 7) = 'INSERT ' FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id AND DIGEST IS NOT NULL
57 --source include/wait_condition.inc
58 --let $digest_insert= `SELECT DIGEST FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id ORDER BY EVENT_ID DESC LIMIT 1`
59 --let $query_insert= `SELECT sys.format_statement(DIGEST_TEXT) FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id ORDER BY EVENT_ID DESC LIMIT 1`
60 connection con1;
61 UPDATE t1 SET val = 1 WHERE id = 1;
62 connection default;
63 --let $wait_condition= SELECT SUBSTRING(SQL_TEXT, 1, 7) = 'UPDATE ' FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id AND DIGEST IS NOT NULL
64 --source include/wait_condition.inc
65 --let $digest_update= `SELECT DIGEST FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id ORDER BY EVENT_ID DESC LIMIT 1`
66 --let $query_update= `SELECT sys.format_statement(DIGEST_TEXT) FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id ORDER BY EVENT_ID DESC LIMIT 1`
67 connection con1;
68 SELECT t1a.* FROM t1 AS t1a LEFT OUTER JOIN (SELECT * FROM t1 AS t1b1 INNER JOIN t1 AS t1b2 USING (id, val)) AS t1b ON t1b.id > t1a.id ORDER BY t1a.val, t1a.id;
69 connection default;
70 --let $wait_condition= SELECT SUBSTRING(SQL_TEXT, 1, 7) = 'SELECT ' FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id AND DIGEST IS NOT NULL
71 --source include/wait_condition.inc
72 --let $digest_select= `SELECT DIGEST FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id ORDER BY EVENT_ID DESC LIMIT 1`
73 --let $query_select= `SELECT sys.format_statement(DIGEST_TEXT) FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id ORDER BY EVENT_ID DESC LIMIT 1`
74
75 # Enable to debug if some digests are not found
76 # --output /tmp/digest
77 # --eval SELECT '$digest_insert' AS DigestInsert, '$digest_update' AS DigestUpdate, '$digest_select' AS DigestSelect
78 # --output /tmp/digest_text
79 # --eval SELECT '$query_insert' AS DigestInsert, '$query_update' AS DigestUpdate, '$query_select' AS DigestSelect
80 # --output /tmp/ps_history
81 # SELECT THREAD_ID, EVENT_ID, END_EVENT_ID, DIGEST, SQL_TEXT FROM performance_schema.events_statements_history_long ORDER BY EVENT_ID;
82
83 # Start collecting data
84 CALL sys.statement_performance_analyzer('create_tmp', 'test.tmp_digests_ini', NULL);
85 CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);
86 CALL sys.statement_performance_analyzer('save', 'test.tmp_digests_ini', NULL);
87
88 # Make sure the last_seen times will be different from the SELECT statement's first_seen.
89 DO SLEEP(1.2);
90
91 # Make some more changes
92 connection con1;
93 INSERT INTO t1 VALUES (2, 0);
94 UPDATE t1 SET val = 1 WHERE id = 2;
95 SELECT t1a.* FROM t1 AS t1a LEFT OUTER JOIN (SELECT * FROM t1 AS t1b1 INNER JOIN t1 AS t1b2 USING (id, val)) AS t1b ON t1b.id > t1a.id ORDER BY t1a.val, t1a.id;
96 disconnect con1;
97
98 # Make the second collection of data and create the output
99 connection default;
100 # Make sure all of the queries executing in con1 has been recorded in performance_schema.events_statements_summary_by_digest
101 # before continuing with the actual tests of pr_statement_performance_analyzer()
102 --let $wait_condition= SELECT COUNT_STAR = 2 FROM performance_schema.events_statements_summary_by_digest WHERE DIGEST = '$digest_select'
103 --source include/wait_condition.inc
104
105 # Get all values to be used in replacements now to minimize the risk of the
106 # values disappearing out of the performance_schema tables
107 --let $o_upd_total_latency= `SELECT total_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'`
108 --let $o_upd_max_latency= `SELECT max_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'`
109 --let $o_upd_avg_latency= `SELECT avg_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'`
110 --let $o_upd_first_seen= `SELECT first_seen FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'`
111 --let $o_upd_last_seen= `SELECT last_seen FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'`
112 --let $o_sel_total_latency= `SELECT total_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'`
113 --let $o_sel_max_latency= `SELECT max_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'`
114 --let $o_sel_avg_latency= `SELECT avg_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'`
115 --let $o_sel_first_seen= `SELECT first_seen FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'`
116 --let $o_sel_last_seen= `SELECT last_seen FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'`
117 --let $o_ins_total_latency= `SELECT total_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'`
118 --let $o_ins_max_latency= `SELECT max_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'`
119 --let $o_ins_avg_latency= `SELECT avg_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'`
120 --let $o_ins_first_seen= `SELECT first_seen FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'`
121 --let $o_ins_last_seen= `SELECT last_seen FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'`
122 --let $o_upd_lock_latency= `SELECT lock_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'`
123 --let $o_sel_lock_latency= `SELECT lock_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'`
124 --let $o_ins_lock_latency= `SELECT lock_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'`
125 --let $o_upd_cpu_latency= `SELECT cpu_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'`
126 --let $o_sel_cpu_latency= `SELECT cpu_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'`
127 --let $o_ins_cpu_latency= `SELECT cpu_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'`
128 --let $o_upd_controlled_mem= `SELECT max_controlled_memory FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'`
129 --let $o_sel_controlled_mem= `SELECT max_controlled_memory FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'`
130 --let $o_ins_controlled_mem= `SELECT max_controlled_memory FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'`
131 --let $o_upd_total_mem= `SELECT max_total_memory FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'`
132 --let $o_sel_total_mem= `SELECT max_total_memory FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'`
133 --let $o_ins_total_mem= `SELECT max_total_memory FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'`
134 --let $d_upd_total_latency= `SELECT format_pico_time(TIMER_WAIT) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_update' ORDER BY EVENT_ID DESC LIMIT 1`
135 --let $d_sel_total_latency= `SELECT format_pico_time(TIMER_WAIT) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_select' ORDER BY EVENT_ID DESC LIMIT 1`
136 --let $d_ins_total_latency= `SELECT format_pico_time(TIMER_WAIT) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_insert' ORDER BY EVENT_ID DESC LIMIT 1`
137 --let $d_upd_tock_latency= `SELECT format_pico_time(LOCK_TIME) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_update' ORDER BY EVENT_ID DESC LIMIT 1`
138 --let $d_sel_tock_latency= `SELECT format_pico_time(LOCK_TIME) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_select' ORDER BY EVENT_ID DESC LIMIT 1`
139 --let $d_ins_tock_latency= `SELECT format_pico_time(LOCK_TIME) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_insert' ORDER BY EVENT_ID DESC LIMIT 1`
140 --let $d_upd_cpu_latency= `SELECT format_pico_time(CPU_TIME) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_update' ORDER BY EVENT_ID DESC LIMIT 1`
141 --let $d_sel_cpu_latency= `SELECT format_pico_time(CPU_TIME) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_select' ORDER BY EVENT_ID DESC LIMIT 1`
142 --let $d_ins_cpu_latency= `SELECT format_pico_time(CPU_TIME) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_insert' ORDER BY EVENT_ID DESC LIMIT 1`
143 --let $d_upd_controlled_mem= `SELECT format_bytes(MAX_CONTROLLED_MEMORY) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_update' ORDER BY EVENT_ID DESC LIMIT 1`
144 --let $d_sel_controlled_mem= `SELECT format_bytes(MAX_CONTROLLED_MEMORY) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_select' ORDER BY EVENT_ID DESC LIMIT 1`
145 --let $d_ins_controlled_mem= `SELECT format_bytes(MAX_CONTROLLED_MEMORY) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_insert' ORDER BY EVENT_ID DESC LIMIT 1`
146 --let $d_upd_total_mem= `SELECT format_bytes(MAX_TOTAL_MEMORY) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_update' ORDER BY EVENT_ID DESC LIMIT 1`
147 --let $d_sel_total_mem= `SELECT format_bytes(MAX_TOTAL_MEMORY) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_select' ORDER BY EVENT_ID DESC LIMIT 1`
148 --let $d_ins_total_mem= `SELECT format_bytes(MAX_TOTAL_MEMORY) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_insert' ORDER BY EVENT_ID DESC LIMIT 1`
149
150 # DEBUG
151 # --echo $o_upd_controlled_mem $o_sel_controlled_mem $o_ins_controlled_mem
152 # --echo $o_upd_total_mem $o_sel_total_mem $o_ins_total_mem
153 # --echo $d_upd_controlled_mem $d_sel_controlled_mem $d_ins_controlled_mem
154 # --echo $d_upd_total_mem $d_sel_total_mem $d_ins_total_mem
155
156 CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);
157
158 # Do a sanity check to ensure we are assuming the queries has the digests they have and that there is nothing else in the events_statements_summary_by_digest than there actually is.
159 --replace_result $digest_insert DIGEST_INSERT $digest_update DIGEST_UPDATE $digest_select DIGEST_SELECT
160 --sorted_result
161 SELECT DIGEST, COUNT_STAR FROM performance_schema.events_statements_summary_by_digest;
162
163 # with_runtimes_in_95th_percentile
164 # It is unknown what the result will be since the execution times for each query are unknown
165 # So just check that no warning or error occurs
166 --disable_result_log
167 CALL sys.statement_performance_analyzer('overall', NULL, 'with_runtimes_in_95th_percentile');
168 CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'with_runtimes_in_95th_percentile');
169 --enable_result_log
170
171 # analysis - as there's no control of the various latencies, it may be the same for two or more of the queries.
172 # So replace_result cannot be used to give it a unique value. Instead just use LATENCY for all rows.
173 --replace_result $query_insert QUERY_INSERT $query_select QUERY_SELECT $query_update QUERY_UPDATE $digest_insert DIGEST_INSERT $digest_update DIGEST_UPDATE $digest_select DIGEST_SELECT $o_upd_total_latency LATENCY $o_upd_max_latency LATENCY $o_upd_avg_latency LATENCY $o_upd_lock_latency LATENCY $o_upd_cpu_latency LATENCY $o_upd_controlled_mem MEMORY $o_upd_total_mem MEMORY $o_upd_first_seen FIRST_SEEN $o_upd_last_seen LAST_SEEN $o_sel_total_latency LATENCY $o_sel_max_latency LATENCY $o_sel_avg_latency LATENCY $o_sel_lock_latency LATENCY $o_sel_cpu_latency LATENCY $o_sel_controlled_mem MEMORY $o_sel_total_mem MEMORY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN $o_ins_total_latency LATENCY $o_ins_max_latency LATENCY $o_ins_avg_latency LATENCY $o_ins_lock_latency LATENCY $o_ins_cpu_latency LATENCY $o_ins_controlled_mem MEMORY $o_ins_total_mem MEMORY $o_ins_first_seen FIRST_SEEN $o_ins_last_seen LAST_SEEN
174 --sorted_result
175 CALL sys.statement_performance_analyzer('overall', NULL, 'analysis');
176
177 --replace_result $query_insert QUERY_INSERT $query_select QUERY_SELECT $query_update QUERY_UPDATE $digest_insert DIGEST_INSERT $digest_update DIGEST_UPDATE $digest_select DIGEST_SELECT $d_upd_total_latency LATENCY $o_upd_max_latency LATENCY $o_upd_first_seen FIRST_SEEN $o_upd_last_seen LAST_SEEN $d_upd_tock_latency LATENCY $d_upd_cpu_latency LATENCY $d_upd_controlled_mem MEMORY $o_upd_controlled_mem MEMORY $d_upd_total_mem MEMORY $o_upd_total_mem MEMORY $d_sel_total_latency LATENCY $o_sel_max_latency LATENCY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN $d_sel_tock_latency LATENCY $d_sel_cpu_latency LATENCY $d_sel_controlled_mem MEMORY $o_sel_controlled_mem MEMORY $d_sel_total_mem MEMORY $o_sel_total_mem MEMORY $d_ins_total_latency LATENCY $o_ins_max_latency LATENCY $o_ins_first_seen FIRST_SEEN $o_ins_last_seen LAST_SEEN $d_ins_tock_latency LATENCY $d_ins_cpu_latency LATENCY $d_ins_controlled_mem MEMORY $o_ins_controlled_mem MEMORY $d_ins_total_mem MEMORY $o_ins_total_mem MEMORY
178 --sorted_result
179 CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'analysis');
180
181 # Should give an empty result except for the banner generated by the procedure
182 CALL sys.statement_performance_analyzer('overall', NULL, 'with_errors_or_warnings');
183 CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'with_errors_or_warnings');
184
185 --replace_result $query_select QUERY_SELECT $digest_select DIGEST_SELECT $o_sel_total_latency LATENCY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN
186 CALL sys.statement_performance_analyzer('overall', NULL, 'with_full_table_scans');
187 --replace_result $query_select QUERY_SELECT $digest_select DIGEST_SELECT $d_sel_total_latency LATENCY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN
188 CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'with_full_table_scans');
189
190 --replace_result $query_select QUERY_SELECT $digest_select DIGEST_SELECT $o_sel_total_latency LATENCY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN
191 CALL sys.statement_performance_analyzer('overall', NULL, 'with_sorting');
192 --replace_result $query_select QUERY_SELECT $digest_select DIGEST_SELECT $d_sel_total_latency LATENCY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN
193 CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'with_sorting');
194
195 --replace_result $query_select QUERY_SELECT $digest_select DIGEST_SELECT $o_sel_total_latency LATENCY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN
196 CALL sys.statement_performance_analyzer('overall', NULL, 'with_temp_tables');
197 --replace_result $query_select QUERY_SELECT $digest_select DIGEST_SELECT $d_sel_total_latency LATENCY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN
198 CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'with_temp_tables');
199
200 # Try a custom view
201 # Sort by the query, then we know the order will be INSERT, SELECT, UPDATE
202 CREATE VIEW test.view_digests AS
203 SELECT sys.format_statement(DIGEST_TEXT) AS query,
204 SCHEMA_NAME AS db,
205 COUNT_STAR AS exec_count,
206 format_pico_time(SUM_TIMER_WAIT) AS total_latency,
207 format_pico_time(AVG_TIMER_WAIT) AS avg_latency,
208 ROUND(IFNULL(SUM_ROWS_SENT / NULLIF(COUNT_STAR, 0), 0)) AS rows_sent_avg,
209 ROUND(IFNULL(SUM_ROWS_EXAMINED / NULLIF(COUNT_STAR, 0), 0)) AS rows_examined_avg,
210 ROUND(IFNULL(SUM_ROWS_AFFECTED / NULLIF(COUNT_STAR, 0), 0)) AS rows_affected_avg,
211 DIGEST AS digest
212 FROM performance_schema.events_statements_summary_by_digest
213 ORDER BY SUBSTRING(query, 1, 6);
214 SET @sys.statement_performance_analyzer.view = 'test.view_digests';
215 --replace_result $query_insert QUERY_INSERT $query_select QUERY_SELECT $query_update QUERY_UPDATE $digest_insert DIGEST_INSERT $digest_update DIGEST_UPDATE $digest_select DIGEST_SELECT $o_upd_total_latency LATENCY $o_upd_avg_latency LATENCY $o_sel_total_latency LATENCY $o_sel_avg_latency LATENCY $o_ins_total_latency LATENCY $o_ins_avg_latency LATENCY
216 CALL sys.statement_performance_analyzer('overall', NULL, 'custom');
217 --replace_result $query_insert QUERY_INSERT $query_select QUERY_SELECT $query_update QUERY_UPDATE $digest_insert DIGEST_INSERT $digest_update DIGEST_UPDATE $digest_select DIGEST_SELECT $d_upd_total_latency LATENCY $d_sel_total_latency LATENCY $d_ins_total_latency LATENCY
218 CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'custom');
219
220 # Verify that setting a limit works
221 SET @sys.statement_performance_analyzer.limit = 2;
222 --replace_result $query_insert QUERY_INSERT $query_select QUERY_SELECT $digest_insert DIGEST_INSERT $digest_select DIGEST_SELECT $o_ins_total_latency LATENCY $o_ins_avg_latency LATENCY $o_sel_total_latency LATENCY $o_sel_avg_latency LATENCY
223 CALL sys.statement_performance_analyzer('overall', NULL, 'custom');
224
225 # Test for error conditions - some of the errors depend on the sql_mode so set it explicitly
226 # Which sql_modes are deprecated depends on the release, so disable warnings while setting the sql_mode
227
228
229 # Use non-strict sql_mode
230 SET SESSION sql_mode = '';
231 # Ask for a non-supported action - since strict more is not used, this will give the error: ERROR 1644 (45000): Unknown action: ''
232 # Note: the action passed to the procedure is actually lost because it's truncated.
233 --error ER_SIGNAL_EXCEPTION
234 CALL sys.statement_performance_analyzer('do magic', NULL, NULL);
235
236
237 # Use 5.7.9+ default:
238 --disable_warnings
239 SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
240 --enable_warnings
241 # Ask for a non-supported action - since strict mode is used, this will give the error: ERROR 1265 (01000): Data truncated for column 'in_action' at row 1
242 --error 1265
243 CALL sys.statement_performance_analyzer('do magic', NULL, NULL);
244 # Try to create reserved table name
245 -- error S45000
246 CALL sys.statement_performance_analyzer('create_tmp', 'sys.tmp_digests', NULL);
247 -- error S45000
248 CALL sys.statement_performance_analyzer('create_tmp', 'sys.tmp_digests_delta', NULL);
249 -- error S45000
250 CALL sys.statement_performance_analyzer('create_tmp', 'tmp_digests', NULL);
251 # This should succeed
252 CALL sys.statement_performance_analyzer('create_tmp', 'test.tmp_digests', NULL);
253 CREATE TABLE test.tmp_unsupported LIKE test.tmp_digests_ini;
254 # Try to create a temporary table that already exists
255 -- error S45000
256 CALL sys.statement_performance_analyzer('create_tmp', 'test.tmp_digests_ini', NULL);
257 # Try to create a base table that already exists
258 -- error S45000
259 CALL sys.statement_performance_analyzer('create_table', 'test.tmp_digests_ini', NULL);
260 -- error S45000
261 CALL sys.statement_performance_analyzer('create_table', 'test.tmp_unsupported', NULL);
262 # Verify that the sanity check for the input table e.g. for saving snapshots works
263 ALTER TABLE test.tmp_unsupported ADD COLUMN myvar int DEFAULT 0;
264 -- error S45000
265 CALL sys.statement_performance_analyzer('save', 'test.tmp_unsupported', NULL);
266 # Try to create a snapshot or overall output with the wrong in_table
267 -- error S45000
268 CALL sys.statement_performance_analyzer('snapshot', 'test.new_table', NULL);
269 -- error S45000
270 CALL sys.statement_performance_analyzer('overall', 'test.new_table', 'analysis');
271 # Try to create a delta output or save a snapshot specifying a non-existing table.
272 -- error S45000
273 CALL sys.statement_performance_analyzer('delta', 'test.new_table', 'analysis');
274 -- error S45000
275 CALL sys.statement_performance_analyzer('save', 'test.new_table', NULL);
276 # Verify custom views doesn't work without the user variable set
277 SET @sys.statement_performance_analyzer.view = NULL;
278 DELETE FROM sys.sys_config WHERE variable = 'statement_performance_analyzer.view';
279 -- error S45000
280 CALL sys.statement_performance_analyzer('overall', NULL, 'custom');
281 # Set the custom view to a regular table - should not work
282 SET @sys.statement_performance_analyzer.view = 'test.tmp_unsupported';
283 -- error S45000
284 CALL sys.statement_performance_analyzer('overall', NULL, 'custom');
285
286
287 # Remove the temporary tables created by the procedure
288 # First ensure the tables actually exists (to avoid passing the test if the table names are changed)
289 CALL sys.table_exists('sys', 'tmp_digests', @exists);
290 SELECT @exists;
291 CALL sys.table_exists('sys', 'tmp_digests_delta', @exists);
292 SELECT @exists;
293 CALL sys.statement_performance_analyzer('cleanup', NULL, NULL);
294 # Verify the internal tables really were removed
295 -- error ER_BAD_TABLE_ERROR
296 DROP TEMPORARY TABLE sys.tmp_digests;
297 -- error ER_BAD_TABLE_ERROR
298 DROP TEMPORARY TABLE sys.tmp_digests_delta;
299
300 # An attempt to create a delta view should fail now as there's no longer an existing snapshot.
301 -- error S45000
302 CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'analysis');
303 # Ensure no delta table was created.
304 -- error ER_BAD_TABLE_ERROR
305 DROP TEMPORARY TABLE sys.tmp_digests_delta;
306
307 # Try and use a table.db name > 129
308 SET @identifier := REPEAT('a', 65);
309 -- error 1406
310 CALL sys.statement_performance_analyzer('snapshot', CONCAT(@identifier, '.', @identifier), NULL);
311
312 # Clean up
313 DROP TEMPORARY TABLE test.tmp_digests_ini;
314 DROP TEMPORARY TABLE test.tmp_digests;
315 DROP TABLE test.tmp_unsupported;
316 DROP TABLE test.t1;
317 DROP VIEW view_digests;
318 SET @identifier := NULL;
319
320 SET SESSION sql_mode = @@global.sql_mode;
321 SET @sys.statement_performance_analyzer.limit = NULL;
322 SET @sys.statement_performance_analyzer.view = NULL;
323 --source ../include/ps_setup_consumers_cleanup.inc
324 --source ../include/ps_threads_cleanup.inc
325 --source ../include/sys_config_cleanup.inc