Implements connection pool for MySQL connections.

mysql-pool.sh

#!/bin/sh

#
# mysql-pool.sh
#
# Implements connection pool for MySQL connections.
#

##### start script name

if [ -z "${script}" ]
then
  script=mysql-pool.sh
fi

##### end script name

# get script start time
start_dt=$( date "+%s" )

# make temporary folder
tmp=/tmp/tmp.${script}.$$

mkdir -m 777 ${tmp} >> /dev/null 2>&1

# log messages to terminal and file
log_file=/tmp/${script}.log

log() {
  now=$( date "+[%Y-%m-%d %H:%M:%S]" )
  pid=[$$]
  message="${1}"

  echo ${now} ${pid} "${message}" | tee -a ${log_file}
}

passthru() {
  tee -a ${log_file}
}

# logger pipe and pid
logger_pipe=${tmp}/logger
logger_pid_file=${tmp}/logger.pid

# make named pipe (FIFO)
mkfifo ${logger_pipe}

# keep pipe open using sleep
sleep 1d > ${logger_pipe} &

# save sleep pid
echo $! > ${logger_pid_file}

# run logger
while read message
do
  # indent message
  log "    ${message}"
done < ${logger_pipe} &

# cleanup
finished="no"

cleanup() {
  if [ "${finished}" = "no" ]
  then
    normal_exit=${1}

    # kill logger
    xargs -a ${logger_pid_file} kill -KILL >> /dev/null 2>&1

    # remove logger pid and pipe
    rm -f ${logger_pid_file} ${logger_pipe}

    ##### start script cleanup

    teardown_connection_pool

    log "wait for connections to finish ..."

    wait

    cleanup_connection_pool

    ##### end script cleanup

    # remove temporary folder
    rm -rf ${tmp}

    # get script end time
    end_dt=$( date "+%s" )

    seconds=$(( ${end_dt} - ${start_dt} ))

    h=$(( ${seconds} / 3600 ))
    m=$(( ( ${seconds} % 3600 ) / 60 ))
    s=$(( ${seconds} % 60 ))

    if [ ${h} -gt 0 ]
    then
      if [ ${h} -eq 1 ]
      then
        hour="1 hour"
      else
        hour="${h} hours"
      fi
    fi

    if [ ${m} -gt 0 ]
    then
      if [ ${m} -eq 1 ]
      then
        minute="1 minute"
      else
        minute="${m} minutes"
      fi
    fi

    if [ ${s} -gt 0 ]
    then
      if [ ${s} -eq 1 ]
      then
        second="1 second"
      else
        second="${s} seconds"
      fi
    fi

    # use xargs to trim leading, trailing, and internal whitespace
    runtime=$( echo ${hour} ${minute} ${second} | xargs )

    if [ -z "${runtime}" ]
    then
      runtime="0 seconds"
    fi

    # display script runtime
    log "script runtime: ${script} ${runtime}"

    finished="yes"

    # handle abnormal exit
    if [ "${normal_exit}" = "no" ]
    then
      # indicate failure
      exit 1
    fi
  fi
}

# abnormal cleanup

# normal exit = no
trap "cleanup no" INT
trap "cleanup no" TERM

# normal cleanup

# normal exit = yes
trap "cleanup yes" EXIT

# display running script
log "running script: ${script}"

##### start script action

# database name
db_database="homestead"

# database username
db_username="homestead"

# database password
db_password="secret"

# connections
connections=10

setup_connection_pool() {
  log "setup connection pool ..."

  # "manager" connection = 0
  manager=0

  # "worker" connections = 1, 2, ..., connections
  worker_list=$( seq 1 ${connections} )

  # "all" connections = 0, 1, 2, 3, ..., connections
  all_list=$( seq 0 ${connections} )

  # timeout, 300 seconds = 5 minutes
  timeout=300

  # timeout = 1, 2, 3, ..., timeout
  timeout_list=$( seq 1 ${timeout} )

  # internal connection id
  connection_id=0

  # setup all connections
  set -- ${all_list}
  for index
  do
    input_pipe=${tmp}/input.${index}
    sleep_pid_file=${tmp}/sleep_pid.${index}
    mysql_pid_file=${tmp}/mysql_pid.${index}
    thread_id_file=${tmp}/thread_id.${index}
    ready_file=${tmp}/ready.${index}

    # just in case
    rm -f ${input_pipe} ${sleep_pid_file} ${mysql_pid_file} ${ready_file}

    # create named pipe (FIFO)
    mkfifo ${input_pipe}

    # keep pipe open using sleep
    sleep 1d > ${input_pipe} &

    # save sleep pid
    echo $! > ${sleep_pid_file}

    # open mysql connection
    mysql -u ${db_username} -p${db_password} ${db_database} >> /dev/null 2>&1 < ${input_pipe} &

    # save mysql pid
    echo $! > ${mysql_pid_file}

    # save connection thread id
    echo "SELECT CONNECTION_ID() INTO OUTFILE '${thread_id_file}';" > ${input_pipe}

    if [ "${quiet}" = "yes" ]
    then
      if [ ${index} -ne ${manager} ]
      then
        echo "SET @m='${script} ${connections} open ${index}',sql_log_off=1;" > ${input_pipe}
      fi
    fi

    # connection ready
    sudo -u mysql touch ${ready_file}
  done
}

