| 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: | |
| 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
[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.
