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

User · Answer

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

User · Answer

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

User · Answer

This is useful to check the status of autocommit   select   autocommit

User · Answer

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

User · Answer

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 gt    send  set autocommit 0  r   expect  mysql gt   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

User · Answer

You do this in 3 different ways    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  Use autocommit 0  every time you instantiate a database connection  For a global setting  add a autocommit 0 variable in your my cnf configuration file in MySQL

User · Answer

For auto commit off then use the below command for sure  Set below in my cnf file        mysqld      autocommit 0

[mysql] How do I turn off autocommit for a MySQL client?

Examples related to mysql

Examples related to client

Examples related to autocommit