Attach to MySQL client entirely via FIFOs

Question

On a Bash script I want to keep MySQL sessions open across several sequential accesses; the common way to access MySQL is by opening an individual session for each SQL command, or set of commands, such as

mysql -u user -e "show tables;"

The limitation of this method is the loss of atomicity and lock statuses for those transactions which need to be twofold: for example, it's not possible to preserve the lock status on a table T for the whole length of the following twofold operation:

### Minimalistic example
data=$(mysql -e "\
    lock table T write;
    select col from T;
")
# ...
# parse 'data' and compute 'output' variable
# ...
mysql -e "insert into T values ($output);"

My approach to the solution is to keep the MySQL session open across multiple accesses by using two FIFOs and hang the process on background.

---

Proposed solution:
Create the pair of FIFOs: mkfifo IN OUT.
Set the MySQL-client instance in place, along with a dummy while to keep the pipes open and prevent SIGPIPE signals:

mysql --xml --batch --raw --skip-column-names \
    -h "$hostname" -u "$username" "$db" >IN <OUT &
while :; do sleep 1; done               <IN >OUT &

Then test it:

echo "show tables;" >OUT
read <IN

---

Result:
It does not work. The echo command completes and bash steps over it, which means MySQL receives the input, but read hangs forever, so no output is produced.
I discovered that eliminating the IN FIFO the whole task doesn't hang:

mysql --xml --batch --raw --skip-column-names \
    -h "$hostname" -u "$username" "$db" <OUT &
while :; do sleep 1; done               >OUT &

echo "show tables;" >OUT  # this produces the expected output

Is this behavior expected? Also I wonder if it is possible to run twofold operations in Bash without custom homebrews.

Answer 1

The problem with FIFOs is that when every process that is inputting data terminates, it signals the processes that are reading (in this case mysql) that it's the end of the data, so it terminates.

The trick is to make sure that there is a process keeping the FIFO input alive at all times. You can do that by running a sleep 999999999 > fifofile in background.

Exemple:

#!/bin/sh

mkfifo /tmp/sqlpipe

sleep 2147483647 > /tmp/sqlpipe &
PID=$!

mysql -B -uUSER -pPASSWORD < /tmp/sqlpipe &

# all set up, now just push the SQL queries to the pipe, exemple:
echo "INSERT INTO table VALUES (...);" > /tmp/sqlpipe
echo "INSERT INTO table VALUES (...);" > /tmp/sqlpipe
echo "INSERT INTO table VALUES (...);" > /tmp/sqlpipe
cat "mysqldump.sql" > /tmp/sqlpipe
echo "DELETE FROM table WHERE ...;" > /tmp/sqlpipe

# done! terminate pipe
kill -s SIGINT $PID
rm /tmp/sqlpipe

In the end we terminate the sleep process to release the FIFO input completely. It will signal mysql that the input has ended and it will automatically die in consequence.

There is also an alternative that don't require FIFOs, but you will need two scripts:

run.sh:

#!/bin/sh
./querygenerator.sh | mysql -B -uUSER -pPASSWORD

querygenerator.sh:

#!/bin/sh
echo "INSERT INTO table VALUES (...);"
echo "INSERT INTO table VALUES (...);"
echo "INSERT INTO table VALUES (...);"
cat "mysqldump.sql"
echo "DELETE FROM table WHERE ...;"

Answer 2

I developed a cheat for this kind of problem a while ago, using a unix socketpair(). It will only persist while the script is running (even in the background), but it is a lot easier to deal with that FIFOs.

The socketpair() call creates an unnamed pair of connected sockets in the specified domain domain, of the specified type, and using the optionally specified protocol.

Below is a complete example, with the source code to the binary included. Don't be daunted, you can play around with the idea very easily within an interactive shell:

local:/# ./socketpair /bin/bash
$ cat <& $DUP1 | tr '[:lower:]' '[:upper:]' &
$ echo 'Hello SocketPair!' >& $DUP2
HELLO SOCKETPAIR!
$

Here's a simple script:

#!./socketpair /usr/bin/env bash
# We are now in a BASH script with a pair of linked sockets,
# $DUP1 and $DUP2

## Background job ## Received data on DUP1
(
    while read -r -u $DUP1
    do
        echo "Received: $REPLY"
    done 
) &


## Foreground task ## Sends data to DUP2
counter=0
while true
do
    echo Test $(( counter++ )) >&$DUP2
    sleep 1
done

and I include the source code within the script, just incase I move the script somewhere and I can't find the binary :)

## Source code for simple 'socketpair' binary
## Compile with "cc -o socketpair socketpair.c"
: <<'SOURCE'
--[ cut here ]--
/** 
* @file socketpair.c
* @author christopher anserson
* @date 2012-04-28
*/
#include <stdio.h>
#include <stdlib.h>
#include <errno.h>
#include <sys/socket.h>

char* custom_itoa(int i) {
    static char output[24];
     return sprintf(output, "%d", i), output;
}

int main(int argc, char **argv) {
    int sv[2]; /* the pair of socket descriptors */

    if (socketpair(AF_UNIX, SOCK_STREAM, 0, sv) == -1) {
        perror("socketpair");
        exit(1);
    }
     setenv("DUP1", custom_itoa(sv[0]), 1);
     setenv("DUP2", custom_itoa(sv[1]), 1);

     /* now exec whatever script needed these paired sockets */
     execv(argv[1], &argv[1]);
    return 0;
}
--[cut here]--
SOURCE