1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
|
# 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
Warnings:
Warning 1681 'performance_schema.setup_timers' is deprecated and will be removed in a future release.
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
SET @orig_sql_mode= @@sql_mode;
SET sql_mode= (SELECT replace(@@sql_mode,'NO_AUTO_CREATE_USER',''));
Warnings:
Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
GRANT ALL PRIVILEGES ON *.* TO 'root012345'@'localhost';
Warnings:
Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement.
SET sql_mode= @orig_sql_mode;
Warnings:
Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
DROP USER 'root012345'@'localhost';
# 2.6 Connect should pass, host = localhost
# length of user name = 14 character
# length of default db = 9 character
SET @orig_sql_mode= @@sql_mode;
SET sql_mode= (SELECT replace(@@sql_mode,'NO_AUTO_CREATE_USER',''));
Warnings:
Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
GRANT ALL PRIVILEGES ON *.* to 'root0123456789'@'localhost';
Warnings:
Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement.
SET sql_mode= @orig_sql_mode;
Warnings:
Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
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
# 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 depends on length of database
# since the database name is written in OK packet.
# Hence the value 2.
# 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
Warnings:
Warning 1681 'performance_schema.setup_timers' is deprecated and will be removed in a future release.
|
