3385 Christopher Powers 2011-08-19
WL#4896 "Performance Schema Net IO"
New test: socket_summary_by_event_name_func
modified:
mysql-test/suite/perfschema/r/socket_summary_by_instance_func.result*
mysql-test/suite/perfschema/t/socket_summary_by_event_name_func.test
3384 Christopher Powers 2011-08-19
WL#4896 "Performance Schema Net IO"
New test: socket_summary_by_event_name_func.test
added:
mysql-test/suite/perfschema/r/socket_summary_by_event_name_func.result
mysql-test/suite/perfschema/t/socket_summary_by_event_name_func.test
3383 Christopher Powers 2011-08-19
WL#4896 "Performance Schema Net IO"
Final version of socket_summary_by_instance_func.test
modified:
mysql-test/suite/perfschema/include/socket_check1.inc
mysql-test/suite/perfschema/include/socket_event.inc
mysql-test/suite/perfschema/include/socket_summary_check.inc
mysql-test/suite/perfschema/r/socket_summary_by_instance_func.result*
mysql-test/suite/perfschema/t/socket_summary_by_instance_func.test
3382 Marc Alff 2011-08-18 [merge]
Merge mysql-trunk --> mysql-trunk-wl4896
added:
mysql-test/r/multi_plugin_load.result
mysql-test/r/multi_plugin_load_add.result
mysql-test/r/multi_plugin_load_add2.result
mysql-test/t/multi_plugin_load-master.opt
mysql-test/t/multi_plugin_load.test
mysql-test/t/multi_plugin_load_add-master.opt
mysql-test/t/multi_plugin_load_add.test
mysql-test/t/multi_plugin_load_add2-master.opt
mysql-test/t/multi_plugin_load_add2.test
modified:
mysql-test/include/plugin.defs
mysql-test/mysql-test-run.pl
mysql-test/r/mysqld--help-notwin.result
mysql-test/r/mysqld--help-win.result
sql/mysqld.cc
sql/mysqld.h
sql/sql_class.cc
sql/sql_derived.cc
sql/sql_plugin.cc
sql/sql_plugin.h
sql/sql_show.cc
storage/innobase/buf/buf0flu.c
=== modified file 'mysql-test/suite/perfschema/include/socket_check1.inc'
--- a/mysql-test/suite/perfschema/include/socket_check1.inc 2011-08-15 06:26:10 +0000
+++ b/mysql-test/suite/perfschema/include/socket_check1.inc 2011-08-19 06:59:27 +0000
@@ -1,3 +1,21 @@
+# Copyright (c) 2003, 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
+#
+
# Example how to use this auxiliary script
#-----------------------------------------
#
@@ -8,9 +26,9 @@
### $check_num is used for the generation of titles and gets incremented after
### every call of the current script.
# let $check_num= 1;
-### $column_list is used for the generation of error information and valid for
+### $diff_column_list is used for the generation of error information and valid for
### every sub test.
-# let $column_list=
+# let $diff_column_list=
# t2.COUNT_READ - t1.COUNT_READ AS D_COUNT_READ,
# t2.COUNT_READ AS S2_COUNT_READ,
# t1.COUNT_READ AS S1_COUNT_READ,
@@ -40,6 +58,10 @@
#
# let stmt1= SELECT col2 FROM does_not_exist;
# let stmt2= SELECT col2 FROM does_not_exist WHERE col1 = 0;
+### $msg is used to generate some explanation of what we compare.
+# let $msg=
+# # One statement is longer than the other.
+# # Both statements fail with the same error message (table does not exist);
# let $my_rules=
# t2.COUNT_READ - t1.COUNT_READ = 0 AND
# t2.SUM_NUMBER_OF_BYTES_READ - t1.SUM_NUMBER_OF_BYTES_READ = LENGTH('$stmt2') - LENGTH('$stmt1') AND
@@ -59,24 +81,27 @@
--echo # $title_prefix.$check_num Compare impact of statements
--echo # $stmt2
--echo # $stmt1
+--echo $msg
-# MLML: Disable this later
-if (`SELECT CONCAT("$stmt1","$stmt2","$my_rules") LIKE '%_not_set%'`)
+# Enable this when extending the checks for SQL statements.
+if(0)
{
- --echo # INTERNAL ERROR:
- --echo # At least one of the variables has no value (is like '%_not_set')
- --echo # stmt1 : $stmt1
- --echo # stmt2 : $stmt2
- --echo # my_rules : $my_rules
- --echo # Sorry, have to abort
- exit;
+ if (`SELECT CONCAT("$stmt1","$stmt2","$my_rules") LIKE '%_not_set%'`)
+ {
+ --echo # INTERNAL ERROR:
+ --echo # At least one of the variables has no value (is like '%_not_set')
+ --echo # stmt1 : $stmt1
+ --echo # stmt2 : $stmt2
+ --echo # my_rules : $my_rules
+ --echo # Sorry, have to abort
+ exit;
+ }
}
-if(`SELECT ($my_rules) <> 1
+if(`SELECT NOT ( $my_rules )
$part
AND t2.statement = '$stmt2' AND t1.statement = '$stmt1'`)
{
- --disable_abort_on_error
--enable_query_log
--enable_result_log
--echo # The compared statistics looks suspicious
@@ -91,16 +116,19 @@ if(`SELECT ($my_rules) <> 1
--echo
--vertical_results
eval
- SELECT $column_list
+ SELECT $diff_column_list
$part
AND t1.statement = '$stmt1' AND t2.statement = '$stmt2';
--echo
--horizontal_results
eval
- SELECT LPAD(COUNT_READ, 8, ' ') AS CNT_READ, LPAD(SUM_NUMBER_OF_BYTES_READ, 10,' ') AS BYTES_READ,
- LPAD(COUNT_WRITE,9, ' ') AS CNT_WRITE,LPAD(SUM_NUMBER_OF_BYTES_WRITE, 11,' ') AS BYTES_WRITE,
- LPAD(COUNT_MISC, 8, ' ') AS CNT_MISC, statement
+ SELECT
+ LPAD(COUNT_READ, 8, ' ') AS CNT_READ,
+ LPAD(SUM_NUMBER_OF_BYTES_READ, 10,' ') AS BYTES_READ,
+ LPAD(COUNT_WRITE,9, ' ') AS CNT_WRITE,
+ LPAD(SUM_NUMBER_OF_BYTES_WRITE, 11,' ') AS BYTES_WRITE,
+ LPAD(COUNT_MISC, 8, ' ') AS CNT_MISC, statement
FROM mysqltest.socket_summary_by_instance_detail
WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
AND EVENT_NAME LIKE ('%client_connection')
@@ -112,6 +140,7 @@ if(`SELECT ($my_rules) <> 1
let $stmt1= stmt1_not_set;
let $stmt2= stmt2_not_set;
let $my_rules= my_rules_not_set;
+let $msg= msg_not_set;
inc $check_num;
=== modified file 'mysql-test/suite/perfschema/include/socket_event.inc'
--- a/mysql-test/suite/perfschema/include/socket_event.inc 2011-08-15 06:26:10 +0000
+++ b/mysql-test/suite/perfschema/include/socket_event.inc 2011-08-19 06:59:27 +0000
@@ -18,20 +18,23 @@
# include/socket_event.inc
#
-# Auxiliary routine
-# - running some statement in connection con1
-# and checking the changes for the client_connction" entry belonging to con1
-# within socket_summary_by_instance and
-# - checking if the changes to values caused by the statement execution are
-# reasonable and stable
+# Auxiliary routine running
+# - some statement in connection con1
+# or
+# - connect/disconnect
+# $loop_rounds times and checking if the changes to values caused by the action
+# are reasonable.
#
# Requirements:
-# 1. Have socket_summary_by_instance_func running
-# 2. Have a connection con1
-# @con1_object_instance_begin needs to be the OBJECT_INSTANCE_BEGIN
-# value of the "client_connction" entry belonging to con1 within
-# socket_summary_by_instance.
-# 3. $statement needs to contain the statement to be executed by con1.
+# 1. Have socket_summary_by_instance_func running
+# 2a. Have a connection con1
+# @con1_object_instance_begin needs to be the OBJECT_INSTANCE_BEGIN
+# value of the "client_connction" entry belonging to con1 within
+# socket_summary_by_instance.
+# $statement needs to contain the statement to be executed by con1.
+# or
+# 2b. Have assigned values to the following variables
+# $connect_host $connect_db $connect_user
#
let $my_errno= 0;
@@ -51,12 +54,23 @@ eval $insert_before;
#==================
if($is_connect)
{
- let $statement= Connect (con1,$connect_host,$connect_user,,$connect_db,,);
+ let $statement= Connect (con*,$connect_host,$connect_user,,$connect_db,,);
# Some statements fail with ER_ACCESS_DENIED_ERROR
--disable_abort_on_error
- --connect (con1,$connect_host,$connect_user,,$connect_db,,)
+ --connect (con$loop_round,$connect_host,$connect_user,,$connect_db,,)
--enable_abort_on_error
let $my_errno= $mysql_errno;
+ if(!$my_errno)
+ {
+ # Note(mleich):
+ # We are aware that this additional statement is overhead.
+ # But it ensures that SUM_NUMBER_OF_BYTES_READ and
+ # SUM_NUMBER_OF_BYTES_WRITE are updated.
+ # And this avoids the instabilities found when running
+ # the connect without this additional statement.
+ DO 1;
+ }
+ --connection default
}
if(!$is_connect)
{
@@ -71,6 +85,7 @@ if(!$is_connect)
# One of the statements to be checked is expected to fail with ER_NO_SUCH_TABLE.
--disable_abort_on_error
eval $statement;
+ --connection default
--enable_abort_on_error
--disable_query_log
--disable_result_log
@@ -82,24 +97,73 @@ if(!$is_connect)
--connection default
# Variants:
#----------
-# 1. Connect failed ($my_errno <> 0)
+# 1. Connect failed ($my_errno <> 0)
# no entry in performance_schema.threads -> wait_till_sleep.inc cannot be used
-# Just 3 seconds sleep
-# 2. Connect with success ($my_errno = 0)
+# short life entry in socket_summary_by_instance -> wait till it doesn't exist
+# 2. Connect with success ($my_errno = 0)
# entry in performance_schema.threads -> wait_till_sleep.inc can be used
-# Additional 3 second sleep
+# entry in socket_summary_by_instance -> wait till it does exist
# 3. SQL command failed ($my_errno <> 0)
# entry in performance_schema.threads -> wait_till_sleep.inc can be used
if($is_connect)
{
+ let $part=
+ FROM performance_schema.socket_summary_by_instance
+ WHERE EVENT_NAME LIKE '%client_connection'
+ AND OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin;
+
if(!$my_errno)
{
+ # Wait till the new connection is visible in performance_schema.threads
+ # and processlist_command is 'Sleep'.
--source ../include/wait_till_sleep.inc
+
+ # A successful connect causes that a new second row in
+ # performance_schema.socket_summary_by_instance shows up.
+ # Wait till this row is there.
+ let $wait_timeout= 10;
+ let $wait_condition=
+ SELECT COUNT(*) = 1
+ $part;
+ --source include/wait_condition.inc
+ if (!$success)
+ {
+ --echo # Error: We did not reach the expected state where a new
+ --echo # row in socket_summary_by_instance is visible
+ eval
+ SELECT *
+ $part;
+ --echo # abort
+ exit;
+ }
}
if($my_errno)
{
- --sleep 6
+ # Experiments with high parallel load showed that there is a very
+ # period of time where a "client_connection" entry for a failing
+ # Connect is visible.
+ # We hope that sleep 1 is long enough so that PERFORMANCE_SCHEMA
+ # can remove this row before we collect the after action state.
+ let $wait_timeou= 5;
+ let $wait_condition=
+ SELECT COUNT(*) = 0
+ $part;
+ --source include/wait_condition.inc
+ if(!$success)
+ {
+ --echo # Error: We did not reach the expected state.
+ --echo # A failing connect causes a "client_connection" entry
+ --echo # within socket_summary_by_instance having an extreme
+ --echo # short lifetime.
+ --echo # This entry must have now disappeared.
+ eval
+ SELECT *
+ $part;
+ --echo # abort
+ exit;
+ }
}
+ # --sleep 3
}
if(!$is_connect)
{
@@ -138,26 +202,48 @@ if($is_connect)
# Only in case the connect was successful ($my_errno = 0) than we have to disconnect.
if(!$my_errno)
{
- --disconnect con1
+ --disconnect con$loop_round
# Wait till the connection using the DB = 'mysqltest' or
- # 'mysqlsupertest' has disappeared
- let $wait_timeout= 5;
- let $wait_condition=
- SELECT COUNT(*) = 0
+ # 'mysqlsupertest' has disappeared from performance_schema.threads
+ let $part=
FROM performance_schema.threads
WHERE processlist_db IN ('mysqltest','mysqlsupertest');
+ let $wait_timeout= 10;
+ let $wait_condition=
+ SELECT COUNT(*) = 0
+ $part;
--source include/wait_condition.inc
if (!$success)
{
--echo # Error: The disconnect of the connection with processlist_db
--echo # IN ('mysqltest','mysqlsupertest') failed
- SELECT * FROM performance_schema.threads
- WHERE processlist_db IN ('mysqltest','mysqlsupertest');
+ SELECT *
+ $part;
+ --echo # abort
+ exit;
+ }
+ # Wait in addition till the corresponding 'client_connection' entry of
+ # the connection using the DB = 'mysqltest' or 'mysqlsupertest' has disappeared.
+ let $part=
+ FROM performance_schema.socket_summary_by_instance
+ WHERE EVENT_NAME LIKE '%client_connection'
+ AND OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin;
+ let $wait_timeout= 10;
+ let $wait_condition=
+ SELECT COUNT(*) = 0
+ $part;
+ --source include/wait_condition.inc
+ if (!$success)
+ {
+ --echo # Error: The entry of the disconnectd connection with processlist_db
+ --echo # IN ('mysqltest','mysqlsupertest') did not disappear
+ SELECT *
+ $part;
--echo # abort
exit;
}
}
- --sleep 3
+ # --sleep 3
}
inc $loop_round;
=== modified file 'mysql-test/suite/perfschema/include/socket_summary_check.inc'
--- a/mysql-test/suite/perfschema/include/socket_summary_check.inc 2011-08-16 15:12:11 +0000
+++ b/mysql-test/suite/perfschema/include/socket_summary_check.inc 2011-08-19 06:59:27 +0000
@@ -52,7 +52,7 @@ eval $insert_after;
# 1. The content of socket_summary_by_instance must be consistent to the
# content of socket_instances
-#-----------------------------------------------------------------------
+#=======================================================================
let $part1=
FROM performance_schema.socket_summary_by_instance
WHERE (EVENT_NAME,OBJECT_INSTANCE_BEGIN)
@@ -66,8 +66,6 @@ if(`SELECT COUNT(*) $part1`)
eval
SELECT 'not in socket_instances' AS state, EVENT_NAME, OBJECT_INSTANCE_BEGIN
$part1;
- --echo # abort 1
-# exit;
}
--vertical_results
@@ -76,7 +74,7 @@ if(`SELECT COUNT(*) $part1`)
# 2. The computation of statistics must be roughly correct.
#
# If we run this check sufficient frequent than AVG_TIMER_* can be removed from other checks.
-#-----------------------------------------------------------------------------------------------
+#===============================================================================================
let $my_lo= 0.98;
let $my_hi= 1.02;
@@ -87,10 +85,14 @@ COUNT_WRITE * AVG_TIMER_WRITE BETWEEN SU
COUNT_MISC * AVG_TIMER_MISC BETWEEN SUM_TIMER_MISC * $my_lo AND SUM_TIMER_MISC * $my_hi;
let $part=
-SUM_TIMER_WAIT * $my_lo, COUNT_STAR * AVG_TIMER_WAIT, SUM_TIMER_WAIT * $my_hi, COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT,
-SUM_TIMER_READ * $my_lo, COUNT_READ * AVG_TIMER_READ, SUM_TIMER_READ * $my_hi, COUNT_READ, SUM_TIMER_READ, AVG_TIMER_READ,
-SUM_TIMER_WRITE * $my_lo, COUNT_WRITE * AVG_TIMER_WRITE, SUM_TIMER_WRITE * $my_hi, COUNT_WRITE, SUM_TIMER_WRITE, AVG_TIMER_WRITE,
-SUM_TIMER_MISC * $my_lo, COUNT_MISC * AVG_TIMER_MISC, SUM_TIMER_MISC * $my_hi, COUNT_MISC, SUM_TIMER_MISC, AVG_TIMER_MISC;
+SUM_TIMER_WAIT * $my_lo, COUNT_STAR * AVG_TIMER_WAIT, SUM_TIMER_WAIT * $my_hi,
+ COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT,
+SUM_TIMER_READ * $my_lo, COUNT_READ * AVG_TIMER_READ, SUM_TIMER_READ * $my_hi,
+ COUNT_READ, SUM_TIMER_READ, AVG_TIMER_READ,
+SUM_TIMER_WRITE * $my_lo, COUNT_WRITE * AVG_TIMER_WRITE, SUM_TIMER_WRITE * $my_hi,
+ COUNT_WRITE, SUM_TIMER_WRITE, AVG_TIMER_WRITE,
+SUM_TIMER_MISC * $my_lo, COUNT_MISC * AVG_TIMER_MISC, SUM_TIMER_MISC * $my_hi,
+ COUNT_MISC, SUM_TIMER_MISC, AVG_TIMER_MISC;
if(`SELECT SUM($my_rules) <> COUNT(*) FROM mysqltest.my_socket_summary_by_instance
WHERE pk = 'After'`)
@@ -111,10 +113,6 @@ if(`SELECT SUM($my_rules) <> COUNT(*) FR
$part
FROM performance_schema.socket_summary_by_instance
ORDER BY EVENT_NAME, OBJECT_INSTANCE_BEGIN;
- --echo # abort 2
- # MLML? SHOW STATUS LIKE 'Performance_schema_socket_%_lost';
- SHOW STATUS LIKE 'Performance_schema_%_lost';
-# exit;
}
@@ -153,9 +151,6 @@ if(`SELECT SUM($my_rules) <> COUNT(*) FR
FROM mysqltest.my_socket_summary_by_instance
WHERE pk = 'After' AND NOT ($my_rules)
ORDER BY EVENT_NAME, OBJECT_INSTANCE_BEGIN;
-
- --echo # abort 3
-# exit;
}
@@ -189,8 +184,6 @@ if(`SELECT SUM($my_rules) <> COUNT(*) FR
FROM mysqltest.my_socket_summary_by_instance
WHERE pk = 'After'
ORDER BY EVENT_NAME, OBJECT_INSTANCE_BEGIN;
- --echo # abort 4
-# exit;
}
@@ -220,8 +213,6 @@ if(`SELECT SUM($my_rules) <> COUNT(*) FR
FROM mysqltest.my_socket_summary_by_instance
WHERE pk = 'After'
ORDER BY EVENT_NAME, OBJECT_INSTANCE_BEGIN;
- --echo # abort 5
-# exit;
}
@@ -251,8 +242,6 @@ if(`SELECT SUM($my_rules) <> COUNT(*) FR
FROM mysqltest.my_socket_summary_by_instance
WHERE pk = 'After'
ORDER BY EVENT_NAME, OBJECT_INSTANCE_BEGIN;
- --echo # abort 6
-# exit;
}
--horizontal_results
=== added file 'mysql-test/suite/perfschema/r/socket_summary_by_event_name_func.result'
--- a/mysql-test/suite/perfschema/r/socket_summary_by_event_name_func.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema/r/socket_summary_by_event_name_func.result 2011-08-19 07:04:02 +0000
@@ -0,0 +1,121 @@
+#==============================================================================
+# Establish the level of IPV6 support
+#==============================================================================
+#==============================================================================
+# Get hostname, port number
+#==============================================================================
+SELECT @@hostname INTO @MY_HOSTNAME;
+SELECT @@port INTO @MY_MASTER_PORT;
+#==============================================================================
+# 1.0 TEST INITIALIZATION
+#==============================================================================
+
+# 1.1 Disable instrumentation of the default (this) connection
+
+UPDATE performance_schema.threads
+SET INSTRUMENTED='NO' WHERE PROCESSLIST_ID = CONNECTION_ID();
+
+# 1.2 Get the default THREAD_ID;
+
+SELECT THREAD_ID INTO @my_thread_id
+FROM performance_schema.threads
+WHERE PROCESSLIST_ID = CONNECTION_ID();
+
+# 1.3 Get the default OBJECT_INSTANCE_BEGIN
+
+SELECT OBJECT_INSTANCE_BEGIN INTO @my_object_instance_begin
+FROM performance_schema.socket_instances
+WHERE THREAD_ID = @my_thread_id;
+
+# 1.4 Create a test database
+
+CREATE SCHEMA mysqltest;
+
+# 1.5 Create a table to store summary values from socket_summary_by_instance
+and socket_summary_by_event_name
+
+CREATE TABLE mysqltest.my_socket_summary AS
+SELECT * FROM performance_schema.socket_summary_by_instance
+WHERE 1 = 0;
+
+# 1.6 Drop object_instance_begin from my_socket_summary
+
+ALTER TABLE mysqltest.my_socket_summary
+DROP COLUMN OBJECT_INSTANCE_BEGIN;
+
+# 1.7 Add an auto_inc column to my_socket_summary
+
+ALTER TABLE mysqltest.my_socket_summary
+ADD COLUMN (n INT AUTO_INCREMENT, PRIMARY KEY(n));
+
+# 1.8 Create test tables
+
+CREATE TABLE mysqltest.t1 (n INT AUTO_INCREMENT, s1 VARCHAR(1024), PRIMARY KEY(n));
+CREATE TABLE mysqltest.t2 (n INT AUTO_INCREMENT, s1 VARCHAR(1024), PRIMARY KEY(n));
+#==============================================================================
+# 2.0 ESTABLISH CLIENT CONNECTIONS
+#==============================================================================
+
+# 2.1 Connection 1 (tcp/ip, 127.0.0.1 or ::1)
+
+
+# 2.2 Connection 2 (localhost or unix domain socket)
+
+#==============================================================================
+# 3.0 RUN THE TESTS
+#==============================================================================
+
+# 3.1 Clear performance schema tables
+
+TRUNCATE performance_schema.socket_summary_by_instance;
+TRUNCATE performance_schema.socket_summary_by_event_name;
+
+# 3.2 Get the 'before' sum of bytes written from socket_summary_by_instance
+for later comparison to the 'after' byte count as a simple confirmation
+that the table was updated.
+
+SELECT sum(SUM_NUMBER_OF_BYTES_WRITE) INTO @my_write_count
+FROM performance_schema.socket_summary_by_instance;
+
+# 3.3 From connection 1, insert one a 1K row of data into t1
+
+USE mysqltest;
+INSERT INTO t1 (s1) VALUES (REPEAT('a', 1024));
+INSERT INTO t1 (s1) SELECT s1 FROM t1;
+
+# 3.4 From connection 2, insert one a 1K row of data into t2
+
+USE mysqltest;
+INSERT INTO t2 (s1) VALUES (REPEAT('a', 1024));
+INSERT INTO t2 (s1) SELECT s1 FROM t2;
+
+# 3.5 Get the 'after' sum of bytes written from socket_summary_by_instance
+
+SELECT sum(SUM_NUMBER_OF_BYTES_WRITE) INTO @my_write_count
+FROM performance_schema.socket_summary_by_instance;
+
+# 3.6 Verify that SUM_NUMBER_OF_BYTES_WRITE increased
+
+
+# socket_summary_by_instance was updated
+
+#==============================================================================
+# 4.0 VERIFY RESULTS
+#==============================================================================
+
+# 4.1 Verify that the totals in socket_summary_by_event_name are
+consistent with totals in socket_summary_by_instance
+
+#==============================================================================
+# 5.0 Clean up
+#==============================================================================
+
+# 5.1 Disconnect con1
+
+
+# 5.2 Disconnect con2
+
+
+# 5.3 Drop mysqltest
+
+DROP DATABASE mysqltest;
=== modified file 'mysql-test/suite/perfschema/r/socket_summary_by_instance_func.result'
--- a/mysql-test/suite/perfschema/r/socket_summary_by_instance_func.result 2011-08-16 03:57:30 +0000
+++ b/mysql-test/suite/perfschema/r/socket_summary_by_instance_func.result 2011-08-19 07:19:08 +0000
@@ -6,9 +6,17 @@
# (logging enabled)
# - the test might abort
#
-# 0. Build or set prequisites
-# 1. Check the base line
-# 2. Check connect
+# 0. Check, build or set prequisites
+UPDATE performance_schema.threads
+SET INSTRUMENTED='NO' WHERE PROCESSLIST_ID = CONNECTION_ID();
+# 1. Basic checks
+# 1.1 Check that the entry of the disconnected old default session really
+# disappeared from performance_schema.socket_summary_by_instance.
+# 1.2 Check the impact of TRUNCATE on socket_summary_by_instance.
+# It must reset all counters.
+TRUNCATE TABLE performance_schema.socket_summary_by_instance;
+# 1.3 Check the base line
+# 2. Variations on Connect
# 2.1 Connect fails because the user is unknown
# length of user name = 4 character
# length of default db = 9 character
@@ -33,103 +41,162 @@ DROP USER 'root012345'@'localhost';
# length of default db = 9 character
GRANT ALL PRIVILEGES ON *.* to 'root0123456789'@'localhost';
DROP USER 'root0123456789'@'localhost';
-# 3 Check SELECTs
+# 2.7 Connect should pass, host = ::ffff:127.0.0.1
+# length of user name = 4 character
+# length of default db = 9 character
+# connection runs through server_tcpip_socket !
+# 3 Variations on SELECT
# 3.1 Check a SELECT ending with server sending an error message.
# Error message is short (unknown table).
SELECT col2 FROM does_not_exist;
ERROR 42S02: Table 'mysqltest.does_not_exist' doesn't exist
-# 3.2 Check a SELECT ending with server sending an error message.
+# 3.2 SELECT ending with server sending an error message.
# Now the statement is a bit longer but the error message
# length does again not depend on statement.
SELECT col2 FROM does_not_exist WHERE col1 = 0;
ERROR 42S02: Table 'mysqltest.does_not_exist' doesn't exist
-# 3.3 Check a SELECT ending with server sending an error message.
+# 3.3 SELECT ending with server sending an error message.
# The statement has the same length like in 3.2 but the error
# message is now different and much longer.
SELECT col2 FROM does_not_exist WHERE col1 A 0;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'A 0' at line 1
-# 3.4 Check a SELECT ending with server sending an error message.
+# 3.4 SELECT ending with server sending an error message.
# Statement and error message are a bit longer than in 3.1
# because the table name is longer.
SELECT col2 FROM does_not_exist0123;
ERROR 42S02: Table 'mysqltest.does_not_exist0123' doesn't exist
-# 3.5 Check a SELECT earning an empty result set.
-SELECT col2 FROM mysqltest.my_aux WHERE col1 = 0;
+# 3.5 SELECT earning an empty result set.
+SELECT col2 FROM mysqltest.my_aux WHERE col1 = -1;
col2
-# 3.6 Check a SELECT earning an empty result set.
+# 3.6 SELECT earning an empty result set.
# Short column name is replaced by longer alias.
-SELECT col2 AS my_lovely_col FROM mysqltest.my_aux WHERE col1 = 0;
-my_lovely_col
-# 3.7 Check a SELECT earning one row with an empty string.
+SELECT col2 AS my_super_col FROM mysqltest.my_aux WHERE col1 = -1;
+my_super_col
+# 3.7 SELECT earning one row with an empty string.
SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1;
col2
-# 3.8 Check a SELECT earning one row with one string one char long.
+# 3.8 SELECT earning one row with one string one char long.
SELECT col2 FROM mysqltest.my_aux WHERE col1 = 2;
col2
a
-# 3.9 Check a SELECT earning one row with one string 1024 char long.
+# 3.9 SELECT earning one row with one string 1024 char long.
SELECT col2 FROM mysqltest.my_aux WHERE col1 = 3;
col2
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa!
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
-# 3.10 Check a SELECT earning two rows with an empty string
-SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1
-UNION ALL
-SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1;
-col2
-
-
-SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1
-UNION DISTINCT
-SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1;
+# 3.10 SELECT earning two rows with an empty string
+SELECT col2 FROM mysqltest.my_aux WHERE col1 < 2;
col2
-SELECT col2 FROM mysqltest.my_aux WHERE col1 = 3
-UNION ALL
-SELECT col2 FROM mysqltest.my_aux WHERE col1 = 3;
-col2
-aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa!
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
-aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa!
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
-SELECT col2 FROM mysqltest.my_aux WHERE col1 < 3;
-col2
-a
-# 4. Check delta details
+# 3.11 Check that the preceding Connects/SQL command runs have not
+# caused some unexpected state.
+# 4. Check delta (value_after_action - value_before_action) details
# 4.1 Check that
-# - increased COUNT_* leads to increased
-# SUM_TIMER_* and SUM_NUMBER_OF_BYTES_*
# - no change in COUNT_* leads to no change in
-# SUM_TIMER_* and SUM_NUMBER_OF_BYTES_*
+# SUM_TIMER_* and no change in SUM_NUMBER_OF_BYTES_*
+# - increased COUNT_READ leads to increased
+# SUM_TIMER_READ and SUM_NUMBER_OF_BYTES_READ
+# - increased COUNT_WRITE leads to increased
+# SUM_TIMER_READ and SUM_NUMBER_OF_BYTES_READ
+# - increased COUNT_MISC leads to increased
+# SUM_TIMER_MISC
+# Attention: There are exceptions but they are not valid
+# for this test.
# 4.2 Results must be stable
-# Everything except "client_connection" for default connection
-# because the default connection runs include/wait_till_sleep.inc
-# which means a varying amount of statements.
-# 4.3 Check the differences caused by SQL statements
-# 4.3.1 Compare impact of statements
+# 4.3 Counters must be 0 in client_connection for the default session
+# Instrumenting is disabled since a long time and the counter were
+# reset via TRUNCATE just after the disabling.
+# 4.4 Check the differences caused by SQL statements
+# 4.4.1 There must be no changes in counters of instances
+# NOT LIKE '%client_connection' because everything gets charged
+# into client_connection of the acting connection.
+# 4.4.2 In case of SELECT and our scenarios even COUNT_READ and COUNT_MISC
+# are stable.
+# 4.4.3 In our testing scenarios we get for the client_connection entry
+# of the acting connection
+# -> OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
+# COUNT_MISC = 0 AND SUM_TIMER_MISC = 0
+# Check the differences between changes caused by SQL statements
+# These differences must correspond to parameters like
+# - statement, table name or column name length
+# - number of rows in result set, size of rows in result set etc.
+# 4.4.4 Compare impact of statements
# SELECT col2 FROM does_not_exist WHERE col1 = 0
# SELECT col2 FROM does_not_exist
-# 4.3.2 Compare impact of statements
+# One statement is longer than the other.
+# Both statements fail with the same error message (table does not exist)
+# 4.4.5 Compare impact of statements
# SELECT col2 FROM does_not_exist WHERE col1 A 0
# SELECT col2 FROM does_not_exist WHERE col1 = 0
-# 4.3.3 Compare impact of statements
+# Both statements have the same length and fail.
+# The length of the error messages differs.
+# 4.4.6 Compare impact of statements
# SELECT col2 FROM does_not_exist0123
# SELECT col2 FROM does_not_exist
-# 4.3.4 Compare impact of statements
-# SELECT col2 AS my_lovely_col FROM mysqltest.my_aux WHERE col1 = 0
-# SELECT col2 FROM mysqltest.my_aux WHERE col1 = 0
-# 4.3.5 Compare impact of statements
+# Both statements fail (table does not exist).
+# The length of the statement and the length of the error messages differs.
+# Reason for both differences is the length of the table name.
+# 4.4.7 Compare impact of statements
+# SELECT col2 AS my_super_col FROM mysqltest.my_aux WHERE col1 = -1
+# SELECT col2 FROM mysqltest.my_aux WHERE col1 = -1
+# Both statements get an empty result set.
+# The length of the statements and the length of the result sets differs.
+# Reason for both differences is the length of the some column name.
+# 4.4.8 Compare impact of statements
# SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1
-# SELECT col2 FROM mysqltest.my_aux WHERE col1 = 0
-# 4.3.6 Compare impact of statements
+# SELECT col2 FROM mysqltest.my_aux WHERE col1 = -1
+# Both statements differ in the statement length.
+# One statement earns an empty result set.
+# The other statement earns one row containing an empty string.
+# 4.4.9 Compare impact of statements
# SELECT col2 FROM mysqltest.my_aux WHERE col1 = 2
# SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1
-# 4.3.7 Compare impact of statements
+# Both statements have the same length.
+# One statement earns an one row containing an empty string.
+# The other statement earns one row containing a string 1 byte long.
+# 4.4.10 Compare impact of statements
# SELECT col2 FROM mysqltest.my_aux WHERE col1 = 3
# SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1
-# 4.3.2 Failing (->2.1) vs. passing (-> 2.3) connect
-# FIXME: To be implemented after the bug fixes
-# 4.3.3 2.3 vs. 2.4
-# FIXME: To be implemented after the bug fixes
-# 4.4 Check the differences caused by SQL statements
-# FIXME: To be implemented after the bug fixes
+# Both statements have the same length.
+# One statement earns an one row containing an empty string.
+# The other statement earns one row containing a string 1024 byte long.
+# 4.4.11 Compare impact of statements
+# SELECT col2 FROM mysqltest.my_aux WHERE col1 < 2
+# SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1
+# Both statements have the same length.
+# One statement earns an one row containing an empty string.
+# The other statement earns two rows containing an empty string.
+# 4.5 Check the differences caused by Connects
+# Attention: Succesful Connects run an additional "DO 1".
+# 4.5.1 Connects do not charge anything into READ or WRITE counters
+# of the instance with EVENT_NAME NOT LIKE ('%client_connection%').
+# This mean all these counters must be 0.
+# 4.5.2 Connects using for host the value 'localhost'
+# 4.5.2.1 For the instance with EVENT_NAME LIKE '%server_tcpip_socket'
+# COUNT_MISC = 0 AND SUM_TIMER_MISC = 0 must be valid
+# because we run through server_unix_socket.
+# 4.5.2.2 For the instance with EVENT_NAME LIKE '%server_unix_socket'
+# COUNT_MISC > 0 AND SUM_TIMER_MISC > 0 must be valid.
+# 4.5.3 Connects using for host a value <> 'localhost'
+# 4.5.3.1 For the instance with EVENT_NAME LIKE '%server_unix_socket'
+# COUNT_MISC = 0 AND SUM_TIMER_MISC = 0 must be valid
+# because we run through server_tcpip_socket.
+# 4.5.3.2 For the instance with EVENT_NAME LIKE '%server_tcpip_socket'
+# COUNT_MISC > 0 AND SUM_TIMER_MISC > 0 must be valid.
+# 4.5.4 Failing Connects do not cause any row with EVENT_NAME
+# LIKE '%client_connection'
+# 4.5.5 Successful Connects cause a new instance with EVENT_NAME
+# LIKE '%client_connection'
+# 4.6 Check the differences caused by Connects
+# - INSTANCES with an EVENT_NAME like server_tcpip_socket or
+# server_unix_socket are already checked
+# - the stability of results is already checked
+# So we con go with the results of the first run.
+# 4.6.1 The SUM_OF_BYTES_WRITE value is the same for all Connects.
+# 4.6.2 The SUM_OF_BYTES_WRITE value hast to be > 100.
+# 4.6.3 COUNT_READ, COUNT_WRITE and COUNT_MISC have to be to be > 0
+# 4.6.4 Checks based on comparison of results for connects
+# 4.6.4 The user name length affects the SUM_OF_BYTES_READ value
+# 4.6.5 The database name length affects the SUM_OF_BYTES_READ value
# 5. Cleanup
=== added file 'mysql-test/suite/perfschema/t/socket_summary_by_event_name_func.test'
--- a/mysql-test/suite/perfschema/t/socket_summary_by_event_name_func.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema/t/socket_summary_by_event_name_func.test 2011-08-19 07:19:08 +0000
@@ -0,0 +1,349 @@
+# 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_event_name.
+#
+# Created: cpowers 2011-08-18
+#==============================================================================
+#
+# Performance schema table socket_summary_by_event_name holds statistics
+# aggreagated by event name. Table socket_summary_by_instances maintains statistics
+# per socket instance.
+#
+# This test verifies that the statistics in socket_summary_by_event_name
+# are consistent with those in socket_summary_by_instance.
+#
+#==============================================================================
+# Embedded server does not support the performance_schema.
+--source include/not_embedded.inc
+
+# Not much can happen without the performance schema
+--source include/have_perfschema.inc
+
+# Wait for any clients from previous tests to disconnect
+--source ../include/wait_for_pfs_thread_count.inc
+
+# The values in the performance_schema tables depend on how much communication
+# happens per SQL statement within our MTR tests. And there is a significant
+# difference between standard statement execution and execution via
+# prepared statement.
+--source ../include/no_protocol.inc
+
+# Set this to enable debugging output
+let $my_socket_debug=0;
+
+--echo #==============================================================================
+--echo # Establish the level of IPV6 support
+--echo #==============================================================================
+--source ../include/socket_ipv6.inc
+
+--echo #==============================================================================
+--echo # Get hostname, port number
+--echo #==============================================================================
+SELECT @@hostname INTO @MY_HOSTNAME;
+SELECT @@port INTO @MY_MASTER_PORT;
+
+if ($my_socket_debug)
+{
+ SELECT @MY_HOSTNAME AS 'Hostname';
+ SELECT @MY_MASTER_PORT AS 'Master Port';
+ --echo MASTER_MYPORT=$MASTER_MYPORT
+}
+#==============================================================================
+# Utility queries
+#==============================================================================
+let $count_client_connections=
+ SELECT COUNT(*) INTO @my_client_connections
+ FROM performance_schema.socket_instances
+ WHERE EVENT_NAME LIKE "%client_connection%";
+
+let $get_thread_id=
+ SELECT THREAD_ID INTO @my_thread_id
+ FROM performance_schema.threads
+ WHERE PROCESSLIST_ID = CONNECTION_ID();
+
+let $get_object_instance_begin=
+ SELECT OBJECT_INSTANCE_BEGIN INTO @my_object_instance_begin
+ FROM performance_schema.socket_instances
+ WHERE THREAD_ID = @my_thread_id;
+
+let $get_port=
+ SELECT PORT INTO @my_port
+ FROM performance_schema.socket_instances
+ WHERE THREAD_ID = @my_thread_id;
+
+let $get_write_count=
+SELECT sum(SUM_NUMBER_OF_BYTES_WRITE) INTO @my_write_count
+ FROM performance_schema.socket_summary_by_instance;
+#==============================================================================
+# Testcase queries
+#==============================================================================
+let $insert_totals=
+ INSERT INTO mysqltest.my_socket_summary SELECT EVENT_NAME,
+ SUM(t1.COUNT_STAR), SUM(t1.SUM_TIMER_WAIT), SUM(t1.MIN_TIMER_WAIT), SUM(t1.AVG_TIMER_WAIT), SUM(t1.MAX_TIMER_WAIT),
+ SUM(t1.COUNT_READ), SUM(t1.SUM_TIMER_READ), SUM(t1.MIN_TIMER_READ), SUM(t1.AVG_TIMER_READ), SUM(t1.MAX_TIMER_READ), SUM(t1.SUM_NUMBER_OF_BYTES_READ),
+ SUM(t1.COUNT_WRITE), SUM(t1.SUM_TIMER_WRITE), SUM(t1.MIN_TIMER_WRITE), SUM(t1.AVG_TIMER_WRITE), SUM(t1.MAX_TIMER_WRITE), SUM(t1.SUM_NUMBER_OF_BYTES_WRITE),
+ SUM(t1.COUNT_MISC), SUM(t1.SUM_TIMER_MISC), SUM(t1.MIN_TIMER_MISC), SUM(t1.AVG_TIMER_MISC), SUM(t1.MAX_TIMER_MISC), NULL
+ FROM performance_schema.socket_summary_by_instance AS t1
+ WHERE EVENT_NAME LIKE '%client_connection%' GROUP BY EVENT_NAME;
+
+let $compare_tables_and_print=
+SELECT EVENT_NAME,
+ (SUM(t_inst.COUNT_STAR) = t_name.COUNT_STAR) AS CNT_STAR,
+ (SUM(t_inst.SUM_TIMER_WAIT) = t_name.SUM_TIMER_WAIT) AS SUM_WAIT,
+ (MAX(t_inst.MAX_TIMER_WAIT) = t_name.MAX_TIMER_WAIT) AS MAX_WAIT,
+# (MIN(t_inst.MIN_TIMER_WAIT) = t_name.MIN_TIMER_WAIT) AS MIN_WAIT,
+ (SUM(t_inst.COUNT_READ) = t_name.COUNT_READ) AS CNT_READ,
+ (SUM(t_inst.SUM_TIMER_READ) = t_name.SUM_TIMER_READ) AS SUM_READ,
+ (MAX(t_inst.MAX_TIMER_READ) = t_name.MAX_TIMER_READ) AS MAX_READ,
+# (MIN(t_inst.MIN_TIMER_READ) = t_name.MIN_TIMER_READ) AS MIN_READ,
+ (SUM(t_inst.SUM_NUMBER_OF_BYTES_READ) = t_name.SUM_NUMBER_OF_BYTES_READ) AS BYTES_READ,
+ (SUM(t_inst.COUNT_WRITE) = t_name.COUNT_WRITE) AS CNT_WRITE,
+ (SUM(t_inst.SUM_TIMER_WRITE) = t_name.SUM_TIMER_WRITE) AS SUM_WRITE,
+ (MAX(t_inst.MAX_TIMER_WRITE) = t_name.MAX_TIMER_WRITE) AS MAX_WRITE,
+# (MIN(t_inst.MIN_TIMER_WRITE) = t_name.MIN_TIMER_WRITE) AS MIN_WRITE,
+ (SUM(t_inst.SUM_NUMBER_OF_BYTES_WRITE) = t_name.SUM_NUMBER_OF_BYTES_WRITE) AS BYTES_WRITE,
+ (SUM(t_inst.COUNT_MISC) = t_name.COUNT_MISC) AS CNT_MISC,
+ (SUM(t_inst.SUM_TIMER_MISC) = t_name.SUM_TIMER_MISC) AS SUM_MISC
+FROM performance_schema.socket_summary_by_instance t_inst
+JOIN performance_schema.socket_summary_by_event_name t_name
+USING (EVENT_NAME)
+WHERE t_inst.event_name like '%client%'
+ AND t_inst.object_instance_begin <> @default_object_instance_begin;
+
+let $compare_tables_and_verify=
+SELECT (
+ (SUM(t_inst.COUNT_STAR) = t_name.COUNT_STAR) AND
+ (SUM(t_inst.SUM_TIMER_WAIT) = t_name.SUM_TIMER_WAIT) AND
+ (MAX(t_inst.MAX_TIMER_WAIT) = t_name.MAX_TIMER_WAIT) AND
+# (MIN(t_inst.MIN_TIMER_WAIT) = t_name.MIN_TIMER_WAIT) AND
+ (SUM(t_inst.COUNT_READ) = t_name.COUNT_READ) AND
+ (SUM(t_inst.SUM_TIMER_READ) = t_name.SUM_TIMER_READ) AND
+ (MAX(t_inst.MAX_TIMER_READ) = t_name.MAX_TIMER_READ) AND
+# (MIN(t_inst.MIN_TIMER_READ) = t_name.MIN_TIMER_READ) AND
+ (SUM(t_inst.SUM_NUMBER_OF_BYTES_READ) = t_name.SUM_NUMBER_OF_BYTES_READ) AND
+ (SUM(t_inst.COUNT_WRITE) = t_name.COUNT_WRITE) AND
+ (SUM(t_inst.SUM_TIMER_WRITE) = t_name.SUM_TIMER_WRITE) AND
+ (MAX(t_inst.MAX_TIMER_WRITE) = t_name.MAX_TIMER_WRITE) AND
+# (MIN(t_inst.MIN_TIMER_WRITE) = t_name.MIN_TIMER_WRITE) AND
+ (SUM(t_inst.SUM_NUMBER_OF_BYTES_WRITE) = t_name.SUM_NUMBER_OF_BYTES_WRITE) AND
+ (SUM(t_inst.COUNT_MISC) = t_name.COUNT_MISC) AND
+ (SUM(t_inst.SUM_TIMER_MISC) = t_name.SUM_TIMER_MISC) ) = 1 INTO @tables_match
+FROM performance_schema.socket_summary_by_instance t_inst
+JOIN performance_schema.socket_summary_by_event_name t_name
+USING (EVENT_NAME)
+WHERE t_inst.event_name like '%client%'
+ AND t_inst.object_instance_begin <> @default_object_instance_begin;
+
+--echo #==============================================================================
+--echo # 1.0 TEST INITIALIZATION
+--echo #==============================================================================
+--echo
+--echo # 1.1 Disable instrumentation of the default (this) connection
+--echo
+--connection default
+UPDATE performance_schema.threads
+ SET INSTRUMENTED='NO' WHERE PROCESSLIST_ID = CONNECTION_ID();
+
+--echo
+--echo # 1.2 Get the default THREAD_ID;
+--echo
+eval $get_thread_id;
+let $default_thread_id= `SELECT @my_thread_id`;
+
+--echo
+--echo # 1.3 Get the default OBJECT_INSTANCE_BEGIN
+--echo
+eval $get_object_instance_begin;
+let $default_object_instance_begin= `SELECT @my_object_instance_begin`;
+
+--disable_query_log
+SELECT @my_object_instance_begin INTO @default_object_instance_begin;
+--enable_query_log
+
+if ($my_socket_debug)
+{
+ --echo # Default object instance begin = $default_object_instance_begin
+ --echo # Default thread id = $default_thread_id
+}
+
+--echo
+--echo # 1.4 Create a test database
+--echo
+CREATE SCHEMA mysqltest;
+
+--echo
+--echo # 1.5 Create a table to store summary values from socket_summary_by_instance
+--echo and socket_summary_by_event_name
+--echo
+CREATE TABLE mysqltest.my_socket_summary AS
+ SELECT * FROM performance_schema.socket_summary_by_instance
+ WHERE 1 = 0;
+
+--echo
+--echo # 1.6 Drop object_instance_begin from my_socket_summary
+--echo
+ALTER TABLE mysqltest.my_socket_summary
+ DROP COLUMN OBJECT_INSTANCE_BEGIN;
+
+--echo
+--echo # 1.7 Add an auto_inc column to my_socket_summary
+--echo
+ALTER TABLE mysqltest.my_socket_summary
+ ADD COLUMN (n INT AUTO_INCREMENT, PRIMARY KEY(n));
+
+--echo
+--echo # 1.8 Create test tables
+--echo
+CREATE TABLE mysqltest.t1 (n INT AUTO_INCREMENT, s1 VARCHAR(1024), PRIMARY KEY(n));
+CREATE TABLE mysqltest.t2 (n INT AUTO_INCREMENT, s1 VARCHAR(1024), PRIMARY KEY(n));
+
+--echo #==============================================================================
+--echo # 2.0 ESTABLISH CLIENT CONNECTIONS
+--echo #==============================================================================
+--echo
+--echo # 2.1 Connection 1 (tcp/ip, 127.0.0.1 or ::1)
+--echo
+--disable_query_log
+--connect (con1,$my_localhost,root,,,,$MASTER_MYPORT)
+--enable_query_log
+
+--echo
+--echo # 2.2 Connection 2 (localhost or unix domain socket)
+--echo
+--disable_query_log
+--connect (con2,localhost,root,,,,)
+--enable_query_log
+
+--echo #==============================================================================
+--echo # 3.0 RUN THE TESTS
+--echo #==============================================================================
+--echo
+--echo # 3.1 Clear performance schema tables
+--echo
+TRUNCATE performance_schema.socket_summary_by_instance;
+TRUNCATE performance_schema.socket_summary_by_event_name;
+
+--echo
+--echo # 3.2 Get the 'before' sum of bytes written from socket_summary_by_instance
+--echo for later comparison to the 'after' byte count as a simple confirmation
+--echo that the table was updated.
+--echo
+--connection default
+eval $get_write_count;
+let $my_count_before= `SELECT @my_write_count`;
+
+--echo
+--echo # 3.3 From connection 1, insert one a 1K row of data into t1
+--echo
+--connection con1
+USE mysqltest;
+INSERT INTO t1 (s1) VALUES (REPEAT('a', 1024));
+INSERT INTO t1 (s1) SELECT s1 FROM t1;
+
+--echo
+--echo # 3.4 From connection 2, insert one a 1K row of data into t2
+--echo
+--connection con2
+USE mysqltest;
+INSERT INTO t2 (s1) VALUES (REPEAT('a', 1024));
+INSERT INTO t2 (s1) SELECT s1 FROM t2;
+
+--echo
+--echo # 3.5 Get the 'after' sum of bytes written from socket_summary_by_instance
+--echo
+--connection default
+eval $get_write_count;
+let $my_count_after= `SELECT @my_write_count`;
+
+--echo
+--echo # 3.6 Verify that SUM_NUMBER_OF_BYTES_WRITE increased
+--echo
+
+if ($my_count_before >= $my_count_after)
+{
+ --echo
+ --echo # ERROR: Write count did not increaase
+ --echo # Before = $my_count_before
+ --echo # After = $my_count_after
+ --echo
+ SELECT * FROM performance_schema.socket_summary_by_instance ORDER BY EVENT_NAME;
+ --echo
+ --echo # Abort
+ exit;
+}
+
+--echo
+--echo # socket_summary_by_instance was updated
+--echo
+
+--echo #==============================================================================
+--echo # 4.0 VERIFY RESULTS
+--echo #==============================================================================
+--echo
+--echo # 4.1 Verify that the totals in socket_summary_by_event_name are
+--echo consistent with totals in socket_summary_by_instance
+--echo
+
+--disable_query_log
+eval $compare_tables_and_verify;
+
+if (`SELECT @tables_match = 0`)
+{
+ --echo
+ --echo # ERROR: Socket summary tables do not match
+ --echo # Column comparison results:
+ --echo
+ eval $compare_tables_and_print;
+ --enable_query_log
+ --echo
+ SELECT * from performance_schema.socket_summary_by_instance ORDER BY EVENT_NAME;
+ --echo
+ SELECT * from performance_schema.socket_summary_by_event_name ORDER BY EVENT_NAME;
+ --echo
+ --echo # Abort
+ exit;
+}
+--enable_query_log
+
+--echo #==============================================================================
+--echo # 5.0 Clean up
+--echo #==============================================================================
+
+--echo
+--echo # 5.1 Disconnect con1
+--echo
+--connection con1
+--disconnect con1
+--source include/wait_until_disconnected.inc
+
+--echo
+--echo # 5.2 Disconnect con2
+--echo
+--connection con2
+--disconnect con2
+--source include/wait_until_disconnected.inc
+
+--echo
+--echo # 5.3 Drop mysqltest
+--echo
+--connection default
+DROP DATABASE mysqltest;
+
+exit;
+
=== modified file 'mysql-test/suite/perfschema/t/socket_summary_by_instance_func.test'
--- a/mysql-test/suite/perfschema/t/socket_summary_by_instance_func.test 2011-08-17 20:29:21 +0000
+++ b/mysql-test/suite/perfschema/t/socket_summary_by_instance_func.test 2011-08-19 06:59:27 +0000
@@ -15,13 +15,46 @@
# 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
+# socket_summary_by_instance.
#
# Created: mleich 2011-07-01
#
+# Rough description of "What is when tested"
+# 1. Impact of successful connect
+# A new row with EVENT_NAME "client_connection" shows up
+# is tested in 0 and 4.5.
+# 2. Impact of disconnect
+# A row with EVENT_NAME "client_connection" disappears up
+# is tested in 0. and 1.2.
+# 3. Disabling the instrumentation for some thread causes that the
+# counter for this thread become static is tested in 4.3.
+# Nearby the beginning of this test and somewhere in the middle.
+# 4. TRUNCATE table resets the counter is tested nearby the beginning.
+# 5. Consistency within a row like MIN_* <= AVG_* <= MAX_*
+# -> include/socket_summary_check.inc which is called at
+# various places
+# 6. Consistency of the instances mentioned in socket_summary_by_event
+# and socket_summary_by_instance is checked per call of
+# include/socket_summary_check.inc
+# 7. Check if changes in counters of instances caused by actions
+# are reasonable is tested for
+# - Connects+SQL statements in 4.1 and 4.2
+# - SQL statements in 4.4
+# - Connects in 4.5
+#
+# Embedded server does not supprt the performance_schema.
--source include/not_embedded.inc
+--source include/not_windows.inc
+
+# The values in the performance_schema tables depend on how much communication
+# happens per SQL statement within our MTR tests. And there is a significant
+# difference between standard statement execution and execution via
+# prepared statement.
+--source ../include/no_protocol.inc
+
#===================================
# Set IP address defaults with respect to IPV6 support
@@ -68,8 +101,8 @@ if($my_socket_debug)
--disable_query_log
---echo # 0. Build or set prequisites
-#===================================
+--echo # 0. Check, 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:
@@ -81,11 +114,80 @@ if($my_socket_debug)
let $print_details= 0;
#
-# Number of attempts within the test checking the stability of counter increments.
+# Number of attempts within the test checking the stability of counter changes.
#
-# let $loop_rounds= 10;
-let $loop_rounds= 1;
+let $loop_rounds= 10;
+# This test of PERFORMANCE_SCHEMA functionality is very vulnerable.
+# Ensure that we have in the moment exact one
+# - connection
+# - instance with EVENT_NAME LIKE '%client_connection'
+# - instance with EVENT_NAME LIKE '%server_tcpip_socket'
+# - instance with EVENT_NAME LIKE '%server_unix_socket'
+#=======================================================
+let $my_rules= COUNT(*) = 1;
+let $part=
+FROM performance_schema.threads
+WHERE NAME LIKE '%one_connection';
+let $wait_condition=
+SELECT $my_rules $part;
+let $wait_timeout= 5;
+--source include/wait_condition.inc
+if(!$success)
+{
+ --echo # ERROR: There must be only one user connection
+ eval
+ SELECT * $part;
+ --echo # abort
+ exit;
+}
+let $part=
+FROM performance_schema.socket_summary_by_instance
+WHERE EVENT_NAME LIKE '%client_connection';
+let $wait_condition=
+SELECT $my_rules $part;
+let $wait_timeout= 5;
+--source include/wait_condition.inc
+if(!$success)
+{
+ --echo # ERROR: There must be only one instance with this EVENT_NAME.
+ eval
+ SELECT * $part;
+ --echo # abort
+ exit;
+}
+let $part=
+FROM performance_schema.socket_summary_by_instance
+WHERE EVENT_NAME LIKE '%server_unix_socket';
+let $wait_condition=
+SELECT $my_rules $part;
+let $wait_timeout= 5;
+--source include/wait_condition.inc
+if(!$success)
+{
+ --echo # ERROR: There must be only one instance with this EVENT_NAME.
+ eval
+ SELECT * $part;
+ --echo # abort
+ exit;
+}
+let $part=
+FROM performance_schema.socket_summary_by_instance
+WHERE EVENT_NAME LIKE '%server_tcpip_socket';
+let $wait_condition=
+SELECT $my_rules $part;
+let $wait_timeout= 5;
+--source include/wait_condition.inc
+if(!$success)
+{
+ --echo # ERROR: There must be only one instance with this EVENT_NAME.
+ eval
+ SELECT * $part;
+ --echo # abort
+ exit;
+}
+
+--disable_query_log
#
# Additional SCHEMA used for
# - detection of our "worker" session within the PROCESSLIST.
@@ -96,18 +198,14 @@ CREATE SCHEMA mysqltest;
CREATE SCHEMA mysqlsupertest;
#
-# Disable the instrumentation for connection default.
-# This connection observes the impact of the acting connections
-# and should not have an impact on statistics.
-#
-# UPDATE performance_schema.threads
-# SET INSTRUMENTED='NO' WHERE PROCESSLIST_ID = CONNECTION_ID();
-
-#
# Clear summary tables of previous entries
#
TRUNCATE performance_schema.socket_summary_by_instance;
TRUNCATE performance_schema.socket_summary_by_event_name;
+# Disable instrumenting of the current default session.
+# Required for check 1.1
+UPDATE performance_schema.threads
+SET INSTRUMENTED='NO' WHERE PROCESSLIST_ID = CONNECTION_ID();
#
# Auxiliary tables for storing current values at certain points of time.
@@ -122,14 +220,24 @@ CREATE TABLE mysqltest.my_socket_summary
SELECT *, 'Pseudo_Before' AS pk FROM performance_schema.socket_summary_by_instance
WHERE 1 = 0;
+# The CAST(... AS DECIMAL) prevents errors which might show up in case
+# we run with the original data type UNSIGNED BIGINT.
+# Example:
+# - A and B UNSIGNED BIGINT
+# - A < B
+# - A - B ---> Error
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
+ CAST(COUNT_READ AS DECIMAL) AS COUNT_READ,
+ CAST(SUM_TIMER_READ AS DECIMAL) AS SUM_TIMER_READ,
+ CAST(SUM_NUMBER_OF_BYTES_READ AS DECIMAL) SUM_NUMBER_OF_BYTES_READ,
+ CAST(COUNT_WRITE AS DECIMAL) AS COUNT_WRITE,
+ CAST(SUM_TIMER_WRITE AS DECIMAL) AS SUM_TIMER_WRITE,
+ CAST(SUM_NUMBER_OF_BYTES_WRITE AS DECIMAL) AS SUM_NUMBER_OF_BYTES_WRITE,
+ CAST(COUNT_MISC AS DECIMAL) AS COUNT_MISC,
+ CAST(SUM_TIMER_MISC AS DECIMAL) AS SUM_TIMER_MISC
FROM performance_schema.socket_summary_by_instance
WHERE 1 = 2;
-
ALTER TABLE mysqltest.socket_summary_by_instance_detail
ADD statement VARCHAR(500), ADD run INTEGER;
@@ -140,15 +248,16 @@ 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 = 0, col2 = REPEAT('a',0);
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);
@@ -220,15 +329,21 @@ 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');
-#
+
+# Use this whenever you print data.
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 $counter_column_list=
-COUNT_READ, SUM_NUMBER_OF_BYTES_READ,
-COUNT_WRITE, SUM_NUMBER_OF_BYTES_WRITE,
-COUNT_MISC, Statement;
+RPAD(EVENT_NAME, 38, ' ') AS 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;
# Determine OBJECT_INSTANCE_BEGIN of the connection default
# which acts as the observer
@@ -241,34 +356,126 @@ eval $insert_before;
eval $insert_after;
eval $get_object_instance_begin;
SET @default_object_instance_begin = @con1_object_instance_begin;
-# SELECT @default_object_instance_begin;
-# SELECT * FROM performance_schema.threads;
-# SELECT * FROM performance_schema.socket_summary_by_instance
-# WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin;
+
+# Wait till the old default connection has disappeared
+let $wait_timeout= 5;
+let $wait_condition=
+SELECT COUNT(*) = 1
+FROM performance_schema.threads
+WHERE processlist_user = 'root';
+--source include/wait_condition.inc
+if (!$success)
+{
+ --enable_query_log
+ --enable_result_log
+ --echo # Error: The disconnect of the old default connection
+ --echo # (user = 'root') failed. We expect to have only one connection
+ --echo # with user = 'root'. And this is our current connection.
+ SELECT * FROM performance_schema.threads
+ WHERE processlist_user = 'root';
+ --echo # abort
+ exit;
+}
+
+# Disable instrumenting of the current default session.
+# Required for check 1.2
UPDATE performance_schema.threads
SET INSTRUMENTED='NO' WHERE PROCESSLIST_ID = CONNECTION_ID();
+
+--echo # 1. Basic checks
+--echo # 1.1 Check that the entry of the disconnected old default session really
+--echo # disappeared from performance_schema.socket_summary_by_instance.
+#===============================================================================
+# This failed at some point in history when the instrumenting for the
+# session to be disconnected was disabled.
+if(`SELECT COUNT(*) FROM performance_schema.socket_summary_by_instance
+WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
+ AND EVENT_NAME LIKE '%client_connection'`)
+{
+ --enable_query_log
+ --enable_result_log
+ --echo # Error: The disconnected old default session did not disappear from
+ --echo # socket_summary_by_instance.
+ SELECT * FROM performance_schema.socket_summary_by_instance
+ WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
+ AND EVENT_NAME LIKE '%client_connection';
+}
+
+--echo # 1.2 Check the impact of TRUNCATE on socket_summary_by_instance.
+--echo # It must reset all counters.
+#=======================================================================
+# We did a Connect and executed some statements within our new default session.
+# This specific history must have caused that none of the counters within
+# the client_connection for this session is 0.
+#
+# C_STAR TIMER_WAIT C_READ TIMER_READ BYTES_READ C_WRITE TIMER_WRITE BYTES_WRITE C_MISC TIMER_MISC
+# 26 98238140 15 14874860 645 5 47217170 178 6 36146110
+#
+# Ensure that the counters are <> 0 so that TRUNCATE has a visible effect.
+let $my_rules=
+COUNT_STAR > 0 AND SUM_TIMER_WAIT > 0
+AND
+COUNT_READ > 0 AND SUM_TIMER_READ > 0 AND SUM_NUMBER_OF_BYTES_READ > 0
+AND
+COUNT_WRITE > 0 AND SUM_TIMER_WRITE > 0 AND SUM_NUMBER_OF_BYTES_WRITE > 0
+AND
+COUNT_MISC > 0 AND SUM_TIMER_MISC > 0;
+if(`SELECT COUNT(*) FROM performance_schema.socket_summary_by_instance
+ WHERE NOT ( $my_rules )
+ AND OBJECT_INSTANCE_BEGIN = @default_object_instance_begin`)
+{
+ --enable_query_log
+ --enable_result_log
+ --echo # The statistics looks suspicious.
+ --echo # We expect
+ --echo # $my_rules
+ --echo #
+ eval
+ SELECT
+ COUNT_STAR, SUM_TIMER_WAIT,
+ 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 OBJECT_INSTANCE_BEGIN = @default_object_instance_begin;
+}
TRUNCATE TABLE performance_schema.socket_summary_by_instance;
-TRUNCATE TABLE performance_schema.socket_summary_by_event_name;
-# TRUNCATE TABLE performance_schema.socket_summary_by_instance;
-# TRUNCATE TABLE performance_schema.socket_summary_by_event_name;
-# sleep 3;
-# SELECT * FROM performance_schema.socket_summary_by_instance
-# WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin;
-# SELECT * FROM performance_schema.socket_summary_by_instance;
-# SELECT * FROM mysqltest.socket_summary_by_instance_detail;
-# SELECT * FROM information_schema.processlist;
-# SELECT * FROM performance_schema.threads;
-# exit;
---disable_query_log
+let $my_rules=
+COUNT_STAR = 0 AND SUM_TIMER_WAIT = 0
+AND
+COUNT_READ = 0 AND SUM_TIMER_READ = 0 AND SUM_NUMBER_OF_BYTES_READ = 0
+AND
+COUNT_WRITE = 0 AND SUM_TIMER_WRITE = 0 AND SUM_NUMBER_OF_BYTES_WRITE = 0
+AND
+COUNT_MISC = 0 AND SUM_TIMER_MISC = 0;
+if(`SELECT COUNT(*) FROM performance_schema.socket_summary_by_instance
+ WHERE NOT ( $my_rules )
+ AND OBJECT_INSTANCE_BEGIN = @default_object_instance_begin`)
+{
+ --enable_query_log
+ --enable_result_log
+ --echo # The statistics looks suspicious.
+ --echo # We expect
+ --echo # $my_rules
+ --echo #
+ eval
+ SELECT
+ COUNT_STAR, SUM_TIMER_WAIT,
+ 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 OBJECT_INSTANCE_BEGIN = @default_object_instance_begin;
+}
---echo # 1. Check the base line
-#==============================
+--echo # 1.3 Check the base line
+#===============================
--source ../include/socket_summary_check.inc
---disable_query_log
---disable_query_log
+# --disable_query_log
+# --disable_query_log
---echo # 2. Check connect
+--echo # 2. Variations on Connect
let $is_connect= 1;
--echo # 2.1 Connect fails because the user is unknown
--echo # length of user name = 4 character
@@ -328,16 +535,28 @@ let $connect_user= root0123456789;
--source ../include/socket_event.inc
DROP USER 'root0123456789'@'localhost';
+--echo # 2.7 Connect should pass, host = $my_localhost
+--echo # length of user name = 4 character
+--echo # length of default db = 9 character
+--echo # connection runs through server_tcpip_socket !
#========================================================================
---connect (con1,$my_localhost,root,,mysqltest,,)
+let $connect_host= $my_localhost;
+let $connect_db= mysqltest;
+let $connect_user= root;
+--source ../include/socket_event.inc
+
+#========================================================================
+--connect (con1,localhost,root,,mysqltest,,)
--connection default
--source ../include/wait_till_sleep.inc
--disable_query_log
-# MLML: Experiment
-let $loop_rounds= 10;
---echo # 3 Check SELECTs
-# Attention: Don't use any double quotes within the statements.
+--echo # 3 Variations on SELECT
+# Attention: Don't use
+# - any double quotes within the statements because sourced scripts
+# already "decorate" $variables with double quotes
+# - UNION because this leads to result set related byte write
+# counters which cannot good compared to select without union
let $is_connect= 0;
eval $get_object_instance_begin;
--echo # 3.1 Check a SELECT ending with server sending an error message.
@@ -346,108 +565,138 @@ eval $get_object_instance_begin;
let $statement= SELECT col2 FROM does_not_exist;
--source ../include/socket_event.inc
---echo # 3.2 Check a SELECT ending with server sending an error message.
+--echo # 3.2 SELECT ending with server sending an error message.
--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.inc
---echo # 3.3 Check a SELECT ending with server sending an error message.
+--echo # 3.3 SELECT ending with server sending an error message.
--echo # The statement has the same length like in 3.2 but the error
--echo # message is now different and much longer.
#=======================================================================
let $statement= SELECT col2 FROM does_not_exist WHERE col1 A 0;
--source ../include/socket_event.inc
---echo # 3.4 Check a SELECT ending with server sending an error message.
+--echo # 3.4 SELECT ending with server sending an error message.
--echo # Statement and error message are a bit longer than in 3.1
--echo # because the table name is longer.
#=======================================================================
let $statement= SELECT col2 FROM does_not_exist0123;
--source ../include/socket_event.inc
---echo # 3.5 Check a SELECT earning an empty result set.
+--echo # 3.5 SELECT earning an empty result set.
#=======================================================
-let $statement= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 0;
+let $statement= SELECT col2 FROM mysqltest.my_aux WHERE col1 = -1;
--source ../include/socket_event.inc
---echo # 3.6 Check a SELECT earning an empty result set.
+--echo # 3.6 SELECT earning an empty result set.
--echo # Short column name is replaced by longer alias.
#==========================================================
-let $statement= SELECT col2 AS my_lovely_col FROM mysqltest.my_aux WHERE col1 = 0;
+let $statement= SELECT col2 AS my_super_col FROM mysqltest.my_aux WHERE col1 = -1;
--source ../include/socket_event.inc
---echo # 3.7 Check a SELECT earning one row with an empty string.
+--echo # 3.7 SELECT earning one row with an empty string.
#================================================================
let $statement= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1;
--source ../include/socket_event.inc
---echo # 3.8 Check a SELECT earning one row with one string one char long.
+--echo # 3.8 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.inc
---echo # 3.9 Check a SELECT earning one row with one string 1024 char long.
+--echo # 3.9 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.inc
---echo # 3.10 Check a SELECT earning two rows with an empty string
+--echo # 3.10 SELECT earning two rows with an empty string
#==========================================================================
-let $statement= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1
-UNION ALL
-SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1;
---source ../include/socket_event.inc
-
-let $statement= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1
-UNION DISTINCT
-SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1;
+let $statement= SELECT col2 FROM mysqltest.my_aux WHERE col1 < 2;
--source ../include/socket_event.inc
-let $statement= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 3
-UNION ALL
-SELECT col2 FROM mysqltest.my_aux WHERE col1 = 3;
---source ../include/socket_event.inc
-
-let $statement= SELECT col2 FROM mysqltest.my_aux WHERE col1 < 3;
---source ../include/socket_event.inc
-
---echo # 4. Check delta details
+--echo # 3.11 Check that the preceding Connects/SQL command runs have not
+--echo # caused some unexpected state.
#==========================================================================
-# 4.0 0. Negative deltas cannot have happened because the counter columns within
+let $my_rules= COUNT(*) = 2;
+let $part=
+FROM performance_schema.threads
+WHERE NAME LIKE '%one_connection';
+if(`SELECT NOT ( $my_rules ) $part `)
+{
+ --echo # ERROR: There must be only two user connections
+ eval
+ SELECT * $part;
+ --echo # abort
+ exit;
+}
+let $part=
+FROM performance_schema.socket_summary_by_instance
+WHERE EVENT_NAME LIKE '%client_connection';
+if(`SELECT NOT ( $my_rules ) $part `)
+{
+ --echo # ERROR: There must be only two instances with this EVENT_NAME.
+ eval
+ SELECT * $part;
+ --echo # abort
+ exit;
+}
+let $my_rules= COUNT(*) = 1;
+let $part=
+FROM performance_schema.socket_summary_by_instance
+WHERE EVENT_NAME LIKE '%server_unix_socket';
+if(`SELECT NOT ( $my_rules ) $part `)
+{
+ --echo # ERROR: There must be only one instance with this EVENT_NAME.
+ eval
+ SELECT * $part;
+ --echo # abort
+ exit;
+}
+let $part=
+FROM performance_schema.socket_summary_by_instance
+WHERE EVENT_NAME LIKE '%server_tcpip_socket';
+if(`SELECT NOT ( $my_rules ) $part `)
+{
+ --echo # ERROR: There must be only one instance with this EVENT_NAME.
+ eval
+ SELECT * $part;
+ --echo # abort
+ exit;
+}
+
+--echo # 4. Check delta (value_after_action - value_before_action) details
+# 4.0 . Negative deltas cannot have happened because the counter columns within
# socket_summary_by_instance_detail are defined as UNSIGNED BIGINT.
# = The INSERT which computes the diff would have been failed.
--echo # 4.1 Check that
---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_*
-
+--echo # SUM_TIMER_* and no change in SUM_NUMBER_OF_BYTES_*
+--echo # - increased COUNT_READ leads to increased
+--echo # SUM_TIMER_READ and SUM_NUMBER_OF_BYTES_READ
+--echo # - increased COUNT_WRITE leads to increased
+--echo # SUM_TIMER_READ and SUM_NUMBER_OF_BYTES_READ
+--echo # - increased COUNT_MISC leads to increased
+--echo # SUM_TIMER_MISC
+--echo # Attention: There are exceptions but they are not valid
+--echo # for this test.
+#==========================================================================
let $my_rules=
-((COUNT_READ = 0 AND SUM_TIMER_READ = 0 AND SUM_NUMBER_OF_BYTES_READ = 0)
+((COUNT_READ = 0 AND SUM_TIMER_READ = 0 AND SUM_NUMBER_OF_BYTES_READ = 0)
OR
- (COUNT_READ > 0 AND SUM_TIMER_READ > 0 AND SUM_NUMBER_OF_BYTES_READ > 0)
- OR
- (COUNT_READ > 0 AND SUM_TIMER_READ = 0 AND SUM_NUMBER_OF_BYTES_READ > 0))
+ (COUNT_READ > 0 AND SUM_TIMER_READ > 0 AND SUM_NUMBER_OF_BYTES_READ > 0))
AND
((COUNT_WRITE = 0 AND SUM_TIMER_WRITE = 0 AND SUM_NUMBER_OF_BYTES_WRITE = 0)
OR
(COUNT_WRITE > 0 AND SUM_TIMER_WRITE > 0 AND SUM_NUMBER_OF_BYTES_WRITE > 0))
AND
-((COUNT_MISC = 0 AND SUM_TIMER_MISC = 0)
- OR
- (COUNT_MISC > 0 AND SUM_TIMER_MISC > 0)
+((COUNT_MISC = 0 AND SUM_TIMER_MISC = 0)
OR
- (COUNT_MISC > 0 AND SUM_TIMER_MISC = 0));
-
-# In case of SQL statements and the instance for the connection which runs the
-# statement COUNT_MISC is 1 AND SUM_TIMER_MISC is 0 !!
-# MLML: Ask Chris, if this is really correct.
-
+(COUNT_MISC > 0 AND SUM_TIMER_MISC > 0));
if(`SELECT COUNT(*) FROM mysqltest.socket_summary_by_instance_detail
- WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
- AND NOT ( $my_rules )`)
+ WHERE NOT ( $my_rules ) `)
{
--enable_query_log
--enable_result_log
@@ -456,46 +705,44 @@ if(`SELECT COUNT(*) FROM mysqltest.socke
--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
+ SELECT
+ $column_list
FROM mysqltest.socket_summary_by_instance_detail
WHERE NOT ( $my_rules )
- ORDER BY EVENT_NAME, OBJECT_INSTANCE_BEGIN;
+ ORDER BY EVENT_NAME, OBJECT_INSTANCE, STATEMENT, RUN;
}
--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.
#==========================================================================
-# This check might fail after the bug fixes
# Enable the following lines for debugging the check
-# UPDATE mysqltest.socket_summary_by_instance_detail
-# SET COUNT_READ = 13
-# WHERE statement LIKE 'Connect%abc%'
-# AND OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
-# LIMIT 1;
-# UPDATE mysqltest.socket_summary_by_instance_detail
-# SET COUNT_WRITE = 13
-# WHERE statement LIKE '%WHERE col1 = 3'
-# AND OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
-# AND EVENT_NAME LIKE '%client_connection'
-# LIMIT 1;
+if(0)
+{
+ --enable_info
+ UPDATE mysqltest.socket_summary_by_instance_detail
+ SET COUNT_WRITE = 13
+ WHERE statement LIKE '%WHERE col1 = 3'
+ AND OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
+ AND EVENT_NAME LIKE '%client_connection'
+ LIMIT 1;
+ --disable_info
+}
-# MLML: IMHO the most reasonable rule
+# eval
+# SELECT
+# $column_list
+# FROM mysqltest.socket_summary_by_instance_detail
+# WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
+# ORDER BY EVENT_NAME, statement,run;
+
+# In case we are able to wait all time till perfschema has finished the
+# maintenance of counters than the following must be valid.
let $my_rules=
-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;
+COUNT(DISTINCT SUM_NUMBER_OF_BYTES_WRITE) = 1;
-# Comment(ML):
-# In case we do not get the results somehow deterministic than we
-# we should go with the less strict check based on CV.
+# In case we do not get the results somehow deterministic than we
+# we should go with the less strict check based on CV.
#
# Compute coefficient of variation (CV) to detect 'notable' variances in the
# byte count and operation counts. The acceptable range for the CV is purely
@@ -512,8 +759,7 @@ 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;
-
+HAVING NOT ($my_rules) ;
if(`SELECT COUNT(statement) $part`)
{
--enable_query_log
@@ -522,53 +768,138 @@ if(`SELECT COUNT(statement) $part`)
--echo # We expect
--echo # $my_rules
--echo # for GROUP BY EVENT_NAME, statement
- select @default_object_instance_begin as 'Default Object Instance';
- # eval
- # SELECT EVENT_NAME, statement
- # $part
- # ORDER BY EVENT_NAME, statement;
+ eval
+ SELECT $column_list
+ FROM mysqltest.socket_summary_by_instance_detail
+ WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
+ AND (EVENT_NAME, statement)
+ IN (SELECT EVENT_NAME, statement
+ $part)
+ ORDER BY EVENT_NAME, statement, run, OBJECT_INSTANCE_BEGIN;
+}
- --echo MLML: Experiment
+--echo # 4.3 Counters must be 0 in client_connection for the default session
+--echo # Instrumenting is disabled since a long time and the counter were
+--echo # reset via TRUNCATE just after the disabling.
+#==========================================================================
+let $my_rules=
+COUNT_STAR = 0 AND SUM_TIMER_WAIT = 0
+AND
+COUNT_READ = 0 AND SUM_TIMER_READ = 0 AND SUM_NUMBER_OF_BYTES_READ = 0
+AND
+COUNT_WRITE = 0 AND SUM_TIMER_WRITE = 0 AND SUM_NUMBER_OF_BYTES_WRITE = 0
+AND
+COUNT_MISC = 0 AND SUM_TIMER_MISC = 0;
+if(`SELECT COUNT(*) FROM performance_schema.socket_summary_by_instance
+ WHERE NOT ( $my_rules )
+ AND OBJECT_INSTANCE_BEGIN = @default_object_instance_begin`)
+{
+ --enable_query_log
+ --enable_result_log
+ --echo # The statistics looks suspicious.
+ --echo # We expect
+ --echo # $my_rules
+ --echo #
+ # MLML: Try $column_list
eval
- SELECT EVENT_NAME, statement,
- $my_rules AS my_col
- $part
- ORDER BY EVENT_NAME, statement;
+ SELECT
+ COUNT_STAR, SUM_TIMER_WAIT,
+ 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 OBJECT_INSTANCE_BEGIN = @default_object_instance_begin;
+}
- # eval
- # SELECT * FROM mysqltest.socket_summary_by_instance_detail
- # WHERE EVENT_NAME, statement IN
- # (SELECT EVENT_NAME, statement
- # $part)
- # ORDER BY EVENT_NAME, statement;
+#---------------------------------------------------------------------------
+--echo # 4.4 Check the differences caused by SQL statements
+--echo # 4.4.1 There must be no changes in counters of instances
+--echo # NOT LIKE '%client_connection' because everything gets charged
+--echo # into client_connection of the acting connection.
+#===========================================================================
+let $my_rules=
+COUNT_READ = 0 AND SUM_TIMER_READ = 0 AND SUM_NUMBER_OF_BYTES_READ = 0
+AND
+COUNT_WRITE = 0 AND SUM_TIMER_WRITE = 0 AND SUM_NUMBER_OF_BYTES_WRITE = 0
+AND
+COUNT_MISC = 0 AND SUM_TIMER_MISC = 0;
+if(`SELECT COUNT(*) FROM mysqltest.socket_summary_by_instance_detail
+ WHERE NOT ( $my_rules )
+ AND EVENT_NAME NOT LIKE '%client_connection'
+ AND statement NOT LIKE 'Connect%'`)
+{
+ --enable_query_log
+ --enable_result_log
+ --echo # The statistics looks suspicious.
+ --echo # We expect
+ --echo # $my_rules
+ --echo #
eval
- SELECT object_instance_begin, $counter_column_list, EVENT_NAME, statement, run
- FROM mysqltest.socket_summary_by_instance_detail t1
- WHERE (EVENT_NAME, statement) IN
- (SELECT EVENT_NAME, statement $part)
- AND OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
- ORDER BY EVENT_NAME, statement, run;
+ SELECT
+ $column_list
+ FROM mysqltest.socket_summary_by_instance_detail
+ WHERE NOT ( $my_rules )
+ AND EVENT_NAME NOT LIKE '%client_connection'
+ AND statement NOT LIKE 'Connect%'
+ ORDER BY EVENT_NAME, OBJECT_INSTANCE_BEGIN, statement, run;
+}
- --echo Debug 4.2a - Dump mysqltest.socket_summary_by_instance_detail
- 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, statement, run;
-
- --echo Debug 4.2b - Std dev and coefficient of variation for mysqltest.socket_summary_by_instance_detail
- select rpad(EVENT_NAME,39,' ') as EVENT_NAME1, rpad(statement,49,' ') as STATEMENT1,
- lpad(count(COUNT_READ),5,' ') as COUNT_READ,
- lpad(std(COUNT_READ)/avg(COUNT_READ),10,' ') as CV_COUNT_READ,
- lpad(std(SUM_NUMBER_OF_BYTES_READ)/avg(SUM_NUMBER_OF_BYTES_READ),10,' ') as CV_BYTES_READ,
- lpad(std(COUNT_WRITE)/avg(COUNT_WRITE),10,' ') as CV_COUNT_WRITE,
- lpad(std(SUM_NUMBER_OF_BYTES_WRITE)/avg(SUM_NUMBER_OF_BYTES_WRITE),10,' ') as CV_BYTES_WRITE,
- lpad(std(COUNT_MISC)/avg(COUNT_MISC),10,' ') as CV_COUNT_MISC
- from mysqltest.socket_summary_by_instance_detail
+--echo # 4.4.2 In case of SELECT and our scenarios even COUNT_READ and COUNT_MISC
+--echo # are stable.
+let $my_rules=
+COUNT(DISTINCT COUNT_READ) = 1 AND
+COUNT(DISTINCT COUNT_MISC) = 1;
+let $part=
+FROM mysqltest.socket_summary_by_instance_detail
+WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
+ AND statement NOT LIKE '%Connect%'
+GROUP BY EVENT_NAME, statement
+HAVING NOT ($my_rules) ;
+if(`SELECT COUNT(statement) $part`)
+{
+ --enable_query_log
+ --enable_result_log
+ --echo # The statistics looks suspicious.
+ --echo # We expect
+ --echo # $my_rules
+ --echo # for GROUP BY EVENT_NAME, statement
+ eval
+ SELECT $column_list
+ FROM mysqltest.socket_summary_by_instance_detail
WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
- group by EVENT_NAME1, STATEMENT1;
+ AND (EVENT_NAME, statement)
+ IN (SELECT EVENT_NAME, statement
+ $part)
+ ORDER BY EVENT_NAME, statement, run, OBJECT_INSTANCE_BEGIN;
+}
+
+--echo # 4.4.3 In our testing scenarios we get for the client_connection entry
+--echo # of the acting connection
+--echo # -> OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
+--echo # COUNT_MISC = 0 AND SUM_TIMER_MISC = 0
+#===========================================================================
+let $my_rules=
+COUNT_MISC = 0 AND SUM_TIMER_MISC = 0;
+let $part=
+FROM mysqltest.socket_summary_by_instance_detail
+WHERE NOT ( $my_rules )
+ AND EVENT_NAME LIKE '%client_connection'
+ AND statement NOT LIKE 'Connect%'
+ AND OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin;
+if(`SELECT COUNT(*) $part`)
+{
+ --enable_query_log
+ --enable_result_log
+ --echo # The statistics looks suspicious.
+ --echo # We expect
+ --echo # $my_rules
+ --echo #
+ eval
+ SELECT
+ $column_list
+ $part
+ ORDER BY EVENT_NAME, OBJECT_INSTANCE_BEGIN, statement, run;
}
# Initialize variables
@@ -576,13 +907,13 @@ let $my_rules= my_rules_not_set;
let $stmt1= stmt1_not_set;
let $stmt2= stmt2_not_set;
# $title_prefix is used for the generation of titles
-let $title_prefix= 4.3;
+let $title_prefix= 4.4;
# $check_num is used for the generation of titles and gets incremented after
# every call of the current script.
-let $check_num= 1;
+let $check_num= 4;
# $column_list is used for the generation of error information and valid for
# every sub test.
-let $column_list=
+let $diff_column_list=
t2.COUNT_READ - t1.COUNT_READ AS D_COUNT_READ,
t2.COUNT_READ AS S2_COUNT_READ,
t1.COUNT_READ AS S1_COUNT_READ,
@@ -592,7 +923,7 @@ t1.SUM_NUMBER_OF_BYTES_READ
t2.COUNT_WRITE - t1.COUNT_WRITE AS D_COUNT_WRITE,
t2.COUNT_WRITE AS S2_COUNT_WRITE,
t1.COUNT_WRITE AS S1_COUNT_WRITE,
-t2.SUM_NUMBER_OF_BYTES_WRITE - t1.SUM_NUMBER_OF_BYTES_WRITE AS D_UM_NUMBER_OF_BYTES_WRITE,
+t2.SUM_NUMBER_OF_BYTES_WRITE - t1.SUM_NUMBER_OF_BYTES_WRITE AS D_SUM_NUMBER_OF_BYTES_WRITE,
t2.SUM_NUMBER_OF_BYTES_WRITE AS S2_SUM_NUMBER_OF_BYTES_WRITE,
t1.SUM_NUMBER_OF_BYTES_WRITE AS S1_SUM_NUMBER_OF_BYTES_WRITE,
t2.COUNT_MISC - t1.COUNT_MISC AS D_COUNT_MISC,
@@ -608,7 +939,10 @@ WHERE OBJECT_INSTANCE_BEGIN <> @default_
AND EVENT_NAME LIKE ('%client_connection')
AND run = 1;
---echo # $title_prefix Check the differences caused by SQL statements
+--echo # Check the differences between changes caused by SQL statements
+--echo # These differences must correspond to parameters like
+--echo # - statement, table name or column name length
+--echo # - number of rows in result set, size of rows in result set etc.
# --> Statement NOT LIKE '%Connect%'
let stmt1= SELECT col2 FROM does_not_exist;
@@ -619,6 +953,9 @@ let stmt2= SELECT col2 FROM does_not_exi
# 3 51 1 59 1 SELECT col2 FROM does_not_exist WHERE col1 = 0
# The string of the statement gets charged into SUM_NUMBER_OF_BYTES_READ.
# The server error message gets charged into SUM_NUMBER_OF_BYTES_WRITE.
+let $msg=
+# One statement is longer than the other.
+# Both statements fail with the same error message (table does not exist);
#
let $my_rules=
t2.COUNT_READ - t1.COUNT_READ = 0 AND
@@ -634,8 +971,9 @@ let $stmt2= SELECT col2 FROM does_not_ex
# CNT_READ BYTES_READ CNT_WRITE BYTES_WRITE CNT_MISC statement
# 3 51 1 59 1 SELECT col2 FROM does_not_exist WHERE col1 = 0
# 3 51 1 162 1 SELECT col2 FROM does_not_exist WHERE col1 A 0
-# Both statements have the same length and fail. But the second statement
-# caused a longer error message.
+let $msg=
+# Both statements have the same length and fail.
+# The length of the error messages differs.;
let $my_rules=
t2.COUNT_READ - t1.COUNT_READ = 0 AND
t2.SUM_NUMBER_OF_BYTES_READ - t1.SUM_NUMBER_OF_BYTES_READ = LENGTH('$stmt2') - LENGTH('$stmt1') AND
@@ -649,7 +987,10 @@ let $stmt2= SELECT col2 FROM does_not_ex
# CNT_READ BYTES_READ CNT_WRITE BYTES_WRITE CNT_MISC statement
# 3 36 1 59 1 SELECT col2 FROM does_not_exist
# 3 40 1 63 1 SELECT col2 FROM does_not_exist0123
-# Statements differ in table name length and table name is part of error message.
+let $msg=
+# Both statements fail (table does not exist).
+# The length of the statement and the length of the error messages differs.
+# Reason for both differences is the length of the table name.;
let $my_rules=
t2.COUNT_READ - t1.COUNT_READ = 0 AND
t2.SUM_NUMBER_OF_BYTES_READ - t1.SUM_NUMBER_OF_BYTES_READ = LENGTH('$stmt2') - LENGTH('$stmt1') AND
@@ -658,40 +999,49 @@ t2.SUM_NUMBER_OF_BYTES_WRITE - t1.SUM_NU
t2.COUNT_MISC - t1.COUNT_MISC = 0;
--source ../include/socket_check1.inc
-# How to enforce the debug info is printed?
+# Assuming that some new check should be added.
+# How to enforce that a lot info is printed so that it becomes easy to derive
+# the right value for $my_rules?
+#----------------------------------------------------------------------------
# let $my_rules= 0;
-# This will cause that suite/perfschema/include/socket_check1.inc conclude that
-# check failed and it will print debug information.
+# This will cause that suite/perfschema/include/socket_check1.inc concludes that
+# the check via $my_rules failed and it will print debug information.
# let $stmt1= SELECT col2 FROM does_not_exist WHERE col1 = 0;
# let $stmt2= SELECT col2 FROM does_not_exist WHERE col1 A 0;
# let $my_rules= 0;
# --source ../include/socket_check1.inc
-let $stmt2= SELECT col2 AS my_lovely_col FROM mysqltest.my_aux WHERE col1 = 0;
-let $stmt1= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 0;
+let $stmt2= SELECT col2 AS my_super_col FROM mysqltest.my_aux WHERE col1 = -1;
+let $stmt1= SELECT col2 FROM mysqltest.my_aux WHERE col1 = -1;
+#
# CNT_READ BYTES_READ CNT_WRITE BYTES_WRITE CNT_MISC statement
-# 3 53 1 78 1 SELECT col2 FROM mysqltest.my_aux WHERE col1 = 0
-# 3 70 1 87 1 SELECT col2 AS my_lovely_col FROM mysqltest.my_aux WHERE col1 = 0
-# Both statements get an empty result set. But the length of the statements and
-# the result set column names differs.
+# 3 54 1 78 1 SELECT col2 FROM mysqltest.my_aux WHERE col1 = -1
+# 3 70 1 86 1 SELECT col2 AS my_super_col FROM mysqltest.my_aux WHERE col1 = -1
+let $msg=
+# Both statements get an empty result set.
+# The length of the statements and the length of the result sets differs.
+# Reason for both differences is the length of the some column name.;
let $my_rules=
t2.COUNT_READ - t1.COUNT_READ = 0 AND
t2.SUM_NUMBER_OF_BYTES_READ - t1.SUM_NUMBER_OF_BYTES_READ = LENGTH('$stmt2') - LENGTH('$stmt1') AND
t2.COUNT_WRITE - t1.COUNT_WRITE = 0 AND
-t2.SUM_NUMBER_OF_BYTES_WRITE - t1.SUM_NUMBER_OF_BYTES_WRITE = LENGTH('my_lovely_col') - LENGTH('col2') AND
+t2.SUM_NUMBER_OF_BYTES_WRITE - t1.SUM_NUMBER_OF_BYTES_WRITE = LENGTH('my_super_col') - LENGTH('col2') AND
t2.COUNT_MISC - t1.COUNT_MISC = 0;
--source ../include/socket_check1.inc
let $stmt2= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1;
-let $stmt1= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 0;
+let $stmt1= SELECT col2 FROM mysqltest.my_aux WHERE col1 = -1;
+#
# CNT_READ BYTES_READ CNT_WRITE BYTES_WRITE CNT_MISC statement
-# 3 53 1 78 1 SELECT col2 FROM mysqltest.my_aux WHERE col1 = 0
-# 3 53 1 83 1 SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1
-# stmt1 gets an empty result set
-# stmt1 gets one row containing an empty result string
+# t1 3 54 1 78 1 SELECT col2 FROM mysqltest.my_aux WHERE col1 = -1
+# t2 3 53 1 83 1 SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1
+let $msg=
+# Both statements differ in the statement length.
+# One statement earns an empty result set.
+# The other statement earns one row containing an empty string.;
let $my_rules=
t2.COUNT_READ - t1.COUNT_READ = 0 AND
-t2.SUM_NUMBER_OF_BYTES_READ - t1.SUM_NUMBER_OF_BYTES_READ = LENGTH('$stmt2') - LENGTH('$stmt1') AND
+t1.SUM_NUMBER_OF_BYTES_READ - t2.SUM_NUMBER_OF_BYTES_READ = LENGTH('$stmt1') - LENGTH('$stmt2') AND
t2.COUNT_WRITE - t1.COUNT_WRITE = 0 AND
t2.SUM_NUMBER_OF_BYTES_WRITE - t1.SUM_NUMBER_OF_BYTES_WRITE > 0 AND
t2.COUNT_MISC - t1.COUNT_MISC = 0;
@@ -699,11 +1049,14 @@ t2.COUNT_MISC - t1.COUNT_
let $stmt2= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 2;
let $stmt1= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1;
+#
# CNT_READ BYTES_READ CNT_WRITE BYTES_WRITE CNT_MISC statement
# 3 53 1 83 1 SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1
# 3 53 1 84 1 SELECT col2 FROM mysqltest.my_aux WHERE col1 = 2
-# stmt2 one row containing a string 1 byte long
-# stmt1 one row containing an empty string
+let $msg=
+# Both statements have the same length.
+# One statement earns an one row containing an empty string.
+# The other statement earns one row containing a string 1 byte long.;
let $my_rules=
t2.COUNT_READ - t1.COUNT_READ = 0 AND
t2.SUM_NUMBER_OF_BYTES_READ - t1.SUM_NUMBER_OF_BYTES_READ = LENGTH('$stmt2') - LENGTH('$stmt1') AND
@@ -714,193 +1067,414 @@ t2.COUNT_MISC - t1.COUNT_
let $stmt2= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 3;
let $stmt1= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1;
+#
# CNT_READ BYTES_READ CNT_WRITE BYTES_WRITE CNT_MISC statement
# 3 53 1 83 1 SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1
# 3 53 1 1109 1 SELECT col2 FROM mysqltest.my_aux WHERE col1 = 3
-# stmt2 one row containing a string 1024 byte long
-# stmt1 one row containing an empty string
+let $msg=
+# Both statements have the same length.
+# One statement earns an one row containing an empty string.
+# The other statement earns one row containing a string 1024 byte long.;
let $my_rules=
t2.COUNT_READ - t1.COUNT_READ = 0 AND
t2.SUM_NUMBER_OF_BYTES_READ - t1.SUM_NUMBER_OF_BYTES_READ = LENGTH('$stmt2') - LENGTH('$stmt1') AND
t2.COUNT_WRITE - t1.COUNT_WRITE = 0 AND
-t2.SUM_NUMBER_OF_BYTES_WRITE - t1.SUM_NUMBER_OF_BYTES_WRITE = 1024 + 2 AND
+t2.SUM_NUMBER_OF_BYTES_WRITE - t1.SUM_NUMBER_OF_BYTES_WRITE >= 1024 AND
t2.COUNT_MISC - t1.COUNT_MISC = 0;
--source ../include/socket_check1.inc
-#let $stmt2= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1
-#UNION ALL
-#SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1;
-#let $stmt1= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1;
-# CNT_READ BYTES_READ CNT_WRITE BYTES_WRITE CNT_MISC statement
-# 3 53 1 83 1 SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1
-# 3 112 1 67 1 SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1
-# UNION ALL
-# SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1
-# MLML: The BYTES_WRITE seem to be wrong.
-#let $my_rules= 0;
-#--source ../include/socket_check1.inc
-
-#let $stmt2= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1
-#UNION DISTINCT
-#SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1;
-#let $stmt1= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1;
-# CNT_READ BYTES_READ CNT_WRITE BYTES_WRITE CNT_MISC statement
-# 3 53 1 83 1 SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1
-# 3 117 1 62 1 SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1
-# UNION DISTINCT
-# SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1
-# MLML: The BYTES_WRITE seem to be wrong.
-#let $my_rules= 0;
-#--source ../include/socket_check1.inc
-
-#let $stmt2= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 3
-#UNION ALL
-#SELECT col2 FROM mysqltest.my_aux WHERE col1 = 3;
-#let $stmt1= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 3;
-# CNT_READ BYTES_READ CNT_WRITE BYTES_WRITE CNT_MISC statement
-# 3 53 1 1109 1 SELECT col2 FROM mysqltest.my_aux WHERE col1 = 3
-# 3 112 1 2119 1 SELECT col2 FROM mysqltest.my_aux WHERE col1 = 3
-# UNION ALL
-# SELECT col2 FROM mysqltest.my_aux WHERE col1 = 3
-#let $my_rules= 0;
-#--source ../include/socket_check1.inc
-
-let $stmt2= SELECT col2 FROM mysqltest.my_aux WHERE col1 < 3;
+let $stmt2= SELECT col2 FROM mysqltest.my_aux WHERE col1 < 2;
let $stmt1= SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1;
-# CNT_READ BYTES_READ CNT_WRITE BYTES_WRITE CNT_MISC statement
-# 3 53 1 83 1 SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1
-# 3 53 1 89 1 SELECT col2 FROM mysqltest.my_aux WHERE col1 < 3
-# stmt2 Gives one row with string 1 byte long and one row with an empty string.
-# stmt1 Gives one row with an empty string.
-# BYTES_WRITE might be ok here.
-#let $my_rules= 0;
-#--source ../include/socket_check1.inc
+#
+# CNT_READ BYTES_READ CNT_WRITE BYTES_WRITE CNT_MISC statement
+# t1 3 53 1 83 1 SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1
+# t2 3 53 1 88 1 SELECT col2 FROM mysqltest.my_aux WHERE col1 < 2
+let $msg=
+# Both statements have the same length.
+# One statement earns an one row containing an empty string.
+# The other statement earns two rows containing an empty string.;
+let $my_rules=
+t2.COUNT_READ - t1.COUNT_READ = 0 AND
+t2.SUM_NUMBER_OF_BYTES_READ - t1.SUM_NUMBER_OF_BYTES_READ = LENGTH('$stmt2') - LENGTH('$stmt1') AND
+t2.COUNT_WRITE - t1.COUNT_WRITE = 0 AND
+t2.SUM_NUMBER_OF_BYTES_WRITE - t1.SUM_NUMBER_OF_BYTES_WRITE > 0 AND
+t2.COUNT_MISC - t1.COUNT_MISC = 0;
+--source ../include/socket_check1.inc
-if(0)
+--echo # 4.5 Check the differences caused by Connects
+--echo # Attention: Succesful Connects run an additional "DO 1".
+--echo # 4.5.1 Connects do not charge anything into READ or WRITE counters
+--echo # of the instance with EVENT_NAME NOT LIKE ('%client_connection%').
+--echo # This mean all these counters must be 0.
+let $my_rules=
+COUNT_READ = 0 AND SUM_TIMER_READ = 0 AND SUM_NUMBER_OF_BYTES_READ = 0 AND
+COUNT_WRITE = 0 AND SUM_TIMER_WRITE = 0 AND SUM_NUMBER_OF_BYTES_WRITE = 0;
+let $part=
+FROM mysqltest.socket_summary_by_instance_detail
+WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
+ AND statement LIKE ('%Connect%')
+ AND EVENT_NAME NOT LIKE ('%client_connection%')
+ AND NOT ( $my_rules );
+if(`SELECT COUNT(*) $part `)
{
---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,
-t2.SUM_NUMBER_OF_BYTES_WRITE - t1.SUM_NUMBER_OF_BYTES_WRITE;
-# t2.COUNT_WRITE - t1.COUNT_WRITE,
-# t2.COUNT_READ - t1.COUNT_READ,
-# t2.COUNT_MISC - t1.COUNT_MISC;
-
-# Successful connections with different username lengths
-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 LIKE '%client_connection%'
- AND run = 1;
+ --enable_query_log
+ --enable_result_log
+ --echo # The statistics looks suspicious.
+ --echo # We expect
+ --echo # $my_rules
+ --echo #
+ eval
+ SELECT
+ $column_list
+ $part;
+}
-#eval
-#SELECT $counter_column_list, EVENT_NAME
-#FROM mysqltest.socket_summary_by_instance_detail t2
-#WHERE t2.statement LIKE 'Connect%root0123456789,%'
-# AND run = 1;
-
-#eval
-#SELECT $counter_column_list, EVENT_NAME
-#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_column_list, EVENT_NAME
-#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.5.2 Connects using for host the value 'localhost'
+--echo # 4.5.2.1 For the instance with EVENT_NAME LIKE '%server_tcpip_socket'
+--echo # COUNT_MISC = 0 AND SUM_TIMER_MISC = 0 must be valid
+--echo # because we run through server_unix_socket.
+#===========================================================================
+let $my_rules=
+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
+ AND statement LIKE ('%Connect%localhost%')
+ AND EVENT_NAME LIKE ('%server_tcpip_socket%')
+ AND NOT ( $my_rules );
+if(`SELECT COUNT(*) $part `)
+{
+ --enable_query_log
+ --enable_result_log
+ --echo # The statistics looks suspicious.
+ --echo # We expect
+ --echo # $my_rules
+ --echo #
+ eval
+ SELECT
+ $column_list
+ $part;
+}
---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.5.2.2 For the instance with EVENT_NAME LIKE '%server_unix_socket'
+--echo # COUNT_MISC > 0 AND SUM_TIMER_MISC > 0 must be valid.
+#===========================================================================
+let $my_rules=
+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
+ AND statement LIKE ('%Connect%localhost%')
+ AND EVENT_NAME LIKE ('%server_unix_socket%')
+ AND NOT ( $my_rules );
+if(`SELECT COUNT(*) $part `)
+{
+ --enable_query_log
+ --enable_result_log
+ --echo # The statistics looks suspicious.
+ --echo # We expect
+ --echo # $my_rules
+ --echo #
+ eval
+ SELECT
+ $column_list
+ $part;
+}
+--echo # 4.5.3 Connects using for host a value <> 'localhost'
+--echo # 4.5.3.1 For the instance with EVENT_NAME LIKE '%server_unix_socket'
+--echo # COUNT_MISC = 0 AND SUM_TIMER_MISC = 0 must be valid
+--echo # because we run through server_tcpip_socket.
+#===========================================================================
+let $my_rules=
+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
+ AND statement LIKE ('%Connect%$my_localhost%')
+ AND EVENT_NAME LIKE ('%server_unix_socket%')
+ AND NOT ( $my_rules );
+if(`SELECT COUNT(*) $part `)
+{
+ --enable_query_log
+ --enable_result_log
+ --echo # The statistics looks suspicious.
+ --echo # We expect
+ --echo # $my_rules
+ --echo #
+ eval
+ SELECT
+ $column_list
+ $part;
+}
+--echo # 4.5.3.2 For the instance with EVENT_NAME LIKE '%server_tcpip_socket'
+--echo # COUNT_MISC > 0 AND SUM_TIMER_MISC > 0 must be valid.
+#===========================================================================
+let $my_rules=
+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
+ AND statement LIKE ('%Connect%$my_localhost%')
+ AND EVENT_NAME LIKE ('%server_tcpip_socket%')
+ AND NOT ( $my_rules );
+if(`SELECT COUNT(*) $part `)
+{
+ --enable_query_log
+ --enable_result_log
+ --echo # The statistics looks suspicious.
+ --echo # We expect
+ --echo # $my_rules
+ --echo #
+ eval
+ SELECT
+ $column_list
+ $part;
+}
---echo # 4.4 Check the differences caused by SQL statements
-# --> Statement NOT LIKE '%Connect%'
---echo # FIXME: To be implemented after the bug fixes
+--echo # 4.5.4 Failing Connects do not cause any row with EVENT_NAME
+--echo # LIKE '%client_connection'
+#===========================================================================
+# Debug: Try with LIKE 'Connect%root%'
+let $part=
+FROM mysqltest.socket_summary_by_instance_detail
+WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
+ AND statement LIKE ('Connect%boot%')
+ AND EVENT_NAME LIKE ('%client_connection');
+if(`SELECT COUNT(*) $part`)
+{
+ --enable_query_log
+ --enable_result_log
+ --echo # The statistics looks suspicious.
+ --echo # We expect that there are no rows because
+ --echo # - failing connects do not cause a row with "client_connection"
+ --echo # - all failing connects contain a user name LIKE '%boot%'
+ eval
+ SELECT
+ $column_list
+ $part;
+}
-# General architecture for checking stuff:
+--echo # 4.5.5 Successful Connects cause a new instance with EVENT_NAME
+--echo # LIKE '%client_connection'
+#===========================================================================
+# MLML: Is checked
+# - connects which should be successful have statement LIKE ('Connect%root%').
+# - We try every type of Connect $loop_rounds times.
+# Therefore we should find $loop_rounds rows with
+# - EVENT_NAME LIKE ('%client_connection')
+# AND
+# - OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
+# We do not want to count the entry of belonging to the default connection.
+# AND
+# - statement LIKE ('Connect%root%')
+# The connects which should be successful.
#
-# 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=
+let $my_rules=
+COUNT(*) = $loop_rounds;
+let $part=
+FROM mysqltest.socket_summary_by_instance_detail
+WHERE EVENT_NAME LIKE ('%client_connection')
+ AND OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
+ AND statement LIKE ('Connect%root%')
+GROUP BY statement
+HAVING NOT ( $my_rules );
+
+# Reveal that the check that follows would catch anything:
+# DELETE
+# FROM mysqltest.socket_summary_by_instance_detail
+# WHERE EVENT_NAME LIKE ('%client_connection')
+# AND OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
+# AND statement LIKE ('Connect%root%')
+# LIMIT 1;
+# Attention: socket_summary_by_instance_detail is after that rotten.
+
+if(`SELECT COUNT(*) $part`)
+{
+ --enable_query_log
+ --enable_result_log
+ --echo # The statistics looks suspicious.
+ --echo # We expect
+ --echo # $my_rules
+ --echo # for GROUP BY statement
+ --echo #
+ eval
+ SELECT
+ $column_list
+ FROM mysqltest.socket_summary_by_instance_detail
+ WHERE EVENT_NAME LIKE ('%client_connection')
+ AND OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
+ AND statement IN
+ (SELECT statement
+ $part);
+}
+
+--echo # 4.6 Check the differences caused by Connects
+--echo # - INSTANCES with an EVENT_NAME like server_tcpip_socket or
+--echo # server_unix_socket are already checked
+--echo # - the stability of results is already checked
+--echo # So we con go with the results of the first run.
+
+# eval
+# SELECT $column_list
# 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 ..... <use $part here>
-# }
-#
-# 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
-#
+# AND EVENT_NAME LIKE ('%client_connection')
+# AND statement LIKE ('Connect%')
+# AND run = 1
+# ORDER BY event_name,statement, run;
+# CREAD TREAD BREAD CWRITE TWRITE BWRITE CMISC TMISC STATEMENT
+# 4 6149616 79 3 34008480 117 3 38914128 Connect (con*,::ffff:127.0.0.1,root,,mysqltest,,)
+# 4 7012368 84 3 10112256 117 2 43067376 Connect (con*,localhost,root,,mysqlsupertest,,)
+# 4 7172880 79 3 10247688 117 2 40128000 Connect (con*,localhost,root,,mysqltest,,)
+# 4 6706392 85 3 15509472 117 2 34851168 Connect (con*,localhost,root012345,,mysqltest,,)
+# 4 10543632 89 3 10578744 117 2 39460872 Connect (con*,localhost,root0123456789,,mysqltest,,)
+
+--echo # 4.6.1 The SUM_OF_BYTES_WRITE value is the same for all Connects.
+#========================================================================
+# MLML: Is checked
+let $my_rules= COUNT(DISTINCT SUM_NUMBER_OF_BYTES_WRITE) = 1;
+let $part=
+FROM mysqltest.socket_summary_by_instance_detail
+WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
+ AND EVENT_NAME LIKE ('%client_connection')
+ AND statement LIKE ('Connect%')
+ AND run = 1;
+if(`SELECT NOT ( $my_rules) $part`)
+{
+ --enable_query_log
+ --enable_result_log
+ --echo # The statistics looks suspicious.
+ --echo # We expect
+ --echo # $my_rules
+ --echo # for all Connects
+ --echo #
+ eval
+ SELECT
+ statement, SUM_NUMBER_OF_BYTES_WRITE
+ $part;
+}
+
+--echo # 4.6.2 The SUM_OF_BYTES_WRITE value hast to be > 100.
+#============================================================
+# MLML: Is checked
+let $my_rules= SUM_NUMBER_OF_BYTES_WRITE > 100;
+let $part=
+FROM mysqltest.socket_summary_by_instance_detail
+WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
+ AND EVENT_NAME LIKE ('%client_connection')
+ AND statement LIKE ('Connect%')
+ AND NOT ( $my_rules )
+ AND run = 1;
+if(`SELECT COUNT(*) $part`)
+{
+ --enable_query_log
+ --enable_result_log
+ --echo # The statistics looks suspicious.
+ --echo # We expect
+ --echo # $my_rules
+ --echo # for all Connects
+ --echo #
+ eval
+ SELECT
+ statement, SUM_NUMBER_OF_BYTES_WRITE
+ $part;
+}
+
+--echo # 4.6.3 COUNT_READ, COUNT_WRITE and COUNT_MISC have to be to be > 0
+#=========================================================================
+# MLML: Is checked
+let $my_rules=
+COUNT_READ > 0 AND COUNT_WRITE > 0 AND COUNT_MISC > 0;
+let $part=
+FROM mysqltest.socket_summary_by_instance_detail
+WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
+ AND EVENT_NAME LIKE ('%client_connection')
+ AND statement LIKE ('Connect%')
+ AND NOT ( $my_rules )
+ AND run = 1;
+if(`SELECT COUNT(*) $part`)
+{
+ --enable_query_log
+ --enable_result_log
+ --echo # The statistics looks suspicious.
+ --echo # We expect
+ --echo # $my_rules
+ --echo # for all Connects
+ --echo #
+ eval
+ SELECT
+ statement, COUNT_READ, COUNT_WRITE, COUNT_MISC
+ $part;
+}
+
+--echo # 4.6.4 Checks based on comparison of results for connects
+let $part0=
+FROM mysqltest.socket_summary_by_instance_detail t1
+JOIN mysqltest.socket_summary_by_instance_detail t2
+USING (EVENT_NAME, run)
+WHERE EVENT_NAME LIKE ('%client_connection')
+ AND run = 1
+ AND t2.OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
+ AND t1.OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin;
+let $part1=
+SELECT RPAD(statement,55,' ') AS STATEMENT,
+ LENGTH(statement), SUM_NUMBER_OF_BYTES_READ
+FROM mysqltest.socket_summary_by_instance_detail
+WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin
+ AND EVENT_NAME LIKE ('%client_connection')
+ AND run = 1;
+
+
+--echo # 4.6.4 The user name length affects the SUM_OF_BYTES_READ value
+#======================================================================
+# MLML: Is checked
+# 4 6706392 85 3 15509472 117 2 34851168 Connect (con*,localhost,root012345,,mysqltest,,)
+# 4 10543632 89 3 10578744 117 2 39460872 Connect (con*,localhost,root0123456789,,mysqltest,,)
+let $stmt2= Connect (con*,localhost,root0123456789,,mysqltest,,);
+let $stmt1= Connect (con*,localhost,root012345,,mysqltest,,);
+let $my_rules=
+t2.SUM_NUMBER_OF_BYTES_READ - t1.SUM_NUMBER_OF_BYTES_READ = LENGTH('$stmt2') - LENGTH('$stmt1');
+if(`SELECT NOT ($my_rules)
+ $part0
+ AND t2.statement = '$stmt2'
+ AND t1.statement = '$stmt1'`)
+{
+ --enable_query_log
+ --enable_result_log
+ --echo # The statistics looks suspicious.
+ --echo # We expect
+ --echo # $my_rules
+ --echo #
+ eval
+ $part1
+ AND statement IN('$stmt2','$stmt1');
+}
+
+--echo # 4.6.5 The database name length affects the SUM_OF_BYTES_READ value
+#==========================================================================
+# 4 7012368 84 3 10112256 117 2 43067376 Connect (con*,localhost,root,,mysqlsupertest,,)
+# 4 7172880 79 3 10247688 117 2 40128000 Connect (con*,localhost,root,,mysqltest,,)
+let $stmt2= Connect (con*,localhost,root,,mysqlsupertest,,);
+let $stmt1= Connect (con*,localhost,root,,mysqltest,,);
+let $my_rules=
+t2.SUM_NUMBER_OF_BYTES_READ - t1.SUM_NUMBER_OF_BYTES_READ = LENGTH('$stmt2') - LENGTH('$stmt1');
+if(`SELECT NOT ($my_rules)
+ $part0
+ AND t2.statement = '$stmt2'
+ AND t1.statement = '$stmt1'`)
+{
+ --enable_query_log
+ --enable_result_log
+ --echo # The statistics looks suspicious.
+ --echo # We expect
+ --echo # $my_rules
+ --echo #
+ eval
+ $part1
+ AND statement IN('$stmt2','$stmt1');
+}
if($print_details)
{
@@ -928,7 +1502,7 @@ if($print_details)
WHERE OBJECT_INSTANCE_BEGIN = @default_object_instance_begin
ORDER BY statement,run;
--echo # 3. The "server_unix_socket"
- # WHERE OBJECT_INSTANCE_BEGIN = @con1_object_instance_begin
+ # WHERE OBJECT_INSTANCE_BEGIN = @default_object_instance_begin
SELECT EVENT_NAME,
COUNT_READ, SUM_NUMBER_OF_BYTES_READ,
COUNT_WRITE, SUM_NUMBER_OF_BYTES_WRITE,
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (chris.powers:3382 to 3385) WL#4896 | Christopher Powers | 22 Aug |
