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

I know this thread is old, but I was also looking for a comfortable bash mysql session implementation and didn't found something good enough for my needs, so I wrote my own one which I'd like to share with the world.

############### BASIC MYSQL SESSION IMPLEMENTATION FOR BASH (by Norman 

Geist 2015) #############
# requires coproc, stdbuf, mysql
#args: handle query
function mysql_check {
  local handle
  handle=(${1//_/ })
  #has right structure && is still running && we opened it?
  if [[ ${#handle[*]} == 3 ]] && ps -p ${handle[2]} 2>> /dev/null >> /dev/null && { echo "" >&${handle[1]}; } 2> /dev/null; then
    return 0
  fi
  return 1
}

# open mysql connection
#args: -u user [-H host] [-p passwd] -d db
#returns $HANDLE
function mysql_connect {
  local argv argc user pass host db HANDLEID i
  #prepare args
  argv=($*)
  argc=${#argv[*]}

  #get options
  user=""
  pass=""
  host="localhost"
  db=""
  for ((i=0; $i < $argc; i++))
  do
    if [[ ${argv[$i]} == "-h" ]]; then
      echo "Usage: -u user [-H host] [-p passwd] -d db"
      return 0
    elif [[ ${argv[$i]} == "-u" ]]; then
      i=$[$i+1]
      if [[ ${#argv[$i]} -gt 0 ]]; then
    user=${argv[$i]}
      else
    echo "ERROR: -u expects argument!"
    return 1
      fi
    elif [[ ${argv[$i]} == "-p" ]]; then
      i=$[$i+1]
      if [[ ${#argv[$i]} -gt 0 ]]; then
    pass="-p"${argv[$i]}
      else
    echo "ERROR: -p expects argument!"
    return 1
      fi
    elif [[ ${argv[$i]} == "-H" ]]; then
      i=$[$i+1]
      if [[ ${#argv[$i]} -gt 0 ]]; then
    host=${argv[$i]}
      else
    echo "ERROR: -H expects argument!"
    return 1
      fi
    elif [[ ${argv[$i]} == "-d" ]]; then
      i=$[$i+1]
      if [[ ${#argv[$i]} -gt 0 ]]; then
    db=${argv[$i]}
      else
    echo "ERROR: -d expects argument!"
    return 1
      fi
    fi
  done

  if [[ ${#user} -lt 1 || ${#db} -lt 1 ]]; then
    echo "ERROR: Options -u user and -d db are required!"
    return 1;
  fi

  #init connection and channels
  #we do it in XML cause otherwise we can't detect the end of data and so would need a read timeout O_o
  HANDLEID="MYSQL$RANDOM"
  eval "coproc $HANDLEID { stdbuf -oL mysql -u $user $pass -h $host -D $db --force --unbuffered --xml -vvv 2>&1; }" 2> /dev/null
  HANDLE=$(eval 'echo ${'${HANDLEID}'[0]}_${'${HANDLEID}'[1]}_${'${HANDLEID}'_PID}')
  if mysql_check $HANDLE; then
    export HANDLE
    return 0
  else
    echo "ERROR: Connection failed to $user@$host->DB:$db!"
    return 1
  fi
}

#args: handle query
#return: $DATA[0] = affected rows/number of sets; 
#        $DATA[1] = key=>values pairs following
#        $DATA[2]key; DATA[3]=val ...
function mysql_query {
  local handle query affected line results_open row_open cols key val 
  if ! mysql_check $1; then
    echo "ERROR: Connection not open!"
    return 1
  fi
  handle=(${1//_/ })

  #delimit query; otherwise we block forever/timeout
  query=$2
  if [[ ! "$query" =~ \;\$ ]]; then
    query="$query;"
  fi
  #send query
  echo "$query" >&${handle[1]}

  #get output
  DATA=();
  DATA[0]=0
  DATA[1]=0
  results_open=0
  row_open=0
  cols=0
  while read -t $MYSQL_READ_TIMEOUT -ru ${handle[0]} line
  do 
    #WAS ERROR?
    if [[ "$line" == *"ERROR"* ]]; then
      echo "$line"
      return 1
    #WAS INSERT/UPDATE?
    elif [[ "$line" == *"Query OK"* ]]; then
      affected=$([[ "$line" =~ Query\ OK\,\ ([0-9]+)\ rows?\ affected ]] && echo ${BASH_REMATCH[1]})
      DATA[0]=$affected
      export DATA
      return 0
    fi

    #BEGIN OF RESULTS
    if [[ $line =~ \<resultset ]]; then
      results_open=1
    fi

    #RESULTS
    if [[ $results_open == 1 ]]; then
      if [[ $line =~ \<row ]]; then
    row_open=1
    cols=0
      elif [[ $line =~ \<field && $row_open == 1 ]]; then
    key=$([[ "$line" =~ name\=\"([^\"]+)\" ]] && echo ${BASH_REMATCH[1]})
    val=$([[ "$line" =~ \>(.*)\<\/ ]] && echo ${BASH_REMATCH[1]} || echo "NULL")
    DATA[${#DATA[*]}]=$key
    DATA[${#DATA[*]}]=$val
    cols=$[$cols+1]
      elif [[ $line =~ \<\/row ]]; then
    row_open=0
    DATA[0]=$[${DATA[0]}+1]
    DATA[1]=$cols
      fi
    fi

    #END OF RESULTS
    if [[ $line =~ \<\/resultset ]]; then
      export DATA
      return 0
    fi
  done
  #we can only get here
  #if read times out O_o
  echo "$FUNCNAME: Read timed out!"
  return 1
}

#args: handle
function mysql_close {
  local handle
  if ! mysql_check $1; then
    echo "ERROR: Connection not open!"
    return 1
  fi
  handle=(${1//_/ })
  echo "exit;" >&${handle[1]}

  if ! mysql_check $1; then
    return 0
  else
    echo "ERROR: Couldn't close connection!"
    return 1
  fi
}
############### END BASIC MYSQL SESSION IMPLEMENTATION FOR BASH ################################

# Example usage
#define timeout for read command, in case of server error etc.
export MYSQL_READ_TIMEOUT=10

# Connect to db and get $HANDLE
mysql_connect -u mydbuser -d mydb -H mydbserver

#query db and get $DATA
mysql_query $HANDLE "SELECT dt_whatever from tbl_lol WHERE dt_rofl=10"

#close connection
mysql_close $HANDLE

NOTES:

• Save $HANDLE to a new variable after connection to open as many connections as you like
• You can't exchange $HANDLE between bash sessions
• You need linux packages "coproc" "stdbuf" "mysql"
• Return DATA is of a bash array

$DATA[0] = affected rows/number of sets;
$DATA[1] = number of key=>values pairs following;
$DATA[2] = key1;
$DATA[3] = value1;
      [...]
$DATA[n-1] = keyn;
$DATA[n]   = valuen;

• Generally all queries should work, even "SELECT count(*)"

Example how to loop the returned data of a two column query

eg. "SELECT dt_id, dt_name FROM ..."

fields=2
for ((i=2; $i<$((${DATA[0]}*${DATA[1]}*$fields)); i+=$((${DATA[1]}*$fields))))
do
    field1key   = ${DATA[$i]};   #this is "dt_id"
    field1value = ${DATA[$i+1]}; #this is the value for dt_id
    field2key   = ${DATA[$i+2]}; #this is "dt_name"
    field2value = ${DATA[$i+3]}; #this is the value  for dt_name
done

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

Answer 3

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 4

Here is a simple example that reproduces the locked-up behavior you describe:

while :; do sleep 1; done <IN >OUT

sed s/^/::/ >IN <OUT

cat IN

echo x > OUT

The echo command completes but neither cat nor sed do. Maybe playing with this example will yield some results.

I am not sure you need to do it this way, though. Maybe it would be better to have a control script that pipes to MySQL, and have MySQL write its output to a file.

#!/bin/bash
set -o errexit -o nounset -o pipefail

# Setup variables, hostname, names of files...

{
  echo 'BEGIN PROGRAM' >&2
  cat <<MYSQL
    # Do something to set the output file, maybe \t?
    lock table T write;
    select col from T;
    # Run a shell command to create a mysql.done file from MySQL
MYSQL
  while [[ ! -f mysql.done ]]
  do sleep 1
  done
  # Create rows to insert from MySQL output file.
  echo 'load data infile input.tsv into table T'
} | mysql --xml --batch --raw --skip-column-names -h "$hostname" -u "$username" "$db"

Control flow via FIFOs is more appealing than waiting for files to show up in a while loop; but FIFOs are surprisingly hard to get right.