Bug #104575 Duplicated threads in performance_schema.threads table

Description:
The `srv_purge_thread` and `srv_worker_thread` threads are duplicated in the P_S.threads table.

Here is the result:

mysql> select thread_id,name,thread_os_id from performance_schema.threads;
+-----------+---------------------------------------------+--------------+
| thread_id | name                                        | thread_os_id |
+-----------+---------------------------------------------+--------------+
|         1 | thread/sql/main                             |        15962 |
|         3 | thread/innodb/io_ibuf_thread                |        15967 |
...
|        32 | thread/innodb/buf_dump_thread               |        15996 |
|        33 | thread/innodb/clone_gtid_thread             |        15997 |
|        34 | thread/innodb/srv_purge_thread              |        15998 |  <<<<<<<<<<
|        35 | thread/innodb/srv_purge_thread              |        15998 |  <<<<<<<<<<
|        36 | thread/innodb/srv_worker_thread             |        15999 |  <<<<<<<<<<
|        37 | thread/innodb/srv_worker_thread             |        15999 |  <<<<<<<<<<
|        38 | thread/innodb/srv_worker_thread             |        16000 |  <<<<<<<<<<
|        39 | thread/innodb/srv_worker_thread             |        16000 |  <<<<<<<<<<
|        40 | thread/innodb/srv_worker_thread             |        16001 |  <<<<<<<<<<
|        41 | thread/innodb/srv_worker_thread             |        16001 |  <<<<<<<<<<
|        42 | thread/sql/event_scheduler                  |        16002 |
...
|        47 | thread/sql/one_connection                   |        16008 |
+-----------+---------------------------------------------+--------------+
43 rows in set (0.05 sec)

Thread 35 is duplicated to thread 34, because they share the same thread_os_id. 
Thread 37/39/41 have the same problem.

I digged the code, the root cause are here:

for `srv_purge_thread`:

/storage/innobase/srv/srv0srv.cc
    3052 void srv_purge_coordinator_thread() {
    3053   srv_slot_t *slot;
    3054
    3055 #ifdef UNIV_PFS_THREAD
>   3056   THD *thd = create_thd(false, true, true, srv_purge_thread_key.m_value);
    3057 #else
    3058   THD *thd = create_thd(false, true, true, 0);
    3059 #endif

for `srv_worker_thread`:

/storage/innobase/srv/srv0srv.cc
    2801 void srv_worker_thread() {
    ...
    2807 #ifdef UNIV_PFS_THREAD
>   2808   THD *thd = create_thd(false, true, true, srv_worker_thread_key.m_value);
    2809 #else
    2810   THD *thd = create_thd(false, true, true, 0);
    2811 #endif

`create_thd` calls `thread_init`, and eventually made `create_thd` in pfs.cc:L2965 been called twice.

/storage/perfschema/pfs.cc
    2959 PSI_thread *pfs_new_thread_vc(PSI_thread_key key, const void *identity,
    2960                               ulonglong processlist_id) {
    2961   PFS_thread *pfs;
    2962
    2963   PFS_thread_class *klass = find_thread_class(key);
    2964   if (likely(klass != nullptr)) {
>>  2965     pfs = create_thread(klass, identity, processlist_id);
    2966   } else {
    2967     pfs = nullptr;
    2968   }

How to repeat:
select THREAD_ID,NAME,THREAD_OS_ID from performance_schema.threads;

Suggested fix:
L2807~L2811 and L3055~L3059 in /storage/innobase/srv/srv0srv.cc might should be deleted.

Hello Kaige Ye,

Thank you for the report and feedback.

regards,
Umesh

Contribution submitted via Github - Bug #104575	Duplicated threads in performance_schema.threads table 
(*) Contribution by Kaige Ye (Github YKG, mysql-server/pull/356#issuecomment-906852097): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_707003564.txt (text/plain), 1.12 KiB.

Thank you for your contribution.

regards,
Umesh

Posted by developer:
 
Fixed as of the upcoming 8.0.27 release:

The srv_purge_thread and srv_worker_thread threads were duplicated in the
performance_schema.threads table. 

Thanks to Kaige Ye for the contribution.

Fixed as of the upcoming 8.0.27 release:

The srv_purge_thread and srv_worker_thread threads were duplicated in the
performance_schema.threads table.

Thanks to Kaige Ye for the contribution.