The variable inside while loop is not remembered.

Question

I've working on this script for quite some time. But I started to see some errors when I tried to add a variable ($EMAIL_MSG) to store some strings of text to later be emailed. It seems that the variable inside while loop is not remembered. (A variable modified inside a while loop is not remembered)

Here is part of the script:

#!/bin/bash
#
# This is bash script checks when auto_increment column is reaching its limit
# To run Script $ ./auto_increment_check.sh [username] [password]

MYSQL_USER="$1"
MYSQL_PASSWD="$2"
MYSQLCONNECT="mysql -u$MYSQL_USER -p$MYSQL_PASSWD"
MAX_RATIO="0.8" # Max percentage of fullness of an auto_increment column   (ex. '0.8' means 80% full)
EMAIL_MSG=""
EMAIL_RCPNT="[e-mail]"

QUERY="
SELECT table_schema,
       table_name,
       data_type,
       ( CASE data_type
           WHEN 'tinyint' THEN 255
           WHEN 'smallint' THEN 65535
           WHEN 'mediumint' THEN 16777215
           WHEN 'int' THEN 4294967295
           WHEN 'bigint' THEN 18446744073709551615
         end >> IF(Locate('unsigned', column_type) > 0, 0, 1) ) AS max_value
FROM   information_schema.columns
WHERE  table_schema NOT IN ( 'mysql', 'information_schema', 'performance_schema')
       AND data_type IN ('tinyint','smallint','mediumint','int','bigint')
       AND extra = 'auto_increment'"

$MYSQLCONNECT --batch -N -e "$QUERY" | while read DATABASE TABLE DATA_TYPE MAX_VALUE;

do
  NEXT_AUTO_INCREMENT=`$MYSQLCONNECT --batch -N -e "show create table $DATABASE.$TABLE" |  awk -F'AUTO_INCREMENT=' 'NF==1{print "0";next}{sub(/ .*/,"",$2);print $2}'`
  AUTO_INCREMENT_RATIO=$(awk 'BEGIN {printf "%3.2f\n", '$NEXT_AUTO_INCREMENT' / '$MAX_VALUE'}')

  if [[ $(awk 'BEGIN{print ('$AUTO_INCREMENT_RATIO'>='$MAX_RATIO')}') -eq 1 ]] ; then
    EMAIL_MSG="$EMAIL_MSG\n\nAuto Increment Warning on $(hostname) - $DATABASE.$TABLE - NEXT AUTO INCREMENT: $NEXT_AUTO_INCREMENT, MAX CAPACITY: $MAX_VALUE, RATIO: $AUTO_INCREMENT_RATIO."
  fi

done

if [ EMAIL_MSG != "" ]; then
    echo -e $EMAIL_MSG | mail -s "Auto Increment Warning on $(hostname) " $EMAIL_RCPNT
fi

The problem seems to be that the while loop is executed in a subshell. So any changes I do for the variable $EMAIL_MSG will not be available once the subshell exits.

I read that I should modify the while loop like this:

while read DATABASE TABLE DATA_TYPE MAX_VALUE;
do
  ...
  ...
  ...
done <<< '$MYSQLCONNECT --batch -N -e "$QUERY"'

if [ EMAIL_MSG != "" ]; then
    echo -e $EMAIL_MSG | mail -s "Auto Increment Warning on $(hostname) " $EMAIL_RCPNT
fi

But I'm getting errors:

[root@localhost /]# ./vagrant/auto.sh root root
Mon Jan 27 21:04:01 UTC 2014: Auto Increment Check starting.
./vagrant/auto.sh: line 53: syntax error near unexpected token `--batch'
./vagrant/auto.sh: line 53: `done <<< $MYSQLCONNECT --batch -N -e "$QUERY"'

Any ideas how to fix that?

Answer 1

The following won't do what you think:

if [ EMAIL_MSG != "" ]; then

because it doesn't expand the variable $EMAIL_MSG, it just uses the literal string "EMAIL_MSG".

Use this:

if [ ! -z "$EMAIL_MSG" ]; then

There are other problems I can see in your script.

• The issue of using <<< is immaterial. Go back to running a command and piping in to while like your original script.

• Don't use single-quotes if you want to expand shell variables inside the string.

• Your passwords will fail if they contain spaces, or semicolon, or ampersand, or hash, or several other special characters.

  Actually, I wouldn't use the -u and -p options at all. Put the username and password into a config file and then pass --defaults-extra-file to load that config file. See http://dev.mysql.com/doc/refman/5.6/en/option-files.html

There might be more problems -- that's just what I noticed.

Answer 2

Try:

done < <($MYSQLCONNECT --batch -N -e "$QUERY")

The construct <(...) is called process substitution. It runs the command inside parentheses and makes it output available as if a FIFO had been created. The construct < <(...) connects the output of the FIFO to stdin on your while loop.

Process substitution requires both bash and an operating system, such as linux, that supports FIFOs. A mere bourne shell will not support it.

Alternatively, this could be done as a here string:

done <<<"$($MYSQLCONNECT --batch -N -e "$QUERY")"

This uses command substitution ($(...)) to capture the output of the $MYSQLCONNECT command and then uses a here-string (<<<) to provide that as stdin to your while read loop. Note the double-quotes outside the command substitution. They are necessary to preserve the newlines within the command output. Without those double-quotes, the output of $MYSQLCONNECT would appear to the while read loop as a single long line.

If single-quotes were used instead of double quotes, as in:

done <<<'$($MYSQLCONNECT --batch -N -e "$QUERY")' # Don't use this

then no command would be executed and the literal string $($MYSQLCONNECT --batch -N -e "$QUERY") would be provided to the while read loop.