[Solved] Disable mySQL autocommit

Hi.

Does anyone know how I can disable auto-commit in mySQL?

I've been Googling it for ages, and one suggestion that always comes up is adding this to /etc/my.cnf :

[mysqld]
...
init_connect='SET autocommit=0'

But that didn't work. I then read that this wouldn't work anyway for a priviliged user, so I created another user with very basic rights (select, insert, update, delete), and still it doesn't work.

So, thinking it might be because I was connecting from localhost, I installed a new VM and mySQL, and, after messing around for ages with iptables, managed to connect remotely to the mySQL server. But still it auto-commits.

I'm probably doing / missing something stupid, and it's driving me up the wall, so any help would be greatly appreciated.

TIA.

[scott@localhost ~]$ mysql --version
mysql  Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (x86_64) using readline 5.1

[scott@localhost ~]$ uname -a
Linux localhost.localdomain 2.6.18-238.12.1.el5 #1 SMP Tue May 31 13:22:04 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux

[scott@localhost ~]$ cat /etc/redhat-release 
CentOS release 5.6 (Final)

Hi Scott,
which storage engine are you using (show create table <table_name>;)?
Bear in mind that MySQL 5.0.x default's storage engine is MyISAM and that storage engine doesn't support transactions (you cannot rollback a statement).

You should take a look at InnoDB if you need transactions (start transaction; do your work; commit or rollback).

InnoDB is the default storage engine in 5.5 (I'm not sure when exactly it became the default storage engine).

Hey radoulov!

That was it! I changed the storage engine to InnoDB, and now it works as I'd expect.

Thank you very much.

Cheers,

Scott.

You're very welcome

Cheers
Radoulov

Since I'm quite new to mySQL, and would never have guessed that, I'll throw in a couple of links I found, once you gave me an idea where to look.

Link 1 | Link 2

Yee-haa! Now I can run updates without a care in the World