内循环的同时是不是想起了变量。 Mysql的bash脚本AUTO_INCREMENT [英] The variable inside while loop is not remembered. Mysql bash script auto_increment

问题描述

我在做这个剧本很长一段时间。但我开始看到一些错误，当我尝试添加一个变量（$ EMAIL_MSG）存储文本的一些字符串以后通过电子邮件发送。看来，while循环中的变量没有记住。 （<一href=\"http://stackoverflow.com/questions/16854280/modifying-variable-inside-while-loop-is-not-remembered\">Modifying而循环不记得里面变量）

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. (Modifying variable inside while loop is not remembered)

下面是脚本的一部分：

#!/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

这个问题似乎是，while循环是在子shell执行。所以，我的变量$ EMAIL_MSG做任何修改将无法使用，一旦子shell退出。

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.

我读，我应该改变这样的while循环：

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?

推荐答案

尝试：

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

将构建≤（...）叫进程替换。它运行括号内的命令，并使其输出作为如果FIFO已经建立。该构建＆LT; ＆LT;（...）连接FIFO的输出到您的while循环标准输入

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.

进程替换既需要庆典和操作系统，如Linux，支持FIFO中。区区的Bourne shell不会支持它。

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")"

本使用命令替换（ $（...））捕捉 $ MYSQLCONNECT 命令的输出然后使用下面的字符串（＆LT;＆LT;＆LT; ）规定的标准输入到你的而读循环。请注意，命令替换外的双引号。他们是必要的preserve命令输出中的换行。如果没有这些双引号， $ MYSQLCONNECT 的输出会出现在而读循环作为一个单一的长行。

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

则没有命令将被执行，文字串 $（$ MYSQLCONNECT --batch -N -e$查询）将提供给而读循环。

这篇关于内循环的同时是不是想起了变量。 Mysql的bash脚本AUTO_INCREMENT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！