Queries logged in the 'Slow query log', and 'start transaction' vs 'set autocommit = 0'
1
vote
1
answer
527
views
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; # (*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 rollback
ed.
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 )*
Asked by Déjà vu
(555 rep)
Aug 23, 2018, 04:03 AM
Last activity: Sep 1, 2018, 06:01 AM
Last activity: Sep 1, 2018, 06:01 AM