path: root/mysql-test/suite/perfschema/t/show_aggregate.test

################# 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
--source include/wait_for_pfs_thread_count.inc

--echo #
--echo # ================================================================================
--echo # SETUP
--echo # ================================================================================
SET @@session.sql_log_bin=OFF;
--echo #
--echo # CREATE 3 CLIENTS, 3 CONNECTIONS, RESULTS TABLE
connection default;
USE test;

flush status;

--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') and processlist_id;
SELECT thread_id INTO @con2_id FROM threads WHERE processlist_user IN ('user2') and processlist_id;
SELECT thread_id INTO @con3_id FROM threads WHERE processlist_user IN ('user3') and processlist_id;

--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;

--echo #
--echo # Verify expected counts for 'handler_delete' per thread
--echo # 
--replace_column 1 con_1
SELECT *, IF (variable_value = 1,'OK1','ERROR1') AS Expected FROM status_by_thread WHERE thread_id = @con1_id AND variable_name IN ('handler_delete', 'handler_rollback');
--echo #
--replace_column 1 con_2
SELECT *, IF (variable_value = 2,'OK2','ERROR2') AS Expected FROM status_by_thread WHERE thread_id = @con2_id AND variable_name IN ('handler_delete', 'handler_rollback');
--echo #
--replace_column 1 con_3
SELECT *, IF (variable_value = 3,'OK3','ERROR3') AS Expected FROM status_by_thread WHERE thread_id = @con3_id AND variable_name IN ('handler_delete', 'handler_rollback');

--echo #
--echo # STATUS_BY_THREAD vs. GLOBAL_STATUS
--echo #
SELECT variable_name, t1, t2, t3, delta, thread, IF(thread=delta,'OK4','ERROR4') 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,'OK5','ERROR5') AS Expected FROM status_by_user WHERE user IN ('user1') AND variable_name IN ('handler_delete');
--echo #
SELECT *, IF (variable_value = 2,'OK6','ERROR6') AS Expected FROM status_by_user WHERE user IN ('user2') AND variable_name IN ('handler_delete');
--echo #
SELECT *, IF (variable_value = 3,'OK7','ERROR7') 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,'OK8','ERROR8') 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,'OK9','ERROR9') AS Expected FROM status_by_account WHERE user IN ('user1') AND variable_name IN ('handler_delete');
--echo #
SELECT *, IF (variable_value = 2,'OKa','ERRORa') AS Expected FROM status_by_account WHERE user IN ('user2') AND variable_name IN ('handler_delete');
--echo #
SELECT *, IF (variable_value = 3,'OKb','ERRORb') 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,'OKc','ERRORc') 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,'OKd','ERRORd') 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,'OKe','ERRORe') 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 # ================================================================================
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;

let $wait_condition= select count(*) = 0 from performance_schema.threads where processlist_user like 'user%' and processlist_id;
--source include/wait_condition.inc

#--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,'OKf','ERRORf') AS Expected FROM status_by_user WHERE user IN ('user1') AND variable_name IN ('handler_delete');
--echo #
SELECT *, IF (variable_value = 2,'OKg','ERRORg') AS Expected FROM status_by_user WHERE user IN ('user2') AND variable_name IN ('handler_delete');
--echo #
SELECT *, IF (variable_value = 3,'OKh','ERRORh') 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,'OKi','ERRORi') 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,'OKj','ERRORj') AS Expected FROM status_by_account WHERE user IN ('user1') AND variable_name IN ('handler_delete');
--echo #
SELECT *, IF (variable_value = 2,'OKk','ERRORk') AS Expected FROM status_by_account WHERE user IN ('user2') AND variable_name IN ('handler_delete');
--echo #
SELECT *, IF (variable_value = 3,'OKl','ERRORl') 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,'OKn','ERRORn') 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,'OKo','ERRORo') 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,'OKp','ERRORp') 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 #