Subshell MySQL-query in bash

Question

I'm trying to set variable in the cycle in Ubuntu bash, which is getting recordset from database, but this variable is setting to its previous value. Here is a code:

#!/bin/bash
PREV_FILE_PATH="127"

while true
do
    echo "$PREV_FILE_PATH"
    mysql -h$DB_HOST -u $DB_USER -p$DB_PASS $DB_NAME --skip-column-names --default-character-set=UTF8  -e "here is a query" | while read "here is getting variables from recordset";

    do

    PREV_FILE_PATH="777"

    done

done

And this code prints every time:

127 127 127

But whe I replaced this block-:

mysql -h$DB_HOST -u $DB_USER -p$DB_PASS $DB_NAME --skip-column-names --default-character-set=UTF8 -e "here is a query" | while read "here is getting variables from recordset";

with just while true and break at the end of cycle it works fine and prints:

127 777 777 777

Script creates some subshell and running that MySQL query in that subshell. So what should I do to make script change that variable?

iscfrc · Answer 1 · 2013-10-03T18:18:07.643

As you noted the issue is due to the creation of a subshell which is being caused by piping the output of the mysql command to the while loop. A simple example:

PREV_FILE_PATH=127
echo test | while read _; do
   PREV_FILE_PATH=777
done
echo $PREV_FILE_PATH
# output: 127

Since you're using BASH you can move the mysql command from being a pipe to a substituted process fed to the while loop via STDIN redirection. Using the previous simple example:

PREV_FILE_PATH=127
while read _; do
   PREV_FILE_PATH=777
done < <(echo test)
echo $PREV_FILE_PATH
# output: 777

So to fix your code, you will want to move your mysql command in the same fashion that I moved the echo command above:

while read "here is getting variables from recordset"
do
   PREV_FILE_PATH="777"
done < <(mysql -h$DB_HOST -u $DB_USER [..remaining options..])

Note that process substitution via <() is a BASH-ism and isn't POSIX compliant.

Subshell MySQL-query in bash

1 Answers1