From: Christopher Powers Date: August 19 2011 7:41pm Subject: bzr push into mysql-trunk branch (chris.powers:3392 to 3394) WL#4896 List-Archive: http://lists.mysql.com/commits/140760 Message-Id: <201108191942.p7JJgBCh010144@acsmt357.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3394 Christopher Powers 2011-08-19 WL#4896 "Performance Schema Net IO" Cleanup before merge to mysql-trunk removed: mysql-test/suite/perfschema/t/socket_summary_by_instance_func_dbg.test modified: mysql-test/suite/perfschema/t/disabled.def 3393 Christopher Powers 2011-08-19 WL#4896 "Peformance Schema Net IO" privilege.test did not restore the 'idle' timer in setup_timers, which caused subsequent testcases sensitive to the idle timer to fail. modified: mysql-test/suite/perfschema/r/privilege.result mysql-test/suite/perfschema/t/privilege.test 3392 Marc Alff 2011-08-19 Removed unexplained diff with trunk modified: config.h.cmake === modified file 'mysql-test/suite/perfschema/r/privilege.result' --- a/mysql-test/suite/perfschema/r/privilege.result 2011-02-14 14:23:55 +0000 +++ b/mysql-test/suite/perfschema/r/privilege.result 2011-08-19 19:34:17 +0000 @@ -575,6 +575,7 @@ DROP USER pfs_user_4; flush privileges; UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES'; UPDATE performance_schema.setup_consumers SET enabled = 'YES'; -UPDATE performance_schema.setup_timers SET timer_name = 'CYCLE' where name="wait_timer"; -UPDATE performance_schema.setup_timers SET timer_name = 'CYCLE' where name="stage_timer"; -UPDATE performance_schema.setup_timers SET timer_name = 'NANOSECOND' where name="statement_timer"; +UPDATE performance_schema.setup_timers SET timer_name = 'MICROSECOND' where name="idle"; +UPDATE performance_schema.setup_timers SET timer_name = 'CYCLE' where name="wait"; +UPDATE performance_schema.setup_timers SET timer_name = 'NANOSECOND' where name="stage"; +UPDATE performance_schema.setup_timers SET timer_name = 'NANOSECOND' where name="statement"; === modified file 'mysql-test/suite/perfschema/t/disabled.def' --- a/mysql-test/suite/perfschema/t/disabled.def 2011-08-17 20:29:21 +0000 +++ b/mysql-test/suite/perfschema/t/disabled.def 2011-08-19 19:39:34 +0000 @@ -24,5 +24,4 @@ # Do not use any TAB characters for whitespace. # ############################################################################## -socket_summary_by_instance_func_dbg : WL#4896 "Performance Schema Net IO" - For debugging only === modified file 'mysql-test/suite/perfschema/t/privilege.test' --- a/mysql-test/suite/perfschema/t/privilege.test 2011-02-14 14:23:55 +0000 +++ b/mysql-test/suite/perfschema/t/privilege.test 2011-08-19 19:34:17 +0000 @@ -338,7 +338,9 @@ DROP USER pfs_user_4; flush privileges; UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES'; UPDATE performance_schema.setup_consumers SET enabled = 'YES'; -UPDATE performance_schema.setup_timers SET timer_name = 'CYCLE' where name="wait_timer"; -UPDATE performance_schema.setup_timers SET timer_name = 'CYCLE' where name="stage_timer"; -UPDATE performance_schema.setup_timers SET timer_name = 'NANOSECOND' where name="statement_timer"; + +UPDATE performance_schema.setup_timers SET timer_name = 'MICROSECOND' where name="idle"; +UPDATE performance_schema.setup_timers SET timer_name = 'CYCLE' where name="wait"; +UPDATE performance_schema.setup_timers SET timer_name = 'NANOSECOND' where name="stage"; +UPDATE performance_schema.setup_timers SET timer_name = 'NANOSECOND' where name="statement"; === removed file 'mysql-test/suite/perfschema/t/socket_summary_by_instance_func_dbg.test' --- a/mysql-test/suite/perfschema/t/socket_summary_by_instance_func_dbg.test 2011-08-17 20:29:21 +0000 +++ b/mysql-test/suite/perfschema/t/socket_summary_by_instance_func_dbg.test 1970-01-01 00:00:00 +0000 @@ -1,776 +0,0 @@ -# Copyright (c) 2011, Oracle and/or its affiliates. All rights reserved. -# -# This program is free software; you can redistribute it and/or -# modify it under the terms of the GNU General Public License -# as published by the Free Software Foundation; version 2 of -# the License. -# -# This program is distributed in the hope that it will be useful, -# but WITHOUT ANY WARRANTY; without even the implied warranty of -# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the -# GNU General Public License for more details. -# -# You should have received a copy of the GNU General Public License -# along with this program; if not, write to the Free Software -# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA -# 02110-1301 USA -# -# Some functional checks for the content of the performance_schema table -# socket_summary_by_instance -# -# Created: mleich 2011-07-01 -# - ---source include/not_embedded.inc - -#=================================== -# Set IP address defaults with respect to IPV6 support -# -# Set this to enable debugging output -let $my_socket_debug=0; - -# -# Determine if IPV6 supported -# -let $check_ipv6_just_check=1; ---source include/check_ipv6.inc - -# -# Determine if IPV4 mapped to IPV6 supported -# -let $check_ipv4_mapped_just_check=1; ---source include/check_ipv4_mapped.inc - -# -# Set the localhost IP default to use when establishing connections -let $my_localhost=127.0.0.1; - -if($check_ipv6_supported) -{ - let $my_localhost=::1; -} - -if($check_ipv4_mapped_supported) -{ - let $my_localhost=::ffff:127.0.0.1; -} - -if($my_socket_debug) -{ - --echo IPV6=$check_ipv6_supported, IPV4_MAPPED=$check_ipv4_mapped_supported, LOCALHOST=$my_localhost -} -#=================================== - ---echo # The logging of commands and result sets is mostly disabled. ---echo # There are some messages which help to observe the progress of the test. ---echo # In case some check fails ---echo # - a message about this will be printed ---echo # - some SQL commands which show the unexpected state will be executed ---echo # (logging enabled) ---echo # - the test might abort ---echo # - -if (!$my_socket_debug) -{ - --disable_query_log -} - ---echo # 0. Build or set prequisites -#=================================== -# Set $print_details to 1 in case you want that the exact diffs caused -# by the execution of a statement get printed. -# Disadvantage of printing details: -# Even minor legimitate changes of the client - server communication can -# cause that the test needs maintenance. -# Advantage: -# More thorough checks. -# -let $print_details= $my_socket_debug; - -# -# Number of attempts within the test checking the stability of counter increments. -# -let $loop_rounds= 3; - -# -# Disable instrumentation for the default connection to simplify record-keeping -# ---echo # Disable instrumenation for the default connection thread -eval UPDATE performance_schema.threads -SET INSTRUMENTED='NO' -WHERE PROCESSLIST_ID = CONNECTION_ID(); - -# -# Disable instrumentation for other event types to isolate net IO -# ---echo # Disable all instrumentation except net IO -UPDATE performance_schema.setup_instruments -SET ENABLED='NO', TIMED='NO' -WHERE NAME NOT LIKE '%socket%' AND NAME NOT LIKE '%idle%'; - -# -# Additional SCHEMA used for -# - detection of our "worker" session within the PROCESSLIST. -# No other connection should use this schema as default schema. -# - easy cleanup because auxiliary objects are stored there -# -CREATE SCHEMA mysqltest; -CREATE SCHEMA mysqlsupertest; - -# -# Auxiliary tables for storing current values at certain points of time. -# We store states of performance_schema.socket_summary_by_instance here -# in order -# - to have initial values from before some action -# - to minimize the impact of statements used for the checks on results. -# CREATE TEMPORARY TABLE my_socket_summary_by_instance AS -# would be nice but the optimizer does not support important statements. -# -CREATE TABLE mysqltest.my_socket_summary_by_instance AS -SELECT *, 'Pseudo_Before' AS pk FROM performance_schema.socket_summary_by_instance -WHERE 1 = 0; - -CREATE TABLE mysqltest.socket_summary_by_instance_detail AS -SELECT EVENT_NAME,OBJECT_INSTANCE_BEGIN, - COUNT_READ, SUM_TIMER_READ, SUM_NUMBER_OF_BYTES_READ, - COUNT_WRITE,SUM_TIMER_WRITE,SUM_NUMBER_OF_BYTES_WRITE, - COUNT_MISC, SUM_TIMER_MISC -FROM performance_schema.socket_summary_by_instance -WHERE 1 = 2; - -ALTER TABLE mysqltest.socket_summary_by_instance_detail -ADD statement VARCHAR(300), ADD run INTEGER; - -# -# Auxiliary SQL functions used to shorten some commands. -# -CREATE FUNCTION mysqltest.min_of_triple - (f1 BIGINT UNSIGNED, f2 BIGINT UNSIGNED, f3 BIGINT UNSIGNED) - RETURNS BIGINT UNSIGNED - RETURN IF(IF(f1 < f2,f1,f2) < f3,IF(f1 < f2,f1,f2), f3); - -CREATE FUNCTION mysqltest.max_of_triple - (f1 BIGINT UNSIGNED, f2 BIGINT UNSIGNED, f3 BIGINT UNSIGNED) - RETURNS BIGINT UNSIGNED - RETURN IF(IF(f1 > f2,f1,f2) > f3,IF(f1 > f2,f1,f2), f3); -# -# Auxiliary table for experiments with SELECTs earning different result sets. -# -CREATE TABLE mysqltest.my_aux (col1 INTEGER, col2 VARCHAR(1024), PRIMARY KEY(col1)); -INSERT INTO mysqltest.my_aux SET col1 = 1, col2 = REPEAT('a',0); -INSERT INTO mysqltest.my_aux SET col1 = 2, col2 = REPEAT('a',1); -INSERT INTO mysqltest.my_aux SET col1 = 3, col2 = REPEAT('a',1024); - -# -# Auxiliary mysqltest variables used to shorten commands and to ensure -# that we run all time the right operation. - -let $truncate= -TRUNCATE TABLE mysqltest.my_socket_summary_by_instance; - -let $insert_before= -INSERT INTO mysqltest.my_socket_summary_by_instance -SELECT *,'Before' FROM performance_schema.socket_summary_by_instance; - -let $insert_after= -INSERT INTO mysqltest.my_socket_summary_by_instance -SELECT *,'After' FROM performance_schema.socket_summary_by_instance; - -let $insert_pseudo_before= -INSERT INTO mysqltest.my_socket_summary_by_instance -(EVENT_NAME, OBJECT_INSTANCE_BEGIN, -COUNT_STAR, SUM_TIMER_WAIT, MIN_TIMER_WAIT, AVG_TIMER_WAIT, MAX_TIMER_WAIT, -COUNT_READ, SUM_TIMER_READ, MIN_TIMER_READ, AVG_TIMER_READ, MAX_TIMER_READ, -SUM_NUMBER_OF_BYTES_READ, -COUNT_WRITE, SUM_TIMER_WRITE, MIN_TIMER_WRITE, AVG_TIMER_WRITE, MAX_TIMER_WRITE, -SUM_NUMBER_OF_BYTES_WRITE, -COUNT_MISC, SUM_TIMER_MISC, MIN_TIMER_MISC, AVG_TIMER_MISC, MAX_TIMER_MISC, pk) -SELECT EVENT_NAME, OBJECT_INSTANCE_BEGIN, -0, 0, 0, 0, 0, -0, 0, 0, 0, 0, -0, -0, 0, 0, 0, 0, -0, -0, 0, 0, 0, 0, -'Pseudo_Before' -FROM mysqltest.my_socket_summary_by_instance t1 -WHERE OBJECT_INSTANCE_BEGIN NOT IN - (SELECT OBJECT_INSTANCE_BEGIN - FROM mysqltest.my_socket_summary_by_instance t2 - WHERE pk = 'Before'); - -let $insert_delta= -INSERT INTO mysqltest.socket_summary_by_instance_detail - (EVENT_NAME,OBJECT_INSTANCE_BEGIN, - COUNT_READ, SUM_TIMER_READ, SUM_NUMBER_OF_BYTES_READ, - COUNT_WRITE,SUM_TIMER_WRITE,SUM_NUMBER_OF_BYTES_WRITE, - COUNT_MISC, SUM_TIMER_MISC, statement,run) -SELECT EVENT_NAME,OBJECT_INSTANCE_BEGIN, - t1.COUNT_READ - t2.COUNT_READ, - t1.SUM_TIMER_READ - t2.SUM_TIMER_READ, - t1.SUM_NUMBER_OF_BYTES_READ - t2.SUM_NUMBER_OF_BYTES_READ, - t1.COUNT_WRITE - t2.COUNT_WRITE, - t1.SUM_TIMER_WRITE - t2.SUM_TIMER_WRITE, - t1.SUM_NUMBER_OF_BYTES_WRITE - t2.SUM_NUMBER_OF_BYTES_WRITE, - t1.COUNT_MISC - t2.COUNT_MISC, - t1.SUM_TIMER_MISC - t2.SUM_TIMER_MISC, - NULL,NULL -FROM mysqltest.my_socket_summary_by_instance t1 -JOIN mysqltest.my_socket_summary_by_instance t2 -USING (EVENT_NAME,OBJECT_INSTANCE_BEGIN) -WHERE t1.pk = 'After' AND t2.pk LIKE '%Before'; - -let $get_object_instance_begin= -SELECT OBJECT_INSTANCE_BEGIN INTO @con1_object_instance_begin -FROM performance_schema.socket_summary_by_instance AS t1 -WHERE (EVENT_NAME,OBJECT_INSTANCE_BEGIN) - NOT IN (SELECT EVENT_NAME,OBJECT_INSTANCE_BEGIN - FROM mysqltest.my_socket_summary_by_instance AS t2 - WHERE pk = 'Before'); - -let $get_object_instance_begin_default= -SELECT OBJECT_INSTANCE_BEGIN INTO @con1_object_instance_begin -FROM performance_schema.socket_summary_by_instance -WHERE EVENT_NAME LIKE "%client_connection%"; - -let $column_list= -COUNT_READ, SUM_TIMER_READ, SUM_NUMBER_OF_BYTES_READ, -COUNT_WRITE, SUM_TIMER_WRITE, SUM_NUMBER_OF_BYTES_WRITE, -COUNT_MISC, SUM_TIMER_MISC, statement; - -let $column_list_formatted= -lpad(COUNT_READ,7,' ') as CREAD, lpad(SUM_TIMER_READ,12,' ') as TREAD, lpad(SUM_NUMBER_OF_BYTES_READ,7,' ') as BREAD, -lpad(COUNT_WRITE,7,' ') as CWRITE, lpad(SUM_TIMER_WRITE,12,' ') as TWRITE,lpad(SUM_NUMBER_OF_BYTES_WRITE,7,' ') as BWRITE, -lpad(COUNT_MISC,7,' ') as CMISC, lpad(SUM_TIMER_MISC,13,' ') as TMISC, -rpad(statement,50,' ') as STATEMENT50; - -let $counter_list= -COUNT_READ, SUM_NUMBER_OF_BYTES_READ, -COUNT_WRITE, SUM_NUMBER_OF_BYTES_WRITE, -COUNT_MISC; - -let $counter_list_formatted= -lpad(COUNT_READ,7,' ') as CREAD, lpad(SUM_NUMBER_OF_BYTES_READ,7,' ') as BREAD, -lpad(COUNT_WRITE,7,' ') as CWRITE, lpad(SUM_NUMBER_OF_BYTES_WRITE,7,' ') as BWRITE, -lpad(COUNT_MISC,7,' ') as CMISC; - -let $cv_column_list_formatted= -rpad(EVENT_NAME,40,' ') as EVENT_NAME40, rpad(statement,50,' ') as STATEMENT50, -lpad(count(COUNT_READ),5,' ') as CREAD, -lpad(std(COUNT_READ)/avg(COUNT_READ),10,' ') as CV_CREAD, -lpad(std(SUM_NUMBER_OF_BYTES_READ)/avg(SUM_NUMBER_OF_BYTES_READ),10,' ') as CV_BREAD, -lpad(std(COUNT_WRITE)/avg(COUNT_WRITE),10,' ') as CV_CWRITE, -lpad(std(SUM_NUMBER_OF_BYTES_WRITE)/avg(SUM_NUMBER_OF_BYTES_WRITE),10,' ') as CV_BWRITE, -lpad(std(COUNT_MISC)/avg(COUNT_MISC),10,' ') as CV_CMISC; - -let $count_client_connections= -SELECT COUNT(*) INTO @client_connections -FROM performance_schema.socket_instances -WHERE EVENT_NAME LIKE "%client_connection%"; - -# -# Determine OBJECT_INSTANCE_BEGIN of the connection default -# which acts as the observer - ---echo # Confirm only one client connection -eval $count_client_connections; -if (`SELECT @client_connections != 1`) -{ - --echo # There should be one client connection - --echo # abort - exit; -} - ---echo # Get the default OBJECT_INSTANCE_BEGIN -eval $get_object_instance_begin_default; -SET @default_object_instance_begin = @con1_object_instance_begin; -if ($my_socket_debug) -{ - SELECT @default_object_instance_begin AS "Default Object Instance Begin"; -} - -# -# Truncate the temporary and socket summary tables to start with clean stats -# ---echo # Truncate my_socket_summary_by_instance -eval $truncate; ---echo # Truncate socket summary tables -TRUNCATE performance_schema.socket_summary_by_instance; -TRUNCATE performance_schema.socket_summary_by_event_name; - -#======================================================================== ---echo # 1. Check the base line ---source ../include/socket_summary_check_dbg.inc - -if (!$my_socket_debug) -{ - --disable_query_log - --disable_result_log -} - -#======================================================================== ---echo # 2. Check connect -let $is_connect= 1; ---echo # 2.1 Connect fails because the user is unknown ---echo # length of user name = 4 character ---echo # length of default db = 9 character - -let $connect_host= localhost; -let $connect_db= mysqltest; -let $connect_user= boot; ---source ../include/socket_event_dbg.inc - -#======================================================================== ---echo # 2.2 Connect fails because the user is unknown ---echo # length of user name = 14 character (10 more than in 2.1) ---echo # length of default db = 9 character - -let $connect_host= localhost; -let $connect_db= mysqltest; -let $connect_user= boot0123456789; ---source ../include/socket_event_dbg.inc - -#======================================================================== ---echo # 2.3 Connect should pass, host = localhost ---echo # length of user name = 4 character ---echo # length of default db = 9 character - -let $connect_host= localhost; -let $connect_db= mysqltest; -let $connect_user= root; ---source ../include/socket_event_dbg.inc - -#======================================================================== ---echo # 2.4 Connect should pass, host = localhost ---echo # length of user name = 4 character ---echo # length of default db = 14 character (5 more than 2.3) - -let $connect_host= localhost; -let $connect_db= mysqlsupertest; -let $connect_user= root; ---source ../include/socket_event_dbg.inc - -#======================================================================== ---echo # 2.5 Connect should pass, host = localhost ---echo # length of user name = 10 character ---echo # length of default db = 9 character - -GRANT ALL PRIVILEGES ON *.* TO 'root012345'@'localhost'; -let $connect_host= localhost; -let $connect_db= mysqltest; -let $connect_user= root012345; ---source ../include/socket_event_dbg.inc -DROP USER 'root012345'@'localhost'; - -#======================================================================== ---echo # 2.6 Connect should pass, host = localhost ---echo # length of user name = 14 character ---echo # length of default db = 9 character - -GRANT ALL PRIVILEGES ON *.* to 'root0123456789'@'localhost'; -let $connect_host= localhost; -let $connect_db= mysqltest; -let $connect_user= root0123456789; ---source ../include/socket_event_dbg.inc -DROP USER 'root0123456789'@'localhost'; - ---connect (con1,$my_localhost,root,,mysqltest,,) ---connection default ---source ../include/wait_till_sleep.inc ---disable_query_log - -#======================================================================== ---echo # 3 Check SELECTs -let $is_connect= 0; -eval $get_object_instance_begin; ---echo # 3.1 Check a SELECT ending with server sending an error message. ---echo # Error message is short (unknown table). - -let $statement= SELECT col2 FROM does_not_exist; ---source ../include/socket_event_dbg.inc - -#======================================================================== ---echo # 3.2 Check a SELECT ending with server sending an error message. ---echo # Error message is longer (syntax error). ---echo # Now the statement is a bit longer but the error message ---echo # length does again not depend on statement. -# -let $statement= SELECT col2 FROM does_not_exist WHERE col1 = 0; ---source ../include/socket_event_dbg.inc - -#======================================================================== ---echo # 3.3 Check a SELECT earning an empty result set. - -let $statement= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 0; ---source ../include/socket_event_dbg.inc - -#======================================================================== ---echo # 3.4 Check a SELECT earning an empty result set. ---echo # Short column name is replaced by longer alias. - -let $statement= SELECT col2 "my_lovely_col" FROM mysqltest.my_aux WHERE col1 = 0; ---source ../include/socket_event_dbg.inc - -#======================================================================== ---echo # 3.5 Check a SELECT earning one row with an empty string. - -let $statement= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1; ---source ../include/socket_event_dbg.inc - -#======================================================================== ---echo # 3.6 Check a SELECT earning one row with one string one char long. - -let $statement= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 2; ---source ../include/socket_event_dbg.inc - -#======================================================================== ---echo # 3.7 Check a SELECT earning one row with one string 1024 char long. - -let $statement= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 3; ---source ../include/socket_event_dbg.inc - -#======================================================================== ---echo # 4. Check delta details -# -# 4.0 0. Negative deltas cannot have happened because the counter columns within -# socket_summary_by_instance_detail are defined as UNSIGNED BIGINT. -# = The INSERT would have been failed. -# ---echo # 4.1 Check that increased COUNT_* leads to increased ---echo # - increased COUNT_* leads to increased ---echo # SUM_TIMER_* and SUM_NUMBER_OF_BYTES_* ---echo # - no change in COUNT_* leads to no change in ---echo # SUM_TIMER_* and SUM_NUMBER_OF_BYTES_* - -let $my_rules= -((COUNT_READ = 0 AND (SUM_TIMER_READ > 0 OR SUM_NUMBER_OF_BYTES_READ > 0)) - OR - (COUNT_WRITE = 0 AND (SUM_TIMER_WRITE > 0 OR SUM_NUMBER_OF_BYTES_WRITE > 0)) - OR - (COUNT_MISC = 0 AND SUM_TIMER_MISC > 0)); - -if(`SELECT COUNT(*) FROM mysqltest.socket_summary_by_instance_detail - WHERE ( $my_rules )`) -{ - --enable_query_log - --enable_result_log - --echo # The statistics looks suspicious. - --echo # We expect - --echo # $my_rules - --echo # - eval - SELECT EVENT_NAME, lpad(OBJECT_INSTANCE_BEGIN,20,' ') as OBJECT_INSTANCE, - lpad(COUNT_READ,7,' ') AS CREAD, lpad(SUM_TIMER_READ,12,' ') AS TREAD, lpad(SUM_NUMBER_OF_BYTES_READ,7,' ') AS BREAD, - lpad(COUNT_WRITE,7,' ') AS CWRITE, lpad(SUM_TIMER_WRITE,12,' ') AS TWRITE,lpad(SUM_NUMBER_OF_BYTES_WRITE,7,' ') AS BWRITE, - lpad(COUNT_MISC,7,' ') AS CMISC, lpad(SUM_TIMER_MISC,13,' ') AS TMISC, - rpad(statement,50,' ') AS STATEMENT, lpad(run, 5, ' ') AS RUN - FROM mysqltest.socket_summary_by_instance_detail ORDER BY EVENT_NAME, OBJECT_INSTANCE_BEGIN; -} - -#======================================================================== ---echo # 4.2 Results must be stable ---echo # Everything except "client_connection" for default connection ---echo # because the default connection runs include/wait_till_sleep.inc ---echo # which means a varying amount of statements. -# -# Enable the following lines for debugging the check -# -# UPDATE mysqltest.socket_summary_by_instance_detail -# SET COUNT_READ = 13 -# WHERE statement LIKE 'Connect%abc%' -# LIMIT 1; -# -# UPDATE mysqltest.socket_summary_by_instance_detail -# SET COUNT_WRITE = 13 -# WHERE statement LIKE '%WHERE col1 = 3' -# AND EVENT_NAME LIKE '%client_connection' -# LIMIT 1; - -let $my_rules1= -COUNT(DISTINCT COUNT_READ) = 1 AND -COUNT(DISTINCT SUM_NUMBER_OF_BYTES_READ) = 1 AND -COUNT(DISTINCT COUNT_WRITE) = 1 AND -COUNT(DISTINCT SUM_NUMBER_OF_BYTES_WRITE) = 1 AND -COUNT(DISTINCT COUNT_MISC) = 1; - -# Compute coefficient of variation (CV) to detect 'notable' variances in the -# byte count and operation counts. The acceptable range for the CV is purely -# subjective, however, the CV is a dimensionless quantity therefore valid -# across platforms. - -let $my_rules2= -STD(COUNT_READ)/AVG(COUNT_READ) <= 0.2 AND -STD(SUM_NUMBER_OF_BYTES_READ)/AVG(SUM_NUMBER_OF_BYTES_READ) <= 0.2 AND -STD(COUNT_WRITE)/AVG(COUNT_WRITE) <= 0.2 AND -STD(SUM_NUMBER_OF_BYTES_WRITE)/AVG(SUM_NUMBER_OF_BYTES_WRITE) <= 0.2 AND -STD(COUNT_MISC)/AVG(COUNT_MISC) <= 0.4; - -let $part1= -FROM mysqltest.socket_summary_by_instance_detail -WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin -GROUP BY EVENT_NAME, STATEMENT -HAVING ($my_rules1) <> 1; - -let $part2= -FROM mysqltest.socket_summary_by_instance_detail -WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin -GROUP BY EVENT_NAME, STATEMENT -HAVING ($my_rules2) <> 1; - -if (0) -#if(`SELECT COUNT(statement) $part1`) -{ - --echo # 4.2a Error: Distinct operation count failed - --enable_query_log - --enable_result_log - --echo # The statistics looks suspicious. We expect: - --echo # $my_rules1 - --echo # for GROUP BY EVENT_NAME, statement - SELECT @default_object_instance_begin AS 'Default Object Instance'; - --echo - --echo # - --echo # 4.2b - List inconsistent rows - --echo # - - eval SELECT OBJECT_INSTANCE_BEGIN, EVENT_NAME, run, $counter_list_formatted, statement - FROM mysqltest.socket_summary_by_instance_detail t1 - WHERE (EVENT_NAME, statement) IN - (SELECT EVENT_NAME, statement $part1) - AND OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin - ORDER BY EVENT_NAME, statement, run; - - --echo # - --echo # 4.2c - Dump all rows - --echo # - - eval SELECT OBJECT_INSTANCE_BEGIN, EVENT_NAME, run, $counter_list_formatted, statement - FROM mysqltest.socket_summary_by_instance_detail - ORDER BY EVENT_NAME, statement, run; -} - -if(`SELECT COUNT(statement) $part2`) -{ - --echo # 4.2d Error: Coefficient of Variation failed - --enable_query_log - --enable_result_log - --echo # The statistics looks suspicious. We expect: - --echo # $my_rules2 - --echo # for GROUP BY EVENT_NAME, statement - SELECT @default_object_instance_begin AS 'Default Object Instance'; - --echo - --echo # - --echo # 4.2e - List inconsistent rows - --echo # - - eval SELECT OBJECT_INSTANCE_BEGIN, EVENT_NAME, run, $counter_list_formatted, statement - FROM mysqltest.socket_summary_by_instance_detail t1 - WHERE (EVENT_NAME, statement) IN - (SELECT EVENT_NAME, statement $part2) - AND OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin - ORDER BY EVENT_NAME, statement, run; - - --echo # - --echo # 4.2f - Dump all rows - --echo # - - eval SELECT OBJECT_INSTANCE_BEGIN, EVENT_NAME, run, $counter_list_formatted, statement - FROM mysqltest.socket_summary_by_instance_detail - ORDER BY EVENT_NAME, statement, run; - - --echo # - --echo # 4.2g - Std dev and coefficient of variation for mysqltest.socket_summary_by_instance_detail - --echo # - - eval SELECT $cv_column_list_formatted - FROM mysqltest.socket_summary_by_instance_detail - WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin - GROUP BY EVENT_NAME40, STATEMENT50; -} - -#======================================================================== ---echo # 4.3 Check the connects ---echo # 4.3.1 Successful connects with different length of user name -# --> t2.statement LIKE 'Connect%root0123456789,%' -# --> t1.statement LIKE 'Connect%root012345,%' - -# There is no client_connection entry for a connect which fails. - -let $my_val= -t2.SUM_NUMBER_OF_BYTES_READ - t1.SUM_NUMBER_OF_BYTES_READ as DIFF_BREAD, -t2.SUM_NUMBER_OF_BYTES_WRITE - t1.SUM_NUMBER_OF_BYTES_WRITE as DIFF_BWRITE; -# t2.COUNT_WRITE - t1.COUNT_WRITE, -# t2.COUNT_READ - t1.COUNT_READ, -# t2.COUNT_MISC - t1.COUNT_MISC; - -let $my_val_not_null= -t2.COUNT_READ > 0 and t1.COUNT_READ > 0 and -t2.COUNT_WRITE > 0 and t1.COUNT_WRITE > 0; - -# -# Successful connects with different length of user name -# -eval -SELECT $my_val, EVENT_NAME -FROM mysqltest.socket_summary_by_instance_detail t2 -JOIN mysqltest.socket_summary_by_instance_detail t1 -USING (EVENT_NAME, run) -WHERE t2.OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin - AND t1.OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin - AND t2.statement LIKE 'Connect%root0123456789,%' - AND t1.statement LIKE 'Connect%root012345,%' - AND t1.EVENT_NAME NOT LIKE '%server_unix_socket%' - AND $my_val_not_null - AND run = 1; - -# -# Successful connects with different length of user name -# -eval -SELECT $counter_list, EVENT_NAME, statement -FROM mysqltest.socket_summary_by_instance_detail t2 -WHERE t2.OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin - AND t2.statement LIKE 'Connect%root0123456789,%' - AND run = 1; - -eval -SELECT $counter_list, EVENT_NAME, statement -FROM mysqltest.socket_summary_by_instance_detail t1 -WHERE t1.OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin - AND t1.statement LIKE 'Connect%root012345,%' - AND run = 1; - -#======================================================================== ---echo # 4.3.2 Failing (->2.1) vs. passing (-> 2.3) connect ---echo # FIXME: To be implemented after the bug fixes -# The difference in fail/pass must somewhere show up. -# - -#======================================================================== ---echo # 4.3.3 2.3 vs. 2.4 ---echo # FIXME: To be implemented after the bug fixes -# The difference in schema name length must somewhere show up. -# - -#======================================================================== ---echo # 4.4 Check the differences caused by SQL statements -# --> Statement NOT LIKE '%Connect%' ---echo # FIXME: To be implemented after the bug fixes - -# General architecture for checking stuff: -# -# let $my_rules= -# COUNT_READ = 0 AND -# SUM_TIMER_READ = 0 AND -# SUM_NUMBER_OF_BYTES_READ = 0 AND -# COUNT_WRITE BETWEEN 1 AND 3 AND -# SUM_TIMER_WRITE > 0 AND -# SUM_NUMBER_OF_BYTES_WRITE BETWEEN 1 AND 1000 AND -# COUNT_MISC = 0 AND -# SUM_TIMER_MISC = 0; -# -# let $part= -# FROM mysqltest.socket_summary_by_instance_detail -# WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin -# GROUP BY EVENT_NAME, statement -# HAVING ($my_rules) <> 1; -# -# if(`Check if something is wrong, use $my_rules and $part here`) -# { -# --enable_query_log -# --enable_result_log -# --echo # The statistics looks suspicious. -# --echo # We expect -# --echo # $my_rules -# eval -# SELECT ..... -# } -# -# Rough idea for "Rules" for $my_rules: -# 1. Do not go with too exact values for the diffs between the diffs -# -# n = exact value measured today -# -# COUNT_READ in mysqltest.socket_summary_by_instance_detail -# means rather costs, -# difference between COUNT_READ after and before execution od connect/SQL -# -# diffdiff means differences in costs between two operations. -# Example: First operation = SELECT with length of column alias name = 1 Byte -# Second operation = Same SELECT but with length of column alias name = 11 Byte -# -# Generous for SUM_TIMER_* -# -> SUM_TIMER_READ = 0 -# -> SUM_TIMER_READ > 0 -# -# Fair strict for COUNT_* -# -> COUNT_READ = 0 -# -> COUNT_READ BETWEEN Minimum of(0.9 * n, n - 2) -# AND Maximum_of(1.1 * n, n + 2) -# -> diffdiffs often = 0 -# -# More strict for SUM_NUMBER_OF_BYTES_READ -# -> SUM_NUMBER_OF_BYTES_READ = 0 -# -> SUM_NUMBER_OF_BYTES_READ BETWEEN Minimum of(0.9 * n, n - 2) -# AND Maximum_of(1.1 * n, n + 2) -# -> SUM_NUMBER_OF_BYTES_READ BETWEEN Minimum of(0.9 * n, n - 2) -# -> diffdiffs = ~ diff in lenght of SQL command or diff in -# length of result set -# - -if($print_details) -{ - --enable_query_log - --enable_result_log - --horizontal_results - --echo # Dump detailed differences after - before statement execution - --echo # 1. The statement executing connection and hopefully noone else - - SELECT EVENT_NAME, - COUNT_READ, SUM_NUMBER_OF_BYTES_READ, - COUNT_WRITE, SUM_NUMBER_OF_BYTES_WRITE, - COUNT_MISC, statement - FROM mysqltest.socket_summary_by_instance_detail - WHERE EVENT_NAME LIKE '%client_connection%' - AND OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin - ORDER BY statement, run; - - --echo # 2. The connection default - SELECT EVENT_NAME, - COUNT_READ, SUM_NUMBER_OF_BYTES_READ, - COUNT_WRITE, SUM_NUMBER_OF_BYTES_WRITE, - COUNT_MISC, statement - FROM mysqltest.socket_summary_by_instance_detail - ORDER BY statement,run; - - --echo # 3. The "server_unix_socket" - # WHERE OBJECT_INSTANCE_BEGIN = @con1_object_instance_begin - SELECT EVENT_NAME, - COUNT_READ, SUM_NUMBER_OF_BYTES_READ, - COUNT_WRITE, SUM_NUMBER_OF_BYTES_WRITE, - COUNT_MISC, statement - FROM mysqltest.socket_summary_by_instance_detail - WHERE EVENT_NAME LIKE '%server_unix_socket%' - ORDER BY statement,run; - - --echo # 4. The "server_tcpip_socket" - SELECT EVENT_NAME, - COUNT_READ, SUM_NUMBER_OF_BYTES_READ, - COUNT_WRITE, SUM_NUMBER_OF_BYTES_WRITE, - COUNT_MISC, statement - FROM mysqltest.socket_summary_by_instance_detail - WHERE EVENT_NAME LIKE '%server_tcpip_socket%' - ORDER BY statement,run; -} - -#======================================================================== ---echo # 5. Cleanup - ---disable_query_log -DROP SCHEMA mysqltest; -DROP SCHEMA mysqlsupertest; ---connection con1 ---disconnect con1 ---source include/wait_until_disconnected.inc ---connection default ---enable_query_log - No bundle (reason: useless for push emails).