diff options
Diffstat (limited to 'mysql-test/suite/perfschema/t/show_aggregate.test')
| -rw-r--r-- | mysql-test/suite/perfschema/t/show_aggregate.test | 306 |
1 files changed, 306 insertions, 0 deletions
diff --git a/mysql-test/suite/perfschema/t/show_aggregate.test b/mysql-test/suite/perfschema/t/show_aggregate.test new file mode 100644 index 00000000000..3f01de15b01 --- /dev/null +++ b/mysql-test/suite/perfschema/t/show_aggregate.test @@ -0,0 +1,306 @@ +################# suite/perfschema/t/show_aggregate.test ####################### +# # +# This test verifies: # +# 1. Status variable values are consistent with corresponding server events. # +# 2. Status variable values are consistent across session, thread, account, # +# host, user and global tables. # +# 3. Status totals for user, host and account are retained after related # +# threads disconnect. # +################################################################################ + +--source include/have_perfschema.inc +--source include/have_innodb.inc +--source include/not_embedded.inc +--source include/no_protocol.inc +--source include/maybe_pool_of_threads.inc + +--enable_connect_log + +--echo # +--echo # ================================================================================ +--echo # SETUP +--echo # ================================================================================ +SET @@session.sql_log_bin=OFF; +--echo # +--echo # CREATE 3 CLIENTS, 3 CONNECTIONS, RESULTS TABLE +connection default; +USE test; + +# Clear user, host and account status accumulated from preliminary checks by mtr. +--disable_query_log +flush status; +--enable_query_log + +--echo # +--echo # Create results table +CREATE TABLE test.status_results + (variable_name VARCHAR(64), start INT DEFAULT 0, stop INT DEFAULT 0, delta INT DEFAULT 0, + t1 INT DEFAULT 0, t2 INT DEFAULT 0, t3 INT DEFAULT 0, thread INT DEFAULT 0, + u1 INT DEFAULT 0, u2 INT DEFAULT 0, u3 INT DEFAULT 0, user INT DEFAULT 0, + h1 INT DEFAULT 0, h2 INT DEFAULT 0, h3 INT DEFAULT 0, host INT DEFAULT 0, + a1 INT DEFAULT 0, a2 INT DEFAULT 0, a3 INT DEFAULT 0, acct INT DEFAULT 0); + +INSERT INTO test.status_results (variable_name, start) + SELECT sg.variable_name, sg.variable_value+0 FROM performance_schema.global_status sg + WHERE variable_name IN ('handler_delete', 'handler_rollback'); + +--echo +--echo # Create test tables +CREATE TABLE t1 (s1 int) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1), (2), (2), (3), (3), (3); +CREATE TABLE t2 (s1 int) ENGINE=InnoDB; +CREATE TABLE t3 (s1 int) ENGINE=InnoDB; +--echo # +set @orig_sql_mode= @@sql_mode; +set sql_mode= (select replace(@@sql_mode,'NO_AUTO_CREATE_USER','')); +GRANT ALL ON *.* to 'user1'@localhost; +GRANT ALL ON *.* to 'user2'@localhost; +GRANT ALL ON *.* to 'user3'@localhost; +set sql_mode= @orig_sql_mode; + +--echo # +--echo # ================================================================================ +--echo # CONNECTION 1: DELETE 1, ROLLBACK x 1 +--echo # ================================================================================ +connect(con1, localhost, user1,,); +SET @@session.sql_log_bin=OFF; +DELETE FROM t1 WHERE s1 = 1; +START TRANSACTION; +INSERT INTO t1 VALUES (1); +ROLLBACK; + +--echo # +--echo # ================================================================================ +--echo # CONNECTION 2: DELETE 2, ROLLBACK x 2 +--echo # ================================================================================ +connect(con2, localhost, user2,,); +SET @@session.sql_log_bin=OFF; +DELETE FROM t1 WHERE s1 = 2; +--echo # +START TRANSACTION; +INSERT INTO t1 VALUES (2); +ROLLBACK; +--echo # +START TRANSACTION; +INSERT INTO t1 VALUES (2); +ROLLBACK; + +--echo # +--echo # ================================================================================ +--echo # CONNECTION 3: DELETE 3, ROLLBACK x 3 +--echo # ================================================================================ +connect(con3, localhost, user3,,); +SET @@session.sql_log_bin=OFF; +DELETE FROM t1 WHERE s1 = 3; +--echo # +START TRANSACTION; +INSERT INTO t1 VALUES (3); +ROLLBACK; +--echo # +START TRANSACTION; +INSERT INTO t1 VALUES (3); +ROLLBACK; +--echo # +START TRANSACTION; +INSERT INTO t1 VALUES (3); +ROLLBACK; + +--echo # +--echo # ================================================================================ +--echo # CONNECTION DEFAULT: Gather results, compare session and global status counts +--echo # ================================================================================ +connection default; +--echo # Get thread ids for each connection. +USE performance_schema; +SELECT thread_id INTO @con1_id FROM threads WHERE processlist_user IN ('user1'); +SELECT thread_id INTO @con2_id FROM threads WHERE processlist_user IN ('user2'); +SELECT thread_id INTO @con3_id FROM threads WHERE processlist_user IN ('user3'); + +--source ../include/show_aggregate.inc + +#--echo DEBUG +#USE test; +#SELECT * FROM test.status_results; + +--echo # +--echo # ================================================================================ +--echo # TEST 1: STATUS_BY_THREAD: Verify expected status counts per thread (1,2,3) +--echo # ================================================================================ +--echo # +--echo # Review per-thread status counts +--echo # +USE performance_schema; +--replace_column 1 thread_id +SELECT * FROM status_by_thread WHERE thread_id IN (@con1_id, @con2_id, @con3_id) AND variable_name IN ('handler_delete', 'handler_rollback') ORDER BY thread_id; + +--echo # +--echo # Verify expected counts for 'handler_delete' per thread +--echo # +--replace_column 1 connect_1 +SELECT *, IF (variable_value = 1,'OK','ERROR') AS Expected FROM status_by_thread WHERE thread_id = @con1_id AND variable_name IN ('handler_delete'); +--echo # +--replace_column 1 connnect_2 +SELECT *, IF (variable_value = 2,'OK','ERROR') AS Expected FROM status_by_thread WHERE thread_id = @con2_id AND variable_name IN ('handler_delete'); +--echo # +--replace_column 1 connnect_3 +SELECT *, IF (variable_value = 3,'OK','ERROR') AS Expected FROM status_by_thread WHERE thread_id = @con3_id AND variable_name IN ('handler_delete'); + +--echo # +--echo # STATUS_BY_THREAD vs. GLOBAL_STATUS +--echo # +SELECT variable_name, t1, t2, t3, delta, thread, IF(thread=delta,'OK','ERROR') Expected +FROM test.status_results +ORDER BY variable_name; + +--echo # +--echo # ================================================================================ +--echo # TEST 2: STATUS_BY_USER: Verify expected status counts per user (1,2,3) +--echo # ================================================================================ +SELECT *, IF (variable_value = 1,'OK','ERROR') AS Expected FROM status_by_user WHERE user IN ('user1') AND variable_name IN ('handler_delete'); +--echo # +SELECT *, IF (variable_value = 2,'OK','ERROR') AS Expected FROM status_by_user WHERE user IN ('user2') AND variable_name IN ('handler_delete'); +--echo # +SELECT *, IF (variable_value = 3,'OK','ERROR') AS Expected FROM status_by_user WHERE user IN ('user3') AND variable_name IN ('handler_delete'); + +--echo # +--echo # STATUS_BY_USER vs. GLOBAL_STATUS +--echo # +SELECT variable_name, u1, u2, u3, delta, user, IF(user=delta,'OK','ERROR') Expected +FROM test.status_results +ORDER BY variable_name; + +--echo # +--echo # ================================================================================ +--echo # TEST 3: STATUS_BY_ACCOUNT: Verify expected status counts per user, host (1,2,3) +--echo # ================================================================================ +SELECT *, IF (variable_value = 1,'OK','ERROR') AS Expected FROM status_by_account WHERE user IN ('user1') AND variable_name IN ('handler_delete'); +--echo # +SELECT *, IF (variable_value = 2,'OK','ERROR') AS Expected FROM status_by_account WHERE user IN ('user2') AND variable_name IN ('handler_delete'); +--echo # +SELECT *, IF (variable_value = 3,'OK','ERROR') AS Expected FROM status_by_account WHERE user IN ('user3') AND variable_name IN ('handler_delete'); + +--echo # +--echo # STATUS_BY_ACCOUNT vs. GLOBAL_STATUS +--echo # +SELECT variable_name, a1, a2, a3, delta, acct, IF(acct=delta,'OK','ERROR') Expected +FROM test.status_results +ORDER BY variable_name; + +--echo # ================================================================================ +--echo # TEST 4: STATUS_BY_HOST: Verify expected status counts per host (6) +--echo # ================================================================================ +SELECT *, IF (variable_value = 6,'OK','ERROR') AS Expected FROM status_by_host WHERE host IN ('localhost') AND variable_name IN ('handler_delete'); + +--echo # +--echo # STATUS_BY_HOST vs. GLOBAL_STATUS +--echo # +--echo # Special case: No way to isolate pre-existing 'localhost' activity, so +--echo # just check global totals = sum(status_by_host). +--echo # +SELECT variable_name, h1, h2, h3, delta, host, IF(host=delta,'OK','ERROR') Expected +FROM test.status_results +ORDER BY variable_name; + +--echo # +--echo # ================================================================================ +--echo # DISCONNECT ALL USERS AND RUN THE TESTS AGAIN. RESULTS SHOULD NOT CHANGE. +--echo # ================================================================================ +connection default; +disconnect con1; +disconnect con2; +disconnect con3; +--echo # +USE test; +--echo # +--echo # Clear results table, leave initial global_status counts +UPDATE test.status_results + SET stop=0, delta=0, + t1=0, t2=0, t3=0, thread=0, + u1=0, u2=0, u3=0, user=0, + h1=0, h2=0, h3=0, host=0, + a1=0, a2=0, a3=0, acct=0; + +#--echo DEBUG +#SELECT * FROM test.status_results; +--echo # +--source ../include/show_aggregate.inc + +USE performance_schema; +--echo # +--echo # ================================================================================ +--echo # TEST X: STATUS_BY_THREAD: Connections are gone--nothing to verify. +--echo # ================================================================================ +--echo # +--echo # ================================================================================ +--echo # TEST 5: STATUS_BY_USER: Verify expected status counts per user (1,2,3) +--echo # ================================================================================ +SELECT *, IF (variable_value = 1,'OK','ERROR') AS Expected FROM status_by_user WHERE user IN ('user1') AND variable_name IN ('handler_delete'); +--echo # +SELECT *, IF (variable_value = 2,'OK','ERROR') AS Expected FROM status_by_user WHERE user IN ('user2') AND variable_name IN ('handler_delete'); +--echo # +SELECT *, IF (variable_value = 3,'OK','ERROR') AS Expected FROM status_by_user WHERE user IN ('user3') AND variable_name IN ('handler_delete'); + +--echo # +--echo # STATUS_BY_USER vs. GLOBAL_STATUS +--echo # +SELECT variable_name, u1, u2, u3, delta, user, IF(user=delta,'OK','ERROR') Expected +FROM test.status_results +ORDER BY variable_name; + +--echo # +--echo # ================================================================================ +--echo # TEST 6: STATUS_BY_ACCOUNT: Verify expected status counts per user:host (1,2,3) +--echo # ================================================================================ +SELECT *, IF (variable_value = 1,'OK','ERROR') AS Expected FROM status_by_account WHERE user IN ('user1') AND variable_name IN ('handler_delete'); +--echo # +SELECT *, IF (variable_value = 2,'OK','ERROR') AS Expected FROM status_by_account WHERE user IN ('user2') AND variable_name IN ('handler_delete'); +--echo # +SELECT *, IF (variable_value = 3,'OK','ERROR') AS Expected FROM status_by_account WHERE user IN ('user3') AND variable_name IN ('handler_delete'); + +--echo # +--echo # STATUS_BY_ACCOUNT vs. GLOBAL_STATUS +--echo # +SELECT variable_name, a1, a2, a3, delta, acct, IF(acct=delta,'OK','ERROR') Expected +FROM test.status_results +ORDER BY variable_name; + +--echo # ================================================================================ +--echo # TEST 7: STATUS_BY_HOST: Verify expected status counts per host (6) +--echo # ================================================================================ +SELECT *, IF (variable_value = 6,'OK','ERROR') AS Expected FROM status_by_host WHERE host IN ('localhost') AND variable_name IN ('handler_delete'); + +--echo # +--echo # STATUS_BY_HOST vs. GLOBAL_STATUS +--echo # +--echo # Special case: No way to isolate pre-existing 'localhost' activity, so +--echo # just check global totals = sum(status_by_host). +--echo # +SELECT variable_name, h1, h2, h3, delta, host, IF(host=delta,'OK','ERROR') Expected +FROM test.status_results +ORDER BY variable_name; + + +--echo # ================================================================================ +--echo # TEST 8: FLUSH STATUS should clear account, host and user status +--echo # ================================================================================ +--echo # +FLUSH STATUS; +--echo # +SELECT * FROM status_by_account WHERE user IN ('user1', 'user2', 'user3') AND variable_name IN ('handler_delete'); +--echo # +SELECT * FROM status_by_host WHERE host IN ('localhost') AND variable_name IN ('handler_delete'); +--echo # +SELECT * FROM status_by_user WHERE user IN ('user1', 'user2', 'user3') AND variable_name IN ('handler_delete'); + + +--echo # ================================================================================ +--echo # CLEANUP +--echo # ================================================================================ +DROP TABLE test.t1; +DROP TABLE test.t2; +DROP TABLE test.t3; +DROP TABLE test.status_results; +DROP USER 'user1'@localhost; +DROP USER 'user2'@localhost; +DROP USER 'user3'@localhost; +--echo # |