Debian Codegraph @

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