# 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 MariaDB 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
# 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.
# 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 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
