Search code examples
mysqlbashubuntuquotesgnu-parallel

mysql query inside escaped bash -c string. How to put quotes?

Hello I am having trouble with a very specific line in a bash script. Here is the code:

ssh $SOURCEIP  "/usr/bin/time -f \"%e\" bash -c \"seq $ITER | parallel -n0 \"mysql --silent -h $TARGET -uroot -ppass -e 'SELECT * FROM dbname.tablename WHERE size = $SIZE;' >> out.txt\""

The problem is I ran out of quotes. The opening and escaped double quotes at the beginning of "mysql" are closing those from "bash -c". I have to put the mysql statement in double quotes and the query in single quotes, otherwise i get an error and I can't figure out how to proceed. I know that I should not pass the password like that and it will be changed later, I get this warning "$ITER"-times everytime i test this because --silent doesn't suppress this. The problematic code is part of a small shell script that is supposed to just perform this data transfer. I want to change to the other machine with ssh first and not via parallel because of consistency with other scripts.

So basically I need the double quotes around the bash -c command to get this whole parallel operation to work, which are already escaped because of the opening ssh doublequotes and also I need to put the mysql command inside quotes as well but they are closing each other somehow.

Any help will be greatly appreciated. Thanks in advance. Largio

Edit: (SOLUTION) As suggested by @ole-tange the following command worked for me.

parallel --shellquote | parallel --shellquote

After invoking in a shell, i pasted my string in question into the prompt and got the masked string back. I still had troubles with finding out what exactly to paste but in the end it is just logical. What exactly i pasted into the quoter was:

sql mysql://root:pass@$TARGET/ 'SELECT data FROM db_name.tablename WHERE size = ${SIZE};' >> out.txt

But still i had some problems with my variables inside my query. The problem here was that i had to de-mask the masking of the 2 variables $TARGET and $SIZE after everything got masked by the parallel quoter. Maybe my thinking has a too laborious manner but i could not get it to work in another way. Also note that i did not put quotes around the whole sql statement, as my plan was before, because now the quoter compensated for that. For consistency reasons i paste the final string that i got working in the end (with my changes afterwards):

ssh $SOURCEIP  "/usr/bin/time -f \"%e\" bash -c \"seq $ITER | parallel -n0 sql\\\ mysql://root:pass@$TARGET/ \\\'SELECT\\\ data\\\ FROM\\\ db_name.tablename\\\ WHERE\\\ size\\\ =\\\ ${SIZE}\\\;\\\'\\\ \\\>\\\>\\\ out.txt\""
Solution

  • GNU Parallel has a quoter:

    $ parallel --shellquote
"*\`$
[CTRL-D]
\"\*\\\`\$

    And you can do it twice:

    $ parallel --shellquote | parallel --shellquote
"*\`$
[CTRL-D]
\\\"\\\*\\\\\\\`\\\$

    So just paste the string you want quoted.

    But you might want to consider using functions and use env_parallel to copy the function:

    myfunc() {
  size=$1
  target=$2

  sql mysql://root:pass@$target/ "SELECT data FROM db_name.tablename WHERE size = $size;" >>  out.txt
}
env_parallel --env myfunc -S $SOURCEIP --nonall myfunc $SIZE $TARGET

    Also: Instead of mysql try sql mysql://root:pass@/ 'SELECT * FROM dbname.tablename WHERE size = $SIZE;'