6
I'm running MySQL Workbench 5.2, and whenever I make a connection to a database auto commit is toggled on.
Is there any way to have the default behavior be changes so that auto commit is toggled off, I'm forgetful and don't want to cause a problematic committed updated just because I forgot to toggle it off manually.
Denis Sadowski
Posted 2011-08-01T21:04:16.970
Reputation: 161
4
Add this to /etc/my.cnf (Linux) or my.ini (Windows)
[mysqld]
autocommit=0
Restart of mysql required !!! :(
UPDATE 2011-08-03 12:18 EDT
In MySQL Workbench you need to make sure the Data Changes Wizard is enabled. Here is why:
Enable Data Changes Commit Wizard - In the SQL Editor, when editing table data and then clicking the Applying changes to data button, a wizard is launched to step you through applying you changes. This gives you a chance to review the SQL that will be applied to the live server to make the requested changes. If the option is deselected, then the changes will simply be applied to the server, without the wizard being displayed, and without a chance to review the changes that will be made.
Here is how to check:
If it is checked, data gets committed via a Wizard requiring user intervention.
If it is unchecked, data gets committed, bypassing MySQL's autocommit protocols.
RolandoMySQLDBA
Posted 2011-08-01T21:04:16.970
Reputation: 2 675
This is simply incorrect. First, the wizard is a separate layer of "protection"; if you cancel an operation in the wizard then the SQL won't execute at all (and then there's nothing to commit), while if it's approved it will be executed. After it has been executed (whether with the wizard or not), the "autocommit" parameter will determine whether an auto-commit will occur or not. Second... (next comment) – Eyal Roth – 2015-04-20T15:12:53.970
Second, if the client (any client) doesn't override the "autocommit" parameter, it will be determined by the global one defined in the DB (in MySQL the default is TRUE). It is possible for the client to override the parameter in each session (running an SQL command), or somehow changing the default behaviour of the client. The method in the answer will not work. I will add a correct answer. – Eyal Roth – 2015-04-20T15:12:58.827
I tried running both SET GLOBAL autocommit = 0 and SET GLOBAL autocommit = 'OFF' from within MySQL Workbench after connecting to a db and I got the following error:
Error Code: 1228. Variable 'autocommit' is a SESSION variable and can't be used with SET GLOBAL – Denis Sadowski – 2011-08-01T21:32:49.457
I tried a restart, and when I run "select @@autocommit" its is still set to 1.
I placed the my.ini file under C:/, C:/Windows, and the Workbench program files directories and none of them helped. :( – Denis Sadowski – 2011-08-01T22:32:57.237
autocommit=0 does not work init_connect="SET AUTOCOMMIT=0" which the docs recommend, also does not work – Noah Yetter – 2011-08-04T04:13:37.093
1
Try SET SESSION autocommit = 0; This switch the autocommit flag to OFF in that session.
It worked for me.
Vijay
Posted 2011-08-01T21:04:16.970
Reputation: 11
1
As of MySQL Workbench 6.0.0, you can set the "Leave autocommit mode enabled by default" preference. Set it under Preferences --> SQL Queries --> General.
philip
Posted 2011-08-01T21:04:16.970
Reputation: 276
1
I'll start by mentioning my own question posted on stack-overflow about a year ago, and it's somewhat of the same question: How can I set client configuration for MySQL Workbench without an installed server?
As already mentioned in an answer here, as of MySQL WB 6 you can set the "leave autocommit mode enabled by default" preference in Preferences > SQL Queries > General.
For earlier versions it might be possible to configure the "my.ini" file.
Why only "possibly"? Because it worked for me before (WB 5?), but doesn't work now on WB 6.3.
First, we need to find out where does WB is looking for the file. There are several questions on that subject, so you might have to fiddle with it a little (frankly, it's a messy issue).
The best way, if available, is to go to the Server menu and then press "Options File". It will either tell you that the file doesn't exist (and will tell you the path it's looking for), or it will open the configuration and will specify the path at the bottom.
Now, we need to add the following line into the "my.ini" file, under the [client] section (add it as well if it doesn't exist):
init-command="SET autocommit=0"
Eyal Roth
Posted 2011-08-01T21:04:16.970
Reputation: 137
Updated My Answer. Restart Required :( – RolandoMySQLDBA – 2011-08-01T21:44:31.790
Did you restart mysql after adding autocommit=0 to my.ini ? – RolandoMySQLDBA – 2011-08-02T04:05:46.470
I restarted mysql, and after that didn't seem to help I restarted the whole machine. – Denis Sadowski – 2011-08-02T16:25:03.840
Updated My Answer with regard to MySQL Workbench. – RolandoMySQLDBA – 2011-08-03T16:18:23.707