why doesn't mysql autocommit gets disabled?

Question

I am trying to disable autocommit. But it never gets off. I tried different methods.

   set autocommit=0;
   set autocommit='OFF';

Also, I edited my.cnf file in [client] section

   init-command='set autocommit=0'
   init-command='set autocommit='OFF''

Also, set global didn't work.

   set global autocommit=0;
   set global autocommit='OFF';

None of these really worked. Please help me how to turn off this autocommit thing.

Possible duplicate of [How do I turn off autocommit for a MySQL client?](http://stackoverflow.com/questions/2280465/how-do-i-turn-off-autocommit-for-a-mysql-client) — Doug, Nov 29 '16 at 00:09
Sorry Sir @doug . I don't see using a third party tool as a solution, I want the root cause and command line fix. No offense. — vkrishna, Nov 30 '16 at 05:49

score 1 · Answer 1 · answered Dec 01 '16 at 11:22

1

Do not edit [client] section for this.

Try by edit [mysqld] or [mysql] section of my.cnf for this change.

answered Dec 01 '16 at 11:22

Aman Aggarwal

DC Slagel · Answer 2 · 2019-04-06T02:32:45.360

From the screenshot png it looks like

set global autocommit='OFF';

was run twice and 'set autocommit='OFF' wasn't run.

If that is the case then

show global variables like '%autocommit%';

should report the global autocommit as OFF, while

show variables like '%autocommit%';

can still report autocommit as ON.

In testing with DELETE followed by a ROLLBACK, the local autocommit setting overrides the global autocommit setting.

2 Answers2