Bug #70423 performance_schema.replication_execute_status_by_worker wrong thread ID
Submitted: 25 Sep 2013 20:38 Modified: 24 Oct 2013 13:01
Reporter: Marcelo Altmann (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.7.2 OS:Linux
Assigned to: CPU Architecture:Any

[25 Sep 2013 20:38] Marcelo Altmann
Description:
When showing replication status from performance_schema.replication_execute_status_by_worker it shows a thread ID that differ from SHOW PROCESSLIST

How to repeat:
mysql> select * from performance_schema.replication_execute_status_by_worker\G
*************************** 1. row ***************************
         CHANNEL_NAME: CHANNEL1
            WORKER_ID: 0
            THREAD_ID: 30
        SERVICE_STATE: ON
LAST_SEEN_TRANSACTION: 
    LAST_ERROR_NUMBER: 0
   LAST_ERROR_MESSAGE: 
 LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 2. row ***************************
         CHANNEL_NAME: CHANNEL1
            WORKER_ID: 1
            THREAD_ID: 31
        SERVICE_STATE: ON
LAST_SEEN_TRANSACTION: 
    LAST_ERROR_NUMBER: 0
   LAST_ERROR_MESSAGE: 
 LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 3. row ***************************
         CHANNEL_NAME: CHANNEL2
            WORKER_ID: 0
            THREAD_ID: 34
        SERVICE_STATE: ON
LAST_SEEN_TRANSACTION: 
    LAST_ERROR_NUMBER: 0
   LAST_ERROR_MESSAGE: 
 LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 4. row ***************************
         CHANNEL_NAME: CHANNEL2
            WORKER_ID: 1
            THREAD_ID: 35
        SERVICE_STATE: ON
LAST_SEEN_TRANSACTION: 
    LAST_ERROR_NUMBER: 0
   LAST_ERROR_MESSAGE: 
 LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
4 rows in set (0.00 sec)

mysql> show processlist;
+----+-------------+-----------+--------------------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User        | Host      | db                 | Command | Time | State                                                                       | Info             |
+----+-------------+-----------+--------------------+---------+------+-----------------------------------------------------------------------------+------------------+
|  8 | root        | localhost | performance_schema | Sleep   |  572 |                                                                             | NULL             |
|  9 | system user |           | NULL               | Connect |  592 | Waiting for master to send event                                            | NULL             |
| 10 | system user |           | NULL               | Connect |  592 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
| 11 | system user |           | NULL               | Connect |  592 | Waiting for an event from Coordinator                                       | NULL             |
| 12 | system user |           | NULL               | Connect |  592 | Waiting for an event from Coordinator                                       | NULL             |
| 13 | system user |           | NULL               | Connect |  592 | Waiting for master to send event                                            | NULL             |
| 14 | system user |           | NULL               | Connect |  591 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
| 15 | system user |           | NULL               | Connect |  592 | Waiting for an event from Coordinator                                       | NULL             |
| 16 | system user |           | NULL               | Connect |  592 | Waiting for an event from Coordinator                                       | NULL             |
| 17 | root        | localhost | performance_schema | Query   |    0 | init                                                                        | show processlist |
+----+-------------+-----------+--------------------+---------+------+-----------------------------------------------------------------------------+------------------+
10 rows in set (0.00 sec)

Suggested fix:
display the right thread ID
[27 Sep 2013 8:33] MySQL Verification Team
Hello Marcelo,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh
[27 Sep 2013 20:49] Shivji Jha
What this bug report states is not a bug in the multi-source
replication labs release but a bug in MySQL 5.7.2 itself.
Note that what Multi source replication labs release now shows
is the intended behaviour.

Some background:
===============
information_schema.Id is the *connection* identifier.
performance_schema's thread_id is a unique thread identifier
(including *both background and foreground threads*), see
http://dev.mysql.com/doc/refman/5.7/en/threads-table.html
Both I_S and P_S maintain their own counters which they
increment with every new thread/connection and hence the
two may differ for say, IO/SQL/worker threads.

About the bug:
=============
In 5.7.2 the replication P_S tables show in thread_id
column what I_S.processlist shows in the Id field.
This is identified as a bug and we have corrected it in
Multi source replication labs release, but not in 5.7 yet.
After this bug fix, we will have the following situation:

1) Thread_id column in replication P_S tables show what
   other P_S tables do.
2) Thread_id column in replication P_S tables may NOT
   match I_S.processlist.Id
3) The mapping between I_S.processlist.Id and
   P_S.thread_id can be seen in the table P_S.threads
[24 Oct 2013 13:01] Jon Stephens
Fixed in 5.7, documented as follows in the 5.7.3 changelog:

        The THREAD_ID column values shown in the
        PERFORMANCE_SCHEMA.REPLICATION_EXECUTE_STATUS_BY_WORKER table
        used the same thread IDs shown in the output from SHOW
        PROCESSLIST, rather than those used by other PERFORMANCE_SCHEMA
        tables.
      

Closed.