mirror of
https://github.com/MariaDB/server.git
synced 2025-08-01 03:47:19 +03:00
bead24b7f3
Remove one of the major sources of race condiitons in mariadb-test. Normally, mariadb_close() sends COM_QUIT to the server and immediately disconnects. In mariadb-test it means the test can switch to another connection and sends queries to the server before the server even started parsing the COM_QUIT packet and these queries can see the connection as fully active, as it didn't reach dispatch_command yet. This is a major source of instability in tests and many - but not all, still less than a half - tests employ workarounds. The correct one is a pair count_sessions.inc/wait_until_count_sessions.inc. Also very popular was wait_until_disconnected.inc, which was completely useless, because it verifies that the connection is closed, and after disconnect it always is, it didn't verify whether the server processed COM_QUIT. Sadly the placebo was as widely used as the real thing. Let's fix this by making mariadb-test `disconnect` command _to wait_ for the server to confirm. This makes almost all workarounds redundant. In some cases count_sessions.inc/wait_until_count_sessions.inc is still needed, though, as only `disconnect` command is changed: * after external tools, like `exec $MYSQL` * after failed `connect` command * replication, after `STOP SLAVE` * Federated/CONNECT/SPIDER/etc after `DROP TABLE` and also in some XA tests, because an XA transaction is dissociated from the THD very late, after the server has closed the client connection. Collateral cleanups: fix comments, remove some redundant statements: * DROP IF EXISTS if nothing is known to exist * DROP table/view before DROP DATABASE * REVOKE privileges before DROP USER etc
222 lines
11 KiB
Plaintext
222 lines
11 KiB
Plaintext
# 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
|
|
CREATE USER 'root012345'@'localhost';
|
|
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
|
|
CREATE USER 'root0123456789'@'localhost';
|
|
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 !
|
|
connect con1,localhost,root,,mysqltest,,;
|
|
DO 1;
|
|
connection default;
|
|
# 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
|
|
connection default;
|
|
# 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
|
|
connection default;
|
|
# 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
|
|
connection default;
|
|
# 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
|
|
connection default;
|
|
# 3.5 SELECT earning an empty result set.
|
|
SELECT col2 FROM mysqltest.my_aux WHERE col1 = -1;
|
|
col2
|
|
connection default;
|
|
# 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
|
|
connection default;
|
|
# 3.7 SELECT earning one row with an empty string.
|
|
SELECT col2 FROM mysqltest.my_aux WHERE col1 = 1;
|
|
col2
|
|
|
|
connection default;
|
|
# 3.8 SELECT earning one row with one string one char long.
|
|
SELECT col2 FROM mysqltest.my_aux WHERE col1 = 2;
|
|
col2
|
|
a
|
|
connection default;
|
|
# 3.9 SELECT earning one row with one string 1024 char long.
|
|
SELECT col2 FROM mysqltest.my_aux WHERE col1 = 3;
|
|
col2
|
|
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
|
|
connection default;
|
|
# 3.10 SELECT earning two rows with an empty string
|
|
SELECT col2 FROM mysqltest.my_aux WHERE col1 < 2;
|
|
col2
|
|
|
|
|
|
connection default;
|
|
# 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: Successful 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 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
|