Bug #57316 It is not clear how to disable autocommit
Submitted: 7 Oct 2010 15:10 Modified: 10 Jan 2011 15:45
Reporter: Sveta Smirnova Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.5+ OS:Any
Assigned to: Guilhem Bichot CPU Architecture:Any

[7 Oct 2010 15:10] Sveta Smirnova
Description:
autocommit is session variable and it is not clear for every user how to change it to 0 for every connection. We have bug #51175 about having this variable as global, but while this is not implemented would be good our documentation provides some ideas how to have it set to 0 for every connection.

How to repeat:
Open http://dev.mysql.com/doc/refman/5.1/en/commit.html, read about autocommit

Suggested fix:
Mention additionally about autocommit is session variable and must be changed to every connection. Add example of init_connect like in bug mentioned in the initial description.
[12 Oct 2010 16:11] Paul Dubois
I modified the description of the autocommit variable to indicate that you can set the init_connect variable to a value of "SET autocommit=0" to disable autocommit for new connections. (Actually, it just points to the description for init_connect, which already described how to do this.)

Recategorizing report as Server bug, because as of 5.5.3 there is a global autocommit variable that could be used for this. However, you cannot set it at server startup. That is, you cannot use
--autocommit=0 at server startup to make the autocommit default be disabled for clients. (The server produces an error and exits.)
[22 Oct 2010 13:32] Guilhem Bichot
It is accidental that @@global.autocommit was introduced, I will remove it in a week.
[2 Nov 2010 11:10] Guilhem Bichot
No it was not accidental, I checked with Sergei who made the change.
So we should now implement the "--autocommit" command-line option, so that we have "something complete" (a session and global variable with a command-line option). I think this would also address a great part of BUG#51175.

That should be an easy fix which I can do.
[2 Nov 2010 21:15] Guilhem Bichot
Several variables went from session-only in 5.1 to global-and-session in 5.5, just like @@autocommit: they are
autocommit
big_tables
sql_big_selects
sql_log_off
sql_log_bin
sql_warnings
sql_notes
sql_auto_is_null
sql_safe_updates
sql_buffer_result
sql_quote_show_create
foreign_key_checks
unique_checks
profiling.
So, in all logic, if we implement a command-line option for @@autocommit, we should also implement a command-line option for all others above.
Now, if we want to do this, there is a technical problem: all variables above (except big_tables and sql_log_bin) are implemented using class Sys_var_bit, and as a comment says in sys_vars.h:
"variables of this class cannot be set from the command line as
my_getopt does not support bits." (my_getopt is the in-house library which parses command line options).

I see those alternative solutions:
- modify the my_getopt library, to make this comment wrong
- change all those variables from Sys_var_bit to Sys_var_mybool. Thus each of the 12 variables would be stored in a byte (instead of in a bit as now), which would grow the size of class system_variables, and thus of THD, by a few bytes. And a change to THD is a change to the storage engine API: is this allowed in 5.5?
- document the workaround below: to set any of those variables globally at server's startup, put in a file:
set global thevar=value
set global theothervar=othervalue
etc
and start mysqld with --init-file=thefileabove .
It will execute all those SET GLOBAL commands before creating connections; so when a connection is created, the session's variable will be the global value set in the file. In effect this achieves what --thevar=value would do.
[2 Nov 2010 22:02] Guilhem Bichot
Effort needed for each alternative:
- modify my_getopt: several days, risk of bugs
- change type of variables to Sys_var_mybool: one day, two if some problem arises
- workaround: only some documentation effort.
[4 Nov 2010 14:14] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/122842

3115 Guilhem Bichot	2010-11-04
      Fix for BUG#57316 "It is not clear how to disable autocommit"
      add boolean command-line option --autocommit. See sys_vars.cc.
     @ mysql-test/mysql-test-run.pl
        do in --gdb like in --ddd: to let the developer debug the startup
        phase (like command-line options parsing), don't "run".
        It's the third time I do this change, it was previously lost
        by merges, port of 6.0 to next-mr...
     @ mysql-test/r/mysqld--help-notwin.result
        new command-line option
     @ mysql-test/r/mysqld--help-win.result
        a Linux user's best guess at what the Windows result should be
     @ mysql-test/suite/sys_vars/inc/autocommit_func2.inc
        new test
     @ mysql-test/suite/sys_vars/t/autocommit_func2-master.opt
        test new option
     @ mysql-test/suite/sys_vars/t/autocommit_func3-master.opt
        test new option
     @ sql/mysqld.cc
        OPTION_AUTOCOMMIT is gone
     @ sql/sql_class.cc
        As we change option_bits, we need to keep "autocommit" in sync.
     @ sql/sql_partition.cc
        What matters to this partitioning quote is to have
        the OPTION_QUOTE_SHOW_CREATE flag down, it's all
        that append_identifier() uses. So we make it explicit.
        If we kept the "=0", we would have option_bits and "autocommit"
        not in sync, so we would need to set "autocommit" to 0,
        then restore it (and so, first save it).
     @ sql/sql_priv.h
        "system_variables::option_bits & OPTION_AUTOCOMMIT" is replaced by system_variables::autocommit
     @ sql/sys_vars.cc
        Sys_var_bit doesn't support command-line options, and we want
        one for "autocommit", so make autocommit a Sys_var_mybool.
        autocommit used to be remembered with the
        OPTION_NOT_AUTOCOMMIT flag in system_variables::option_bits;
        we keep this, as it's used all around and also option_bits
        is stored in the binlog. We keep the flag and the new
        "autocommit" my_bool in sync all the time.
        The OPTION_AUTOCOMMIT flag served only when updating @@autocommit,
        we delete it as system_variables::autocommit can replace it.
[5 Nov 2010 9:25] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/122910

