Bug #84305 The fix for Bug#78777 has different behavior with perf. schema vs. without it
Submitted: 22 Dec 2016 1:22 Modified: 24 Mar 2017 14:43
Reporter: Jay Edgar Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S2 (Serious)
Version:5.6.35 OS:Any
Assigned to: CPU Architecture:Any
Tags: performance schema

[22 Dec 2016 1:22] Jay Edgar
Description:
Bug 78777 (where idle threads showed a state as 'cleaning up') was fixed in 5.6.35, but the fix causes different behavior depending on whether you are using performance schema or not - the State data is different.

With performance schema:
show processlist;
Id      User    Host    db      Command Time    State   Info    Rows examined   Rows sent       Tid
2       root    localhost       test    Query   0       init    show processlist        0       0       411285

Without performance schema:
show processlist;
Id      User    Host    db      Command Time    State   Info    Rows examined   Rows sent       Tid
2       root    localhost       test    Query   0       NULL    show processlist        0       0       414212

How to repeat:
1. Build with performance schema (cmake -DWITHOUT_PERFSCHEMA_STORAGE_ENGINE=1; make -j32)
2. Start the server
3. Connect to the server
4. Issue a "SHOW PROCESSLIST;" command
5. Build without performance schema (cmake; make -j32)
6. Start the server
7. Connect to the server
8. Issue a "SHOW PROCESSLIST;" command

Suggested fix:
The problem is that the current location of setting the THD's state to INIT is occurring in code that is only executed via performance schema (net_after_header_psi()) and the code that calls this function doesn't appear to have access to the THD pointer.

I solved the problem by adding a call to "THD_STAGE_INFO(thd, stage_init);" in do_command() (sql/sql_parse.cc) after the packet has been fully read, but I don't know if this is the best location.

diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 6bfa5dd..7062c97 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -1098,6 +1098,7 @@ bool do_command(THD *thd)
     goto out;
   }

+  THD_STAGE_INFO(thd, stage_init);
   packet= (char*) net->read_pos;
   /*
     'packet_length' contains length of data, as it was stored in packet
[22 Dec 2016 7:47] Umesh Shastry
Hello Jay Edgar,

Thank you for the report.
Verified as described.

Thanks,
Umesh
[24 Mar 2017 14:43] Paul Dubois
Posted by developer:
 
Noted in 5.6.36, 5.7.19, 8.0.2 changelogs.

The fix for Bug #78777 had different effects depending on whether the
Performance Schema is enabled.