Home / server / Questions / 513858
Accepted
senorsmile
Asked: 2013-06-07 09:35:24 +0800 CST

"while read line" returns early when calling ssh "mysql..."

  • 772

I have a script that reads in data from mysql (the get_sql_results function below) I then read in each line, parsing individual columns with awk.
Everything works fine except for the line that assigns the results of a mysql query via ssh to cmusername. When this line is included, only the first line of the $results is read, and the script seems to "escape" the while read loop, after it finishes its first iteration.
When I comment out the cmusername= line, it displays the multiple lines as I would expect.

Here's the main script:

new_results() {
get_sql_results

printf %s "$results" |
while read -r line 
do        
    accounttyperaw=$(echo -en "$line" | awk -F $'\t' '{print $5}')
    accountstatusraw=$(echo -en "$line" | awk -F $'\t' '{print $3}')
    accountname=$(echo -en "$line" | awk -F $'\t' '{print $4}')
    datelastchange=$(echo -en "$line" | awk -F $'\t' '{print $8}')
    cmuserid=$(echo -en "$line" | awk -F $'\t' '{print $9}')

    accounttype=$( get_account_type $accounttyperaw )
    accountstatus=$( get_account_status $accountstatusraw )
    cmusername=$(ssh -p $mysqlport username@remotemysqlserver "/usr/local/mysql/bin/mysql -hdb00 -udba -ppassword -N -e \"SELECT username from userapp.users where userapp.users.user_id rlike '$cmuserid'; \" ")

    echo -en "$domainname\t"
    echo -en "$accounttype\t"
    echo -en "$accountstatus\t"
    echo -en "$accountname\t"
    echo -en "$datelastchange\t"
    echo -en "$cmusername\t"

    echo
done 
}

new_results

Edit - Solved:

ssh needed -n passed to it. see my answer below.

mysql

3 Answers

  1. Best Answer

    Needed to include -n in the ssh command as per

    https://stackoverflow.com/questions/346445/bash-while-read-loop-breaking-early

     -n      Redirects stdin from /dev/null (actually, prevents reading from stdin).  This must be used when ssh is run in the background.  A common trick is to use this to run X11 programs on a remote machine.  For example, ssh -n shadows.cs.hut.fi emacs & will
         start an emacs on shadows.cs.hut.fi, and the X11 connection will be automatically forwarded over an encrypted channel.  The ssh program will be put in the background.  (This does not work if ssh needs to ask for a password or passphrase; see also the
         -f option.)
    • 1

  2. Another way to make ssh work in a piped while loop is to use process substitution and an alternate file descriptor if your version of Bash supports these features.

    while read -r -u 3 line
do
    ...
    cmusername=$(ssh -p $mysqlport username@remotemysqlserver "/usr/local/mysql/bin/mysql -hdb00 -udba -ppassword -N -e \"SELECT username from userapp.users where userapp.users.user_id rlike '$cmuserid'; \" ")
    ...
done 3< <(printf %s "$results")

    This has the additional advantage of not creating a subshell in the while loop so variables set in the loop are available after the loop ends. This can be used even if you're not using the alternate file descriptor.

    • 1

  3. I see no closing parenthezis in your line. Could that be the issue?

        cmusername=$(ssh -p $mysqlport username@remotemysqlserver "/usr/local/mysql/bin/mysql -hdb00 -udba -ppassword -N -e \"SELECT username from userapp.users where userapp.users.user_id rlike '$cmuserid'; \" "

    Should it be

        cmusername=$(ssh -p $mysqlport username@remotemysqlserver "/usr/local/mysql/bin/mysql -hdb00 -udba -ppassword -N -e \"SELECT username from userapp.users where userapp.users.user_id rlike '$cmuserid'; \" ")
    • 0