Currently long_query_time is set to 5, and that works.
However, many queries are part of a transaction, and the slow query log only keeps the final commit;.
# Time: 2018-08-15T15:18:18.205309Z
# User@Host: bijin[bijin] @ localhost [127.0.0.1] Id: 3274529
# Query_time: 7.510110 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
use interferences_db;
SET timestamp=1534329958;
commit; # <=== only 'commit' is logged
Which is not very informative ( while it's correct, as seen from the db server ).
Is there a way to see the queries up to the commit, meaning all queries from the transaction?
(or at least some part of it allowing to identify the culprit query)
~~~ edit ~~~
The answer from @AmitPatel had me perform some tests using
set autocommit = 0
instead of
start transaction
to initiate a transaction and, indeed, with autocommit = 0 the slow query log has the details of every slow query, even if they're eventually rollbacked.
So, in order to log slow queries that are part of a transaction, can I safely replace, in the code,
start transaction .... commit / rollback
with
set autocommit = 0 .... commit or rollback and set autocommit = 1 ?
( The final autocommit = 1 is to keep the same behavior in the code, after a transaction is rollbacked or committed )
