MySQL command line and transactions

Question

I have a question about MySQL and have been unable to find an answer. I know auto-commit is turned on by default in MySQL. I need to run a few update queries from the command line in one transaction but I don't know how MySQL will handle them. If I have something like this:

mysql -uroot -proot -e 'QUERY 1; QUERY 2; QUERY3'

will it execute as one transaction or will MySQL auto-commit each statement individually? I need to ensure atomicity.

Answer 1

You can use MySQL's START TRANSACTIONsyntax to create a transactional commit:

Source: http://dev.mysql.com/doc/refman/5.0/en/commit.html

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

You could also write your query in a .sql file, and pipe it into mysql:

$ cat query.sql | mysql -uroot -proot

Answer 2

Pipe is a great idea!

echo "START TRANSACTION;" > start.sql
echo "COMMIT;" > commit.sql

cat start.sql yourScript.sql commit.sql | mysql -uroot -proot

or

cat start.sql yourScript.sql - | mysql -uroot -proot

and so, you can commit o rollback by hand according the result of yourScript

Good luck!