sql transaction in bash linux

Posted on 2011-03-02
Last Modified: 2012-05-11
I have a function in shell script which executes sql statements.
I want to use transactions for it.

Begin transaction # i want SQL equivalent of this in shell scripting

source /etc/ipaccess/omcr-server/ipaccess-omcr;
mysql -u${MYSQL_ROOT_USER} -p${MYSQL_ROOT_PASSWORD} -e "Delete from omcr.AlarmNotification where alarmLifecycleId=50;"
commit transaction # i want SQL equivalent of this in shell scripting

How to execute the lines for "Begin transaction" and "commit"?????????
Question by:pvinodp

Assisted Solution

RealRaven earned 100 total points
ID: 35017414
Sorry, I don't think that is possible. Each connection to mysql will loose it's transactions on disconnect and it's not possible to first start a transaction, then connect again and do something and then connect a third time and do commit or rollback.

But if your example is complete and all you want to do is run ONE delete you'll have an automatic transaction around it whether you like it or not.

Author Comment

ID: 35017567
hi raven
the function() contains multiple SQL statements.
LVL 68

Assisted Solution

woolmilkporc earned 50 total points
ID: 35017665
You could put all your statements (including begin/commit) into a textfile to be read by mysql, like

mysql -u${MYSQL_ROOT_USER} -p${MYSQL_ROOT_PASSWORD} < /text/file

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


Expert Comment

ID: 35020190
As woolmilkporc points out a script file is always possible but it doesn't look like a good solution in this case.

You would have to write to a temporary file, send that to mysql and then delete it. It's possible but not pretty.

Accepted Solution

mwiercin earned 350 total points
ID: 35027001

The simplest way would be to concatenate all the queries together delimited with semi colon (you can also USE mysql's DELIMITER keyword to  change it).  You can use a temp file for doing so, or combine use of SOURCE  command, i.e.

$ mysql -u${MYSQL_ROOT_USER} -p${MYSQL_ROOT_PASSWORD} -e "BEGIN; SOURCE /etc/ipaccess/omcr-server/ipaccess-omcr; Delete from omcr.AlarmNotification where alarmLifecycleId=50; COMMIT;"

Open in new window

If you want to maintain more control over execution, (named) pipes would be the way to go.

One way communication using a pipe:

$ (echo "SELECT NOW(); SELECT CONNECTION_ID(); "; sleep 1; echo "SELECT NOW();SELECT CONNECTION_ID();" ) | mysql
2011-03-03 13:20:13
2011-03-03 13:20:14

Open in new window

Named pipes (mypipe being MySQL entry, outpipe - output).:
$ cat mypipe |  mysql  > outpipe &
[1] 17660
$ echo "SELECT NOW();" > mypipe; cat outpipe;
2011-03-03 13:30:03
[1]+  Done                    cat mypipe | mysql > outpipe

Open in new window

You can also use socat in similar manner as above.

I don't recommend making it too complicated as handling both output and input  streams in bash will mean that you are effectively implementing your own database driver, thus if you need more control it's better to use a proper driver (mean switch to Perl/PHP/Python/Ruby etc.).

Author Closing Comment

ID: 35065883
Thanks all.
Finally i did put in the solution by mwiercin :

$ mysql -u${MYSQL_ROOT_USER} -p${MYSQL_ROOT_PASSWORD} -e "BEGIN; SOURCE /etc/ipaccess/omcr-server/ipaccess-omcr; Delete from omcr.AlarmNotification where alarmLifecycleId=50; COMMIT;"

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Setting up Secure Ubuntu server on VMware 1.      Insert the Ubuntu Server distribution CD or attach the ISO of the CD which is in the “Datastore”. Note that it is important to install the x64 edition on servers, not the X86 editions. 2.      Power on th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now