InnoDB_trx_id not logged in slow query log for BEGIN/COMMIT

[Aurimas Mikalauskas]

When using InnoDB_trx_id to filter out statements issued by a certain transaction, it is not easy to get the time transaction started and finished exactly, espcially if connections are reused by many transactions. Had InnoDB_trx_id been logged for BEGIN/COMMIT statement, that would help me save a great deal of time in this process.

AFAIK, there's no InnoDB_trx_id when BEGIN statement is run since transaction is only started when first statement is run against InnoDB table, however it should definitely be known at the time of COMMIT.

Here's a sample slow query log entry for BEGIN/COMMIT statement:

# Time: 111129 13:35:25.0000307027
# User@Host: s[s] @ a [172.16.0.126]
# Thread_id: 3683352 Schema: s Last_errno: 0 Killed: 0
# Query_time: 0.000017 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 Rows_affected: 0 Rows_read: 1
# Bytes_sent: 11 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# Filesort: No Filesort_on_disk: No Merge_passes: 0
# No InnoDB statistics available for this query
SET timestamp=1322573725;
BEGIN;

# Time: 111129 13:35:25.0000305323
# User@Host: s[s] @ a [172.16.0.126]
# Thread_id: 3683352 Schema: s Last_errno: 0 Killed: 0
# Query_time: 0.000565 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 Rows_affected: 0 Rows_read: 1
# Bytes_sent: 11 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# Filesort: No Filesort_on_disk: No Merge_passes: 0
# No InnoDB statistics available for this query
SET timestamp=1322573725;
COMMIT;

[Valentine Gostev (longbow)]

Since we cannot retrieve InnoDB_trx_id for BEGIN statement, as MySQL does not know wether we will touch InnoDB tables or not, InnoDB_trx_id could not be used to identify transaction ID for BEGIN statement. At the same it is possible to find InnoDB_trx_id if transaction contained queries related to InnoDB.

But it make sense to use the same transaction identity both for BEGIN and COMMIT.

What should be done:
1. Check the available list of identities, which could be used for BEGIN and COMMIT (and possibly all other queries)
2. Implement

Marking bug as a Feature Request. Passing to Oleg

[Nickolay Ihalainen (ihanick)]

https://github.com/percona/percona-server/pull/1243
https://github.com/percona/percona-server/pull/1302

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1228