How do I turn off autocommit for a MySQL client?

Question

I have a web app that has been written with the assumption that autocommit is turned on on the database, so I don't want to make any changes there. However all the documentation I can find only seems to talk about using init_connect on the database, i.e. a global setting for all client connections.

Is there a way to set autocommit=0 just when running mysql on a Linux command line (without having to type it in every time)?

Answer 1

Do you mean the mysql text console? Then:

START TRANSACTION; ... your queries. ... COMMIT;

Is what I recommend.

However if you want to avoid typing this all the time you need to make add the following to the [mysqld] section of your my.cnf file.

init_connect='set autocommit=0'

This would set autocommit to be off for every client though.

Answer 2

You do this in 3 different ways.

1) Before you do an insert, always issue a BEGIN; statement. This will turn off your autocommits. You will need to do a COMMIT; once you want your data to be persisted in the database.

2) Use autocommit=0; everytime you instantiate a database connection

3) For a global setting, add a autocommit=0 variable in your my.cnf configuration file in MySQL.

Answer 3

Perhaps the best way is to write a script that starts the mysql command line client and then automatically runs whatever sql you want before it hands over the control to you.

linux comes with an application called 'expect'. it interacts with the shell in such a way as to mimic your key strokes. it can be set to start mysql, wait for you to enter your password. run further commands such as SET autocommit = 0; then go into interactive mode so you can run any command you want.

for more on the command SET autocommit = 0; see.. http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html

I use expect to log in to a command line utility in my case it starts ssh, connects to the remote server, starts the application enters my username and password then turns over control to me. saves me heaps of typing :)

http://linux.die.net/man/1/expect

DC

Expect script provided by Michael Hinds

spawn /usr/local/mysql/bin/mysql 
expect "mysql>" 
send "set autocommit=0;\r" 
expect "mysql>" interact

expect is pretty powerful and can make life a lot easier as in this case.

if you want to make the script run without calling expect use the shebang line

insert this as the first line in your script (hint: use which expect to find the location of your expect executable)

#! /usr/bin/expect

then change the permissions of your script with..

chmod 0744 myscript

then call the script

./myscript

DC