Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

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

3 Answers 3

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.

share|improve this answer
    
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 '11 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 '11 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 '11 at 4:13

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

It worked for me.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.