1

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.

2
  • ssh -p $mysqlport? Jun 6, 2013 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, 2013 at 17:47

3 Answers 3

1

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

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.

4
  • 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, 2013 at 14:24
  • @senorsmile: What line gives the syntax error? Jun 12, 2013 at 19:16
  • It's the done 3< <(printf %s "$results") line.
    – senorsmile
    Jun 12, 2013 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. Jun 13, 2013 at 1:59
0

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'; \" ")
4
  • 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, 2013 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, 2013 at 17:53
  • 1
    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, 2013 at 18:00
  • @senorsmile Maybe a set -e somewhere before and for whatever reason ssh returns >0? Jun 6, 2013 at 18:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .