# The logging of commands and result sets is mostly disabled.
# There are some messages which help to observe the progress of the test.
# In case some check fails
# - a message about this will be printed
# - some SQL commands which show the unexpected state will be executed
# (logging enabled)
# - the test might abort
#
# 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.
TRUNCATE TABLE performance_schema.socket_summary_by_instance;
# 1.2 Check the base line
TRUNCATE TABLE mysqltest.my_socket_summary_by_instance;
# 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
ERROR 28000: Access denied for user 'boot'@'localhost' (using password: NO)
# 2.2 Connect fails because the user is unknown
# length of user name = 14 character (10 more than in 2.1)
# length of default db = 9 character
ERROR 28000: Access denied for user 'boot0123456789'@'localhost' (using password: NO)
# 2.3 Connect should pass, host = localhost
# length of user name = 4 character
# length of default db = 9 character
# 2.4 Connect should pass, host = localhost
# length of user name = 4 character
# length of default db = 14 character (5 more than 2.3)
# 2.5 Connect should pass, host = localhost
# length of user name = 10 character
# length of default db = 9 character
GRANT ALL PRIVILEGES ON *.* TO 'root012345'@'localhost';
DROP USER 'root012345'@'localhost';
# 2.6 Connect should pass, host = localhost
# length of user name = 14 character
# length of default db = 9 character
GRANT ALL PRIVILEGES ON *.* to 'root0123456789'@'localhost';
DROP USER 'root0123456789'@'localhost';
# 2.7 Connect should pass, host = my_localhost
# length of user name = 4 character
# length of default db = 9 character
# connection runs through server_tcpip_socket !
DO 1;
# 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 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 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 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 SELECT earning an empty result set.
SELECT col2 FROM mysqltest.my_aux WHERE col1 = -1;
col2
# 3.6 SELECT earning an empty result set.
# Short column name is replaced by longer alias.
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 SELECT earning one row with one string one char long.
SELECT col2 FROM mysqltest.my_aux WHERE col1 = 2;
col2
a
# 3.9 SELECT earning one row with one string 1024 char long.
SELECT col2 FROM mysqltest.my_aux WHERE col1 = 3;
col2
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
# 3.10 SELECT earning two rows with an empty string
SELECT col2 FROM mysqltest.my_aux WHERE col1 < 2;
col2
# 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
# - no change in COUNT_* leads to no change in
# SUM_TIMER_* and no change in SUM_NUMBER_OF_BYTES_*
# - increased COUNT_READ leads to increased
# SUM_NUMBER_OF_BYTES_READ
# - increased COUNT_WRITE leads to increased
# SUM_NUMBER_OF_BYTES_WRITE
# Attention:
# The time required for some action might be below timer resolution.
# Therefore some increased COUNT_* does not need to lead to an
# increased SUM_TIMER_*.
# 4.2 Results must be stable
# 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
# SKIPPED FOR WINDOWS
# 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
# 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
# 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
# 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 = -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
# 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
# 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 must be valid.
# SKIPPED FOR WINDOWS
# 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.
# SKIPPED FOR WINDOWS
# 4.5.3.2 For the instance with EVENT_NAME LIKE '%server_tcpip_socket'
# COUNT_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. Check the impact of TRUNCATE on socket_summary_by_instance.
# It must reset all counters.
TRUNCATE TABLE performance_schema.socket_summary_by_instance;
# 6. Cleanup