Impossible Is Possible!
>but I need to get output from MySQL half way through to pick up the requests. Does somebody have a recipe for this?
Here is my solution demo:
#!/bin/bash
################################################################################
# Bash synchronous client-server #
# or #
# Bash parallel processes with synchronized rendezvous points #
# or #
# Bash interprocess communication with named pipes #
################################################################################
#Declare your session 'globals'
args= #command line arguments are stored here
DIR= #script's current path is stored here
#Early store your bash args before you lose them
for arg in "$@"; do
args[i]=$arg
(( i+=1 ))
done
################################################################################
## Initialize constants ##
#------------------------------------------------------------------------------#
# Explain more... #
################################################################################
function init_vars() {
#absolute path of this script
DIR=$( cd "$( dirname "$0" )" && pwd )
#global MYSQL_PASS is defined in ~/.profile
#this is the file where we store MySQL root password
if [[ ! $MYSQL_PASS ]]; then
MYSQL_PASS='.mysqlpass'
fi
#do some other stuff here...
}
################################################################################
## Rotate over passed arguments ##
#------------------------------------------------------------------------------#
#Parse array 'args' based on the logic of your command line argument syntax #
#Say, '--db <database>' denotes the processing of one database, '--db all' of #
#all databases etc. #
#Linux utilities have contradicting rules for such kind of argument processing #
#(full of crap!), i.e. see tar: tar -xvf or tar --xfv or tar xvf, mysql -p #
#<pass> or mysql -p=<pass> or mysql -p'<pass>' (???) #
################################################################################
function parse_args() {
#your logic goes here
}
################################################################################
## Opens a MySQL client session ##
#------------------------------------------------------------------------------#
# For security reasons password is kept in a file given by MYSQL_PASS. #
################################################################################
function mysql_client() {
mysql --host=127.0.0.1 --port=3306 --default-character-set=utf8 -u root -p"$( cd $DIR; cat $MYSQL_PASS )"
}
################################################################################
## A job dispatcher ##
#------------------------------------------------------------------------------#
# This thread initiates the workers and also reads intermediate results from #
# the spawned jobs. We don't want to parallelize the whole part of a job but #
# rather to put some parts to run in parallel with the dispatcher and some #
# parts in a sequential order determined by what we call rendezvous points. #
# See the diagram: A, B, C, D, E and F are events or actions during the life of#
# a program. #
# #
# dispatcher #
# | start #
# A ----------------> worker #
# | | #
# B (read blocks) C #
# | signals | #
# B (unblocks)<--------------- D (write blocks) #
# | | #
# E (1st set of results) F #
# | | #
# We can only guarantee that E comes after D or, D->E in time but E->F or F->E #
# and B->C or C->B (we don't care) #
################################################################################
function dispatcher() {
#go to the directory of this script
cd $DIR
#make a temporary directory secured in 'time & space'
TMPDIR=$( mktemp -d XXXXXXXXXX )
#catch exit of this script and delete temporary folder
trap 'cd $DIR && rm -rf "$TMPDIR"' EXIT
#move into the temporary folder
cd ${TMPDIR}
#create named pipes-global to all functions of this script
TMPSQL=mysql-$RANDOM.$RANDOM.$RANDOM.$$
TMPSCRIPT=myscript-$RANDOM.$RANDOM.$RANDOM.$$
mkfifo $TMPSQL
mkfifo $TMPSCRIPT
#exec 3<> ${DIR}/${TMPDIR}/$TMPSQL || (echo 'error'; exit 1)
#exec 4<> ${DIR}/${TMPDIR}/$TMPSCRIPT || ( echo 'error'; exit 1)
echo '===1.PARENT=== Starting background jobs...'
echo 'We can guarantee the succession only of 2->3,4 and 5->6,7 but NOT 3->4 or 6->7!'
worker &
##################################
cat $TMPSQL #make a blocking read!
##################################
echo '===4.PARENT=== ...1st query has been read'
##################################
cat $TMPSQL #make a blocking read!
##################################
echo '===7.PARENT=== ...2nd query has been read'
#don't you dare to exit!
##################################
cat $TMPSQL #make a blocking read!
##################################
}
################################################################################
## A job spawned by the dispatcher ##
#------------------------------------------------------------------------------#
################################################################################
function worker() {
echo "===2.CHILD=== Executing 1st query......writing to $TMPSQL"
#the dash symbol '-' makes tabs at the beginning of line to be
#ignored inside the here-doc but improves formation!
mysql_client <<- QUERY
\! tee $TMPSQL
use test;
select * from customers;
\! echo '===3.CHILD=== End 1st query'
#####################################
#\. $TMPSCRIPT
#####################################
QUERY
echo "===5.CHILD=== Executing 2nd query......writing to $TMPSQL"
mysql_client <<- QUERY
\! tee $TMPSQL
use test;
select * from cars;
\! echo '===6.CHILD=== End 2nd query'
#####################################
#\. $TMPSCRIPT
#####################################
QUERY
echo '===8.CHILD=== End of background jobs'
echo > $TMPSQL
}
#parse command line arguments
parse_args
#initialize variables
init_vars
#call dispatcher
dispatcher
Suppose you have a 'test' database then you can see the following output:
===1.PARENT=== Starting background jobs...
We can guarantee the succession only of 2->3,4 and 5->6,7 but NOT 3->4 or 6->7!
===2.CHILD=== Executing 1st query......writing to mysql-30627.1495.5394.7533
===4.PARENT=== ...1st query has been read
id_customer firstname secondname
1 John Pincolo
2 Mark Denonto
3 Ann Curtis
4 Jeny Wirth
===3.CHILD=== End 1st query
===5.CHILD=== Executing 2nd query......writing to mysql-30627.1495.5394.7533
===7.PARENT=== ...2nd query has been read
id_car type plate date_rent date_returned
1 fiat BG-457 2012-07-18 00:00:00 2012-07-20 00:00:00
2 renault AS-1234 2012-07-20 00:00:00 2012-07-25 00:00:00
3 fiat JYB-2856 2012-06-23 00:00:00 2012-06-24 00:00:00
===6.CHILD=== End 2nd query
===8.CHILD=== End of background jobs
As you can see between 2 and 5 lies my first database result set!
You wanted intermediate results between your query and the "main" program and you have them!
You may wonder: "Hey, I want to obtain a database lock, stop the session in terms of freeze while my 'main' program does something else!"
Well, there is an answer too.
Function 'worker' has 2 commented commands:
#####################################
#\. $TMPSCRIPT
#####################################
by enabling them your execution hangs!!! That's it! Now, you can start doing things in the dispatcher and when you finish send an 'echo' like this:
echo > $TMPSCRIPT
By adding this line of code to our workers we converted to blocking threads both the dispatcher and the worker: now both listen each other. Schematically:
dispatcher
| start
A ----------------> worker
| |
B (read blocks) C
| signals |
B (unblocks)<--------------- D (write blocks)
| |
E (1st set of results) F
| |
| G (read blocks)
| signals |
H ----------------> G (unblocks)
| (write blocks) |
if you need more excitement what about sending
echo 'set @x=@x+1;' > $TMPSCRIPT
where '@x' a user defined variable! What we have here? Well, we simply 'injected' live code to our sql job! More than great!!!
Now, one can obtain a LOCk of a database, make a logical backup, stop and continue the mysql client and do whatever he wants while the database LOCK is still active!
It can be even better: what about creating binary backups? (just saved you 2,000-10,000 USD per year, see: https://shop.oracle.com)
It can be better than better: a bunch of say 10 databases can be locked and a backup can start, the first that finishes send an acknowledgement to the dispatcher which adds another database in the list until it becomes empty!
Only remember that foe every worker we need 2 locks for bidirectional locking.
We can even start 3 dispatchers at the same time as it is impossible to face a collition of their named pipes as they are unique in space.
That's my 2,000$ advice
Sweet!
(centurian)
