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.
969 lines
32 KiB
Plaintext
969 lines
32 KiB
Plaintext
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;
|
|
NAME ENABLED
|
|
events_stages_current YES
|
|
events_stages_history YES
|
|
events_stages_history_long YES
|
|
events_statements_current YES
|
|
events_statements_history YES
|
|
events_statements_history_long YES
|
|
events_transactions_current YES
|
|
events_transactions_history YES
|
|
events_transactions_history_long YES
|
|
events_waits_current YES
|
|
events_waits_history YES
|
|
events_waits_history_long YES
|
|
global_instrumentation YES
|
|
thread_instrumentation YES
|
|
statements_digest YES
|
|
connect con1, localhost, user1, , ;
|
|
update performance_schema.threads
|
|
set INSTRUMENTED='YES', HISTORY='YES'
|
|
where PROCESSLIST_ID = connection_id();
|
|
connect con2, localhost, user2, , ;
|
|
update performance_schema.threads
|
|
set INSTRUMENTED='YES', HISTORY='NO'
|
|
where PROCESSLIST_ID = connection_id();
|
|
connect con3, localhost, user3, , ;
|
|
update performance_schema.threads
|
|
set INSTRUMENTED='NO', HISTORY='YES'
|
|
where PROCESSLIST_ID = connection_id();
|
|
connect con4, localhost, user4, , ;
|
|
update performance_schema.threads
|
|
set INSTRUMENTED='NO', HISTORY='NO'
|
|
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";
|
|
Hi from con1
|
|
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";
|
|
Hi from con2
|
|
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";
|
|
Hi from con3
|
|
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";
|
|
Hi from con4
|
|
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;
|
|
########################### Transactions user 1 - 1
|
|
select /*1-1*/ XID_FORMAT_ID, XID_GTRID, XID_BQUAL from performance_schema.events_transactions_current
|
|
where THREAD_ID = $con1_thread_id;
|
|
XID_FORMAT_ID XID_GTRID XID_BQUAL
|
|
12 XA_CON1 XA_BQUAL
|
|
select /*1-1*/ XID_FORMAT_ID, XID_GTRID, XID_BQUAL from performance_schema.events_transactions_history
|
|
where THREAD_ID = $con1_thread_id;
|
|
XID_FORMAT_ID XID_GTRID XID_BQUAL
|
|
12 XA_CON1 XA_BQUAL
|
|
select /*1-1*/ XID_FORMAT_ID, XID_GTRID, XID_BQUAL from performance_schema.events_transactions_history_long
|
|
where THREAD_ID = $con1_thread_id;
|
|
XID_FORMAT_ID XID_GTRID XID_BQUAL
|
|
12 XA_CON1 XA_BQUAL
|
|
########################### Transactions user 2 - 1
|
|
select /*2-1*/ XID_FORMAT_ID, XID_GTRID, XID_BQUAL from performance_schema.events_transactions_current
|
|
where THREAD_ID = $con2_thread_id;
|
|
XID_FORMAT_ID XID_GTRID XID_BQUAL
|
|
12 XA_CON2 XA_BQUAL
|
|
select /*2-1*/ count(*) from performance_schema.events_transactions_history
|
|
where THREAD_ID = $con2_thread_id;
|
|
count(*)
|
|
0
|
|
select /*2-1*/ count(*) from performance_schema.events_transactions_history_long
|
|
where THREAD_ID = $con2_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Transactions user 3 - 1
|
|
select /*3-1*/ count(*) from performance_schema.events_transactions_current
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
select /*3-1*/ count(*) from performance_schema.events_transactions_history
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
select /*3-1*/ count(*) from performance_schema.events_transactions_history_long
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Transactions user 4 - 1
|
|
select /*4-1*/ count(*) from performance_schema.events_transactions_current
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
select /*4-1*/ count(*) from performance_schema.events_transactions_history
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
select /*4-1*/ count(*) from performance_schema.events_transactions_history_long
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Statements user 1 - 1
|
|
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;
|
|
EVENT_NAME SQL_TEXT
|
|
statement/sql/xa_commit XA COMMIT 'XA_CON1', 'XA_BQUAL', 12
|
|
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;
|
|
EVENT_NAME SQL_TEXT
|
|
statement/sql/xa_start XA START 'XA_CON1', 'XA_BQUAL', 12
|
|
statement/sql/select select "Hi from con1"
|
|
statement/sql/xa_end XA END 'XA_CON1', 'XA_BQUAL', 12
|
|
statement/sql/xa_prepare XA PREPARE 'XA_CON1', 'XA_BQUAL', 12
|
|
statement/sql/xa_commit XA COMMIT 'XA_CON1', 'XA_BQUAL', 12
|
|
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;
|
|
EVENT_NAME SQL_TEXT
|
|
statement/sql/xa_start XA START 'XA_CON1', 'XA_BQUAL', 12
|
|
statement/sql/select select "Hi from con1"
|
|
statement/sql/xa_end XA END 'XA_CON1', 'XA_BQUAL', 12
|
|
statement/sql/xa_prepare XA PREPARE 'XA_CON1', 'XA_BQUAL', 12
|
|
statement/sql/xa_commit XA COMMIT 'XA_CON1', 'XA_BQUAL', 12
|
|
########################### Statements user 2 - 1
|
|
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;
|
|
EVENT_NAME SQL_TEXT
|
|
statement/sql/xa_commit XA COMMIT 'XA_CON2', 'XA_BQUAL', 12
|
|
select /*2-1*/ count(*) from performance_schema.events_statements_history
|
|
where THREAD_ID = $con2_thread_id;
|
|
count(*)
|
|
0
|
|
select /*2-1*/ count(*) from performance_schema.events_statements_history_long
|
|
where THREAD_ID = $con2_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Statements user 3 - 1
|
|
select /*3-1*/ count(*) from performance_schema.events_statements_current
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
select /*3-1*/ count(*) from performance_schema.events_statements_history
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
select /*3-1*/ count(*) from performance_schema.events_statements_history_long
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Statements user 4 - 1
|
|
select /*4-1*/ count(*) from performance_schema.events_statements_current
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
select /*4-1*/ count(*) from performance_schema.events_statements_history
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
select /*4-1*/ count(*) from performance_schema.events_statements_history_long
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Stages user 1 - 1
|
|
select /*1-1*/ EVENT_NAME from performance_schema.events_stages_current
|
|
where THREAD_ID = $con1_thread_id order by THREAD_ID, EVENT_ID;
|
|
EVENT_NAME
|
|
select /*1-1*/ EVENT_NAME from performance_schema.events_stages_history
|
|
where THREAD_ID = $con1_thread_id order by THREAD_ID, EVENT_ID;
|
|
EVENT_NAME
|
|
stage/sql/Starting cleanup
|
|
stage/sql/Freeing items
|
|
stage/sql/Reset for next command
|
|
stage/sql/starting
|
|
stage/sql/Query end
|
|
stage/sql/closing tables
|
|
stage/sql/Query end
|
|
stage/sql/Starting cleanup
|
|
stage/sql/Freeing items
|
|
stage/sql/Reset for next command
|
|
select /*1-1*/ EVENT_NAME from performance_schema.events_stages_history_long
|
|
where THREAD_ID = $con1_thread_id order by THREAD_ID, EVENT_ID;
|
|
EVENT_NAME
|
|
stage/sql/starting
|
|
stage/sql/Query end
|
|
stage/sql/closing tables
|
|
stage/sql/Query end
|
|
stage/sql/Starting cleanup
|
|
stage/sql/Freeing items
|
|
stage/sql/Reset for next command
|
|
stage/sql/starting
|
|
stage/sql/checking permissions
|
|
stage/sql/Opening tables
|
|
stage/sql/After opening tables
|
|
stage/sql/init
|
|
stage/sql/Optimizing
|
|
stage/sql/Executing
|
|
stage/sql/End of update loop
|
|
stage/sql/Query end
|
|
stage/sql/closing tables
|
|
stage/sql/Query end
|
|
stage/sql/Starting cleanup
|
|
stage/sql/Freeing items
|
|
stage/sql/Reset for next command
|
|
stage/sql/starting
|
|
stage/sql/Query end
|
|
stage/sql/closing tables
|
|
stage/sql/Query end
|
|
stage/sql/Starting cleanup
|
|
stage/sql/Freeing items
|
|
stage/sql/Reset for next command
|
|
stage/sql/starting
|
|
stage/sql/Query end
|
|
stage/sql/closing tables
|
|
stage/sql/Query end
|
|
stage/sql/Starting cleanup
|
|
stage/sql/Freeing items
|
|
stage/sql/Reset for next command
|
|
stage/sql/starting
|
|
stage/sql/Query end
|
|
stage/sql/closing tables
|
|
stage/sql/Query end
|
|
stage/sql/Starting cleanup
|
|
stage/sql/Freeing items
|
|
stage/sql/Reset for next command
|
|
########################### Stages user 2 - 1
|
|
select /*2-1*/ EVENT_NAME from performance_schema.events_stages_current
|
|
where THREAD_ID = $con2_thread_id order by THREAD_ID, EVENT_ID;
|
|
EVENT_NAME
|
|
select /*2-1*/ count(*) from performance_schema.events_stages_history
|
|
where THREAD_ID = $con2_thread_id;
|
|
count(*)
|
|
0
|
|
select /*2-1*/ count(*) from performance_schema.events_stages_history_long
|
|
where THREAD_ID = $con2_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Stages user 3 - 1
|
|
select /*3-1*/ count(*) from performance_schema.events_stages_current
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
select /*3-1*/ count(*) from performance_schema.events_stages_history
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
select /*3-1*/ count(*) from performance_schema.events_stages_history_long
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Stages user 4 - 1
|
|
select /*4-1*/ count(*) from performance_schema.events_stages_current
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
select /*4-1*/ count(*) from performance_schema.events_stages_history
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
select /*4-1*/ count(*) from performance_schema.events_stages_history_long
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Waits user 1 - 1
|
|
select /*1-1*/ EVENT_NAME from performance_schema.events_waits_current
|
|
where THREAD_ID = $con1_thread_id order by THREAD_ID, EVENT_ID;
|
|
EVENT_NAME
|
|
idle
|
|
select /*1-1*/ (count(*) > 5) as has_waits from performance_schema.events_waits_history
|
|
where THREAD_ID = $con1_thread_id;
|
|
has_waits
|
|
1
|
|
select /*1-1*/ (count(*) > 15) as has_waits from performance_schema.events_waits_history_long
|
|
where THREAD_ID = $con1_thread_id;
|
|
has_waits
|
|
1
|
|
########################### Waits user 2 - 1
|
|
select /*2-1*/ EVENT_NAME from performance_schema.events_waits_current
|
|
where THREAD_ID = $con2_thread_id order by THREAD_ID, EVENT_ID;
|
|
EVENT_NAME
|
|
idle
|
|
select /*2-1*/ count(*) from performance_schema.events_waits_history
|
|
where THREAD_ID = $con2_thread_id;
|
|
count(*)
|
|
0
|
|
select /*2-1*/ count(*) from performance_schema.events_waits_history_long
|
|
where THREAD_ID = $con2_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Waits user 3 - 1
|
|
select /*3-1*/ count(*) from performance_schema.events_waits_current
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
select /*3-1*/ count(*) from performance_schema.events_waits_history
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
select /*3-1*/ count(*) from performance_schema.events_waits_history_long
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Waits user 4 - 1
|
|
select /*4-1*/ count(*) from performance_schema.events_waits_current
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
select /*4-1*/ count(*) from performance_schema.events_waits_history
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
select /*4-1*/ count(*) from performance_schema.events_waits_history_long
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
connection default;
|
|
update performance_schema.setup_consumers
|
|
set enabled='NO' where name like "%history%";
|
|
select * from performance_schema.setup_consumers;
|
|
NAME ENABLED
|
|
events_stages_current YES
|
|
events_stages_history NO
|
|
events_stages_history_long NO
|
|
events_statements_current YES
|
|
events_statements_history NO
|
|
events_statements_history_long NO
|
|
events_transactions_current YES
|
|
events_transactions_history NO
|
|
events_transactions_history_long NO
|
|
events_waits_current YES
|
|
events_waits_history NO
|
|
events_waits_history_long NO
|
|
global_instrumentation YES
|
|
thread_instrumentation YES
|
|
statements_digest YES
|
|
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";
|
|
Hi from con1
|
|
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";
|
|
Hi from con2
|
|
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";
|
|
Hi from con3
|
|
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";
|
|
Hi from con4
|
|
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;
|
|
########################### Transactions user 1 - 2
|
|
select /*1-2*/ XID_FORMAT_ID, XID_GTRID, XID_BQUAL from performance_schema.events_transactions_current
|
|
where THREAD_ID = $con1_thread_id;
|
|
XID_FORMAT_ID XID_GTRID XID_BQUAL
|
|
12 XA_CON1 XA_BQUAL
|
|
select /*1-2*/ count(*) from performance_schema.events_transactions_history
|
|
where THREAD_ID = $con1_thread_id;
|
|
count(*)
|
|
0
|
|
select /*1-2*/ count(*) from performance_schema.events_transactions_history_long
|
|
where THREAD_ID = $con1_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Transactions user 2 - 2
|
|
select /*2-2*/ XID_FORMAT_ID, XID_GTRID, XID_BQUAL from performance_schema.events_transactions_current
|
|
where THREAD_ID = $con2_thread_id;
|
|
XID_FORMAT_ID XID_GTRID XID_BQUAL
|
|
12 XA_CON2 XA_BQUAL
|
|
select /*2-2*/ count(*) from performance_schema.events_transactions_history
|
|
where THREAD_ID = $con2_thread_id;
|
|
count(*)
|
|
0
|
|
select /*2-2*/ count(*) from performance_schema.events_transactions_history_long
|
|
where THREAD_ID = $con2_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Transactions user 3 - 2
|
|
select /*3-2*/ count(*) from performance_schema.events_transactions_current
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
select /*3-2*/ count(*) from performance_schema.events_transactions_history
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
select /*3-2*/ count(*) from performance_schema.events_transactions_history_long
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Transactions user 4 - 2
|
|
select /*4-2*/ count(*) from performance_schema.events_transactions_current
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
select /*4-2*/ count(*) from performance_schema.events_transactions_history
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
select /*4-2*/ count(*) from performance_schema.events_transactions_history_long
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Statements user 1 - 2
|
|
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;
|
|
EVENT_NAME SQL_TEXT
|
|
statement/sql/xa_commit XA COMMIT 'XA_CON1', 'XA_BQUAL', 12
|
|
select /*1-2*/ count(*) from performance_schema.events_statements_history
|
|
where THREAD_ID = $con1_thread_id;
|
|
count(*)
|
|
0
|
|
select /*1-2*/ count(*) from performance_schema.events_statements_history_long
|
|
where THREAD_ID = $con1_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Statements user 2 - 2
|
|
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;
|
|
EVENT_NAME SQL_TEXT
|
|
statement/sql/xa_commit XA COMMIT 'XA_CON2', 'XA_BQUAL', 12
|
|
select /*2-2*/ count(*) from performance_schema.events_statements_history
|
|
where THREAD_ID = $con2_thread_id;
|
|
count(*)
|
|
0
|
|
select /*2-2*/ count(*) from performance_schema.events_statements_history_long
|
|
where THREAD_ID = $con2_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Statements user 3 - 2
|
|
select /*3-2*/ count(*) from performance_schema.events_statements_current
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
select /*3-2*/ count(*) from performance_schema.events_statements_history
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
select /*3-2*/ count(*) from performance_schema.events_statements_history_long
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Statements user 4 - 2
|
|
select /*4-2*/ count(*) from performance_schema.events_statements_current
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
select /*4-2*/ count(*) from performance_schema.events_statements_history
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
select /*4-2*/ count(*) from performance_schema.events_statements_history_long
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Stages user 1 - 2
|
|
select /*1-2*/ EVENT_NAME from performance_schema.events_stages_current
|
|
where THREAD_ID = $con1_thread_id order by THREAD_ID, EVENT_ID;
|
|
EVENT_NAME
|
|
select /*1-2*/ count(*) from performance_schema.events_stages_history
|
|
where THREAD_ID = $con1_thread_id;
|
|
count(*)
|
|
0
|
|
select /*1-2*/ count(*) from performance_schema.events_stages_history_long
|
|
where THREAD_ID = $con1_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Stages user 2 - 2
|
|
select /*2-2*/ EVENT_NAME from performance_schema.events_stages_current
|
|
where THREAD_ID = $con2_thread_id order by THREAD_ID, EVENT_ID;
|
|
EVENT_NAME
|
|
select /*2-2*/ count(*) from performance_schema.events_stages_history
|
|
where THREAD_ID = $con2_thread_id;
|
|
count(*)
|
|
0
|
|
select /*2-2*/ count(*) from performance_schema.events_stages_history_long
|
|
where THREAD_ID = $con2_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Stages user 3 - 2
|
|
select /*3-2*/ count(*) from performance_schema.events_stages_current
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
select /*3-2*/ count(*) from performance_schema.events_stages_history
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
select /*3-2*/ count(*) from performance_schema.events_stages_history_long
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Stages user 4 - 2
|
|
select /*4-2*/ count(*) from performance_schema.events_stages_current
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
select /*4-2*/ count(*) from performance_schema.events_stages_history
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
select /*4-2*/ count(*) from performance_schema.events_stages_history_long
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Waits user 1 - 2
|
|
select /*1-2*/ EVENT_NAME from performance_schema.events_waits_current
|
|
where THREAD_ID = $con1_thread_id order by THREAD_ID, EVENT_ID;
|
|
EVENT_NAME
|
|
idle
|
|
select /*1-2*/ count(*) as has_waits from performance_schema.events_waits_history
|
|
where THREAD_ID = $con1_thread_id;
|
|
has_waits
|
|
0
|
|
select /*1-2*/ count(*) as has_waits from performance_schema.events_waits_history_long
|
|
where THREAD_ID = $con1_thread_id;
|
|
has_waits
|
|
0
|
|
########################### Waits user 2 - 2
|
|
select /*2-2*/ EVENT_NAME from performance_schema.events_waits_current
|
|
where THREAD_ID = $con2_thread_id order by THREAD_ID, EVENT_ID;
|
|
EVENT_NAME
|
|
idle
|
|
select /*2-2*/ count(*) from performance_schema.events_waits_history
|
|
where THREAD_ID = $con2_thread_id;
|
|
count(*)
|
|
0
|
|
select /*2-2*/ count(*) from performance_schema.events_waits_history_long
|
|
where THREAD_ID = $con2_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Waits user 3 - 2
|
|
select /*3-2*/ count(*) from performance_schema.events_waits_current
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
select /*3-2*/ count(*) from performance_schema.events_waits_history
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
select /*3-2*/ count(*) from performance_schema.events_waits_history_long
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Waits user 4 - 2
|
|
select /*4-2*/ count(*) from performance_schema.events_waits_current
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
select /*4-2*/ count(*) from performance_schema.events_waits_history
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
select /*4-2*/ count(*) from performance_schema.events_waits_history_long
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
connection default;
|
|
update performance_schema.setup_consumers
|
|
set enabled='YES' where name like "%history%";
|
|
select * from performance_schema.setup_consumers;
|
|
NAME ENABLED
|
|
events_stages_current YES
|
|
events_stages_history YES
|
|
events_stages_history_long YES
|
|
events_statements_current YES
|
|
events_statements_history YES
|
|
events_statements_history_long YES
|
|
events_transactions_current YES
|
|
events_transactions_history YES
|
|
events_transactions_history_long YES
|
|
events_waits_current YES
|
|
events_waits_history YES
|
|
events_waits_history_long YES
|
|
global_instrumentation YES
|
|
thread_instrumentation YES
|
|
statements_digest YES
|
|
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";
|
|
Hi from con1
|
|
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";
|
|
Hi from con2
|
|
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";
|
|
Hi from con3
|
|
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";
|
|
Hi from con4
|
|
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;
|
|
########################### Transactions user 1 - 3
|
|
select /*1-3*/ XID_FORMAT_ID, XID_GTRID, XID_BQUAL from performance_schema.events_transactions_current
|
|
where THREAD_ID = $con1_thread_id;
|
|
XID_FORMAT_ID XID_GTRID XID_BQUAL
|
|
12 XA_CON1 XA_BQUAL
|
|
select /*1-3*/ XID_FORMAT_ID, XID_GTRID, XID_BQUAL from performance_schema.events_transactions_history
|
|
where THREAD_ID = $con1_thread_id;
|
|
XID_FORMAT_ID XID_GTRID XID_BQUAL
|
|
12 XA_CON1 XA_BQUAL
|
|
select /*1-3*/ XID_FORMAT_ID, XID_GTRID, XID_BQUAL from performance_schema.events_transactions_history_long
|
|
where THREAD_ID = $con1_thread_id;
|
|
XID_FORMAT_ID XID_GTRID XID_BQUAL
|
|
12 XA_CON1 XA_BQUAL
|
|
########################### Transactions user 2 - 3
|
|
select /*2-3*/ XID_FORMAT_ID, XID_GTRID, XID_BQUAL from performance_schema.events_transactions_current
|
|
where THREAD_ID = $con2_thread_id;
|
|
XID_FORMAT_ID XID_GTRID XID_BQUAL
|
|
12 XA_CON2 XA_BQUAL
|
|
select /*2-3*/ count(*) from performance_schema.events_transactions_history
|
|
where THREAD_ID = $con2_thread_id;
|
|
count(*)
|
|
0
|
|
select /*2-3*/ count(*) from performance_schema.events_transactions_history_long
|
|
where THREAD_ID = $con2_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Transactions user 3 - 3
|
|
select /*3-3*/ count(*) from performance_schema.events_transactions_current
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
select /*3-3*/ count(*) from performance_schema.events_transactions_history
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
select /*3-3*/ count(*) from performance_schema.events_transactions_history_long
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Transactions user 4 - 3
|
|
select /*4-3*/ count(*) from performance_schema.events_transactions_current
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
select /*4-3*/ count(*) from performance_schema.events_transactions_history
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
select /*4-3*/ count(*) from performance_schema.events_transactions_history_long
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Statements user 1 - 3
|
|
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;
|
|
EVENT_NAME SQL_TEXT
|
|
statement/sql/xa_commit XA COMMIT 'XA_CON1', 'XA_BQUAL', 12
|
|
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;
|
|
EVENT_NAME SQL_TEXT
|
|
statement/sql/xa_start XA START 'XA_CON1', 'XA_BQUAL', 12
|
|
statement/sql/select select "Hi from con1"
|
|
statement/sql/xa_end XA END 'XA_CON1', 'XA_BQUAL', 12
|
|
statement/sql/xa_prepare XA PREPARE 'XA_CON1', 'XA_BQUAL', 12
|
|
statement/sql/xa_commit XA COMMIT 'XA_CON1', 'XA_BQUAL', 12
|
|
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;
|
|
EVENT_NAME SQL_TEXT
|
|
statement/sql/xa_start XA START 'XA_CON1', 'XA_BQUAL', 12
|
|
statement/sql/select select "Hi from con1"
|
|
statement/sql/xa_end XA END 'XA_CON1', 'XA_BQUAL', 12
|
|
statement/sql/xa_prepare XA PREPARE 'XA_CON1', 'XA_BQUAL', 12
|
|
statement/sql/xa_commit XA COMMIT 'XA_CON1', 'XA_BQUAL', 12
|
|
########################### Statements user 2 - 3
|
|
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;
|
|
EVENT_NAME SQL_TEXT
|
|
statement/sql/xa_commit XA COMMIT 'XA_CON2', 'XA_BQUAL', 12
|
|
select /*2-3*/ count(*) from performance_schema.events_statements_history
|
|
where THREAD_ID = $con2_thread_id;
|
|
count(*)
|
|
0
|
|
select /*2-3*/ count(*) from performance_schema.events_statements_history_long
|
|
where THREAD_ID = $con2_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Statements user 3 - 3
|
|
select /*3-3*/ count(*) from performance_schema.events_statements_current
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
select /*3-3*/ count(*) from performance_schema.events_statements_history
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
select /*3-3*/ count(*) from performance_schema.events_statements_history_long
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Statements user 4 - 3
|
|
select /*4-3*/ count(*) from performance_schema.events_statements_current
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
select /*4-3*/ count(*) from performance_schema.events_statements_history
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
select /*4-3*/ count(*) from performance_schema.events_statements_history_long
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Stages user 1 - 3
|
|
select /*1-3*/ EVENT_NAME from performance_schema.events_stages_current
|
|
where THREAD_ID = $con1_thread_id order by THREAD_ID, EVENT_ID;
|
|
EVENT_NAME
|
|
select /*1-3*/ EVENT_NAME from performance_schema.events_stages_history
|
|
where THREAD_ID = $con1_thread_id order by THREAD_ID, EVENT_ID;
|
|
EVENT_NAME
|
|
stage/sql/Starting cleanup
|
|
stage/sql/Freeing items
|
|
stage/sql/Reset for next command
|
|
stage/sql/starting
|
|
stage/sql/Query end
|
|
stage/sql/closing tables
|
|
stage/sql/Query end
|
|
stage/sql/Starting cleanup
|
|
stage/sql/Freeing items
|
|
stage/sql/Reset for next command
|
|
select /*1-3*/ EVENT_NAME from performance_schema.events_stages_history_long
|
|
where THREAD_ID = $con1_thread_id order by THREAD_ID, EVENT_ID;
|
|
EVENT_NAME
|
|
stage/sql/starting
|
|
stage/sql/Query end
|
|
stage/sql/closing tables
|
|
stage/sql/Query end
|
|
stage/sql/Starting cleanup
|
|
stage/sql/Freeing items
|
|
stage/sql/Reset for next command
|
|
stage/sql/starting
|
|
stage/sql/checking permissions
|
|
stage/sql/Opening tables
|
|
stage/sql/After opening tables
|
|
stage/sql/init
|
|
stage/sql/Optimizing
|
|
stage/sql/Executing
|
|
stage/sql/End of update loop
|
|
stage/sql/Query end
|
|
stage/sql/closing tables
|
|
stage/sql/Query end
|
|
stage/sql/Starting cleanup
|
|
stage/sql/Freeing items
|
|
stage/sql/Reset for next command
|
|
stage/sql/starting
|
|
stage/sql/Query end
|
|
stage/sql/closing tables
|
|
stage/sql/Query end
|
|
stage/sql/Starting cleanup
|
|
stage/sql/Freeing items
|
|
stage/sql/Reset for next command
|
|
stage/sql/starting
|
|
stage/sql/Query end
|
|
stage/sql/closing tables
|
|
stage/sql/Query end
|
|
stage/sql/Starting cleanup
|
|
stage/sql/Freeing items
|
|
stage/sql/Reset for next command
|
|
stage/sql/starting
|
|
stage/sql/Query end
|
|
stage/sql/closing tables
|
|
stage/sql/Query end
|
|
stage/sql/Starting cleanup
|
|
stage/sql/Freeing items
|
|
stage/sql/Reset for next command
|
|
########################### Stages user 2 - 3
|
|
select /*2-3*/ EVENT_NAME from performance_schema.events_stages_current
|
|
where THREAD_ID = $con2_thread_id order by THREAD_ID, EVENT_ID;
|
|
EVENT_NAME
|
|
select /*2-3*/ count(*) from performance_schema.events_stages_history
|
|
where THREAD_ID = $con2_thread_id;
|
|
count(*)
|
|
0
|
|
select /*2-3*/ count(*) from performance_schema.events_stages_history_long
|
|
where THREAD_ID = $con2_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Stages user 3 - 3
|
|
select /*3-3*/ count(*) from performance_schema.events_stages_current
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
select /*3-3*/ count(*) from performance_schema.events_stages_history
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
select /*3-3*/ count(*) from performance_schema.events_stages_history_long
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Stages user 4 - 3
|
|
select /*4-3*/ count(*) from performance_schema.events_stages_current
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
select /*4-3*/ count(*) from performance_schema.events_stages_history
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
select /*4-3*/ count(*) from performance_schema.events_stages_history_long
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Waits user 1 - 3
|
|
select /*1-3*/ EVENT_NAME from performance_schema.events_waits_current
|
|
where THREAD_ID = $con1_thread_id order by THREAD_ID, EVENT_ID;
|
|
EVENT_NAME
|
|
idle
|
|
select /*1-3*/ (count(*) > 5) as has_waits from performance_schema.events_waits_history
|
|
where THREAD_ID = $con1_thread_id;
|
|
has_waits
|
|
1
|
|
select /*1-3*/ (count(*) > 15) as has_waits from performance_schema.events_waits_history_long
|
|
where THREAD_ID = $con1_thread_id;
|
|
has_waits
|
|
1
|
|
########################### Waits user 2 - 3
|
|
select /*2-3*/ EVENT_NAME from performance_schema.events_waits_current
|
|
where THREAD_ID = $con2_thread_id order by THREAD_ID, EVENT_ID;
|
|
EVENT_NAME
|
|
idle
|
|
select /*2-3*/ count(*) from performance_schema.events_waits_history
|
|
where THREAD_ID = $con2_thread_id;
|
|
count(*)
|
|
0
|
|
select /*2-3*/ count(*) from performance_schema.events_waits_history_long
|
|
where THREAD_ID = $con2_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Waits user 3 - 3
|
|
select /*3-3*/ count(*) from performance_schema.events_waits_current
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
select /*3-3*/ count(*) from performance_schema.events_waits_history
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
select /*3-3*/ count(*) from performance_schema.events_waits_history_long
|
|
where THREAD_ID = $con3_thread_id;
|
|
count(*)
|
|
0
|
|
########################### Waits user 4 - 3
|
|
select /*4-3*/ count(*) from performance_schema.events_waits_current
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
select /*4-3*/ count(*) from performance_schema.events_waits_history
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
select /*4-3*/ count(*) from performance_schema.events_waits_history_long
|
|
where THREAD_ID = $con4_thread_id;
|
|
count(*)
|
|
0
|
|
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;
|
|
HOST USER ROLE ENABLED HISTORY
|
|
% % % YES YES
|