Bug #74278 tĥread_id title in binary log output is wrong. Should be process(list)_id
Submitted: 8 Oct 2014 15:38 Modified: 21 Aug 2015 9:30
Reporter: Oli Sennhauser Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.6.17, 5.6.26 OS:Any
Assigned to: CPU Architecture:Any
Tags: binary log, CONNECTION_ID, processlist_id, thread_id

[8 Oct 2014 15:38] Oli Sennhauser
Description:
The output of:

shell> mysqlbinlog binlog.000193

delivers wrong or at least inconsisten/missleading information:

# at 120
#141008 17:26:18 server id 15614  end_log_pos 192 CRC32 0x7ef0e34d      Query   thread_id=29    exec_time=0     error_code=0

the thread_id (like in Performance Schema) is in fact not the thread_id but the processlist_id, process_id or connection_id.

How to repeat:
mysql> INSERT INTO test VALUES (NULL, 'P_S test', NULL);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT CONNECTION_ID();
+-----------------+
| connection_id() |
+-----------------+
|              29 |
+-----------------+

mysql> SELECT * FROM threads WHERE processlist_id = 29;
+-----------+---------------------------+------------+----------------+------------------+------------------+--------------------+---------------------+------------------+-------------------+-------------------------------------------------+------------------+------+--------------+
| THREAD_ID | NAME                      | TYPE       | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB     | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO                                | PARENT_THREAD_ID | ROLE | INSTRUMENTED |
+-----------+---------------------------+------------+----------------+------------------+------------------+--------------------+---------------------+------------------+-------------------+-------------------------------------------------+------------------+------+--------------+
|        48 | thread/sql/one_connection | FOREGROUND |             29 | root             | localhost        | performance_schema | Query               |                0 | Sending data      | select * from threads where processlist_id = 29 |             NULL | NULL | YES          |
+-----------+---------------------------+------------+----------------+------------------+------------------+--------------------+---------------------+------------------+-------------------+-------------------------------------------------+------------------+------+--------------+

shell> grep ' 29 ' general_test.log 
Time               Id Command    Argument
141008 17:26:18    29 Query     insert into test values (null, 'P_S test', null)
141008 17:26:25    29 Query     select process_id()
141008 17:26:31    29 Query     select connection_id()
141008 17:27:11    29 Query     SELECT DATABASE()
                   29 Init DB   performance_schema
141008 17:27:43    29 Query     select * from threads where processlist_id = 29
141008 17:28:39    29 Quit

shell> mysqlbinlog binlog.000193
# at 120
#141008 17:26:18 server id 15614  end_log_pos 192 CRC32 0x7ef0e34d      Query   thread_id=29    exec_time=0     error_code=0

Suggested fix:
A consistent naming of the values should be used over the whole instance: Either connection_id or process_id or processlist_id but it IS NOT the thread_id according to the performance_schema naming conventions!!!
[17 Jul 2015 14:17] Georgi Kodinov
mysqlbinlog belongs to the replication category.
[21 Aug 2015 9:30] Umesh Shastry
Hello Oli,

Thank you for the report.
Confirmed this with 5.6.26 build.

Thanks,
Umesh