From c4ec826f5842be695acc18510a7cf7160fd77ef7 Mon Sep 17 00:00:00 2001 From: yuxianjiang Date: Mon, 11 Apr 2022 15:43:10 +0800 Subject: [PATCH] [bugfix] issue#516 Truncate pfs table accounts will cause global status abnormal Problem ======= Sometimes MySQL instance status infomation such as Slow_queries changed in a incredible way in short time. But we did not find any entries in slow log file. PFS account ojbects depends on host and user objects. When truncating accounts table, PFS engine will purge account objects. This purge also affect host user objects. User can specify hosts table size for limitation of host objects. If failed to create host object during generate a new account object, purge of account will try to add status of this account object to global status(variables global_status_var in mysqld). This cause the counting twice. Solution ======== Remove duplicate count of global status when truncate pfs account table. --- .../truncate_account_cause_status_abnormal.result | 29 +++++++++++++++ ...uncate_account_cause_status_abnormal-master.opt | 1 + .../t/truncate_account_cause_status_abnormal.test | 41 ++++++++++++++++++++++ storage/perfschema/pfs_account.cc | 4 --- 4 files changed, 71 insertions(+), 4 deletions(-) create mode 100644 mysql-test/suite/perfschema/r/truncate_account_cause_status_abnormal.result create mode 100644 mysql-test/suite/perfschema/t/truncate_account_cause_status_abnormal-master.opt create mode 100644 mysql-test/suite/perfschema/t/truncate_account_cause_status_abnormal.test diff --git a/mysql-test/suite/perfschema/r/truncate_account_cause_status_abnormal.result b/mysql-test/suite/perfschema/r/truncate_account_cause_status_abnormal.result new file mode 100644 index 0000000..9d428f2 --- /dev/null +++ b/mysql-test/suite/perfschema/r/truncate_account_cause_status_abnormal.result @@ -0,0 +1,29 @@ +###Create user and connect with 127.0.0.1 +create user tu1@'%' identified by '123456'; +###Check Performance_schema_hosts_lost +show global status like 'Performance_schema_hosts_lost'; +Variable_name Value +Performance_schema_hosts_lost 2 +###Make more slow logs +set long_query_time=0.1; +select sleep(0.1); +sleep(0.1) +0 +select sleep(0.1); +sleep(0.1) +0 +select sleep(0.1); +sleep(0.1) +0 +###Retrieve Slow_queries status befor truncate +###"show global status like 'Slow_queries';" before truncate account +### Now slow queries is 3 +###Truncate accounts +use performance_schema; +truncate table accounts; +###Retrieve Slow_queries status after truncate +###"show global status like 'Slow_queries';" after truncate account +### Now slow queries is 3 +include/assert.inc [Slow log entries before truncate account show be eqaul after truncate] +###Cleanup### +drop user tu1; diff --git a/mysql-test/suite/perfschema/t/truncate_account_cause_status_abnormal-master.opt b/mysql-test/suite/perfschema/t/truncate_account_cause_status_abnormal-master.opt new file mode 100644 index 0000000..faa4cdd --- /dev/null +++ b/mysql-test/suite/perfschema/t/truncate_account_cause_status_abnormal-master.opt @@ -0,0 +1 @@ +--performance_schema_hosts_size=1 \ No newline at end of file diff --git a/mysql-test/suite/perfschema/t/truncate_account_cause_status_abnormal.test b/mysql-test/suite/perfschema/t/truncate_account_cause_status_abnormal.test new file mode 100644 index 0000000..9d5098f --- /dev/null +++ b/mysql-test/suite/perfschema/t/truncate_account_cause_status_abnormal.test @@ -0,0 +1,41 @@ +###Test for truncate table of + +connection default; + +--echo ###Create user and connect with 127.0.0.1 +create user tu1@'%' identified by '123456'; +connect (con1,127.0.0.1,tu1,123456,,,); +--connection con1 + +--echo ###Check Performance_schema_hosts_lost +connection default; +show global status like 'Performance_schema_hosts_lost'; + +--echo ###Make more slow logs +--connection con1 +set long_query_time=0.1; +select sleep(0.1); +select sleep(0.1); +select sleep(0.1); + +--echo ###Retrieve Slow_queries status befor truncate +connection default; +--echo ###"show global status like 'Slow_queries';" before truncate account +--let $slows_before = query_get_value(show global status like 'Slow_queries', Value, 1) +--echo ### Now slow queries is $slows_before + +--echo ###Truncate accounts +use performance_schema; +truncate table accounts; + +--echo ###Retrieve Slow_queries status after truncate +--echo ###"show global status like 'Slow_queries';" after truncate account +--let $slows_after = query_get_value(show global status like 'Slow_queries', Value, 1) +--echo ### Now slow queries is $slows_after + +--let $assert_text=Slow log entries before truncate account show be eqaul after truncate +--let $assert_cond= $slows_after = $slows_before +--source include/assert.inc + +--echo ###Cleanup### +drop user tu1; \ No newline at end of file diff --git a/storage/perfschema/pfs_account.cc b/storage/perfschema/pfs_account.cc index e8d36f8..0c07dd0 100644 --- a/storage/perfschema/pfs_account.cc +++ b/storage/perfschema/pfs_account.cc @@ -538,10 +538,8 @@ void PFS_account::aggregate_status(PFS_user *safe_user, PFS_host *safe_host) /* Aggregate STATUS_BY_ACCOUNT to: - STATUS_BY_USER - - GLOBAL_STATUS */ safe_user->m_status_stats.aggregate(& m_status_stats); - m_status_stats.aggregate_to(& global_status_var); m_status_stats.reset(); return; } @@ -559,9 +557,7 @@ void PFS_account::aggregate_status(PFS_user *safe_user, PFS_host *safe_host) /* Aggregate STATUS_BY_ACCOUNT to: - - GLOBAL_STATUS */ - m_status_stats.aggregate_to(& global_status_var); m_status_stats.reset(); return; } -- 1.8.3.1