mirror of
https://github.com/MariaDB/server.git
synced 2025-08-01 03:47:19 +03:00
e3d9369774
let's always disconnect a user connection before dropping the said user. MariaDB is traditionally very tolerant to active connections of the dropped user, which isn't the case for most other databases. Let's avoid unintentionally spreading incompatible behavior and disconnect before drop. Except in cases when the test specifically tests such a behavior.
670 lines
27 KiB
Plaintext
670 lines
27 KiB
Plaintext
# Check the impact of changes done in HISTORY column in
|
|
# performance_schema.setup_actors.
|
|
|
|
--source include/not_embedded.inc
|
|
--source include/have_perfschema.inc
|
|
--source include/no_protocol.inc
|
|
|
|
# Presence of the query cache changes query stages,
|
|
# causing noise in the test output
|
|
#--source include/have_query_cache_disabled.inc
|
|
|
|
create user user1@localhost;
|
|
create user user2@localhost;
|
|
create user user3@localhost;
|
|
create user user4@localhost;
|
|
|
|
grant ALL on *.* to user1@localhost;
|
|
grant ALL on *.* to user2@localhost;
|
|
grant ALL on *.* to user3@localhost;
|
|
grant ALL on *.* to user4@localhost;
|
|
|
|
flush privileges;
|
|
|
|
select * from performance_schema.setup_consumers;
|
|
|
|
connect (con1, localhost, user1, , );
|
|
|
|
update performance_schema.threads
|
|
set INSTRUMENTED='YES', HISTORY='YES'
|
|
where PROCESSLIST_ID = connection_id();
|
|
|
|
let $con1_thread_id= `select THREAD_ID from performance_schema.threads
|
|
where PROCESSLIST_ID = connection_id()`;
|
|
|
|
connect (con2, localhost, user2, , );
|
|
|
|
update performance_schema.threads
|
|
set INSTRUMENTED='YES', HISTORY='NO'
|
|
where PROCESSLIST_ID = connection_id();
|
|
|
|
let $con2_thread_id= `select THREAD_ID from performance_schema.threads
|
|
where PROCESSLIST_ID = connection_id()`;
|
|
|
|
connect (con3, localhost, user3, , );
|
|
|
|
update performance_schema.threads
|
|
set INSTRUMENTED='NO', HISTORY='YES'
|
|
where PROCESSLIST_ID = connection_id();
|
|
|
|
let $con3_thread_id= `select THREAD_ID from performance_schema.threads
|
|
where PROCESSLIST_ID = connection_id()`;
|
|
|
|
connect (con4, localhost, user4, , );
|
|
|
|
update performance_schema.threads
|
|
set INSTRUMENTED='NO', HISTORY='NO'
|
|
where PROCESSLIST_ID = connection_id();
|
|
|
|
let $con4_thread_id= `select THREAD_ID from performance_schema.threads
|
|
where PROCESSLIST_ID = connection_id()`;
|
|
|
|
--connection default
|
|
|
|
truncate table performance_schema.events_transactions_current;
|
|
truncate table performance_schema.events_transactions_history;
|
|
truncate table performance_schema.events_transactions_history_long;
|
|
truncate table performance_schema.events_statements_current;
|
|
truncate table performance_schema.events_statements_history;
|
|
truncate table performance_schema.events_statements_history_long;
|
|
truncate table performance_schema.events_stages_current;
|
|
truncate table performance_schema.events_stages_history;
|
|
truncate table performance_schema.events_stages_history_long;
|
|
truncate table performance_schema.events_waits_current;
|
|
truncate table performance_schema.events_waits_history;
|
|
truncate table performance_schema.events_waits_history_long;
|
|
|
|
--connection con1
|
|
|
|
XA START 'XA_CON1', 'XA_BQUAL', 12;
|
|
select "Hi from con1";
|
|
XA END 'XA_CON1', 'XA_BQUAL', 12;
|
|
XA PREPARE 'XA_CON1', 'XA_BQUAL', 12;
|
|
XA COMMIT 'XA_CON1', 'XA_BQUAL', 12;
|
|
|
|
--connection con2
|
|
|
|
XA START 'XA_CON2', 'XA_BQUAL', 12;
|
|
select "Hi from con2";
|
|
XA END 'XA_CON2', 'XA_BQUAL', 12;
|
|
XA PREPARE 'XA_CON2', 'XA_BQUAL', 12;
|
|
XA COMMIT 'XA_CON2', 'XA_BQUAL', 12;
|
|
|
|
--connection con3
|
|
|
|
XA START 'XA_CON3', 'XA_BQUAL', 12;
|
|
select "Hi from con3";
|
|
XA END 'XA_CON3', 'XA_BQUAL', 12;
|
|
XA PREPARE 'XA_CON3', 'XA_BQUAL', 12;
|
|
XA COMMIT 'XA_CON3', 'XA_BQUAL', 12;
|
|
|
|
--connection con4
|
|
|
|
XA START 'XA_CON4', 'XA_BQUAL', 12;
|
|
select "Hi from con4";
|
|
XA END 'XA_CON4', 'XA_BQUAL', 12;
|
|
XA PREPARE 'XA_CON4', 'XA_BQUAL', 12;
|
|
XA COMMIT 'XA_CON4', 'XA_BQUAL', 12;
|
|
|
|
--connection default
|
|
|
|
echo ########################### Transactions user 1 - 1;
|
|
|
|
evalp select /*1-1*/ XID_FORMAT_ID, XID_GTRID, XID_BQUAL from performance_schema.events_transactions_current
|
|
where THREAD_ID = $con1_thread_id;
|
|
evalp select /*1-1*/ XID_FORMAT_ID, XID_GTRID, XID_BQUAL from performance_schema.events_transactions_history
|
|
where THREAD_ID = $con1_thread_id;
|
|
evalp select /*1-1*/ XID_FORMAT_ID, XID_GTRID, XID_BQUAL from performance_schema.events_transactions_history_long
|
|
where THREAD_ID = $con1_thread_id;
|
|
|
|
echo ########################### Transactions user 2 - 1;
|
|
|
|
evalp select /*2-1*/ XID_FORMAT_ID, XID_GTRID, XID_BQUAL from performance_schema.events_transactions_current
|
|
where THREAD_ID = $con2_thread_id;
|
|
evalp select /*2-1*/ count(*) from performance_schema.events_transactions_history
|
|
where THREAD_ID = $con2_thread_id;
|
|
evalp select /*2-1*/ count(*) from performance_schema.events_transactions_history_long
|
|
where THREAD_ID = $con2_thread_id;
|
|
|
|
echo ########################### Transactions user 3 - 1;
|
|
|
|
evalp select /*3-1*/ count(*) from performance_schema.events_transactions_current
|
|
where THREAD_ID = $con3_thread_id;
|
|
evalp select /*3-1*/ count(*) from performance_schema.events_transactions_history
|
|
where THREAD_ID = $con3_thread_id;
|
|
evalp select /*3-1*/ count(*) from performance_schema.events_transactions_history_long
|
|
where THREAD_ID = $con3_thread_id;
|
|
|
|
echo ########################### Transactions user 4 - 1;
|
|
|
|
evalp select /*4-1*/ count(*) from performance_schema.events_transactions_current
|
|
where THREAD_ID = $con4_thread_id;
|
|
evalp select /*4-1*/ count(*) from performance_schema.events_transactions_history
|
|
where THREAD_ID = $con4_thread_id;
|
|
evalp select /*4-1*/ count(*) from performance_schema.events_transactions_history_long
|
|
where THREAD_ID = $con4_thread_id;
|
|
|
|
echo ########################### Statements user 1 - 1;
|
|
|
|
evalp select /*1-1*/ EVENT_NAME, SQL_TEXT from performance_schema.events_statements_current
|
|
where THREAD_ID = $con1_thread_id order by THREAD_ID, EVENT_ID;
|
|
evalp select /*1-1*/ EVENT_NAME, SQL_TEXT from performance_schema.events_statements_history
|
|
where THREAD_ID = $con1_thread_id order by THREAD_ID, EVENT_ID;
|
|
evalp select /*1-1*/ EVENT_NAME, SQL_TEXT from performance_schema.events_statements_history_long
|
|
where THREAD_ID = $con1_thread_id order by THREAD_ID, EVENT_ID;
|
|
|
|
echo ########################### Statements user 2 - 1;
|
|
|
|
evalp select /*2-1*/ EVENT_NAME, SQL_TEXT from performance_schema.events_statements_current
|
|
where THREAD_ID = $con2_thread_id order by THREAD_ID, EVENT_ID;
|
|
evalp select /*2-1*/ count(*) from performance_schema.events_statements_history
|
|
where THREAD_ID = $con2_thread_id;
|
|
evalp select /*2-1*/ count(*) from performance_schema.events_statements_history_long
|
|
where THREAD_ID = $con2_thread_id;
|
|
|
|
echo ########################### Statements user 3 - 1;
|
|
|
|
evalp select /*3-1*/ count(*) from performance_schema.events_statements_current
|
|
where THREAD_ID = $con3_thread_id;
|
|
evalp select /*3-1*/ count(*) from performance_schema.events_statements_history
|
|
where THREAD_ID = $con3_thread_id;
|
|
evalp select /*3-1*/ count(*) from performance_schema.events_statements_history_long
|
|
where THREAD_ID = $con3_thread_id;
|
|
|
|
echo ########################### Statements user 4 - 1;
|
|
|
|
evalp select /*4-1*/ count(*) from performance_schema.events_statements_current
|
|
where THREAD_ID = $con4_thread_id;
|
|
evalp select /*4-1*/ count(*) from performance_schema.events_statements_history
|
|
where THREAD_ID = $con4_thread_id;
|
|
evalp select /*4-1*/ count(*) from performance_schema.events_statements_history_long
|
|
where THREAD_ID = $con4_thread_id;
|
|
|
|
echo ########################### Stages user 1 - 1;
|
|
|
|
evalp select /*1-1*/ EVENT_NAME from performance_schema.events_stages_current
|
|
where THREAD_ID = $con1_thread_id order by THREAD_ID, EVENT_ID;
|
|
evalp select /*1-1*/ EVENT_NAME from performance_schema.events_stages_history
|
|
where THREAD_ID = $con1_thread_id order by THREAD_ID, EVENT_ID;
|
|
evalp select /*1-1*/ EVENT_NAME from performance_schema.events_stages_history_long
|
|
where THREAD_ID = $con1_thread_id order by THREAD_ID, EVENT_ID;
|
|
|
|
echo ########################### Stages user 2 - 1;
|
|
|
|
evalp select /*2-1*/ EVENT_NAME from performance_schema.events_stages_current
|
|
where THREAD_ID = $con2_thread_id order by THREAD_ID, EVENT_ID;
|
|
evalp select /*2-1*/ count(*) from performance_schema.events_stages_history
|
|
where THREAD_ID = $con2_thread_id;
|
|
evalp select /*2-1*/ count(*) from performance_schema.events_stages_history_long
|
|
where THREAD_ID = $con2_thread_id;
|
|
|
|
echo ########################### Stages user 3 - 1;
|
|
|
|
evalp select /*3-1*/ count(*) from performance_schema.events_stages_current
|
|
where THREAD_ID = $con3_thread_id;
|
|
evalp select /*3-1*/ count(*) from performance_schema.events_stages_history
|
|
where THREAD_ID = $con3_thread_id;
|
|
evalp select /*3-1*/ count(*) from performance_schema.events_stages_history_long
|
|
where THREAD_ID = $con3_thread_id;
|
|
|
|
echo ########################### Stages user 4 - 1;
|
|
|
|
evalp select /*4-1*/ count(*) from performance_schema.events_stages_current
|
|
where THREAD_ID = $con4_thread_id;
|
|
evalp select /*4-1*/ count(*) from performance_schema.events_stages_history
|
|
where THREAD_ID = $con4_thread_id;
|
|
evalp select /*4-1*/ count(*) from performance_schema.events_stages_history_long
|
|
where THREAD_ID = $con4_thread_id;
|
|
|
|
echo ########################### Waits user 1 - 1;
|
|
|
|
evalp select /*1-1*/ EVENT_NAME from performance_schema.events_waits_current
|
|
where THREAD_ID = $con1_thread_id order by THREAD_ID, EVENT_ID;
|
|
evalp select /*1-1*/ (count(*) > 5) as has_waits from performance_schema.events_waits_history
|
|
where THREAD_ID = $con1_thread_id;
|
|
evalp select /*1-1*/ (count(*) > 15) as has_waits from performance_schema.events_waits_history_long
|
|
where THREAD_ID = $con1_thread_id;
|
|
|
|
echo ########################### Waits user 2 - 1;
|
|
|
|
evalp select /*2-1*/ EVENT_NAME from performance_schema.events_waits_current
|
|
where THREAD_ID = $con2_thread_id order by THREAD_ID, EVENT_ID;
|
|
evalp select /*2-1*/ count(*) from performance_schema.events_waits_history
|
|
where THREAD_ID = $con2_thread_id;
|
|
evalp select /*2-1*/ count(*) from performance_schema.events_waits_history_long
|
|
where THREAD_ID = $con2_thread_id;
|
|
|
|
echo ########################### Waits user 3 - 1;
|
|
|
|
evalp select /*3-1*/ count(*) from performance_schema.events_waits_current
|
|
where THREAD_ID = $con3_thread_id;
|
|
evalp select /*3-1*/ count(*) from performance_schema.events_waits_history
|
|
where THREAD_ID = $con3_thread_id;
|
|
evalp select /*3-1*/ count(*) from performance_schema.events_waits_history_long
|
|
where THREAD_ID = $con3_thread_id;
|
|
|
|
echo ########################### Waits user 4 - 1;
|
|
|
|
evalp select /*4-1*/ count(*) from performance_schema.events_waits_current
|
|
where THREAD_ID = $con4_thread_id;
|
|
evalp select /*4-1*/ count(*) from performance_schema.events_waits_history
|
|
where THREAD_ID = $con4_thread_id;
|
|
evalp select /*4-1*/ count(*) from performance_schema.events_waits_history_long
|
|
where THREAD_ID = $con4_thread_id;
|
|
|
|
--connection default
|
|
|
|
update performance_schema.setup_consumers
|
|
set enabled='NO' where name like "%history%";
|
|
|
|
select * from performance_schema.setup_consumers;
|
|
|
|
truncate table performance_schema.events_transactions_current;
|
|
truncate table performance_schema.events_transactions_history;
|
|
truncate table performance_schema.events_transactions_history_long;
|
|
truncate table performance_schema.events_statements_current;
|
|
truncate table performance_schema.events_statements_history;
|
|
truncate table performance_schema.events_statements_history_long;
|
|
truncate table performance_schema.events_stages_current;
|
|
truncate table performance_schema.events_stages_history;
|
|
truncate table performance_schema.events_stages_history_long;
|
|
truncate table performance_schema.events_waits_current;
|
|
truncate table performance_schema.events_waits_history;
|
|
truncate table performance_schema.events_waits_history_long;
|
|
|
|
--connection con1
|
|
|
|
XA START 'XA_CON1', 'XA_BQUAL', 12;
|
|
select "Hi from con1";
|
|
XA END 'XA_CON1', 'XA_BQUAL', 12;
|
|
XA PREPARE 'XA_CON1', 'XA_BQUAL', 12;
|
|
XA COMMIT 'XA_CON1', 'XA_BQUAL', 12;
|
|
|
|
--connection con2
|
|
|
|
XA START 'XA_CON2', 'XA_BQUAL', 12;
|
|
select "Hi from con2";
|
|
XA END 'XA_CON2', 'XA_BQUAL', 12;
|
|
XA PREPARE 'XA_CON2', 'XA_BQUAL', 12;
|
|
XA COMMIT 'XA_CON2', 'XA_BQUAL', 12;
|
|
|
|
--connection con3
|
|
|
|
XA START 'XA_CON3', 'XA_BQUAL', 12;
|
|
select "Hi from con3";
|
|
XA END 'XA_CON3', 'XA_BQUAL', 12;
|
|
XA PREPARE 'XA_CON3', 'XA_BQUAL', 12;
|
|
XA COMMIT 'XA_CON3', 'XA_BQUAL', 12;
|
|
|
|
--connection con4
|
|
|
|
XA START 'XA_CON4', 'XA_BQUAL', 12;
|
|
select "Hi from con4";
|
|
XA END 'XA_CON4', 'XA_BQUAL', 12;
|
|
XA PREPARE 'XA_CON4', 'XA_BQUAL', 12;
|
|
XA COMMIT 'XA_CON4', 'XA_BQUAL', 12;
|
|
|
|
--connection default
|
|
|
|
echo ########################### Transactions user 1 - 2;
|
|
|
|
evalp select /*1-2*/ XID_FORMAT_ID, XID_GTRID, XID_BQUAL from performance_schema.events_transactions_current
|
|
where THREAD_ID = $con1_thread_id;
|
|
evalp select /*1-2*/ count(*) from performance_schema.events_transactions_history
|
|
where THREAD_ID = $con1_thread_id;
|
|
evalp select /*1-2*/ count(*) from performance_schema.events_transactions_history_long
|
|
where THREAD_ID = $con1_thread_id;
|
|
|
|
echo ########################### Transactions user 2 - 2;
|
|
|
|
evalp select /*2-2*/ XID_FORMAT_ID, XID_GTRID, XID_BQUAL from performance_schema.events_transactions_current
|
|
where THREAD_ID = $con2_thread_id;
|
|
evalp select /*2-2*/ count(*) from performance_schema.events_transactions_history
|
|
where THREAD_ID = $con2_thread_id;
|
|
evalp select /*2-2*/ count(*) from performance_schema.events_transactions_history_long
|
|
where THREAD_ID = $con2_thread_id;
|
|
|
|
echo ########################### Transactions user 3 - 2;
|
|
|
|
evalp select /*3-2*/ count(*) from performance_schema.events_transactions_current
|
|
where THREAD_ID = $con3_thread_id;
|
|
evalp select /*3-2*/ count(*) from performance_schema.events_transactions_history
|
|
where THREAD_ID = $con3_thread_id;
|
|
evalp select /*3-2*/ count(*) from performance_schema.events_transactions_history_long
|
|
where THREAD_ID = $con3_thread_id;
|
|
|
|
echo ########################### Transactions user 4 - 2;
|
|
|
|
evalp select /*4-2*/ count(*) from performance_schema.events_transactions_current
|
|
where THREAD_ID = $con4_thread_id;
|
|
evalp select /*4-2*/ count(*) from performance_schema.events_transactions_history
|
|
where THREAD_ID = $con4_thread_id;
|
|
evalp select /*4-2*/ count(*) from performance_schema.events_transactions_history_long
|
|
where THREAD_ID = $con4_thread_id;
|
|
|
|
echo ########################### Statements user 1 - 2;
|
|
|
|
evalp select /*1-2*/ EVENT_NAME, SQL_TEXT from performance_schema.events_statements_current
|
|
where THREAD_ID = $con1_thread_id order by THREAD_ID, EVENT_ID;
|
|
evalp select /*1-2*/ count(*) from performance_schema.events_statements_history
|
|
where THREAD_ID = $con1_thread_id;
|
|
evalp select /*1-2*/ count(*) from performance_schema.events_statements_history_long
|
|
where THREAD_ID = $con1_thread_id;
|
|
|
|
echo ########################### Statements user 2 - 2;
|
|
|
|
evalp select /*2-2*/ EVENT_NAME, SQL_TEXT from performance_schema.events_statements_current
|
|
where THREAD_ID = $con2_thread_id order by THREAD_ID, EVENT_ID;
|
|
evalp select /*2-2*/ count(*) from performance_schema.events_statements_history
|
|
where THREAD_ID = $con2_thread_id;
|
|
evalp select /*2-2*/ count(*) from performance_schema.events_statements_history_long
|
|
where THREAD_ID = $con2_thread_id;
|
|
|
|
echo ########################### Statements user 3 - 2;
|
|
|
|
evalp select /*3-2*/ count(*) from performance_schema.events_statements_current
|
|
where THREAD_ID = $con3_thread_id;
|
|
evalp select /*3-2*/ count(*) from performance_schema.events_statements_history
|
|
where THREAD_ID = $con3_thread_id;
|
|
evalp select /*3-2*/ count(*) from performance_schema.events_statements_history_long
|
|
where THREAD_ID = $con3_thread_id;
|
|
|
|
echo ########################### Statements user 4 - 2;
|
|
|
|
evalp select /*4-2*/ count(*) from performance_schema.events_statements_current
|
|
where THREAD_ID = $con4_thread_id;
|
|
evalp select /*4-2*/ count(*) from performance_schema.events_statements_history
|
|
where THREAD_ID = $con4_thread_id;
|
|
evalp select /*4-2*/ count(*) from performance_schema.events_statements_history_long
|
|
where THREAD_ID = $con4_thread_id;
|
|
|
|
echo ########################### Stages user 1 - 2;
|
|
|
|
evalp select /*1-2*/ EVENT_NAME from performance_schema.events_stages_current
|
|
where THREAD_ID = $con1_thread_id order by THREAD_ID, EVENT_ID;
|
|
evalp select /*1-2*/ count(*) from performance_schema.events_stages_history
|
|
where THREAD_ID = $con1_thread_id;
|
|
evalp select /*1-2*/ count(*) from performance_schema.events_stages_history_long
|
|
where THREAD_ID = $con1_thread_id;
|
|
|
|
echo ########################### Stages user 2 - 2;
|
|
|
|
evalp select /*2-2*/ EVENT_NAME from performance_schema.events_stages_current
|
|
where THREAD_ID = $con2_thread_id order by THREAD_ID, EVENT_ID;
|
|
evalp select /*2-2*/ count(*) from performance_schema.events_stages_history
|
|
where THREAD_ID = $con2_thread_id;
|
|
evalp select /*2-2*/ count(*) from performance_schema.events_stages_history_long
|
|
where THREAD_ID = $con2_thread_id;
|
|
|
|
echo ########################### Stages user 3 - 2;
|
|
|
|
evalp select /*3-2*/ count(*) from performance_schema.events_stages_current
|
|
where THREAD_ID = $con3_thread_id;
|
|
evalp select /*3-2*/ count(*) from performance_schema.events_stages_history
|
|
where THREAD_ID = $con3_thread_id;
|
|
evalp select /*3-2*/ count(*) from performance_schema.events_stages_history_long
|
|
where THREAD_ID = $con3_thread_id;
|
|
|
|
echo ########################### Stages user 4 - 2;
|
|
|
|
evalp select /*4-2*/ count(*) from performance_schema.events_stages_current
|
|
where THREAD_ID = $con4_thread_id;
|
|
evalp select /*4-2*/ count(*) from performance_schema.events_stages_history
|
|
where THREAD_ID = $con4_thread_id;
|
|
evalp select /*4-2*/ count(*) from performance_schema.events_stages_history_long
|
|
where THREAD_ID = $con4_thread_id;
|
|
|
|
echo ########################### Waits user 1 - 2;
|
|
|
|
evalp select /*1-2*/ EVENT_NAME from performance_schema.events_waits_current
|
|
where THREAD_ID = $con1_thread_id order by THREAD_ID, EVENT_ID;
|
|
evalp select /*1-2*/ count(*) as has_waits from performance_schema.events_waits_history
|
|
where THREAD_ID = $con1_thread_id;
|
|
evalp select /*1-2*/ count(*) as has_waits from performance_schema.events_waits_history_long
|
|
where THREAD_ID = $con1_thread_id;
|
|
|
|
echo ########################### Waits user 2 - 2;
|
|
|
|
evalp select /*2-2*/ EVENT_NAME from performance_schema.events_waits_current
|
|
where THREAD_ID = $con2_thread_id order by THREAD_ID, EVENT_ID;
|
|
evalp select /*2-2*/ count(*) from performance_schema.events_waits_history
|
|
where THREAD_ID = $con2_thread_id;
|
|
evalp select /*2-2*/ count(*) from performance_schema.events_waits_history_long
|
|
where THREAD_ID = $con2_thread_id;
|
|
|
|
echo ########################### Waits user 3 - 2;
|
|
|
|
evalp select /*3-2*/ count(*) from performance_schema.events_waits_current
|
|
where THREAD_ID = $con3_thread_id;
|
|
evalp select /*3-2*/ count(*) from performance_schema.events_waits_history
|
|
where THREAD_ID = $con3_thread_id;
|
|
evalp select /*3-2*/ count(*) from performance_schema.events_waits_history_long
|
|
where THREAD_ID = $con3_thread_id;
|
|
|
|
echo ########################### Waits user 4 - 2;
|
|
|
|
evalp select /*4-2*/ count(*) from performance_schema.events_waits_current
|
|
where THREAD_ID = $con4_thread_id;
|
|
evalp select /*4-2*/ count(*) from performance_schema.events_waits_history
|
|
where THREAD_ID = $con4_thread_id;
|
|
evalp select /*4-2*/ count(*) from performance_schema.events_waits_history_long
|
|
where THREAD_ID = $con4_thread_id;
|
|
|
|
--connection default
|
|
|
|
update performance_schema.setup_consumers
|
|
set enabled='YES' where name like "%history%";
|
|
|
|
select * from performance_schema.setup_consumers;
|
|
|
|
truncate table performance_schema.events_transactions_current;
|
|
truncate table performance_schema.events_transactions_history;
|
|
truncate table performance_schema.events_transactions_history_long;
|
|
truncate table performance_schema.events_statements_current;
|
|
truncate table performance_schema.events_statements_history;
|
|
truncate table performance_schema.events_statements_history_long;
|
|
truncate table performance_schema.events_stages_current;
|
|
truncate table performance_schema.events_stages_history;
|
|
truncate table performance_schema.events_stages_history_long;
|
|
truncate table performance_schema.events_waits_current;
|
|
truncate table performance_schema.events_waits_history;
|
|
truncate table performance_schema.events_waits_history_long;
|
|
|
|
--connection con1
|
|
|
|
XA START 'XA_CON1', 'XA_BQUAL', 12;
|
|
select "Hi from con1";
|
|
XA END 'XA_CON1', 'XA_BQUAL', 12;
|
|
XA PREPARE 'XA_CON1', 'XA_BQUAL', 12;
|
|
XA COMMIT 'XA_CON1', 'XA_BQUAL', 12;
|
|
|
|
--connection con2
|
|
|
|
XA START 'XA_CON2', 'XA_BQUAL', 12;
|
|
select "Hi from con2";
|
|
XA END 'XA_CON2', 'XA_BQUAL', 12;
|
|
XA PREPARE 'XA_CON2', 'XA_BQUAL', 12;
|
|
XA COMMIT 'XA_CON2', 'XA_BQUAL', 12;
|
|
|
|
--connection con3
|
|
|
|
XA START 'XA_CON3', 'XA_BQUAL', 12;
|
|
select "Hi from con3";
|
|
XA END 'XA_CON3', 'XA_BQUAL', 12;
|
|
XA PREPARE 'XA_CON3', 'XA_BQUAL', 12;
|
|
XA COMMIT 'XA_CON3', 'XA_BQUAL', 12;
|
|
|
|
--connection con4
|
|
|
|
XA START 'XA_CON4', 'XA_BQUAL', 12;
|
|
select "Hi from con4";
|
|
XA END 'XA_CON4', 'XA_BQUAL', 12;
|
|
XA PREPARE 'XA_CON4', 'XA_BQUAL', 12;
|
|
XA COMMIT 'XA_CON4', 'XA_BQUAL', 12;
|
|
|
|
--connection default
|
|
|
|
echo ########################### Transactions user 1 - 3;
|
|
|
|
evalp select /*1-3*/ XID_FORMAT_ID, XID_GTRID, XID_BQUAL from performance_schema.events_transactions_current
|
|
where THREAD_ID = $con1_thread_id;
|
|
evalp select /*1-3*/ XID_FORMAT_ID, XID_GTRID, XID_BQUAL from performance_schema.events_transactions_history
|
|
where THREAD_ID = $con1_thread_id;
|
|
evalp select /*1-3*/ XID_FORMAT_ID, XID_GTRID, XID_BQUAL from performance_schema.events_transactions_history_long
|
|
where THREAD_ID = $con1_thread_id;
|
|
|
|
echo ########################### Transactions user 2 - 3;
|
|
|
|
evalp select /*2-3*/ XID_FORMAT_ID, XID_GTRID, XID_BQUAL from performance_schema.events_transactions_current
|
|
where THREAD_ID = $con2_thread_id;
|
|
evalp select /*2-3*/ count(*) from performance_schema.events_transactions_history
|
|
where THREAD_ID = $con2_thread_id;
|
|
evalp select /*2-3*/ count(*) from performance_schema.events_transactions_history_long
|
|
where THREAD_ID = $con2_thread_id;
|
|
|
|
echo ########################### Transactions user 3 - 3;
|
|
|
|
evalp select /*3-3*/ count(*) from performance_schema.events_transactions_current
|
|
where THREAD_ID = $con3_thread_id;
|
|
evalp select /*3-3*/ count(*) from performance_schema.events_transactions_history
|
|
where THREAD_ID = $con3_thread_id;
|
|
evalp select /*3-3*/ count(*) from performance_schema.events_transactions_history_long
|
|
where THREAD_ID = $con3_thread_id;
|
|
|
|
echo ########################### Transactions user 4 - 3;
|
|
|
|
evalp select /*4-3*/ count(*) from performance_schema.events_transactions_current
|
|
where THREAD_ID = $con4_thread_id;
|
|
evalp select /*4-3*/ count(*) from performance_schema.events_transactions_history
|
|
where THREAD_ID = $con4_thread_id;
|
|
evalp select /*4-3*/ count(*) from performance_schema.events_transactions_history_long
|
|
where THREAD_ID = $con4_thread_id;
|
|
|
|
echo ########################### Statements user 1 - 3;
|
|
|
|
evalp select /*1-3*/ EVENT_NAME, SQL_TEXT from performance_schema.events_statements_current
|
|
where THREAD_ID = $con1_thread_id order by THREAD_ID, EVENT_ID;
|
|
evalp select /*1-3*/ EVENT_NAME, SQL_TEXT from performance_schema.events_statements_history
|
|
where THREAD_ID = $con1_thread_id order by THREAD_ID, EVENT_ID;
|
|
evalp select /*1-3*/ EVENT_NAME, SQL_TEXT from performance_schema.events_statements_history_long
|
|
where THREAD_ID = $con1_thread_id order by THREAD_ID, EVENT_ID;
|
|
|
|
echo ########################### Statements user 2 - 3;
|
|
|
|
evalp select /*2-3*/ EVENT_NAME, SQL_TEXT from performance_schema.events_statements_current
|
|
where THREAD_ID = $con2_thread_id order by THREAD_ID, EVENT_ID;
|
|
evalp select /*2-3*/ count(*) from performance_schema.events_statements_history
|
|
where THREAD_ID = $con2_thread_id;
|
|
evalp select /*2-3*/ count(*) from performance_schema.events_statements_history_long
|
|
where THREAD_ID = $con2_thread_id;
|
|
|
|
echo ########################### Statements user 3 - 3;
|
|
|
|
evalp select /*3-3*/ count(*) from performance_schema.events_statements_current
|
|
where THREAD_ID = $con3_thread_id;
|
|
evalp select /*3-3*/ count(*) from performance_schema.events_statements_history
|
|
where THREAD_ID = $con3_thread_id;
|
|
evalp select /*3-3*/ count(*) from performance_schema.events_statements_history_long
|
|
where THREAD_ID = $con3_thread_id;
|
|
|
|
echo ########################### Statements user 4 - 3;
|
|
|
|
evalp select /*4-3*/ count(*) from performance_schema.events_statements_current
|
|
where THREAD_ID = $con4_thread_id;
|
|
evalp select /*4-3*/ count(*) from performance_schema.events_statements_history
|
|
where THREAD_ID = $con4_thread_id;
|
|
evalp select /*4-3*/ count(*) from performance_schema.events_statements_history_long
|
|
where THREAD_ID = $con4_thread_id;
|
|
|
|
echo ########################### Stages user 1 - 3;
|
|
|
|
evalp select /*1-3*/ EVENT_NAME from performance_schema.events_stages_current
|
|
where THREAD_ID = $con1_thread_id order by THREAD_ID, EVENT_ID;
|
|
evalp select /*1-3*/ EVENT_NAME from performance_schema.events_stages_history
|
|
where THREAD_ID = $con1_thread_id order by THREAD_ID, EVENT_ID;
|
|
evalp select /*1-3*/ EVENT_NAME from performance_schema.events_stages_history_long
|
|
where THREAD_ID = $con1_thread_id order by THREAD_ID, EVENT_ID;
|
|
|
|
echo ########################### Stages user 2 - 3;
|
|
|
|
evalp select /*2-3*/ EVENT_NAME from performance_schema.events_stages_current
|
|
where THREAD_ID = $con2_thread_id order by THREAD_ID, EVENT_ID;
|
|
evalp select /*2-3*/ count(*) from performance_schema.events_stages_history
|
|
where THREAD_ID = $con2_thread_id;
|
|
evalp select /*2-3*/ count(*) from performance_schema.events_stages_history_long
|
|
where THREAD_ID = $con2_thread_id;
|
|
|
|
echo ########################### Stages user 3 - 3;
|
|
|
|
evalp select /*3-3*/ count(*) from performance_schema.events_stages_current
|
|
where THREAD_ID = $con3_thread_id;
|
|
evalp select /*3-3*/ count(*) from performance_schema.events_stages_history
|
|
where THREAD_ID = $con3_thread_id;
|
|
evalp select /*3-3*/ count(*) from performance_schema.events_stages_history_long
|
|
where THREAD_ID = $con3_thread_id;
|
|
|
|
echo ########################### Stages user 4 - 3;
|
|
|
|
evalp select /*4-3*/ count(*) from performance_schema.events_stages_current
|
|
where THREAD_ID = $con4_thread_id;
|
|
evalp select /*4-3*/ count(*) from performance_schema.events_stages_history
|
|
where THREAD_ID = $con4_thread_id;
|
|
evalp select /*4-3*/ count(*) from performance_schema.events_stages_history_long
|
|
where THREAD_ID = $con4_thread_id;
|
|
|
|
echo ########################### Waits user 1 - 3;
|
|
|
|
evalp select /*1-3*/ EVENT_NAME from performance_schema.events_waits_current
|
|
where THREAD_ID = $con1_thread_id order by THREAD_ID, EVENT_ID;
|
|
evalp select /*1-3*/ (count(*) > 5) as has_waits from performance_schema.events_waits_history
|
|
where THREAD_ID = $con1_thread_id;
|
|
evalp select /*1-3*/ (count(*) > 15) as has_waits from performance_schema.events_waits_history_long
|
|
where THREAD_ID = $con1_thread_id;
|
|
|
|
echo ########################### Waits user 2 - 3;
|
|
|
|
evalp select /*2-3*/ EVENT_NAME from performance_schema.events_waits_current
|
|
where THREAD_ID = $con2_thread_id order by THREAD_ID, EVENT_ID;
|
|
evalp select /*2-3*/ count(*) from performance_schema.events_waits_history
|
|
where THREAD_ID = $con2_thread_id;
|
|
evalp select /*2-3*/ count(*) from performance_schema.events_waits_history_long
|
|
where THREAD_ID = $con2_thread_id;
|
|
|
|
echo ########################### Waits user 3 - 3;
|
|
|
|
evalp select /*3-3*/ count(*) from performance_schema.events_waits_current
|
|
where THREAD_ID = $con3_thread_id;
|
|
evalp select /*3-3*/ count(*) from performance_schema.events_waits_history
|
|
where THREAD_ID = $con3_thread_id;
|
|
evalp select /*3-3*/ count(*) from performance_schema.events_waits_history_long
|
|
where THREAD_ID = $con3_thread_id;
|
|
|
|
echo ########################### Waits user 4 - 3;
|
|
|
|
evalp select /*4-3*/ count(*) from performance_schema.events_waits_current
|
|
where THREAD_ID = $con4_thread_id;
|
|
evalp select /*4-3*/ count(*) from performance_schema.events_waits_history
|
|
where THREAD_ID = $con4_thread_id;
|
|
evalp select /*4-3*/ count(*) from performance_schema.events_waits_history_long
|
|
where THREAD_ID = $con4_thread_id;
|
|
|
|
--connection default
|
|
--disconnect con1
|
|
--disconnect con2
|
|
--disconnect con3
|
|
--disconnect con4
|
|
|
|
drop user user1@localhost;
|
|
drop user user2@localhost;
|
|
drop user user3@localhost;
|
|
drop user user4@localhost;
|
|
flush privileges;
|
|
|
|
truncate table performance_schema.setup_actors;
|
|
|
|
insert into performance_schema.setup_actors
|
|
values ('%', '%', '%', 'YES', 'YES');
|
|
|
|
select * from performance_schema.setup_actors;
|
|
|