teardown_connection_pool() {
  log "teardown connection pool ..."

  # handle abnormal exit
  if [ "${normal_exit}" = "no" ]
  then
    manager_input_pipe=${tmp}/input.${manager}

    # kill all "worker" connections
    set -- ${worker_list}
    for index
    do
      thread_id_file=${tmp}/thread_id.${index}

      if [ -f ${thread_id_file} ]
      then
        # get connection thread id
        thread_id=$( cat ${thread_id_file} )

        # terminate currently executing statement then kill connection thread
        echo "KILL CONNECTION ${thread_id};" > ${manager_input_pipe}

        rm -f ${thread_id_file}
      fi
    done
  else
    # close all "worker" connections
    set -- ${worker_list}
    for index
    do
      input_pipe=${tmp}/input.${index}

      echo "SET sql_log_off=0;" > ${input_pipe}

      echo "SET @m='${script} ${connections} close ${index}';" > ${input_pipe}
    done
  fi

  # teardown all connections
  set -- ${all_list}
  for index
  do
    sleep_pid_file=${tmp}/sleep_pid.${index}

    if [ -f ${sleep_pid_file} ]
    then
      # kill sleep allows pipe to close
      xargs -a ${sleep_pid_file} kill -KILL >> /dev/null 2>&1

      rm -f ${sleep_pid_file}
    fi
  done
}

cleanup_connection_pool() {
  log "cleanup connection pool ..."

  # cleanup all connections
  set -- ${all_list}
  for index
  do
    input_pipe=${tmp}/input.${index}
    ready_file=${tmp}/ready.${index}
    mysql_pid_file=${tmp}/mysql_pid.${index}

    if [ -p ${input_pipe} ]
    then
      rm -f ${input_pipe}
    fi

    if [ -f ${ready_file} ]
    then
      if [ -s ${ready_file} ]
      then
        # delete sql file in ready file
        xargs -a ${ready_file} rm -f >> /dev/null 2>&1
      fi

      sudo -u mysql rm -f ${ready_file}
    fi
  done
}

get_connection() {
  ready=-1

  # timeout, 300 seconds = 5 minutes
  set -- ${timeout_list}
  for time
  do
    set -- ${worker_list}
    for index
    do
      ready_file=${tmp}/ready.${index}

      if [ -f ${ready_file} ]
      then
        if [ -s ${ready_file} ]
        then
          # delete sql file in ready file
          xargs -a ${ready_file} rm -f >> /dev/null 2>&1
        fi

        sudo -u mysql rm -f ${ready_file}

        ready=${index}

        break
      fi
    done

    if [ ${ready} -lt 0 ]
    then
      sleep 1
    else
      break
    fi
  done

  connection=${ready}

  # increment internal connection id
  connection_id=$(( connection_id + 1 ))
}

# queries file
queries_file=${tmp}/queries

# just in case
touch ${queries_file}

# internal query id
query_id=1

add_query() {
  query="${1}"
  # default group = 1
  group=${2:-1}
  # default priority = 1
  priority=${3:-1}
  message=${4}

  query_file=${tmp}/query.${query_id}

  echo "${query}" > ${query_file}

  add_query_file ${query_file} ${group} ${priority} "none" "${message}"

  # increment internal query id
  query_id=$(( query_id + 1 ))
}

add_query_file() {
  query_file=${1}
  # default group = 1
  group=${2:-1}
  # default priority = 1
  priority=${3:-1}
  filter=${4}
  message=${5}

  if [ -s ${query_file} ]
  then
    echo ${query_file} ${group} ${priority} ${filter} ${message} >> ${queries_file}
  fi
}

execute_queries() {
  # setup connection pool
  setup_connection_pool

  sorted_queries_file=${tmp}/sorted_queries

  # sort by group, priority, and file
  sort -k2n -k3n -k1V ${queries_file} > ${sorted_queries_file}

  cat ${sorted_queries_file} |
  while read file group priority filter message
  do
    # get connection
    get_connection

    # log query message
    log "${message}"

    input_pipe=${tmp}/input.${connection}
    ready_file=${tmp}/ready.${connection}
    sql_file=${tmp}/sql.${connection_id}.${connection}

    basename_file=$( basename ${file} )

    {
      if [ "${quiet}" = "yes" ]
      then
        echo "SET sql_log_off=0;" ;

        echo "SET @m='${script} ${connection_id} begin ${connection} ${basename_file}',sql_log_off=1;" ;
      fi ;

      if [ "${filter}" = "none" ]
      then
        # no filter
        cat ${file} ;
      else
        # apply filter
        ${filter} < ${file} ;
      fi ;

      echo ";" ;

      # save sql file in ready file
      echo "SELECT '${sql_file}' INTO OUTFILE '${ready_file}';" ;

      if [ "${quiet}" = "yes" ]
      then
        echo "SET sql_log_off=0;" ;

        echo "SET @m='${script} ${connection_id} end ${connection} ${basename_file}',sql_log_off=1;" ;
      fi ;
    } > ${sql_file}

    cat ${sql_file} > ${input_pipe} &
  done
}

##### end script action