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)?

Scheer

did this work : init_connect='set autocommit=0' – Twentytwo

No, that is a parameter or command line argument for mysqld. I'm talking about the mysql client, which gives the error "mysql: unknown variable 'init_connect=set autocommit=0'" – Scheer

Answer 1

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

Apodaca

That's perfect, cheers! I already have MySQL configured with a default username, password etc. so I don't need to pass it those. Can't seem to format the code despite what meta says, so feel free to add it to the main response. My expect script is: spawn /usr/local/mysql/bin/mysql expect "mysql>" send "set autocommit=0;\r" expect "mysql>" interact – Scheer

Thanks - if by "format the code" you mean in the comments, then no there is no formatting available. I'll add your code to the answer – Apodaca

Answer 2

You do this in 3 different ways:

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

2. Use autocommit=0; every time you instantiate a database connection.

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

Karoline

autocommit=0 in ~/.my.cnf does not work with mysql-5.7.33, I get the following error: mysql: [ERROR] unknown variable 'autocommit=0' – Coralyn

init-command='set autocommit=0' works as in another answer – Coralyn

Answer 3

It looks like you can add it to your ~/.my.cnf, but it needs to be added as an argument to the init-command flag in your [client] section, like so:

[client]
init-command='set autocommit=0'

Symmetrical

The expect approach works too, this is just cleaner. BTW, this is for mysql 5.5, not sure if it works for earlier versions. – Symmetrical

This worked for me on MySQL 5.5 running on Ubuntu 14.4 by adding to /etc/my.cnf. – Gullah

I recommend adding this setting into [mysql] section. If in [client] I get error for unknown variable when using mysqldump. Tested on MySQL 5.6 – Roger

Answer 4

This is useful to check the status of autocommit;

select @@autocommit;

Pussyfoot

and you can use this command to change the status: set @@autocommit=0; – Tiffinytiffy

Answer 5

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 each time you need to run this sort of query, 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.

Freddie

That doesn't turn off autocommit, and I want something that I don't have to type even every session, let alone for every piece of DML. – Scheer

Sorry I should have read your post more carefully. As far as I know, autocommit is not something that can pass through as a init connect parameter - that's because it's a global variable. As you know global variables can only be set by super users. Now the catch 22: super users are not allowed to use the init_connect setting relevent page of the manual is here: dev.mysql.com/doc/refman/5.0/en/server-system-variables.html (scroll down till you get to the --init-connect section) – Freddie

Answer 6

For auto commit off then use the below command for sure. Set below in my.cnf file:

    [mysqld]
    autocommit=0

Mcgruter

Answer 7

Instead of switching autocommit off manually at restore time you can already dump your MySQL data in a way that includes all necessary statements right into your SQL file.

The command line parameter for mysqldump is --no-autocommit. You might also consider to add --opt which sets a combination of other parameters to speed up restore operations.

Here is an example for a complete mysqldump command line as I use it, containing --no-autocommit and --opt:

mysqldump -hlocalhost -uMyUser -p'MyPassword' --no-autocommit --opt --default-character-set=utf8 --quote-names  MyDbName  >  dump.sql

For details of these parameters see the reference of mysqldump

Steffy

Answer 8

Default value of autocommit variable in mysql is 1; To view the current value of autocommit in mysql use command mysql> select @@autocommit; then if you want to off autocommit use command mysql> set autocommit = 0;

Redcap