| 1 |
# The logging of commands and result sets is mostly disabled. |
| 2 |
# There are some messages which help to observe the progress of the test. |
| 3 |
# In case some check fails |
| 4 |
# - a message about this will be printed |
| 5 |
# - some SQL commands which show the unexpected state will be executed |
| 6 |
# (logging enabled) |
| 7 |
# - the test might abort |
| 8 |
# |
| 9 |
# 0. Check, build or set prequisites |
| 10 |
Warnings: |
| 11 |
Warning 1681 'performance_schema.setup_timers' is deprecated and will be removed in a future release. |
| 12 |
UPDATE performance_schema.threads |
| 13 |
SET INSTRUMENTED='NO' WHERE PROCESSLIST_ID = CONNECTION_ID(); |
| 14 |
# 1. Basic checks |
| 15 |
# 1.1 Check that the entry of the disconnected old default session really |
| 16 |
# disappeared from performance_schema.socket_summary_by_instance. |
| 17 |
TRUNCATE TABLE performance_schema.socket_summary_by_instance; |
| 18 |
# 1.2 Check the base line |
| 19 |
TRUNCATE TABLE mysqltest.my_socket_summary_by_instance; |
| 20 |
# 2. Variations on Connect |
| 21 |
# 2.1 Connect fails because the user is unknown |
| 22 |
# length of user name = 4 character |
| 23 |
# length of default db = 9 character |
| 24 |
ERROR 28000: Access denied for user 'boot'@'localhost' (using password: NO) |
| 25 |
# 2.2 Connect fails because the user is unknown |
| 26 |
# length of user name = 14 character (10 more than in 2.1) |
| 27 |
# length of default db = 9 character |
| 28 |
ERROR 28000: Access denied for user 'boot0123456789'@'localhost' (using password: NO) |
| 29 |
# 2.3 Connect should pass, host = localhost |
| 30 |
# length of user name = 4 character |
| 31 |
# length of default db = 9 character |
| 32 |
# 2.4 Connect should pass, host = localhost |
| 33 |
# length of user name = 4 character |
| 34 |
# length of default db = 14 character (5 more than 2.3) |
| 35 |
# 2.5 Connect should pass, host = localhost |
| 36 |
# length of user name = 10 character |
| 37 |
# length of default db = 9 character |
| 38 |
SET @orig_sql_mode= @@sql_mode; |
| 39 |
SET sql_mode= (SELECT replace(@@sql_mode,'NO_AUTO_CREATE_USER','')); |
| 40 |
Warnings: |
| 41 |
Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. |
| 42 |
GRANT ALL PRIVILEGES ON *.* TO 'root012345'@'localhost'; |
| 43 |
Warnings: |
| 44 |
Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |
| 45 |
SET sql_mode= @orig_sql_mode; |
| 46 |
Warnings: |
| 47 |
Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. |
| 48 |
DROP USER 'root012345'@'localhost'; |
| 49 |
# 2.6 Connect should pass, host = localhost |
| 50 |
# length of user name = 14 character |
| 51 |
# length of default db = 9 character |
| 52 |
SET @orig_sql_mode= @@sql_mode; |
| 53 |
SET sql_mode= (SELECT replace(@@sql_mode,'NO_AUTO_CREATE_USER','')); |
| 54 |
Warnings: |
| 55 |
Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. |
| 56 |
GRANT ALL PRIVILEGES ON *.* to 'root0123456789'@'localhost'; |
| 57 |
Warnings: |
| 58 |
Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |
| 59 |
SET sql_mode= @orig_sql_mode; |
| 60 |
Warnings: |
| 61 |
Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. |
| 62 |
DROP USER 'root0123456789'@'localhost'; |
| 63 |
# 2.7 Connect should pass, host = my_localhost |
| 64 |
# length of user name = 4 character |
| 65 |
# length of default db = 9 character |
| 66 |
# connection runs through server_tcpip_socket ! |
| 67 |
DO 1; |
| 68 |
# 3 Variations on SELECT |
| 69 |
# 3.1 Check a SELECT ending with server sending an error message. |
| 70 |
# Error message is short (unknown table). |
| 71 |
SELECT col2 FROM does_not_exist; |
| 72 |
ERROR 42S02: Table 'mysqltest.does_not_exist' doesn't exist |
| 73 |
# 3.2 SELECT ending with server sending an error message. |
| 74 |
# Now the statement is a bit longer but the error message |
| 75 |
# length does again not depend on statement. |
| 76 |
SELECT col2 FROM does_not_exist WHERE col1 = 0; |
| 77 |
ERROR 42S02: Table 'mysqltest.does_not_exist' doesn't exist |
| 78 |
# 3.3 SELECT ending with server sending an error message. |
| 79 |
# The statement has the same length like in 3.2 but the error |
| 80 |
# message is now different and much longer. |
| 81 |
SELECT col2 FROM does_not_exist WHERE col1 A 0; |
| 82 |
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 |
| 83 |
# 3.4 SELECT ending with server sending an error message. |
| 84 |
# Statement and error message are a bit longer than in 3.1 |
| 85 |
# because the table name is longer. |
| 86 |
SELECT col2 FROM does_not_exist0123; |
| 87 |
ERROR 42S02: Table 'mysqltest.does_not_exist0123' doesn't exist |
| 88 |
# 3.5 SELECT earning an empty result set. |
| 89 |
SELECT col2 FROM mysqltest.my_aux WHERE col1 = -1; |
| 90 |
col2 |
| 91 |
# 3.6 SELECT earning an empty result set. |
| 92 |
# Short column name is replaced by longer alias. |
| 93 |
SELECT col2 AS my_super_col FROM mysqltest.my_aux WHERE col1 = -1; |
| 94 |
my_super_col |
| 95 |
# 3.7 SELECT earning one row with an empty string. |
| 96 |
SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1; |
| 97 |
col2 |
| 98 |
|
| 99 |
# 3.8 SELECT earning one row with one string one char long. |
| 100 |
SELECT col2 FROM mysqltest.my_aux WHERE col1 = 2; |
| 101 |
col2 |
| 102 |
a |
| 103 |
# 3.9 SELECT earning one row with one string 1024 char long. |
| 104 |
SELECT col2 FROM mysqltest.my_aux WHERE col1 = 3; |
| 105 |
col2 |
| 106 |
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
| 107 |
# 3.10 SELECT earning two rows with an empty string |
| 108 |
SELECT col2 FROM mysqltest.my_aux WHERE col1 < 2; |
| 109 |
col2 |
| 110 |
|
| 111 |
|
| 112 |
# 3.11 Check that the preceding Connects/SQL command runs have not |
| 113 |
# caused some unexpected state. |
| 114 |
# 4. Check delta (value_after_action - value_before_action) details |
| 115 |
# 4.1 Check that |
| 116 |
# - no change in COUNT_* leads to no change in |
| 117 |
# SUM_TIMER_* and no change in SUM_NUMBER_OF_BYTES_* |
| 118 |
# - increased COUNT_READ leads to increased |
| 119 |
# SUM_NUMBER_OF_BYTES_READ |
| 120 |
# - increased COUNT_WRITE leads to increased |
| 121 |
# SUM_NUMBER_OF_BYTES_WRITE |
| 122 |
# Attention: |
| 123 |
# The time required for some action might be below timer resolution. |
| 124 |
# Therefore some increased COUNT_* does not need to lead to an |
| 125 |
# increased SUM_TIMER_*. |
| 126 |
# 4.2 Results must be stable |
| 127 |
# 4.3 Counters must be 0 in client_connection for the default session |
| 128 |
# Instrumenting is disabled since a long time and the counter were |
| 129 |
# reset via TRUNCATE just after the disabling. |
| 130 |
# 4.4 Check the differences caused by SQL statements |
| 131 |
# 4.4.1 There must be no changes in counters of instances |
| 132 |
# NOT LIKE '%client_connection' because everything gets charged |
| 133 |
# into client_connection of the acting connection. |
| 134 |
# 4.4.2 In case of SELECT and our scenarios even COUNT_READ and COUNT_MISC |
| 135 |
# are stable. |
| 136 |
# 4.4.3 In our testing scenarios we get for the client_connection entry |
| 137 |
# of the acting connection |
| 138 |
# -> OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin |
| 139 |
# COUNT_MISC = 0 AND SUM_TIMER_MISC = 0 |
| 140 |
# SKIPPED FOR WINDOWS |
| 141 |
# Check the differences between changes caused by SQL statements |
| 142 |
# These differences must correspond to parameters like |
| 143 |
# - statement, table name or column name length |
| 144 |
# - number of rows in result set, size of rows in result set etc. |
| 145 |
# 4.4.4 Compare impact of statements |
| 146 |
# SELECT col2 FROM does_not_exist WHERE col1 = 0 |
| 147 |
# SELECT col2 FROM does_not_exist |
| 148 |
# One statement is longer than the other. |
| 149 |
# Both statements fail with the same error message (table does not exist) |
| 150 |
# 4.4.5 Compare impact of statements |
| 151 |
# SELECT col2 FROM does_not_exist WHERE col1 A 0 |
| 152 |
# SELECT col2 FROM does_not_exist WHERE col1 = 0 |
| 153 |
# Both statements have the same length and fail. |
| 154 |
# The length of the error messages differs. |
| 155 |
# 4.4.6 Compare impact of statements |
| 156 |
# SELECT col2 FROM does_not_exist0123 |
| 157 |
# SELECT col2 FROM does_not_exist |
| 158 |
# Both statements fail (table does not exist). |
| 159 |
# The length of the statement and the length of the error messages differs. |
| 160 |
# Reason for both differences is the length of the table name. |
| 161 |
# 4.4.7 Compare impact of statements |
| 162 |
# SELECT col2 AS my_super_col FROM mysqltest.my_aux WHERE col1 = -1 |
| 163 |
# SELECT col2 FROM mysqltest.my_aux WHERE col1 = -1 |
| 164 |
# Both statements get an empty result set. |
| 165 |
# The length of the statements and the length of the result sets differs. |
| 166 |
# Reason for both differences is the length of the some column name. |
| 167 |
# 4.4.8 Compare impact of statements |
| 168 |
# SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1 |
| 169 |
# SELECT col2 FROM mysqltest.my_aux WHERE col1 = -1 |
| 170 |
# Both statements differ in the statement length. |
| 171 |
# One statement earns an empty result set. |
| 172 |
# The other statement earns one row containing an empty string. |
| 173 |
# 4.4.9 Compare impact of statements |
| 174 |
# SELECT col2 FROM mysqltest.my_aux WHERE col1 = 2 |
| 175 |
# SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1 |
| 176 |
# Both statements have the same length. |
| 177 |
# One statement earns an one row containing an empty string. |
| 178 |
# The other statement earns one row containing a string 1 byte long. |
| 179 |
# 4.4.10 Compare impact of statements |
| 180 |
# SELECT col2 FROM mysqltest.my_aux WHERE col1 = 3 |
| 181 |
# SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1 |
| 182 |
# Both statements have the same length. |
| 183 |
# One statement earns an one row containing an empty string. |
| 184 |
# The other statement earns one row containing a string 1024 byte long. |
| 185 |
# 4.4.11 Compare impact of statements |
| 186 |
# SELECT col2 FROM mysqltest.my_aux WHERE col1 < 2 |
| 187 |
# SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1 |
| 188 |
# Both statements have the same length. |
| 189 |
# One statement earns an one row containing an empty string. |
| 190 |
# The other statement earns two rows containing an empty string. |
| 191 |
# 4.5 Check the differences caused by Connects |
| 192 |
# Attention: Succesful Connects run an additional "DO 1". |
| 193 |
# 4.5.1 Connects do not charge anything into READ or WRITE counters |
| 194 |
# of the instance with EVENT_NAME NOT LIKE ('%client_connection%'). |
| 195 |
# This mean all these counters must be 0. |
| 196 |
# 4.5.2 Connects using for host the value 'localhost' |
| 197 |
# 4.5.2.1 For the instance with EVENT_NAME LIKE '%server_tcpip_socket' |
| 198 |
# COUNT_MISC = 0 AND SUM_TIMER_MISC = 0 must be valid |
| 199 |
# because we run through server_unix_socket. |
| 200 |
# 4.5.2.2 For the instance with EVENT_NAME LIKE '%server_unix_socket' |
| 201 |
# COUNT_MISC > 0 must be valid. |
| 202 |
# SKIPPED FOR WINDOWS |
| 203 |
# 4.5.3 Connects using for host a value <> 'localhost' |
| 204 |
# 4.5.3.1 For the instance with EVENT_NAME LIKE '%server_unix_socket' |
| 205 |
# COUNT_MISC = 0 AND SUM_TIMER_MISC = 0 must be valid |
| 206 |
# because we run through server_tcpip_socket. |
| 207 |
# SKIPPED FOR WINDOWS |
| 208 |
# 4.5.3.2 For the instance with EVENT_NAME LIKE '%server_tcpip_socket' |
| 209 |
# COUNT_MISC > 0 must be valid. |
| 210 |
# 4.5.4 Failing Connects do not cause any row with EVENT_NAME |
| 211 |
# LIKE '%client_connection' |
| 212 |
# 4.5.5 Successful Connects cause a new instance with EVENT_NAME |
| 213 |
# LIKE '%client_connection' |
| 214 |
# 4.6 Check the differences caused by Connects |
| 215 |
# - INSTANCES with an EVENT_NAME like server_tcpip_socket or |
| 216 |
# server_unix_socket are already checked |
| 217 |
# - the stability of results is already checked |
| 218 |
# So we con go with the results of the first run. |
| 219 |
# 4.6.1 The SUM_OF_BYTES_WRITE value depends on length of database |
| 220 |
# since the database name is written in OK packet. |
| 221 |
# Hence the value 2. |
| 222 |
# 4.6.2 The SUM_OF_BYTES_WRITE value hast to be > 100. |
| 223 |
# 4.6.3 COUNT_READ, COUNT_WRITE and COUNT_MISC have to be to be > 0 |
| 224 |
# 4.6.4 Checks based on comparison of results for connects |
| 225 |
# 4.6.4 The user name length affects the SUM_OF_BYTES_READ value |
| 226 |
# 4.6.5 The database name length affects the SUM_OF_BYTES_READ value |
| 227 |
# 5. Check the impact of TRUNCATE on socket_summary_by_instance. |
| 228 |
# It must reset all counters. |
| 229 |
TRUNCATE TABLE performance_schema.socket_summary_by_instance; |
| 230 |
# 6. Cleanup |
| 231 |
Warnings: |
| 232 |
Warning 1681 'performance_schema.setup_timers' is deprecated and will be removed in a future release. |