MySQL Bugs: #53630: include the connection ID in the slow query log

Bug #53630	include the connection ID in the slow query log
Submitted:	13 May 2010 20:44	Modified:	26 Apr 2011 14:58
Reporter:	Mark Callaghan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Logging	Severity:	S4 (Feature request)
Version:	5.1	OS:	Any
Assigned to:	Tatiana Azundris Nuernberg	CPU Architecture:	Any
Tags:	log, query, slow

Description:
The connection ID is not in the slow query log. It is very difficult to map the output back to transactions for concurrent workloads. The general log has the connection ID, but doesn't have performance data. I prefer not to join the logs to get all of the data that I need.

How to repeat:
general log output

Time                 Id Command    Argument
100513 13:24:01     7 Quit      
100513 13:24:13    10 Connect   root@db1 on test
                   10 Query     SHOW TABLE STATUS LIKE 'sbtest'

slow query log output

# User@Host: root[root] @ db2 [1.1.1.1]
# Query_time: 0.000026  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1273779632;
BEGIN;

Suggested fix:
Add the connection ID to the slow query log

Thank you for the feature request.

For this feature, the `mysql`.`slow_log` table structure must be amended, and this must be taken into account by the mysql_upgrade process....

Somewhat related (general lack of information in logging):

Bug #28377 (Add connection auditing log)
Bug #30924 (please record more metadata in binlog)
Bug #36022 (please log more information about "Sort aborted" queries)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

http://lists.mysql.com/commits/128077

3349 Tatiana Azundris Nurnberg 2011-01-06
Bug#53630: include the connection ID in the slow query log

Include thread_ID in both file and table forms of slow query log.
Add new column to upgrade and 1st_install paths (CREATE/ALTER TABLE
slow_log). Add integrity check for General and Slow Query Log Table.
@ mysql-test/suite/sys_vars/r/slow_query_log_func.result
nominally test for thread_id column
@ mysql-test/suite/sys_vars/t/slow_query_log_func.test
pro forma test, not that we needed one -- we don't test for the other
columns either, and pretty much any integer is a good integer, though
sanity should dictate that the last SLEEP() is from our connection.
@ scripts/mysql_system_tables.sql
if creating slow_log from scratch, have thread_id in there
@ scripts/mysql_system_tables_fix.sql
when upgrading an existing slow_log, add thread_id to slow_log if not already present
@ scripts/mysqldumpslow.sh
parse out thread_ID if it exists. not useful in itself, more something to
build additional filters on.
@ sql/log.cc
add thread_ID to slow_log, both the file and the table variants.
the table is what gets replicated if and when, no extra code required.
add integrity check for General and Slow Query Log Table.
@ sql/log.h
enum General and Slow Query Log Table fields, for clarity and checks

correcting, pushed for 5.6.2-m5

Noted in 5.6.2 changelog.

The server now includes the thread ID in rows written to the slow
query log. In the slow query log file, the thread ID is the last
value in the line. In the mysql.slow_loglog table, there is a new
thread_id column.
      
To update the slow_log table if you are upgrading from an earlier
release, run mysql_upgrade and restart the server.

CHANGESET - http://lists.mysql.com/commits/128077