1

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 )

2

Mysql run with autocommit feature , it look like your application code forcefully commit the each DML.

  • Please see my edit – Ring Ø Sep 1 '18 at 6:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.