Sample Header Ad - 728x90

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 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 )*
Asked by Déjà vu (555 rep)
Aug 23, 2018, 04:03 AM
Last activity: Sep 1, 2018, 06:01 AM