3

I have to set autocommit to false, and I have done the changes in my.cnf configuration file.

I have added autocommit=0 for setting globally autocommit OFF but after adding autocommit=0 property in my my.cnf file my sql server not started.

But when I comment this new added line my sql server started properly.

I m starting my sql server with "/etc/init.d/mysql start" this command.

Is there any wrong to setting autocommit false?

If so, then anyone please tell me how I can do that?

please reply me as soon as possible.

thank you.

S L
  • 14,262
  • 17
  • 77
  • 116
Lokesh Paunikar
  • 1,679
  • 5
  • 21
  • 26
  • 1
    See http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_autocommit for how to set it in your options file – Dan Grossman Feb 07 '11 at 08:46

3 Answers3

6

phew..So this is 2015 December, close to new year and I have mysql 5.6 installed on my macbook pro. After trying all the above options, I wasn't able to get the autocommit to be 'OFF' by default. And after spending a lot of time on this, I was finally able to figure this out. Thought I might share it here, as there a few things that are easy to miss, even when you do everything else right.

  1. syntax of turning the autocommit off globally

    [mysqld]

    autocommit=0

  2. Where you're turning this off: As per the answer to this post by @nanda, it can be done in three ways. So if we were to do it globally, you will add the above line in the my.cnf file

  3. Location of my.cnf (very easy to miss, and this is where I wasted most of the time) As per this mysql dev guideline (please pay attention to the version of your mysql and look for the same documentation in your appropriate version).

enter image description here

The my.cnf file has to be in '/etc' folder. In my case, the my.cnf file is located at /usr/local/mysql/my.cnf

This can vary depending on how you installed your mysql, and also your platform. So only when I copied my my.cnf to the /etc location it started to work right.

Hope this helps. cheers!

Community
  • 1
  • 1
brownmamba
  • 755
  • 1
  • 8
  • 16
  • To find the `my.cnf`, you should find the line after "Default options are read from the following files in the given order:" when doing `mysql --help`. It shows a lift of possible locations for your `my.cnf` – Luuk Jun 20 '21 at 17:30
4

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_autocommit

To set the variable as just shown using an option file, include these lines:

[mysqld]
init_connect='SET autocommit=0'
juangiordana
  • 105
  • 6
1

I know it's an old question but I ran into this one myself today and I just had to put it out there.

Even though I've 

set autocommit = 0;

on all my transactions, I was not allowed to rollback and all the changes were autocommited. I just got the Warning: some non-transactional changed tables couldn't be rolled back. Setting the 

init_connect='SET autocommit=0'

(as @juangiordana mentioned above, quoting the MySQL official documetation) did not fix my problem either.

So by doing a 

SHOW TABLE STATUS LIKE 'some_table_name_here';

you can find out which Engine your tables are created on. So the thing is. the Default Engine as of MySQL 3.23 is MyISAM which doesn't support autocommit=0 because it's a non-transactional table.

Now I've switched to InnoDB and it works like a charm.

Community
  • 1
  • 1
Vlad A.
  • 181
  • 2
  • 8