3115 Guilhem Bichot	2010-11-05
      Fix for BUG#57316 "It is not clear how to disable autocommit"
      add boolean command-line option --autocommit.
     @ mysql-test/mysql-test-run.pl
        do in --gdb like in --ddd: to let the developer debug the startup
        phase (like command-line options parsing), don't "run".
        It's the third time I do this change, it was previously lost
        by merges, port of 6.0 to next-mr...
     @ mysql-test/r/mysqld--help-notwin.result
        new command-line option
     @ mysql-test/r/mysqld--help-win.result
        a Linux user's best guess at what the Windows result should be
     @ mysql-test/suite/sys_vars/inc/autocommit_func2.inc
        new test
     @ mysql-test/suite/sys_vars/t/autocommit_func2-master.opt
        test new option
     @ mysql-test/suite/sys_vars/t/autocommit_func3-master.opt
        test new option
     @ sql/mysqld.cc
        new --autocommit
     @ sql/mysqld.h
        new --autocommit
     @ sql/sql_partition.cc
        What matters to this partitioning quote is to have
        the OPTION_QUOTE_SHOW_CREATE flag down, it's all
        that append_identifier() uses. So we make it explicit.
[5 Nov 2010 13:16] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/122948

3115 Guilhem Bichot	2010-11-05
      Fix for BUG#57316 "It is not clear how to disable autocommit"
      add boolean command-line option --autocommit.
     @ mysql-test/mysql-test-run.pl
        do in --gdb like in --ddd: to let the developer debug the startup
        phase (like command-line options parsing), don't "run".
        It's the third time I do this change, it was previously lost
        by merges, port of 6.0 to next-mr...
     @ mysql-test/r/mysqld--help-notwin.result
        new command-line option
     @ mysql-test/r/mysqld--help-win.result
        a Linux user's best guess at what the Windows result should be
     @ mysql-test/suite/sys_vars/inc/autocommit_func2.inc
        new test
     @ mysql-test/suite/sys_vars/t/autocommit_func2-master.opt
        test new option
     @ mysql-test/suite/sys_vars/t/autocommit_func3-master.opt
        test new option
     @ sql/mysqld.cc
        new --autocommit
     @ sql/mysqld.h
        new --autocommit
     @ sql/sql_partition.cc
        What matters to this partitioning quote is to have
        the OPTION_QUOTE_SHOW_CREATE flag down, it's all
        that append_identifier() uses. So we make it explicit.
[5 Nov 2010 13:29] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/122952

3123 Guilhem Bichot	2010-11-05
      Fix for BUG#57316 "It is not clear how to disable autocommit"
      add boolean command-line option --autocommit.
     @ mysql-test/mysql-test-run.pl
        do in --gdb like in --ddd: to let the developer debug the startup
        phase (like command-line options parsing), don't "run".
        It's the third time I do this change, it was previously lost
        by merges, port of 6.0 to next-mr...
     @ mysql-test/r/mysqld--help-notwin.result
        new command-line option
     @ mysql-test/r/mysqld--help-win.result
        a Linux user's best guess at what the Windows result should be
     @ mysql-test/suite/sys_vars/inc/autocommit_func2.inc
        new test
     @ mysql-test/suite/sys_vars/t/autocommit_func2-master.opt
        test new option
     @ mysql-test/suite/sys_vars/t/autocommit_func3-master.opt
        test new option
     @ sql/mysqld.cc
        new --autocommit
     @ sql/mysqld.h
        new --autocommit
     @ sql/sql_partition.cc
        What matters to this partitioning quote is to have
        the OPTION_QUOTE_SHOW_CREATE flag down, it's all
        that append_identifier() uses. So we make it explicit.
[5 Nov 2010 14:15] Guilhem Bichot
queued to 5.1-bugteam, 5.5-bugteam, trunk-bugfixing.
Reminder: since 5.5.3 there is a global variable @@autocommit, but there was no corresponding command-line option.
Changelog for this bugfix: added command-line option --autocommit to the MySQL server. --autocommit means "global value of @@autocommit is set to 1"; so do --autocommit=1 and --enable-autocommit; it is the default. --autocommit=0, --disable-autocommit and --skip-autocommit mean "global value of @@autocommit is set to 0".
This makes the documented "init_connect workaround" unneeded. Note that contrary to init_connect, using --autocommit applies to users having SUPER too.
[13 Nov 2010 16:14] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)
[13 Nov 2010 16:37] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:alexander.nozdrin@oracle.com-20101113152540-gxro4g0v29l27f5x) (pib:21)
[16 Dec 2010 22:31] Bugs System
Pushed into mysql-5.5 5.5.9 (revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (version source revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)
[10 Jan 2011 15:45] Paul Dubois
Noted in 5.5.8 changelog.

The autocommit system variable is enabled by default for all user
connections, and the session value can be set for each new connection
by setting the init_connect system variable to SET autoconnect=0.
However, this has no effect for users who have the SUPER privilege.

Now the global autocommit value can be set at server startup, and
this value is used to initialize the session value for all new
connections, including those for users with the SUPER privilege. The
variable is treated as a boolean value so it can be enabled with
--autocommit, --autocommit=1, or --enable-autocommit. It can be
disabled with --autocommit=0, --skip-autocommit, or
--disable-autocommit.
[10 Jan 2011 16:05] Valeriy Kravchuk
This effectively implements also Bug #51175.
[11 Jan 2011 20:30] Guilhem Bichot
In an earlier post I wrote:
"queued to 5.1-bugteam, 5.5-bugteam, trunk-bugfixing"
but this is incorrect: it was not queued to 5.1-bugteam. No fix was pushed to 5.1, and none will be ("target version" is 5.5+).
Sorry for confusion.