[Solved] Disable mySQL autocommit


 
Thread Tools Search this Thread
Top Forums Web Development [Solved] Disable mySQL autocommit
# 1  
Old 06-19-2011
[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:

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

Code:
[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)

# 2  
Old 06-19-2011
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).
This User Gave Thanks to radoulov For This Post:
# 3  
Old 06-19-2011
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.
# 4  
Old 06-19-2011
You're very welcome Smilie

Cheers
Radoulov
# 5  
Old 06-19-2011
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 Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

7 More Discussions You Might Find Interesting

1. Emergency UNIX and Linux Support

[Solved] Mysql - Take data from row and copy it to another row

Sorry if I repost my question in this section, but I'm really in a hurry since I have to finish my work... :( Dear community, I have a table with two rows like: Row1 Row2 ======= ======= 7,3 text 1 1,3 text 2 1,2,3 blabla What i need to do is add/copy... (2 Replies)
Discussion started by: Lord Spectre
2 Replies

2. Emergency UNIX and Linux Support

[Solved] EMERGENCY - can I revert 1 hour of mysql changes

I know, its stupid.. MYSQL on CENTOS 5 using WHM Imported a lot of users/posts from old forum to new forum, but my backup was lost. Can I somehow simply remove all mysql table changes for the last 3 hours, returning to pre-import status. ---------- Post updated at 03:44 AM ----------... (6 Replies)
Discussion started by: lawstudent
6 Replies

3. Red Hat

SSL/TLS renegotiation DoS -how to disable? Is it advisable to disable?

Hi all Expertise, I have following issue to solve, SSL / TLS Renegotiation DoS (low) 222.225.12.13 Ease of Exploitation Moderate Port 443/tcp Family Miscellaneous Following is the problem description:------------------ Description The remote service encrypts traffic using TLS / SSL and... (2 Replies)
Discussion started by: manalisharmabe
2 Replies

4. UNIX and Linux Applications

command to check value of autocommit and isolation level

Hi, Pls let me know command to get following: 1. how to check current value of autocommit 2. how to check current value of isolation level I am using mysql-5.0.26 on unix -Thanks (2 Replies)
Discussion started by: newbielgn
2 Replies

5. UNIX for Dummies Questions & Answers

[SOLVED] mysql.sock is missing..

mysql.sock file is missing in /opt/lampp/etc/ is there any backup file available in unix... since without that file .. project is not opening.. reply me as soon as possible ... (19 Replies)
Discussion started by: senkerth
19 Replies

6. Shell Programming and Scripting

How to disable Enable/Disable Tab Key

Hi All, I have bash script, so what is sintax script in bash for Enable and Disable Tab Key. Thanks for your help.:( Thanks, Rico (1 Reply)
Discussion started by: carnegiex
1 Replies

7. AIX

how to set AUTOCOMMIT option in AIX

Hi, Can anybody tell,how to set the auto commit option in AIX,i have tried with environmental variables option like 'export db2option=-c +a'. But its not working in my environment. is there any other option? (1 Reply)
Discussion started by: DB2AIX
1 Replies
Login or Register to Ask a Question

Featured Tech Videos