7

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.

Improve this question
4
  • Updated My Answer. Restart Required :(
    – RolandoMySQLDBA
    Aug 1, 2011 at 21:44
  • Did you restart mysql after adding autocommit=0 to my.ini ?
    – RolandoMySQLDBA
    Aug 2, 2011 at 4:05
  • I restarted mysql, and after that didn't seem to help I restarted the whole machine.
    – Denis Sadowski
    Aug 2, 2011 at 16:25
  • Updated My Answer with regard to MySQL Workbench.
    – RolandoMySQLDBA
    Aug 3, 2011 at 16:18

5 Answers 5

Reset to default
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:

  • Open MySQL Workbench
  • Click Edit on Main Menu Bar
  • Click Preferences Under Edit
  • Click SQL Editor Tab
  • Look for the Last CheckBox Under Query Results

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.

Improve this answer
5
  • 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
    Aug 1, 2011 at 21:32
  • 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
    Aug 1, 2011 at 22:32
  • autocommit=0 does not work init_connect="SET AUTOCOMMIT=0" which the docs recommend, also does not work
    – Noah Yetter
    Aug 4, 2011 at 4:13
  • 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
    Apr 20, 2015 at 15:12
  • 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
    Apr 20, 2015 at 15:12
2

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.

Improve this answer
1

Try SET SESSION autocommit = 0; This switch the autocommit flag to OFF in that session.

It worked for me.

Improve this answer
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"
Improve this answer
1

As of MySQL Workbench 6.3 you can just go to Query->Auto-Commit Transactions

enter image description here

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .