Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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() {

printf %s "$results" |
while read -r line 
    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"



Edit - Solved:

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

share|improve this question
ssh -p $mysqlport? – Hauke Laging Jun 6 '13 at 17:39
that's previously defined. I cleaned up portions of the code that have any specific details that aren't needed. – senorsmile Jun 6 '13 at 17:47
up vote 1 down vote accepted

Needed to include -n in the ssh command as per

 -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 emacs & will
         start an emacs on, 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.)
share|improve this answer

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
    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.

share|improve this answer
This solution doesn't seem to work at all. Bash just complains giving "syntax error". Bash version = GNU bash, version 4.2.42(1)-release – senorsmile Jun 12 '13 at 14:24
@senorsmile: What line gives the syntax error? – Dennis Williamson Jun 12 '13 at 19:16
It's the done 3< <(printf %s "$results") line. – senorsmile Jun 12 '13 at 22:40
@senorsmile: Try: done 3< <(printf '%s\n' "$results") (I had copied that from your question without realizing that it needs a newline). Make sure your shebang is calling Bash. There's nothing in the line that should cause a syntax error in the version you listed. – Dennis Williamson Jun 13 '13 at 1:59

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'; \" ")
share|improve this answer
Ah, I messed with that line for posting to serverfault. There is indeed a ")" at the end. In fact, it works properly, but only for one line of data. – senorsmile Jun 6 '13 at 17:47
$cmuserid will never be expanded, because it is within single quotes, have you tried escapiong those too?; I'd also try adding a set -x befre your line- the debug output might help you pin-point the issue. – Petter H Jun 6 '13 at 17:53
It is actually within double quotes. The single quotes themselves are also within the double quotes; the very reason why I used them there. The ssh command itself works great, it just for whatever reason ends the while read line loop. – senorsmile Jun 6 '13 at 18:00
@senorsmile Maybe a set -e somewhere before and for whatever reason ssh returns >0? – Hauke Laging Jun 6 '13 at 18:05

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.