0

I am trying to write a Bash script to run a MySQL query from a string constructed in the script. I have been able to construct the query string, and it looks correct when echo'd. And I can cut and paste it into the shell command line and it runs. But, I need to basically cut the query results to just the words, OK or CRITICAL. Nothing else. When I try to save the query result to a bash variable I get:

mysql: unknown option '-2'

If I cut out the grep and cut code, I get the mysql --help results. So, it's not liking my mysql string, but I don't know why. Help?

BTW- Running CentOS 6.3

alert=`mysql myDB -e "select value from config_table where name=\"ach_alert_time\"\G" | /bin/grep value: | /bin/cut -f2 -d:`
echo $alert  #Brings back 18,50,00

sql="mysql myDB -e 'select if(count(*) > 0,\"CRITICAL\",\"OK\") as STATUS from xyz_batch where timestamp > concat(date(now()),\" \",maketime("$alert"))\G' | /bin/grep OK  | /bin/cut -f2 -d:"
echo $sql # mysql myDB -e 'select if(count(*) > 0,"CRITICAL","OK") as STATUS from xyz_batch where timestamp > concat(date(now())," ",maketime( 18,50,00))\G' | /bin/grep OK | /bin/cut -f2 -d

status=`$sql`

# Whoops!
# mysql: unknown option '-2'

echo $status
Improve this question

3 Answers 3

Reset to default
1

The problem is that when you run $sql as a command, it under goes word-splitting, filename expansion, and so on, with no respect for any quotation-marks that $sql might contain. And it won't handle pipes and other shell metacharacters. For example, if you're in a directory that contains the files foo and bar, then this:

cmd="echo '     *     ' | grep baz"
$cmd

will print ' bar foo ' | grep baz — it won't preserve whitespace, it will expand the * into a list of files, and it will treat | and grep as arguments to echo.

You can work around this by using the command eval "$sql" instead of just $sql:

status=`eval "$sql"`

but you may want to re-evaluate your design a bit. Maybe there's a better way to achieve what you want?

Edited to add: You say that you're constructing the query in the script, but do you actually need to store the entire command in a variable? Something like this:

sql="select if(count(*) > 0,\"CRITICAL\",\"OK\") as STATUS from xyz_batch where timestamp > concat(date(now()),\" \",maketime("$alert"))\G"
status=`mysql myDB -e "$sql" | /bin/grep OK  | /bin/cut -f2 -d:`

would not have this problem, since then $sql is then just a single argument, so you don't need Bash to do anything special with it.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Looks to me like a quoting problem. Make sure your use of backticks and quotes is what you intended. Assigning variables the result value of a backtick command might be causing some confusion as well. Break that up and it will be easier to debug.
@djangofan: No, not really; it's a running-a-variable-that-contains-a-command-is-not-the-same-as-running-the-command-directly problem. :-P
1

You can drive yourself crazy trying to get every last thing properly escaped. Break it into manageable steps:

# Multiple lines allowed, for readability. Inside single quotes, no
# escaping needed.
sql_template='select if (count(*) > 0, "CRITICAL", "OK") as STATUS
  from xyz_batch
  where timestamp > concat( date( now() ), " ", maketime("%s"))\G'

# Bash 4 only
printf -v sql_stmt "$sql_template" "$alert"
# Bash 3 equivalent
# sql_stmt=$( printf "$sql_template" "$alert" )

status=$( mysql myDB -e "$sql_stmt" | awk -F: '/OK/ {print $2}' )
Improve this answer

5 Comments

-1, sorry. This has the exact same problems as the OP's original code: it will perform word-splitting and filename-expansion on the contents of $sql_pipeline, and it will pass |, awk, and so on as arguments to mysql.
(In other words: the OP's problem isn't really an issue of escaping; or rather -- treating it as an issue of escaping is a surefire way to drive yourself crazy.)
True. I debated whether to preserve that aspect of the code; I'm going to save myself the trouble and just recommend writing the pipeline out.
Which is the same thing I recommended. :-)
Yeah, you modified your answer while I was writing mine up. Overlap! :)
0

There is a problem with double code. 

sql="mysql myDB -e 'select if(count(*) > 0,\"CRITICAL\",\"OK\") as STATUS from xyz_batch where timestamp > concat(date(now()),\" \",maketime("$alert"))\G' | /bin/grep OK  | /bin/cut -f2 -d:"

Replace with 

sql=`mysql myDB -e 'select if(count(*) > 0,\"CRITICAL\",\"OK\") as STATUS from xyz_batch where timestamp > concat(date(now()),\" \",maketime("$alert"))\G' | /bin/grep OK  | /bin/cut -f2 -d:`
Improve this answer

5 Comments

Tips: sh -x scriptname to debug shell script.
I think you're misunderstanding what the OP is trying to do. In the actual script, the variable $sql is constructed programmatically, so what you describe is not an option.
No, he's not. He's storing the command in $sql. The output goes into $status.
In my solution he can use status=$sql
Satish: But your solution isn't a solution. He has this variable, $sql, that's he's constructed using a script. He needs to run the contents of the variable as a command, and capture the output. Your "solution" is simply not to construct the variable to begin with, but rather, to have hard-coded the command into the script. That doesn't accomplish what he needs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.