Bug #106939 TRUNCATE performance_schema.accounts causes duplicated counts in global_status

Description:
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.

How to repeat:
1,  turn on pfs
2,  set performance_schema_hosts_size=1 in configuration file
3, create user tu1@'%' identified by '123456';
4, connect root and tu1 with difference host to mysql
./bin/mysql -uroot -S mysql.sock
./bin/mysql -utu1 -p123456 --port=3307 --host=127.0.0.1
5, now mysql looks like this
==================================
mysql> select * from accounts;
+------+-----------+---------------------+-------------------+
| USER | HOST      | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |
+------+-----------+---------------------+-------------------+
| NULL | NULL      |                  87 |                90 |
| root | localhost |                   1 |                 1 |
| tu1  | localhost |                   1 |                 1 |
+------+-----------+---------------------+-------------------+
3 rows in set (0.00 sec)

mysql> select * from hosts;
+------+---------------------+-------------------+
| HOST | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |
+------+---------------------+-------------------+
| NULL |                  87 |                90 |
+------+---------------------+-------------------+
1 row in set (0.00 sec)

mysql> show global status like 'Performance_schema_hosts_lost';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Performance_schema_hosts_lost | 2     |
+-------------------------------+-------+
1 row in set (0.01 sec)
==================================
6, create some slow entry by tu1 connection
set long_query_time=0.1;
select sleep(1);select sleep(1);select sleep(1);
===========
now mysql looks like this
mysql> show global status like 'Slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 3     |
+---------------+-------+
1 row in set (0.02 sec)

===========
7, truncate accounts by root user
truncate table accounts;
===========
now mysqld looks like this
mysql> show global status like 'Slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 6     |
+---------------+-------+
1 row in set (0.01 sec)

Suggested fix:
diff --git a/storage/perfschema/pfs_account.cc b/storage/perfschema/pfs_account.cc
index e8d36f8..f11bed8 100644
--- a/storage/perfschema/pfs_account.cc
+++ b/storage/perfschema/pfs_account.cc
@@ -541,7 +541,7 @@ void PFS_account::aggregate_status(PFS_user *safe_user, PFS_host *safe
       - GLOBAL_STATUS
     */
     safe_user->m_status_stats.aggregate(& m_status_stats);
-    m_status_stats.aggregate_to(& global_status_var);
+    //m_status_stats.aggregate_to(& global_status_var);
     m_status_stats.reset();
     return;
   }
@@ -561,7 +561,7 @@ void PFS_account::aggregate_status(PFS_user *safe_user, PFS_host *safe
     Aggregate STATUS_BY_ACCOUNT to:
     - GLOBAL_STATUS
   */
-  m_status_stats.aggregate_to(& global_status_var);
+  //m_status_stats.aggregate_to(& global_status_var);
   m_status_stats.reset();
   return;
 }
(

rewrite title

contribution by Ten:Truncate pfs table accounts cause global status abnormal

rewrite title

contribution by Ten:Truncate pfs table accounts cause global status abnormal

rewrite title again

Hello yuxiang jiang,

Thank you for the report and contribution.
Please ensure to re-send the patch "Contribution" tab. Otherwise we would not be able to accept it.

regards,
Umesh

patch for this defect

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: 0001-bugfix-issue-516-Truncate-pfs-table-accounts-will-ca.patch (application/octet-stream, text), 5.56 KiB.

Contribution by Tencent.