Debian Codegraph @

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

raw
1 # Some functional checks for the content of the performance_schema table
2 # socket_summary_by_instance.
3 #
4 # Created: mleich 2011-07-01
5 #
6 # Rough description of "What is when tested"
7 # 1. Impact of successful connect
8 # A new row with EVENT_NAME "client_connection" shows up
9 # is tested in 0 and 4.5.
10 # 2. Impact of disconnect
11 # A row with EVENT_NAME "client_connection" disappears
12 # is tested in 0. and 2.
13 # 3. Disabling the instrumentation for some thread causes that the
14 # counter for this thread become static is tested in 4.3.
15 # Nearby the beginning of this test and somewhere in the middle.
16 # 4. TRUNCATE table resets the counters is tested in 5.
17 # 5. Consistency within a row like MIN_* <= AVG_* <= MAX_*
18 # -> include/socket_summary_check.inc which is called at
19 # various places
20 # 6. Consistency of the instances mentioned in socket_summary_by_event
21 # and socket_summary_by_instance is checked per call of
22 # include/socket_summary_check.inc
23 # 7. Check if changes in counters of instances caused by actions
24 # are reasonable is tested for
25 # - Connects+SQL statements in 4.1 and 4.2
26 # - SQL statements in 4.4
27 # - Connects in 4.5
28 #
29
30 # Embedded server does not support the performance_schema.
31 --source include/not_embedded.inc
32
33 # Windows platforms only
34 --source include/windows.inc
35 --source include/have_perfschema.inc
36
37 # The values in the performance_schema tables depend on how much communication
38 # happens per SQL statement within our MTR tests. And there is a significant
39 # difference between standard statement execution and execution via
40 # prepared statement.
41 --source include/no_protocol.inc
42
43
44 #===================================
45 # Set IP address defaults with respect to IPV6 support
46 #
47 # Set this to enable debugging output
48 let $my_socket_debug_dbug= 0;
49 #
50 # Determine if IPV6 supported
51 #
52 let $check_ipv6_just_check= 1;
53 --source include/check_ipv6.inc
54 #
55 # Determine if IPV4 mapped to IPV6 supported
56 #
57 let $check_ipv4_mapped_just_check= 1;
58 --source include/have_ipv4_mapped.inc
59 #
60 # Set the localhost IP default to use when establishing connections
61 let $my_localhost=127.0.0.1;
62 if($check_ipv6_supported)
63 {
64 let $my_localhost=::1;
65 }
66 if($check_ipv4_mapped_supported)
67 {
68 let $my_localhost=::ffff:127.0.0.1;
69 }
70 #
71 let $my_socket_debug_dbug= 0;
72 if($my_socket_debug)
73 {
74 --echo IPV6=$check_ipv6_supported, IPV4_MAPPED = $check_ipv4_mapped_supported, LOCALHOST = $my_localhost
75 }
76 #===================================
77
78 --echo # The logging of commands and result sets is mostly disabled.
79 --echo # There are some messages which help to observe the progress of the test.
80 --echo # In case some check fails
81 --echo # - a message about this will be printed
82 --echo # - some SQL commands which show the unexpected state will be executed
83 --echo # (logging enabled)
84 --echo # - the test might abort
85 --echo #
86
87 --disable_query_log
88
89 --echo # 0. Check, build or set prequisites
90 #==========================================
91 # Set $print_details to 1 in case you want that the exact diffs caused
92 # by the execution of a statement get printed.
93 # Disadvantage of printing details:
94 # Even minor legimitate changes of the client - server communication can
95 # cause that the test needs maintenance.
96 # Advantage:
97 # More thorough checks.
98 # If any of the checks detects some suspicious/unexpected state than
99 # $print_details will be automatically switched to 1.
100 #
101 let $print_details= 0;
102
103 #
104 # Number of attempts within the test checking the stability of counter changes.
105 #
106 let $loop_rounds= 10;
107
108 # This test of PERFORMANCE_SCHEMA functionality is very vulnerable.
109 # Ensure that we have in the moment exact one
110 # - connection
111 # - instance with EVENT_NAME LIKE '%client_connection'
112 # - instance with EVENT_NAME LIKE '%server_tcpip_socket'
113 # - instance with EVENT_NAME LIKE '%server_unix_socket'
114 #=======================================================
115 let $my_rules= COUNT(*) = 1;
116 let $part=
117 FROM performance_schema.threads
118 WHERE NAME LIKE '%one_connection';
119 let $wait_condition=
120 SELECT $my_rules $part;
121 let $wait_timeout= 5;
122 --source include/wait_condition.inc
123 if(!$success)
124 {
125 --echo # ERROR: There must be only one user connection
126 eval
127 SELECT * $part;
128 --echo # abort
129 die;
130 }
131 let $part=
132 FROM performance_schema.socket_summary_by_instance
133 WHERE EVENT_NAME LIKE '%client_connection';
134 let $wait_condition=
135 SELECT $my_rules $part;
136 let $wait_timeout= 5;
137 --source include/wait_condition.inc
138 if(!$success)
139 {
140 --echo # ERROR: There must be only one instance with this EVENT_NAME.
141 eval
142 SELECT * $part;
143 --echo # abort
144 die;
145 }
146 # DISABLED FOR WINDOWS
147 let $part=
148 FROM performance_schema.socket_summary_by_instance
149 WHERE EVENT_NAME LIKE '%server_unix_socket';
150 let $wait_condition=
151 SELECT $my_rules $part;
152 let $wait_timeout= 5;
153 #--source include/wait_condition.inc
154 #if(!$success)
155 #{
156 # --echo # ERROR: There must be only one instance with this EVENT_NAME.
157 # eval
158 # SELECT * $part;
159 # --echo # abort
160 # die;
161 #}
162 let $part=
163 FROM performance_schema.socket_summary_by_instance
164 WHERE EVENT_NAME LIKE '%server_tcpip_socket';
165 let $wait_condition=
166 SELECT $my_rules $part;
167 let $wait_timeout= 5;
168 --source include/wait_condition.inc
169 if(!$success)
170 {
171 --echo # ERROR: There must be only one instance with this EVENT_NAME.
172 eval
173 SELECT * $part;
174 --echo # abort
175 die;
176 }
177
178 --disable_query_log
179
180 #
181 # Lower the resolution of the wait timer from the default 'CYCLE'
182 # to 'NANOSECOND'.
183 # Hint: The timer columns contains values in picoseconds independent
184 # of the timer resolution.
185 # The timer resolution has an impact on the precision of the value.
186 # This should prevent the failures seen on some suspicious PB boxes where
187 # - calculations exceeded the BIGINT UNSIGNED (data type of the counter columns)
188 # value range.
189 # - we have reached from whatever reason 20 digit values
190 # The consequence for the current test is the following:
191 # The common sense rule
192 # In case COUNT_<A> increases than SUM_TIMER_<A> must also increase
193 # is no more valid because some action might need less time than the
194 # timer resolution.
195 #
196 let $wait_timer= `SELECT TIMER_NAME FROM performance_schema.setup_timers
197 WHERE NAME = 'wait'`;
198 UPDATE performance_schema.setup_timers
199 SET TIMER_NAME = 'NANOSECOND'
200 WHERE NAME = 'wait';
201
202 #
203 # Additional SCHEMA used for
204 # - detection of our "worker" session within the PROCESSLIST.
205 # No other connection should use this schema as default schema.
206 # - easy cleanup because auxiliary objects are stored there
207 #
208 CREATE SCHEMA mysqltest;
209 CREATE SCHEMA mysqlsupertest;
210
211 #
212 # Clear summary tables of previous entries
213 #
214 TRUNCATE performance_schema.socket_summary_by_instance;
215 TRUNCATE performance_schema.socket_summary_by_event_name;
216 # Disable instrumenting of the current default session.
217 # Required for check 1.1
218 UPDATE performance_schema.threads
219 SET INSTRUMENTED='NO' WHERE PROCESSLIST_ID = CONNECTION_ID();
220
221 #
222 # Auxiliary tables for storing current values at certain points of time.
223 # We store states of performance_schema.socket_summary_by_instance here
224 # in order
225 # - to have initial values from before some action
226 # - to minimize the impact of statements used for the checks on results.
227 # CREATE TEMPORARY TABLE my_socket_summary_by_instance AS
228 # would be nice but some statements are not supported for temporary tables.
229 #
230 # DECIMAL(60,0) is used instead of BIGINT UNSIGNED. The goal is to prevent
231 # errors during calculations
232 # Example:
233 # - A and B UNSIGNED BIGINT
234 # - A < B
235 # - A - B ---> Error
236 # though the columns in all queries are orderd to avoid this too.
237 #
238 CREATE TABLE mysqltest.my_socket_summary_by_instance (
239 EVENT_NAME varchar(128) NOT NULL,
240 OBJECT_INSTANCE_BEGIN bigint(20) unsigned NOT NULL,
241 COUNT_STAR DECIMAL(60,0) NOT NULL,
242 SUM_TIMER_WAIT DECIMAL(60,0) NOT NULL,
243 MIN_TIMER_WAIT DECIMAL(60,0) NOT NULL,
244 AVG_TIMER_WAIT DECIMAL(60,0) NOT NULL,
245 MAX_TIMER_WAIT DECIMAL(60,0) NOT NULL,
246 COUNT_READ DECIMAL(60,0) NOT NULL,
247 SUM_TIMER_READ DECIMAL(60,0) NOT NULL,
248 MIN_TIMER_READ DECIMAL(60,0) NOT NULL,
249 AVG_TIMER_READ DECIMAL(60,0) NOT NULL,
250 MAX_TIMER_READ DECIMAL(60,0) NOT NULL,
251 SUM_NUMBER_OF_BYTES_READ DECIMAL(60,0) NOT NULL,
252 COUNT_WRITE DECIMAL(60,0) NOT NULL,
253 SUM_TIMER_WRITE DECIMAL(60,0) NOT NULL,
254 MIN_TIMER_WRITE DECIMAL(60,0) NOT NULL,
255 AVG_TIMER_WRITE DECIMAL(60,0) NOT NULL,
256 MAX_TIMER_WRITE DECIMAL(60,0) NOT NULL,
257 SUM_NUMBER_OF_BYTES_WRITE DECIMAL(60,0) NOT NULL,
258 COUNT_MISC DECIMAL(60,0) NOT NULL,
259 SUM_TIMER_MISC DECIMAL(60,0) NOT NULL,
260 MIN_TIMER_MISC DECIMAL(60,0) NOT NULL,
261 AVG_TIMER_MISC DECIMAL(60,0) NOT NULL,
262 MAX_TIMER_MISC DECIMAL(60,0) NOT NULL,
263 pk VARCHAR(20),
264 PRIMARY KEY(pk, EVENT_NAME, OBJECT_INSTANCE_BEGIN)
265 ) DEFAULT CHARSET=utf8;
266
267
268 # The CAST(... AS DECIMAL(60,0)) prevents errors which might show up in case
269 # we run with the original data type UNSIGNED BIGINT.
270 CREATE TABLE mysqltest.socket_summary_by_instance_detail (
271 EVENT_NAME varchar(128) NOT NULL,
272 OBJECT_INSTANCE_BEGIN bigint(20) unsigned NOT NULL,
273 COUNT_READ DECIMAL(60,0) NOT NULL,
274 SUM_TIMER_READ DECIMAL(60,0) NOT NULL,
275 SUM_NUMBER_OF_BYTES_READ DECIMAL(60,0) NOT NULL,
276 COUNT_WRITE DECIMAL(60,0) NOT NULL,
277 SUM_TIMER_WRITE DECIMAL(60,0) NOT NULL,
278 SUM_NUMBER_OF_BYTES_WRITE DECIMAL(60,0) NOT NULL,
279 COUNT_MISC DECIMAL(60,0) NOT NULL,
280 SUM_TIMER_MISC DECIMAL(60,0) NOT NULL,
281 statement VARCHAR(500),
282 run INTEGER
283 ) DEFAULT CHARSET=utf8;
284
285 #
286 # Auxiliary SQL functions used to shorten some commands.
287 #
288 CREATE FUNCTION mysqltest.min_of_triple
289 (f1 DECIMAL(60,0), f2 DECIMAL(60,0), f3 DECIMAL(60,0))
290 RETURNS DECIMAL(60,0)
291 RETURN IF(IF(f1 < f2,f1,f2) < f3,IF(f1 < f2,f1,f2), f3);
292 CREATE FUNCTION mysqltest.max_of_triple
293 (f1 DECIMAL(60,0), f2 DECIMAL(60,0), f3 DECIMAL(60,0))
294 RETURNS DECIMAL(60,0)
295 RETURN IF(IF(f1 > f2,f1,f2) > f3,IF(f1 > f2,f1,f2), f3);
296
297 #
298 # Auxiliary table for experiments with SELECTs earning different result sets.
299 #
300 CREATE TABLE mysqltest.my_aux (col1 INTEGER, col2 VARCHAR(1024), PRIMARY KEY(col1));
301 INSERT INTO mysqltest.my_aux SET col1 = 0, col2 = REPEAT('a',0);
302 INSERT INTO mysqltest.my_aux SET col1 = 1, col2 = REPEAT('a',0);
303 INSERT INTO mysqltest.my_aux SET col1 = 2, col2 = REPEAT('a',1);
304 INSERT INTO mysqltest.my_aux SET col1 = 3, col2 = REPEAT('a',1024);
305
306 #
307 # Auxiliary mysqltest variables used to shorten commands and to ensure
308 # that we run all time the right operation.
309 #
310 let $truncate=
311 TRUNCATE TABLE mysqltest.my_socket_summary_by_instance;
312 #
313 let $insert_before=
314 INSERT INTO mysqltest.my_socket_summary_by_instance
315 SELECT *,'Before' FROM performance_schema.socket_summary_by_instance;
316 #
317 let $insert_after=
318 INSERT INTO mysqltest.my_socket_summary_by_instance
319 SELECT *,'After' FROM performance_schema.socket_summary_by_instance;
320 #
321 let $insert_pseudo_before=
322 INSERT INTO mysqltest.my_socket_summary_by_instance
323 (EVENT_NAME, OBJECT_INSTANCE_BEGIN,
324 COUNT_STAR, SUM_TIMER_WAIT, MIN_TIMER_WAIT, AVG_TIMER_WAIT, MAX_TIMER_WAIT,
325 COUNT_READ, SUM_TIMER_READ, MIN_TIMER_READ, AVG_TIMER_READ, MAX_TIMER_READ,
326 SUM_NUMBER_OF_BYTES_READ,
327 COUNT_WRITE, SUM_TIMER_WRITE, MIN_TIMER_WRITE, AVG_TIMER_WRITE, MAX_TIMER_WRITE,
328 SUM_NUMBER_OF_BYTES_WRITE,
329 COUNT_MISC, SUM_TIMER_MISC, MIN_TIMER_MISC, AVG_TIMER_MISC, MAX_TIMER_MISC,
330 pk)
331 SELECT EVENT_NAME, OBJECT_INSTANCE_BEGIN,
332 0, 0, 0, 0, 0,
333 0, 0, 0, 0, 0,
334 0,
335 0, 0, 0, 0, 0,
336 0,
337 0, 0, 0, 0, 0,
338 'Pseudo_Before'
339 FROM mysqltest.my_socket_summary_by_instance t1
340 WHERE OBJECT_INSTANCE_BEGIN NOT IN
341 (SELECT OBJECT_INSTANCE_BEGIN
342 FROM mysqltest.my_socket_summary_by_instance t2
343 WHERE pk = 'Before');
344 #
345 let $insert_delta=
346 INSERT INTO mysqltest.socket_summary_by_instance_detail
347 (EVENT_NAME,OBJECT_INSTANCE_BEGIN,
348 COUNT_READ, SUM_TIMER_READ, SUM_NUMBER_OF_BYTES_READ,
349 COUNT_WRITE,SUM_TIMER_WRITE,SUM_NUMBER_OF_BYTES_WRITE,
350 COUNT_MISC, SUM_TIMER_MISC, statement,run)
351 SELECT EVENT_NAME,OBJECT_INSTANCE_BEGIN,
352 t1.COUNT_READ - t2.COUNT_READ,
353 t1.SUM_TIMER_READ - t2.SUM_TIMER_READ,
354 t1.SUM_NUMBER_OF_BYTES_READ - t2.SUM_NUMBER_OF_BYTES_READ,
355 t1.COUNT_WRITE - t2.COUNT_WRITE,
356 t1.SUM_TIMER_WRITE - t2.SUM_TIMER_WRITE,
357 t1.SUM_NUMBER_OF_BYTES_WRITE - t2.SUM_NUMBER_OF_BYTES_WRITE,
358 t1.COUNT_MISC - t2.COUNT_MISC,
359 t1.SUM_TIMER_MISC - t2.SUM_TIMER_MISC,
360 NULL,NULL
361 FROM mysqltest.my_socket_summary_by_instance t1
362 JOIN mysqltest.my_socket_summary_by_instance t2
363 USING (EVENT_NAME,OBJECT_INSTANCE_BEGIN)
364 WHERE t1.pk = 'After' AND t2.pk LIKE '%Before';
365 #
366 let $get_object_instance_begin=
367 SELECT OBJECT_INSTANCE_BEGIN INTO @con1_object_instance_begin
368 FROM performance_schema.socket_summary_by_instance AS t1
369 WHERE (EVENT_NAME,OBJECT_INSTANCE_BEGIN)
370 NOT IN (SELECT EVENT_NAME,OBJECT_INSTANCE_BEGIN
371 FROM mysqltest.my_socket_summary_by_instance AS t2
372 WHERE pk = 'Before');
373
374 # Use this whenever you print data.
375 let $column_list=
376 RPAD(EVENT_NAME, 38, ' ') AS EVENT_NAME,
377 LPAD(OBJECT_INSTANCE_BEGIN, 20, ' ') AS OBJECT_INSTANCE,
378 LPAD(COUNT_READ, 7, ' ') AS CREAD,
379 LPAD(SUM_TIMER_READ, 12, ' ') AS TREAD,
380 LPAD(SUM_NUMBER_OF_BYTES_READ, 7, ' ') AS BREAD,
381 LPAD(COUNT_WRITE, 7, ' ') AS CWRITE,
382 LPAD(SUM_TIMER_WRITE, 12, ' ') AS TWRITE,
383 LPAD(SUM_NUMBER_OF_BYTES_WRITE, 7, ' ') AS BWRITE,
384 LPAD(COUNT_MISC, 7, ' ') AS CMISC,
385 LPAD(SUM_TIMER_MISC, 13, ' ') AS TMISC,
386 RPAD(STATEMENT, 50, ' ') AS STATEMENT,
387 LPAD(RUN, 5, ' ') AS RUN;
388
389 # Determine OBJECT_INSTANCE_BEGIN of the connection default
390 # which acts as the observer
391 eval $truncate;
392 eval $insert_before;
393 --disconnect default
394 --connect (default,localhost,root,,,,)
395 # --echo ########### Disconnect/Connect
396 # --enable_query_log
397 eval $insert_after;
398 eval $get_object_instance_begin;
399 SET @default_object_instance_begin = @con1_object_instance_begin;
400
401 # Wait till the old default connection has disappeared
402 let $wait_timeout= 5;
403 let $wait_condition=
404 SELECT COUNT(*) = 1
405 FROM performance_schema.threads
406 WHERE processlist_user = 'root';
407 --source include/wait_condition.inc
408 --enable_query_log
409 if (!$success)
410 {
411 --enable_query_log
412 --enable_result_log
413 --echo # Error: The disconnect of the old default connection
414 --echo # (user = 'root') failed. We expect to have only one connection
415 --echo # with user = 'root'. And this is our current connection.
416 SELECT * FROM performance_schema.threads
417 WHERE processlist_user = 'root';
418 --echo # abort
419 die;
420 }
421
422 # Disable instrumenting of the current default session.
423 # Required for check 1.2
424 UPDATE performance_schema.threads
425 SET INSTRUMENTED='NO' WHERE PROCESSLIST_ID = CONNECTION_ID();
426
427 --echo # 1. Basic checks
428 --echo # 1.1 Check that the entry of the disconnected old default session really
429 --echo # disappeared from performance_schema.socket_summary_by_instance.
430 #===============================================================================
431 # This failed at some point in history when the instrumenting for the
432 # session to be disconnected was disabled.
433 if(`SELECT COUNT(*) FROM performance_schema.socket_summary_by_instance
434 WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
435 AND EVENT_NAME LIKE '%client_connection'`)
436 {
437 --enable_query_log
438 --enable_result_log
439 --echo # Error: The disconnected old default session did not disappear from
440 --echo # socket_summary_by_instance.
441 SELECT * FROM performance_schema.socket_summary_by_instance
442 WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
443 AND EVENT_NAME LIKE '%client_connection';
444 }
445
446 TRUNCATE TABLE performance_schema.socket_summary_by_instance;
447
448 --echo # 1.2 Check the base line
449 #===============================
450 eval $truncate;
451 --source ../include/socket_summary_check.inc
452
453 # --disable_query_log
454 # --disable_query_log
455
456 --echo # 2. Variations on Connect
457 let $is_connect= 1;
458 --echo # 2.1 Connect fails because the user is unknown
459 --echo # length of user name = 4 character
460 --echo # length of default db = 9 character
461 #========================================================================
462 let $connect_host= localhost;
463 let $connect_db= mysqltest;
464 let $connect_user= boot;
465 --source ../include/socket_event.inc
466
467 --echo # 2.2 Connect fails because the user is unknown
468 --echo # length of user name = 14 character (10 more than in 2.1)
469 --echo # length of default db = 9 character
470 #========================================================================
471 let $connect_host= localhost;
472 let $connect_db= mysqltest;
473 let $connect_user= boot0123456789;
474 --source ../include/socket_event.inc
475
476 --echo # 2.3 Connect should pass, host = localhost
477 --echo # length of user name = 4 character
478 --echo # length of default db = 9 character
479 #========================================================================
480 let $connect_host= localhost;
481 let $connect_db= mysqltest;
482 let $connect_user= root;
483 --source ../include/socket_event.inc
484
485 --echo # 2.4 Connect should pass, host = localhost
486 --echo # length of user name = 4 character
487 --echo # length of default db = 14 character (5 more than 2.3)
488 #========================================================================
489 let $connect_host= localhost;
490 let $connect_db= mysqlsupertest;
491 let $connect_user= root;
492 --source ../include/socket_event.inc
493
494 --echo # 2.5 Connect should pass, host = localhost
495 --echo # length of user name = 10 character
496 --echo # length of default db = 9 character
497 #========================================================================
498 SET @orig_sql_mode= @@sql_mode;
499 SET sql_mode= (SELECT replace(@@sql_mode,'NO_AUTO_CREATE_USER',''));
500 GRANT ALL PRIVILEGES ON *.* TO 'root012345'@'localhost';
501 SET sql_mode= @orig_sql_mode;
502 let $connect_host= localhost;
503 let $connect_db= mysqltest;
504 let $connect_user= root012345;
505 --source ../include/socket_event.inc
506 DROP USER 'root012345'@'localhost';
507
508 --echo # 2.6 Connect should pass, host = localhost
509 --echo # length of user name = 14 character
510 --echo # length of default db = 9 character
511 #========================================================================
512 SET @orig_sql_mode= @@sql_mode;
513 SET sql_mode= (SELECT replace(@@sql_mode,'NO_AUTO_CREATE_USER',''));
514 GRANT ALL PRIVILEGES ON *.* to 'root0123456789'@'localhost';
515 SET sql_mode= @orig_sql_mode;
516 let $connect_host= localhost;
517 let $connect_db= mysqltest;
518 let $connect_user= root0123456789;
519 --source ../include/socket_event.inc
520 DROP USER 'root0123456789'@'localhost';
521
522 --echo # 2.7 Connect should pass, host = my_localhost
523 --echo # length of user name = 4 character
524 --echo # length of default db = 9 character
525 --echo # connection runs through server_tcpip_socket !
526 #========================================================================
527 let $connect_host= $my_localhost;
528 let $connect_db= mysqltest;
529 let $connect_user= root;
530 --source ../include/socket_event.inc
531
532 #========================================================================
533 --connect (con1,localhost,root,,mysqltest,,)
534 # Experiments showed some unexpected result in the counter difference
535 # which got con1 for the next statement (see 3.1).
536 # The measured diff was too high and was probably caused by some
537 # too much delayed counter maintenance for the connect.
538 # We run here just some SQL statement because counter maintenance
539 # for SQL statements is more fast and reliable than far Connect.
540 DO 1;
541 --connection default
542 --source ../include/wait_till_sleep.inc
543 --disable_query_log
544
545 --echo # 3 Variations on SELECT
546 # Attention: Don't use
547 # - any double quotes within the statements because sourced scripts
548 # already "decorate" $variables with double quotes
549 # - UNION because this leads to result set related byte write
550 # counters which cannot good compared to select without union
551 let $is_connect= 0;
552 eval $get_object_instance_begin;
553 --echo # 3.1 Check a SELECT ending with server sending an error message.
554 --echo # Error message is short (unknown table).
555 #========================================================================
556 let $statement= SELECT col2 FROM does_not_exist;
557 --source ../include/socket_event.inc
558
559 --echo # 3.2 SELECT ending with server sending an error message.
560 --echo # Now the statement is a bit longer but the error message
561 --echo # length does again not depend on statement.
562 #=======================================================================
563 let $statement= SELECT col2 FROM does_not_exist WHERE col1 = 0;
564 --source ../include/socket_event.inc
565
566 --echo # 3.3 SELECT ending with server sending an error message.
567 --echo # The statement has the same length like in 3.2 but the error
568 --echo # message is now different and much longer.
569 #=======================================================================
570 let $statement= SELECT col2 FROM does_not_exist WHERE col1 A 0;
571 --source ../include/socket_event.inc
572
573 --echo # 3.4 SELECT ending with server sending an error message.
574 --echo # Statement and error message are a bit longer than in 3.1
575 --echo # because the table name is longer.
576 #=======================================================================
577 let $statement= SELECT col2 FROM does_not_exist0123;
578 --source ../include/socket_event.inc
579
580 --echo # 3.5 SELECT earning an empty result set.
581 #=======================================================
582 let $statement= SELECT col2 FROM mysqltest.my_aux WHERE col1 = -1;
583 --source ../include/socket_event.inc
584
585 --echo # 3.6 SELECT earning an empty result set.
586 --echo # Short column name is replaced by longer alias.
587 #==========================================================
588 let $statement= SELECT col2 AS my_super_col FROM mysqltest.my_aux WHERE col1 = -1;
589 --source ../include/socket_event.inc
590
591 --echo # 3.7 SELECT earning one row with an empty string.
592 #================================================================
593 let $statement= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1;
594 --source ../include/socket_event.inc
595
596 --echo # 3.8 SELECT earning one row with one string one char long.
597 #=========================================================================
598 let $statement= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 2;
599 --source ../include/socket_event.inc
600
601 --echo # 3.9 SELECT earning one row with one string 1024 char long.
602 #==========================================================================
603 let $statement= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 3;
604 --source ../include/socket_event.inc
605
606 --echo # 3.10 SELECT earning two rows with an empty string
607 #==========================================================================
608 let $statement= SELECT col2 FROM mysqltest.my_aux WHERE col1 < 2;
609 --source ../include/socket_event.inc
610
611 --echo # 3.11 Check that the preceding Connects/SQL command runs have not
612 --echo # caused some unexpected state.
613 #==========================================================================
614 let $my_rules= COUNT(*) = 2;
615 let $part=
616 FROM performance_schema.threads
617 WHERE NAME LIKE '%one_connection';
618 if(`SELECT NOT ( $my_rules ) $part `)
619 {
620 --echo # ERROR: There must be only two user connections
621 eval
622 SELECT * $part;
623 --echo # abort
624 die;
625 }
626 let $part=
627 FROM performance_schema.socket_summary_by_instance
628 WHERE EVENT_NAME LIKE '%client_connection';
629 if(`SELECT NOT ( $my_rules ) $part `)
630 {
631 --echo # ERROR: There must be only two instances with this EVENT_NAME.
632 eval
633 SELECT * $part;
634 --echo # abort
635 die;
636 }
637 let $my_rules= COUNT(*) = 1;
638 let $part=
639 FROM performance_schema.socket_summary_by_instance
640 WHERE EVENT_NAME LIKE '%server_unix_socket';
641 #DISABLED FOR WINDOWS
642 #if(`SELECT NOT ( $my_rules ) $part `)
643 if (0)
644 {
645 --echo # ERROR: There must be only one instance with this EVENT_NAME.
646 eval
647 SELECT * $part;
648 --echo # abort
649 die;
650 }
651 let $part=
652 FROM performance_schema.socket_summary_by_instance
653 WHERE EVENT_NAME LIKE '%server_tcpip_socket';
654 if(`SELECT NOT ( $my_rules ) $part `)
655 {
656 --echo # ERROR: There must be only one instance with this EVENT_NAME.
657 eval
658 SELECT * $part;
659 --echo # abort
660 die;
661 }
662
663 --echo # 4. Check delta (value_after_action - value_before_action) details
664 # 4.0 . Negative deltas cannot have happened because the counter columns within
665 # socket_summary_by_instance_detail are defined as UNSIGNED BIGINT.
666 # = The INSERT which computes the diff would have been failed.
667 --echo # 4.1 Check that
668 --echo # - no change in COUNT_* leads to no change in
669 --echo # SUM_TIMER_* and no change in SUM_NUMBER_OF_BYTES_*
670 --echo # - increased COUNT_READ leads to increased
671 --echo # SUM_NUMBER_OF_BYTES_READ
672 --echo # - increased COUNT_WRITE leads to increased
673 --echo # SUM_NUMBER_OF_BYTES_WRITE
674 --echo # Attention:
675 --echo # The time required for some action might be below timer resolution.
676 --echo # Therefore some increased COUNT_* does not need to lead to an
677 --echo # increased SUM_TIMER_*.
678 #==========================================================================
679 # Enable the following lines for debugging the check.
680 # Attention: socket_summary_by_instance_detail is after that rotten.
681 if(0)
682 {
683 --enable_info
684 UPDATE mysqltest.socket_summary_by_instance_detail
685 SET SUM_TIMER_MISC = 13, COUNT_MISC = 0
686 WHERE statement LIKE '%WHERE col1 = 3'
687 AND EVENT_NAME LIKE '%client_connection'
688 AND OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
689 LIMIT 1;
690 --disable_info
691 }
692
693 let $my_rules=
694 ((COUNT_READ = 0 AND SUM_TIMER_READ = 0 AND SUM_NUMBER_OF_BYTES_READ = 0)
695 OR
696 (COUNT_READ > 0 AND SUM_NUMBER_OF_BYTES_READ > 0))
697 AND
698 ((COUNT_WRITE = 0 AND SUM_TIMER_WRITE = 0 AND SUM_NUMBER_OF_BYTES_WRITE = 0)
699 OR
700 (COUNT_WRITE > 0 AND SUM_NUMBER_OF_BYTES_WRITE > 0))
701 AND
702 ((COUNT_MISC = 0 AND SUM_TIMER_MISC = 0)
703 OR
704 (COUNT_MISC > 0));
705 if(`SELECT COUNT(*) FROM mysqltest.socket_summary_by_instance_detail
706 WHERE NOT ( $my_rules ) `)
707 {
708 --enable_query_log
709 --enable_result_log
710 --echo # The statistics looks suspicious.
711 --echo # We expect
712 --echo # $my_rules
713 --echo #
714 eval
715 SELECT
716 $column_list
717 FROM mysqltest.socket_summary_by_instance_detail
718 WHERE NOT ( $my_rules )
719 ORDER BY EVENT_NAME, OBJECT_INSTANCE, STATEMENT, RUN;
720 let $print_details= 1;
721 }
722
723 --echo # 4.2 Results must be stable
724 #==========================================================================
725 # Enable the following lines for debugging the check.
726 # Attention: socket_summary_by_instance_detail is after that rotten.
727 if(0)
728 {
729 --enable_info
730 UPDATE mysqltest.socket_summary_by_instance_detail
731 SET COUNT_WRITE = 13
732 WHERE statement LIKE '%WHERE col1 = 3'
733 AND OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
734 AND EVENT_NAME LIKE '%client_connection'
735 LIMIT 1;
736 --disable_info
737 }
738
739 # eval
740 # SELECT
741 # $column_list
742 # FROM mysqltest.socket_summary_by_instance_detail
743 # WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
744 # ORDER BY EVENT_NAME, statement,run;
745
746 # In case we are able to wait all time till perfschema has finished the
747 # maintenance of counters than the following must be valid.
748 let $my_rules=
749 COUNT(DISTINCT SUM_NUMBER_OF_BYTES_READ) = 1 AND
750 COUNT(DISTINCT COUNT_WRITE) = 1 AND
751 COUNT(DISTINCT SUM_NUMBER_OF_BYTES_WRITE) = 1;
752
753 # In case we do not get the results somehow deterministic than we
754 # we should go with the less strict check based on CV.
755 #
756 # Compute coefficient of variation (CV) to detect 'notable' variances in the
757 # byte count and operation counts. The acceptable range for the CV is purely
758 # subjective, however, the CV is a dimensionless quantity therefore valid
759 # across platforms.
760 # let $my_rules=
761 # STD(COUNT_READ)/AVG(COUNT_READ) <= 0.2 AND
762 # STD(SUM_NUMBER_OF_BYTES_READ)/AVG(SUM_NUMBER_OF_BYTES_READ) <= 0.2 AND
763 # STD(COUNT_WRITE)/AVG(COUNT_WRITE) <= 0.2 AND
764 # STD(SUM_NUMBER_OF_BYTES_WRITE)/AVG(SUM_NUMBER_OF_BYTES_WRITE) <= 0.2 AND
765 # STD(COUNT_MISC)/AVG(COUNT_MISC) <= 0.4;
766
767 let $part=
768 FROM mysqltest.socket_summary_by_instance_detail
769 WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
770 GROUP BY EVENT_NAME, statement
771 HAVING NOT ($my_rules) ;
772 if(`SELECT COUNT(statement) $part`)
773 {
774 --enable_query_log
775 --enable_result_log
776 --echo # The statistics looks suspicious.
777 --echo # We expect
778 --echo # $my_rules
779 --echo # for GROUP BY EVENT_NAME, statement
780 eval
781 SELECT $column_list
782 FROM mysqltest.socket_summary_by_instance_detail
783 WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
784 AND (EVENT_NAME, statement)
785 IN (SELECT EVENT_NAME, statement
786 $part)
787 ORDER BY EVENT_NAME, statement, run, OBJECT_INSTANCE_BEGIN;
788 let $print_details= 1;
789 }
790
791 --echo # 4.3 Counters must be 0 in client_connection for the default session
792 --echo # Instrumenting is disabled since a long time and the counter were
793 --echo # reset via TRUNCATE just after the disabling.
794 #==========================================================================
795 let $my_rules=
796 COUNT_STAR = 0 AND SUM_TIMER_WAIT = 0
797 AND
798 COUNT_READ = 0 AND SUM_TIMER_READ = 0 AND SUM_NUMBER_OF_BYTES_READ = 0
799 AND
800 COUNT_WRITE = 0 AND SUM_TIMER_WRITE = 0 AND SUM_NUMBER_OF_BYTES_WRITE = 0
801 AND
802 COUNT_MISC = 0 AND SUM_TIMER_MISC = 0;
803 if(`SELECT COUNT(*) FROM performance_schema.socket_summary_by_instance
804 WHERE NOT ( $my_rules )
805 AND OBJECT_INSTANCE_BEGIN = @default_object_instance_begin`)
806 {
807 --enable_query_log
808 --enable_result_log
809 --echo # The statistics looks suspicious.
810 --echo # We expect
811 --echo # $my_rules
812 --echo #
813 # Attention: We use here performance_schema.socket_summary_by_instance
814 # and not mysqltest.socket_summary_by_instance_detail.
815 # Therefore the convenient $column_list cannot be used.
816 eval
817 SELECT
818 COUNT_STAR, SUM_TIMER_WAIT,
819 COUNT_READ,SUM_TIMER_READ,SUM_NUMBER_OF_BYTES_READ,
820 COUNT_WRITE,SUM_TIMER_WRITE,SUM_NUMBER_OF_BYTES_WRITE,
821 COUNT_MISC,SUM_TIMER_MISC
822 FROM performance_schema.socket_summary_by_instance
823 WHERE OBJECT_INSTANCE_BEGIN = @default_object_instance_begin;
824 let $print_details= 1;
825 }
826
827 #---------------------------------------------------------------------------
828
829 --echo # 4.4 Check the differences caused by SQL statements
830 --echo # 4.4.1 There must be no changes in counters of instances
831 --echo # NOT LIKE '%client_connection' because everything gets charged
832 --echo # into client_connection of the acting connection.
833 #===========================================================================
834 # Enable the following lines for debugging the check.
835 # Attention: socket_summary_by_instance_detail is after that rotten.
836 if(0)
837 {
838 --enable_info
839 UPDATE mysqltest.socket_summary_by_instance_detail
840 SET COUNT_WRITE = 13
841 WHERE statement LIKE '%WHERE col1 = 3'
842 AND EVENT_NAME NOT LIKE '%client_connection'
843 LIMIT 1;
844 --disable_info
845 }
846
847 let $my_rules=
848 COUNT_READ = 0 AND SUM_TIMER_READ = 0 AND SUM_NUMBER_OF_BYTES_READ = 0
849 AND
850 COUNT_WRITE = 0 AND SUM_TIMER_WRITE = 0 AND SUM_NUMBER_OF_BYTES_WRITE = 0
851 AND
852 COUNT_MISC = 0 AND SUM_TIMER_MISC = 0;
853 let $part=
854 FROM mysqltest.socket_summary_by_instance_detail
855 WHERE NOT ( $my_rules )
856 AND EVENT_NAME NOT LIKE '%client_connection'
857 AND statement NOT LIKE 'Connect%';
858 if(`SELECT COUNT(*) $part`)
859 {
860 --enable_query_log
861 --enable_result_log
862 --echo # The statistics looks suspicious.
863 --echo # We expect
864 --echo # $my_rules
865 --echo #
866 eval
867 SELECT
868 $column_list
869 $part
870 ORDER BY EVENT_NAME, OBJECT_INSTANCE_BEGIN, statement, run;
871 let $print_details= 1;
872 }
873
874 --echo # 4.4.2 In case of SELECT and our scenarios even COUNT_READ and COUNT_MISC
875 --echo # are stable.
876 #===========================================================================
877 # Enable the following lines for debugging the check.
878 # Attention: socket_summary_by_instance_detail is after that rotten.
879 if(0)
880 {
881 --enable_info
882 UPDATE mysqltest.socket_summary_by_instance_detail
883 SET COUNT_READ = 13
884 WHERE statement LIKE '%WHERE col1 = 3'
885 LIMIT 1;
886 --disable_info
887 }
888
889 let $my_rules=
890 COUNT(DISTINCT COUNT_READ) = 1 AND
891 COUNT(DISTINCT COUNT_MISC) = 1;
892 let $part=
893 FROM mysqltest.socket_summary_by_instance_detail
894 WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
895 AND statement NOT LIKE '%Connect%'
896 GROUP BY EVENT_NAME, statement
897 HAVING NOT ($my_rules) ;
898 if(`SELECT COUNT(statement) $part`)
899 {
900 --enable_query_log
901 --enable_result_log
902 --echo # The statistics looks suspicious.
903 --echo # We expect
904 --echo # $my_rules
905 --echo # for GROUP BY EVENT_NAME, statement
906 eval
907 SELECT $column_list
908 FROM mysqltest.socket_summary_by_instance_detail
909 WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
910 AND (EVENT_NAME, statement)
911 IN (SELECT EVENT_NAME, statement
912 $part)
913 ORDER BY EVENT_NAME, statement, run, OBJECT_INSTANCE_BEGIN;
914 let $print_details= 1;
915 }
916
917 --echo # 4.4.3 In our testing scenarios we get for the client_connection entry
918 --echo # of the acting connection
919 --echo # -> OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
920 --echo # COUNT_MISC = 0 AND SUM_TIMER_MISC = 0
921 --echo # SKIPPED FOR WINDOWS
922 #===========================================================================
923 # Enable the following lines for debugging the check.
924 # Attention: socket_summary_by_instance_detail is after that rotten.
925 if(0)
926 {
927 --enable_info
928 UPDATE mysqltest.socket_summary_by_instance_detail
929 SET COUNT_MISC = 13
930 WHERE statement LIKE '%WHERE col1 = 3'
931 AND EVENT_NAME LIKE '%client_connection'
932 AND OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
933 LIMIT 1;
934 --disable_info
935 }
936
937 let $my_rules=
938 COUNT_MISC = 0 AND SUM_TIMER_MISC = 0;
939 let $part=
940 FROM mysqltest.socket_summary_by_instance_detail
941 WHERE NOT ( $my_rules )
942 AND EVENT_NAME LIKE '%client_connection'
943 AND statement NOT LIKE 'Connect%'
944 AND OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin;
945 #if(`SELECT COUNT(*) $part`)
946 if (0)
947 {
948 --enable_query_log
949 --enable_result_log
950 --echo # The statistics looks suspicious.
951 --echo # We expect
952 --echo # $my_rules
953 --echo #
954 eval
955 SELECT
956 $column_list
957 $part
958 ORDER BY EVENT_NAME, OBJECT_INSTANCE_BEGIN, statement, run;
959 let $print_details= 1;
960 }
961
962 # Initialize variables
963 let $my_rules= my_rules_not_set;
964 let $stmt1= stmt1_not_set;
965 let $stmt2= stmt2_not_set;
966 # $title_prefix is used for the generation of titles
967 let $title_prefix= 4.4;
968 # $check_num is used for the generation of titles and gets incremented after
969 # every call of the current script.
970 let $check_num= 4;
971 # $column_list is used for the generation of error information and valid for
972 # every sub test.
973 let $diff_column_list=
974 t2.COUNT_READ - t1.COUNT_READ AS D_COUNT_READ,
975 t2.COUNT_READ AS S2_COUNT_READ,
976 t1.COUNT_READ AS S1_COUNT_READ,
977 t2.SUM_NUMBER_OF_BYTES_READ - t1.SUM_NUMBER_OF_BYTES_READ AS D_SUM_NUMBER_OF_BYTES_READ,
978 t2.SUM_NUMBER_OF_BYTES_READ AS S2_SUM_NUMBER_OF_BYTES_READ,
979 t1.SUM_NUMBER_OF_BYTES_READ AS S1_SUM_NUMBER_OF_BYTES_READ,
980 t2.COUNT_WRITE - t1.COUNT_WRITE AS D_COUNT_WRITE,
981 t2.COUNT_WRITE AS S2_COUNT_WRITE,
982 t1.COUNT_WRITE AS S1_COUNT_WRITE,
983 t2.SUM_NUMBER_OF_BYTES_WRITE - t1.SUM_NUMBER_OF_BYTES_WRITE AS D_SUM_NUMBER_OF_BYTES_WRITE,
984 t2.SUM_NUMBER_OF_BYTES_WRITE AS S2_SUM_NUMBER_OF_BYTES_WRITE,
985 t1.SUM_NUMBER_OF_BYTES_WRITE AS S1_SUM_NUMBER_OF_BYTES_WRITE,
986 t2.COUNT_MISC - t1.COUNT_MISC AS D_COUNT_MISC,
987 t2.COUNT_MISC AS S2_COUNT_MISC,
988 t1.COUNT_MISC AS S1_COUNT_MISC;
989 # $part is used for the generation of "check" statements + error information
990 # and valid for every sub test.
991 let $part=
992 FROM mysqltest.socket_summary_by_instance_detail t1
993 JOIN mysqltest.socket_summary_by_instance_detail t2
994 USING (EVENT_NAME, OBJECT_INSTANCE_BEGIN, run)
995 WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
996 AND EVENT_NAME LIKE ('%client_connection')
997 AND run = 1;
998
999 --echo # Check the differences between changes caused by SQL statements
1000 --echo # These differences must correspond to parameters like
1001 --echo # - statement, table name or column name length
1002 --echo # - number of rows in result set, size of rows in result set etc.
1003 # --> Statement NOT LIKE '%Connect%'
1004
1005 let stmt1= SELECT col2 FROM does_not_exist;
1006 let stmt2= SELECT col2 FROM does_not_exist WHERE col1 = 0;
1007 #
1008 # CNT_READ BYTES_READ CNT_WRITE BYTES_WRITE CNT_MISC statement
1009 # 3 36 1 59 1 SELECT col2 FROM does_not_exist
1010 # 3 51 1 59 1 SELECT col2 FROM does_not_exist WHERE col1 = 0
1011 # The string of the statement gets charged into SUM_NUMBER_OF_BYTES_READ.
1012 # The server error message gets charged into SUM_NUMBER_OF_BYTES_WRITE.
1013 let $msg=
1014 # One statement is longer than the other.
1015 # Both statements fail with the same error message (table does not exist);
1016 #
1017 let $my_rules=
1018 t2.COUNT_READ - t1.COUNT_READ = 0 AND
1019 t2.SUM_NUMBER_OF_BYTES_READ - t1.SUM_NUMBER_OF_BYTES_READ = LENGTH('$stmt2') - LENGTH('$stmt1') AND
1020 t2.COUNT_WRITE - t1.COUNT_WRITE = 0 AND
1021 t2.SUM_NUMBER_OF_BYTES_WRITE - t1.SUM_NUMBER_OF_BYTES_WRITE = 0 AND
1022 t2.COUNT_MISC - t1.COUNT_MISC = 0;
1023 --source ../include/socket_check1.inc
1024
1025 let $stmt1= SELECT col2 FROM does_not_exist WHERE col1 = 0;
1026 let $stmt2= SELECT col2 FROM does_not_exist WHERE col1 A 0;
1027 #
1028 # CNT_READ BYTES_READ CNT_WRITE BYTES_WRITE CNT_MISC statement
1029 # 3 51 1 59 1 SELECT col2 FROM does_not_exist WHERE col1 = 0
1030 # 3 51 1 162 1 SELECT col2 FROM does_not_exist WHERE col1 A 0
1031 let $msg=
1032 # Both statements have the same length and fail.
1033 # The length of the error messages differs.;
1034 let $my_rules=
1035 t2.COUNT_READ - t1.COUNT_READ = 0 AND
1036 t2.SUM_NUMBER_OF_BYTES_READ - t1.SUM_NUMBER_OF_BYTES_READ = LENGTH('$stmt2') - LENGTH('$stmt1') AND
1037 t2.COUNT_WRITE - t1.COUNT_WRITE = 0 AND
1038 t2.SUM_NUMBER_OF_BYTES_WRITE - t1.SUM_NUMBER_OF_BYTES_WRITE > 0 AND
1039 t2.COUNT_MISC - t1.COUNT_MISC = 0;
1040 --source ../include/socket_check1.inc
1041
1042 let $stmt1= SELECT col2 FROM does_not_exist;
1043 let $stmt2= SELECT col2 FROM does_not_exist0123;
1044 # CNT_READ BYTES_READ CNT_WRITE BYTES_WRITE CNT_MISC statement
1045 # 3 36 1 59 1 SELECT col2 FROM does_not_exist
1046 # 3 40 1 63 1 SELECT col2 FROM does_not_exist0123
1047 let $msg=
1048 # Both statements fail (table does not exist).
1049 # The length of the statement and the length of the error messages differs.
1050 # Reason for both differences is the length of the table name.;
1051 let $my_rules=
1052 t2.COUNT_READ - t1.COUNT_READ = 0 AND
1053 t2.SUM_NUMBER_OF_BYTES_READ - t1.SUM_NUMBER_OF_BYTES_READ = LENGTH('$stmt2') - LENGTH('$stmt1') AND
1054 t2.COUNT_WRITE - t1.COUNT_WRITE = 0 AND
1055 t2.SUM_NUMBER_OF_BYTES_WRITE - t1.SUM_NUMBER_OF_BYTES_WRITE = LENGTH('$stmt2') - LENGTH('$stmt1') AND
1056 t2.COUNT_MISC - t1.COUNT_MISC = 0;
1057 --source ../include/socket_check1.inc
1058
1059 # Assuming that some new check should be added.
1060 # How to enforce that a lot info is printed so that it becomes easy to derive
1061 # the right value for $my_rules?
1062 #----------------------------------------------------------------------------
1063 # let $my_rules= 0;
1064 # This will cause that suite/perfschema/include/socket_check1.inc concludes that
1065 # the check via $my_rules failed and it will print debug information.
1066 # let $stmt1= SELECT col2 FROM does_not_exist WHERE col1 = 0;
1067 # let $stmt2= SELECT col2 FROM does_not_exist WHERE col1 A 0;
1068 # let $my_rules= 0;
1069 # --source ../include/socket_check1.inc
1070
1071 let $stmt2= SELECT col2 AS my_super_col FROM mysqltest.my_aux WHERE col1 = -1;
1072 let $stmt1= SELECT col2 FROM mysqltest.my_aux WHERE col1 = -1;
1073 #
1074 # CNT_READ BYTES_READ CNT_WRITE BYTES_WRITE CNT_MISC statement
1075 # 3 54 1 78 1 SELECT col2 FROM mysqltest.my_aux WHERE col1 = -1
1076 # 3 70 1 86 1 SELECT col2 AS my_super_col FROM mysqltest.my_aux WHERE col1 = -1
1077 let $msg=
1078 # Both statements get an empty result set.
1079 # The length of the statements and the length of the result sets differs.
1080 # Reason for both differences is the length of the some column name.;
1081 let $my_rules=
1082 t2.COUNT_READ - t1.COUNT_READ = 0 AND
1083 t2.SUM_NUMBER_OF_BYTES_READ - t1.SUM_NUMBER_OF_BYTES_READ = LENGTH('$stmt2') - LENGTH('$stmt1') AND
1084 t2.COUNT_WRITE - t1.COUNT_WRITE = 0 AND
1085 t2.SUM_NUMBER_OF_BYTES_WRITE - t1.SUM_NUMBER_OF_BYTES_WRITE = LENGTH('my_super_col') - LENGTH('col2') AND
1086 t2.COUNT_MISC - t1.COUNT_MISC = 0;
1087 --source ../include/socket_check1.inc
1088
1089 let $stmt2= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1;
1090 let $stmt1= SELECT col2 FROM mysqltest.my_aux WHERE col1 = -1;
1091 #
1092 # CNT_READ BYTES_READ CNT_WRITE BYTES_WRITE CNT_MISC statement
1093 # t1 3 54 1 78 1 SELECT col2 FROM mysqltest.my_aux WHERE col1 = -1
1094 # t2 3 53 1 83 1 SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1
1095 let $msg=
1096 # Both statements differ in the statement length.
1097 # One statement earns an empty result set.
1098 # The other statement earns one row containing an empty string.;
1099 let $my_rules=
1100 t2.COUNT_READ - t1.COUNT_READ = 0 AND
1101 t1.SUM_NUMBER_OF_BYTES_READ - t2.SUM_NUMBER_OF_BYTES_READ = LENGTH('$stmt1') - LENGTH('$stmt2') AND
1102 t2.COUNT_WRITE - t1.COUNT_WRITE = 0 AND
1103 t2.SUM_NUMBER_OF_BYTES_WRITE - t1.SUM_NUMBER_OF_BYTES_WRITE > 0 AND
1104 t2.COUNT_MISC - t1.COUNT_MISC = 0;
1105 --source ../include/socket_check1.inc
1106
1107 let $stmt2= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 2;
1108 let $stmt1= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1;
1109 #
1110 # CNT_READ BYTES_READ CNT_WRITE BYTES_WRITE CNT_MISC statement
1111 # 3 53 1 83 1 SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1
1112 # 3 53 1 84 1 SELECT col2 FROM mysqltest.my_aux WHERE col1 = 2
1113 let $msg=
1114 # Both statements have the same length.
1115 # One statement earns an one row containing an empty string.
1116 # The other statement earns one row containing a string 1 byte long.;
1117 let $my_rules=
1118 t2.COUNT_READ - t1.COUNT_READ = 0 AND
1119 t2.SUM_NUMBER_OF_BYTES_READ - t1.SUM_NUMBER_OF_BYTES_READ = LENGTH('$stmt2') - LENGTH('$stmt1') AND
1120 t2.COUNT_WRITE - t1.COUNT_WRITE = 0 AND
1121 t2.SUM_NUMBER_OF_BYTES_WRITE - t1.SUM_NUMBER_OF_BYTES_WRITE = 1 AND
1122 t2.COUNT_MISC - t1.COUNT_MISC = 0;
1123 --source ../include/socket_check1.inc
1124
1125 let $stmt2= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 3;
1126 let $stmt1= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1;
1127 #
1128 # CNT_READ BYTES_READ CNT_WRITE BYTES_WRITE CNT_MISC statement
1129 # 3 53 1 83 1 SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1
1130 # 3 53 1 1109 1 SELECT col2 FROM mysqltest.my_aux WHERE col1 = 3
1131 let $msg=
1132 # Both statements have the same length.
1133 # One statement earns an one row containing an empty string.
1134 # The other statement earns one row containing a string 1024 byte long.;
1135 let $my_rules=
1136 t2.COUNT_READ - t1.COUNT_READ = 0 AND
1137 t2.SUM_NUMBER_OF_BYTES_READ - t1.SUM_NUMBER_OF_BYTES_READ = LENGTH('$stmt2') - LENGTH('$stmt1') AND
1138 t2.COUNT_WRITE - t1.COUNT_WRITE = 0 AND
1139 t2.SUM_NUMBER_OF_BYTES_WRITE - t1.SUM_NUMBER_OF_BYTES_WRITE >= 1024 AND
1140 t2.COUNT_MISC - t1.COUNT_MISC = 0;
1141 --source ../include/socket_check1.inc
1142
1143 let $stmt2= SELECT col2 FROM mysqltest.my_aux WHERE col1 < 2;
1144 let $stmt1= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1;
1145 #
1146 # CNT_READ BYTES_READ CNT_WRITE BYTES_WRITE CNT_MISC statement
1147 # t1 3 53 1 83 1 SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1
1148 # t2 3 53 1 88 1 SELECT col2 FROM mysqltest.my_aux WHERE col1 < 2
1149 let $msg=
1150 # Both statements have the same length.
1151 # One statement earns an one row containing an empty string.
1152 # The other statement earns two rows containing an empty string.;
1153 let $my_rules=
1154 t2.COUNT_READ - t1.COUNT_READ = 0 AND
1155 t2.SUM_NUMBER_OF_BYTES_READ - t1.SUM_NUMBER_OF_BYTES_READ = LENGTH('$stmt2') - LENGTH('$stmt1') AND
1156 t2.COUNT_WRITE - t1.COUNT_WRITE = 0 AND
1157 t2.SUM_NUMBER_OF_BYTES_WRITE - t1.SUM_NUMBER_OF_BYTES_WRITE > 0 AND
1158 t2.COUNT_MISC - t1.COUNT_MISC = 0;
1159 --source ../include/socket_check1.inc
1160
1161 --echo # 4.5 Check the differences caused by Connects
1162 --echo # Attention: Succesful Connects run an additional "DO 1".
1163 --echo # 4.5.1 Connects do not charge anything into READ or WRITE counters
1164 --echo # of the instance with EVENT_NAME NOT LIKE ('%client_connection%').
1165 --echo # This mean all these counters must be 0.
1166 let $my_rules=
1167 COUNT_READ = 0 AND SUM_TIMER_READ = 0 AND SUM_NUMBER_OF_BYTES_READ = 0 AND
1168 COUNT_WRITE = 0 AND SUM_TIMER_WRITE = 0 AND SUM_NUMBER_OF_BYTES_WRITE = 0;
1169 let $part=
1170 FROM mysqltest.socket_summary_by_instance_detail
1171 WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
1172 AND statement LIKE ('%Connect%')
1173 AND EVENT_NAME NOT LIKE ('%client_connection%')
1174 AND NOT ( $my_rules );
1175 if(`SELECT COUNT(*) $part `)
1176 {
1177 --enable_query_log
1178 --enable_result_log
1179 --echo # The statistics looks suspicious.
1180 --echo # We expect
1181 --echo # $my_rules
1182 --echo #
1183 eval
1184 SELECT
1185 $column_list
1186 $part;
1187 let $print_details= 1;
1188 }
1189
1190 --echo # 4.5.2 Connects using for host the value 'localhost'
1191 --echo # 4.5.2.1 For the instance with EVENT_NAME LIKE '%server_tcpip_socket'
1192 --echo # COUNT_MISC = 0 AND SUM_TIMER_MISC = 0 must be valid
1193 --echo # because we run through server_unix_socket.
1194 #===========================================================================
1195 # Enable the following lines for debugging the check.
1196 # Attention: socket_summary_by_instance_detail is after that rotten.
1197 if(0)
1198 {
1199 --enable_info
1200 UPDATE mysqltest.socket_summary_by_instance_detail
1201 SET COUNT_MISC = 13
1202 WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
1203 AND statement LIKE ('%Connect%localhost%')
1204 AND EVENT_NAME LIKE ('%server_tcpip_socket%')
1205 LIMIT 1;
1206 --disable_info
1207 }
1208
1209 let $my_rules=
1210 COUNT_MISC = 0 AND SUM_TIMER_MISC = 0;
1211 let $part=
1212 FROM mysqltest.socket_summary_by_instance_detail
1213 WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
1214 AND statement LIKE ('%Connect%localhost%')
1215 AND EVENT_NAME LIKE ('%server_tcpip_socket%')
1216 AND NOT ( $my_rules );
1217 #if(`SELECT COUNT(*) $part `)
1218 # DISABLED FOR WINDOWS
1219 if (0)
1220 {
1221 --enable_query_log
1222 --enable_result_log
1223 --echo # The statistics looks suspicious.
1224 --echo # We expect
1225 --echo # $my_rules
1226 --echo #
1227 eval
1228 SELECT
1229 $column_list
1230 $part;
1231 let $print_details= 1;
1232 }
1233
1234 --echo # 4.5.2.2 For the instance with EVENT_NAME LIKE '%server_unix_socket'
1235 --echo # COUNT_MISC > 0 must be valid.
1236 --echo # SKIPPED FOR WINDOWS
1237 #===========================================================================
1238 # Enable the following lines for debugging the check.
1239 # Attention: socket_summary_by_instance_detail is after that rotten.
1240 if(0)
1241 {
1242 --enable_info
1243 UPDATE mysqltest.socket_summary_by_instance_detail
1244 SET COUNT_MISC = 0
1245 WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
1246 AND statement LIKE ('%Connect%localhost%')
1247 AND EVENT_NAME LIKE ('%server_unix_socket%')
1248 LIMIT 1;
1249 --disable_info
1250 }
1251
1252 let $my_rules=
1253 COUNT_MISC > 0;
1254 let $part=
1255 FROM mysqltest.socket_summary_by_instance_detail
1256 WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
1257 AND statement LIKE ('%Connect%localhost%')
1258 AND EVENT_NAME LIKE ('%server_unix_socket%')
1259 AND NOT ( $my_rules );
1260 #if(`SELECT COUNT(*) $part `)
1261 if (0)
1262 {
1263 --enable_query_log
1264 --enable_result_log
1265 --echo # The statistics looks suspicious.
1266 --echo # We expect
1267 --echo # $my_rules
1268 --echo #
1269 eval
1270 SELECT
1271 $column_list
1272 $part;
1273 let $print_details= 1;
1274 }
1275
1276 --echo # 4.5.3 Connects using for host a value <> 'localhost'
1277 --echo # 4.5.3.1 For the instance with EVENT_NAME LIKE '%server_unix_socket'
1278 --echo # COUNT_MISC = 0 AND SUM_TIMER_MISC = 0 must be valid
1279 --echo # because we run through server_tcpip_socket.
1280 --echo # SKIPPED FOR WINDOWS
1281 #===========================================================================
1282 # Enable the following lines for debugging the check.
1283 # Attention: socket_summary_by_instance_detail is after that rotten.
1284 if(0)
1285 {
1286 --enable_info
1287 eval
1288 UPDATE mysqltest.socket_summary_by_instance_detail
1289 SET COUNT_MISC = 13
1290 WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
1291 AND EVENT_NAME LIKE ('%server_unix_socket%')
1292 AND statement LIKE ('%Connect%$my_localhost%')
1293 LIMIT 1;
1294 --disable_info
1295 }
1296
1297 let $my_rules=
1298 COUNT_MISC = 0 AND SUM_TIMER_MISC = 0;
1299 let $part=
1300 FROM mysqltest.socket_summary_by_instance_detail
1301 WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
1302 AND statement LIKE ('%Connect%$my_localhost%')
1303 AND EVENT_NAME LIKE ('%server_unix_socket%')
1304 AND NOT ( $my_rules );
1305 #if(`SELECT COUNT(*) $part `)
1306 if (0)
1307 {
1308 --enable_query_log
1309 --enable_result_log
1310 --echo # The statistics looks suspicious.
1311 --echo # We expect
1312 --echo # $my_rules
1313 --echo #
1314 eval
1315 SELECT
1316 $column_list
1317 $part;
1318 let $print_details= 1;
1319 }
1320
1321 --echo # 4.5.3.2 For the instance with EVENT_NAME LIKE '%server_tcpip_socket'
1322 --echo # COUNT_MISC > 0 must be valid.
1323 #===========================================================================
1324 # Enable the following lines for debugging the check.
1325 # Attention: socket_summary_by_instance_detail is after that rotten.
1326 if(0)
1327 {
1328 --enable_info
1329 eval
1330 UPDATE mysqltest.socket_summary_by_instance_detail
1331 SET COUNT_MISC = 0
1332 WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
1333 AND statement LIKE ('%Connect%$my_localhost%')
1334 AND EVENT_NAME LIKE ('%server_tcpip_socket%')
1335 LIMIT 1;
1336 --disable_info
1337 }
1338
1339 let $my_rules=
1340 COUNT_MISC > 0;
1341 let $part=
1342 FROM mysqltest.socket_summary_by_instance_detail
1343 WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
1344 AND statement LIKE ('%Connect%$my_localhost%')
1345 AND EVENT_NAME LIKE ('%server_tcpip_socket%')
1346 AND NOT ( $my_rules );
1347 if(`SELECT COUNT(*) $part `)
1348 {
1349 --enable_query_log
1350 --enable_result_log
1351 --echo # The statistics looks suspicious.
1352 --echo # We expect
1353 --echo # $my_rules
1354 --echo #
1355 eval
1356 SELECT
1357 $column_list
1358 $part;
1359 let $print_details= 1;
1360 }
1361
1362 --echo # 4.5.4 Failing Connects do not cause any row with EVENT_NAME
1363 --echo # LIKE '%client_connection'
1364 #===========================================================================
1365 # Enable the following lines for debugging the check.
1366 # Attention: socket_summary_by_instance_detail is after that rotten.
1367 if(0)
1368 {
1369 --enable_info
1370 eval
1371 UPDATE mysqltest.socket_summary_by_instance_detail
1372 SET statement = 'Connect boot '
1373 WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
1374 AND statement LIKE ('Connect%root%')
1375 AND EVENT_NAME LIKE ('%client_connection')
1376 LIMIT 1;
1377 --disable_info
1378 }
1379
1380 let $part=
1381 FROM mysqltest.socket_summary_by_instance_detail
1382 WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
1383 AND statement LIKE ('Connect%boot%')
1384 AND EVENT_NAME LIKE ('%client_connection');
1385 if(`SELECT COUNT(*) $part`)
1386 {
1387 --enable_query_log
1388 --enable_result_log
1389 --echo # The statistics looks suspicious.
1390 --echo # We expect that there are no rows because
1391 --echo # - failing connects do not cause a row with "client_connection"
1392 --echo # - all failing connects contain a user name LIKE '%boot%'
1393 eval
1394 SELECT
1395 $column_list
1396 $part;
1397 let $print_details= 1;
1398 }
1399
1400 --echo # 4.5.5 Successful Connects cause a new instance with EVENT_NAME
1401 --echo # LIKE '%client_connection'
1402 #===========================================================================
1403 # Enable the following lines for debugging the check.
1404 # Attention: socket_summary_by_instance_detail is after that rotten.
1405 if(0)
1406 {
1407 --enable_info
1408 DELETE
1409 FROM mysqltest.socket_summary_by_instance_detail
1410 WHERE EVENT_NAME LIKE ('%client_connection')
1411 AND OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
1412 AND statement LIKE ('Connect%root%')
1413 LIMIT 1;
1414 --disable_info
1415 }
1416
1417 # - connects which should be successful have statement LIKE ('Connect%root%').
1418 # - We try every type of Connect $loop_rounds times.
1419 # Therefore we should find $loop_rounds rows with
1420 # - EVENT_NAME LIKE ('%client_connection')
1421 # AND
1422 # - OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
1423 # We do not want to count the entry of belonging to the default connection.
1424 # AND
1425 # - statement LIKE ('Connect%root%')
1426 # The connects which should be successful.
1427 #
1428 let $my_rules=
1429 COUNT(*) = $loop_rounds;
1430 let $part=
1431 FROM mysqltest.socket_summary_by_instance_detail
1432 WHERE EVENT_NAME LIKE ('%client_connection')
1433 AND OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
1434 AND statement LIKE ('Connect%root%')
1435 GROUP BY statement
1436 HAVING NOT ( $my_rules );
1437
1438 if(`SELECT COUNT(*) $part`)
1439 {
1440 --enable_query_log
1441 --enable_result_log
1442 --echo # The statistics looks suspicious.
1443 --echo # We expect
1444 --echo # $my_rules
1445 --echo # for GROUP BY statement
1446 --echo #
1447 eval
1448 SELECT
1449 $column_list
1450 FROM mysqltest.socket_summary_by_instance_detail
1451 WHERE EVENT_NAME LIKE ('%client_connection')
1452 AND OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
1453 AND statement IN
1454 (SELECT statement
1455 $part);
1456 let $print_details= 1;
1457 }
1458
1459 --echo # 4.6 Check the differences caused by Connects
1460 --echo # - INSTANCES with an EVENT_NAME like server_tcpip_socket or
1461 --echo # server_unix_socket are already checked
1462 --echo # - the stability of results is already checked
1463 --echo # So we con go with the results of the first run.
1464
1465 # Typical content of mysqltest.socket_summary_by_instance_detail
1466 #
1467 # eval
1468 # SELECT $column_list
1469 # FROM mysqltest.socket_summary_by_instance_detail
1470 # WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
1471 # AND EVENT_NAME LIKE ('%client_connection')
1472 # AND statement LIKE ('Connect%')
1473 # AND run = 1
1474 # ORDER BY event_name,statement, run;
1475 # CREAD TREAD BREAD CWRITE TWRITE BWRITE CMISC TMISC STATEMENT
1476 # 4 6149616 79 3 34008480 117 3 38914128 Connect (con*,::ffff:127.0.0.1,root,,mysqltest,,)
1477 # 4 7012368 84 3 10112256 117 2 43067376 Connect (con*,localhost,root,,mysqlsupertest,,)
1478 # 4 7172880 79 3 10247688 117 2 40128000 Connect (con*,localhost,root,,mysqltest,,)
1479 # 4 6706392 85 3 15509472 117 2 34851168 Connect (con*,localhost,root012345,,mysqltest,,)
1480 # 4 10543632 89 3 10578744 117 2 39460872 Connect (con*,localhost,root0123456789,,mysqltest,,)
1481
1482 --echo # 4.6.1 The SUM_OF_BYTES_WRITE value depends on length of database
1483 --echo # since the database name is written in OK packet.
1484 --echo # Hence the value 2.
1485 #========================================================================
1486 let $my_rules= COUNT(DISTINCT SUM_NUMBER_OF_BYTES_WRITE) = 2;
1487 let $part=
1488 FROM mysqltest.socket_summary_by_instance_detail
1489 WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
1490 AND EVENT_NAME LIKE ('%client_connection')
1491 AND statement LIKE ('Connect%')
1492 AND run = 1;
1493 if(`SELECT NOT ( $my_rules) $part`)
1494 {
1495 --enable_query_log
1496 --enable_result_log
1497 --echo # The statistics looks suspicious.
1498 --echo # We expect
1499 --echo # $my_rules
1500 --echo # for all Connects
1501 --echo #
1502 eval
1503 SELECT
1504 statement, SUM_NUMBER_OF_BYTES_WRITE
1505 $part;
1506 let $print_details= 1;
1507 }
1508
1509 --echo # 4.6.2 The SUM_OF_BYTES_WRITE value hast to be > 100.
1510 #============================================================
1511 let $my_rules= SUM_NUMBER_OF_BYTES_WRITE > 100;
1512 let $part=
1513 FROM mysqltest.socket_summary_by_instance_detail
1514 WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
1515 AND EVENT_NAME LIKE ('%client_connection')
1516 AND statement LIKE ('Connect%')
1517 AND NOT ( $my_rules )
1518 AND run = 1;
1519 if(`SELECT COUNT(*) $part`)
1520 {
1521 --enable_query_log
1522 --enable_result_log
1523 --echo # The statistics looks suspicious.
1524 --echo # We expect
1525 --echo # $my_rules
1526 --echo # for all Connects
1527 --echo #
1528 eval
1529 SELECT
1530 statement, SUM_NUMBER_OF_BYTES_WRITE
1531 $part;
1532 let $print_details= 1;
1533 }
1534
1535 --echo # 4.6.3 COUNT_READ, COUNT_WRITE and COUNT_MISC have to be to be > 0
1536 #=========================================================================
1537 let $my_rules=
1538 COUNT_READ > 0 AND COUNT_WRITE > 0 AND COUNT_MISC > 0;
1539 let $part=
1540 FROM mysqltest.socket_summary_by_instance_detail
1541 WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
1542 AND EVENT_NAME LIKE ('%client_connection')
1543 AND statement LIKE ('Connect%')
1544 AND NOT ( $my_rules )
1545 AND run = 1;
1546 if(`SELECT COUNT(*) $part`)
1547 {
1548 --enable_query_log
1549 --enable_result_log
1550 --echo # The statistics looks suspicious.
1551 --echo # We expect
1552 --echo # $my_rules
1553 --echo # for all Connects
1554 --echo #
1555 eval
1556 SELECT
1557 statement, COUNT_READ, COUNT_WRITE, COUNT_MISC
1558 $part;
1559 let $print_details= 1;
1560 }
1561
1562 --echo # 4.6.4 Checks based on comparison of results for connects
1563 let $part0=
1564 FROM mysqltest.socket_summary_by_instance_detail t1
1565 JOIN mysqltest.socket_summary_by_instance_detail t2
1566 USING (EVENT_NAME, run)
1567 WHERE EVENT_NAME LIKE ('%client_connection')
1568 AND run = 1
1569 AND t2.OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
1570 AND t1.OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin;
1571 let $part1=
1572 SELECT RPAD(statement,55,' ') AS STATEMENT,
1573 LENGTH(statement), SUM_NUMBER_OF_BYTES_READ
1574 FROM mysqltest.socket_summary_by_instance_detail
1575 WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
1576 AND EVENT_NAME LIKE ('%client_connection')
1577 AND run = 1;
1578
1579
1580 --echo # 4.6.4 The user name length affects the SUM_OF_BYTES_READ value
1581 #======================================================================
1582 # CREAD TREAD BREAD CWRITE TWRITE BWRITE CMISC TMISC STATEMENT
1583 # 4 6706392 85 3 15509472 117 2 34851168 Connect (con*,localhost,root012345,,mysqltest,,)
1584 # 4 10543632 89 3 10578744 117 2 39460872 Connect (con*,localhost,root0123456789,,mysqltest,,)
1585 let $stmt2= Connect (con*,localhost,root0123456789,,mysqltest,,);
1586 let $stmt1= Connect (con*,localhost,root012345,,mysqltest,,);
1587 let $my_rules=
1588 t2.SUM_NUMBER_OF_BYTES_READ - t1.SUM_NUMBER_OF_BYTES_READ = LENGTH('$stmt2') - LENGTH('$stmt1');
1589 if(`SELECT NOT ($my_rules)
1590 $part0
1591 AND t2.statement = '$stmt2'
1592 AND t1.statement = '$stmt1'`)
1593 {
1594 --enable_query_log
1595 --enable_result_log
1596 --echo # The statistics looks suspicious.
1597 --echo # We expect
1598 --echo # $my_rules
1599 --echo #
1600 eval
1601 $part1
1602 AND statement IN('$stmt2','$stmt1');
1603 let $print_details= 1;
1604 }
1605
1606 --echo # 4.6.5 The database name length affects the SUM_OF_BYTES_READ value
1607 #==========================================================================
1608 # CREAD TREAD BREAD CWRITE TWRITE BWRITE CMISC TMISC STATEMENT
1609 # 4 7012368 84 3 10112256 117 2 43067376 Connect (con*,localhost,root,,mysqlsupertest,,)
1610 # 4 7172880 79 3 10247688 117 2 40128000 Connect (con*,localhost,root,,mysqltest,,)
1611 let $stmt2= Connect (con*,localhost,root,,mysqlsupertest,,);
1612 let $stmt1= Connect (con*,localhost,root,,mysqltest,,);
1613 let $my_rules=
1614 t2.SUM_NUMBER_OF_BYTES_READ - t1.SUM_NUMBER_OF_BYTES_READ = LENGTH('$stmt2') - LENGTH('$stmt1');
1615 if(`SELECT NOT ($my_rules)
1616 $part0
1617 AND t2.statement = '$stmt2'
1618 AND t1.statement = '$stmt1'`)
1619 {
1620 --enable_query_log
1621 --enable_result_log
1622 --echo # The statistics looks suspicious.
1623 --echo # We expect
1624 --echo # $my_rules
1625 --echo #
1626 eval
1627 $part1
1628 AND statement IN('$stmt2','$stmt1');
1629 let $print_details= 1;
1630 }
1631
1632 --echo # 5. Check the impact of TRUNCATE on socket_summary_by_instance.
1633 --echo # It must reset all counters.
1634 #=======================================================================
1635 # We do not need to check if the majority of counters is <> 0 because if
1636 # we ever hit such a bad state than a lot of the preceding checks would
1637 # have already failed and reported this.
1638 TRUNCATE TABLE performance_schema.socket_summary_by_instance;
1639 let $my_rules=
1640 COUNT_STAR = 0 AND SUM_TIMER_WAIT = 0
1641 AND
1642 COUNT_READ = 0 AND SUM_TIMER_READ = 0 AND SUM_NUMBER_OF_BYTES_READ = 0
1643 AND
1644 COUNT_WRITE = 0 AND SUM_TIMER_WRITE = 0 AND SUM_NUMBER_OF_BYTES_WRITE = 0
1645 AND
1646 COUNT_MISC = 0 AND SUM_TIMER_MISC = 0;
1647 if(`SELECT COUNT(*) FROM performance_schema.socket_summary_by_instance
1648 WHERE NOT ( $my_rules )
1649 AND OBJECT_INSTANCE_BEGIN = @default_object_instance_begin`)
1650 {
1651 --enable_query_log
1652 --enable_result_log
1653 --echo # The statistics looks suspicious.
1654 --echo # We expect
1655 --echo # $my_rules
1656 --echo #
1657 eval
1658 SELECT
1659 COUNT_STAR, SUM_TIMER_WAIT,
1660 COUNT_READ,SUM_TIMER_READ,SUM_NUMBER_OF_BYTES_READ,
1661 COUNT_WRITE,SUM_TIMER_WRITE,SUM_NUMBER_OF_BYTES_WRITE,
1662 COUNT_MISC,SUM_TIMER_MISC
1663 FROM performance_schema.socket_summary_by_instance
1664 WHERE OBJECT_INSTANCE_BEGIN = @default_object_instance_begin;
1665 }
1666
1667
1668 if($print_details)
1669 {
1670 --enable_query_log
1671 --enable_result_log
1672 --horizontal_results
1673 --echo # Dump detailed differences after - before statement execution
1674 --echo # 1. The statement executing connection and hopefully no one else
1675 SELECT @default_object_instance_begin;
1676 SELECT EVENT_NAME, OBJECT_INSTANCE_BEGIN,
1677 COUNT_READ, SUM_NUMBER_OF_BYTES_READ,
1678 COUNT_WRITE, SUM_NUMBER_OF_BYTES_WRITE,
1679 COUNT_MISC, statement
1680 FROM mysqltest.socket_summary_by_instance_detail
1681 WHERE EVENT_NAME LIKE '%client_connection%'
1682 AND OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
1683 ORDER BY statement, run;
1684 --echo # 2. The connection default
1685 SELECT EVENT_NAME,
1686 COUNT_READ, SUM_NUMBER_OF_BYTES_READ,
1687 COUNT_WRITE, SUM_NUMBER_OF_BYTES_WRITE,
1688 COUNT_MISC, statement
1689 FROM mysqltest.socket_summary_by_instance_detail
1690 WHERE OBJECT_INSTANCE_BEGIN = @default_object_instance_begin
1691 ORDER BY statement,run;
1692 --echo # 3. The "server_unix_socket"
1693 # WHERE OBJECT_INSTANCE_BEGIN = @default_object_instance_begin
1694 SELECT EVENT_NAME,
1695 COUNT_READ, SUM_NUMBER_OF_BYTES_READ,
1696 COUNT_WRITE, SUM_NUMBER_OF_BYTES_WRITE,
1697 COUNT_MISC, statement
1698 FROM mysqltest.socket_summary_by_instance_detail
1699 WHERE EVENT_NAME LIKE '%server_unix_socket%'
1700 ORDER BY statement,run;
1701 --echo # 4. The "server_tcpip_socket"
1702 SELECT EVENT_NAME,
1703 COUNT_READ, SUM_NUMBER_OF_BYTES_READ,
1704 COUNT_WRITE, SUM_NUMBER_OF_BYTES_WRITE,
1705 COUNT_MISC, statement
1706 FROM mysqltest.socket_summary_by_instance_detail
1707 WHERE EVENT_NAME LIKE '%server_tcpip_socket%'
1708 ORDER BY statement,run;
1709 --echo # 5. mysqltest.my_socket_summary_by_instance
1710 --vertical_results
1711 SELECT * FROM mysqltest.my_socket_summary_by_instance;
1712 --horizontal_results
1713 }
1714
1715 --echo # 6. Cleanup
1716 #==================
1717 # Cleanup
1718 --disable_query_log
1719 eval
1720 UPDATE performance_schema.setup_timers
1721 SET TIMER_NAME = '$wait_timer'
1722 WHERE NAME = 'wait';
1723 DROP SCHEMA mysqltest;
1724 DROP SCHEMA mysqlsupertest;
1725 --connection con1
1726 --disconnect con1
1727 --source include/wait_until_disconnected.inc
1728 --connection default
1729 --enable_query_log
1730