Bug #103804 Show global status return wrong results due to thd's status double aggregated

• Submitted: 26 May 2021 0:56
• Modified: 31 May 2021 17:12
• Reporter: Baolin Huang
• Status: Verified
• Category: MySQL Server: Information schema
• Severity: S3 (Non-critical)
• Version: 8.0.24
• OS: Any
• CPU Architecture: Any
• Tags: Contribution

[26 May 2021 0:56] Baolin Huang

Description:
When observing monitoring data, we found that the global status 'Com_select' can decrease.

After analyzing code, we found a position on which the thd's status may be aggregated twice. 
The position is given below.

How to repeat:
1. Add a bit debug code.
```
diff --git a/sql/conn_handler/connection_handler_per_thread.cc b/sql/conn_handler/connection_handler_per_thread.cc
index 0668c4f571c..6d646aa30b3 100644
--- a/sql/conn_handler/connection_handler_per_thread.cc
+++ b/sql/conn_handler/connection_handler_per_thread.cc
@@ -306,7 +306,7 @@ static void *handle_connection(void *arg) {
 
     thd->get_stmt_da()->reset_diagnostics_area();
     thd->release_resources();
-
+    DBUG_EXECUTE_IF("global_status_repeat_sum", my_sleep(10000000););
     // Clean up errors now, before possibly waiting for a new connection.
 #if OPENSSL_VERSION_NUMBER < 0x10100000L
     ERR_remove_thread_state(0);
```

2. run the test case
connect (con1,localhost,root,,);
connect (con2,localhost,root,,);

connection default;
create table test.t1 (id int);
set global debug='+d,global_status_repeat_sum';

connection con2;
show global status like 'Com_select';

connection con1;
select * from test.t1;
select * from test.t1;
select * from test.t1;
select * from test.t1;
select * from test.t1;
select * from test.t1;
select * from test.t1;
select * from test.t1;
select * from test.t1;
--disconnect con1

connection con2;
show global status like 'Com_select';
select sleep(10);
show global status like 'Com_select';

connection default;
set global debug='-d,global_status_repeat_sum';
drop table test.t1;

3. The result file:
...
show global status like 'Com_select';
Variable_name	Value
Com_select	28
...
show global status like 'Com_select';
Variable_name	Value
Com_select	46
...
show global status like 'Com_select';
Variable_name	Value
Com_select	38

Suggested fix:
When thd->release_resources(), the thd's status is added to global global_status_var, but the local status is not cleaned.

On the older 'show status' logic, there is a variable thd->status_var_aggregated to help not re-aggregate. But the current show logic does not use the variable.

[31 May 2021 17:12] MySQL Verification Team

Hello Baolin Huang,

Thank you for the report and feedback.

regards,
Umesh

[6 Jun 6:19] ChangAo Chen

This patch may fix this bug:

diff --git a/storage/perfschema/pfs_visitor.cc b/storage/perfschema/pfs_visitor.cc
index e64884fe057..524cd7f54d4 100644
--- a/storage/perfschema/pfs_visitor.cc
+++ b/storage/perfschema/pfs_visitor.cc
@@ -1203,7 +1203,8 @@ void PFS_connection_status_visitor::visit_account(PFS_account *pfs) {
 void PFS_connection_status_visitor::visit_thread(PFS_thread *) {}
 
 void PFS_connection_status_visitor::visit_THD(THD *thd) {
-  add_to_status(m_status_vars, &thd->status_var);
+  if (!thd->status_var_aggregated)
+    add_to_status(m_status_vars, &thd->status_var);
 }
 
 PFS_instance_wait_visitor::PFS_instance_wait_visitor() = default;

[12 Aug 7:34] ChangAo Chen

Attach a patch to fix it.

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

Contribution: v1-0001-fix-bug-103804.patch (application/octet-stream, text), 937 bytes.

[12 Aug 7:42] MySQL Verification Team

Thank you for your contribution.

Sincerely,
Umesh Shastry