[SOLVED] #!bin/sh script mysql command won't redirect to /dev/null

RagingRaven · 10-25-2016, 01:07 PM

I've created a script with bin/sh to do some work on my mysql database.

Before doing work on the database I do a "FLUSH TABLES WITH READ LOCK; SELECT SLEEP (86400)" QUERY which is all working fine

But later on in the script i'm trying to do a "KILL QUERY <id>" command and I don't want to see the output, but for some reason I can't get it to redirect to /dev/null

The exact command is:

Code:

mysql -u<user> -p<password> -Ae"KILL QUERY <id>;" > /dev/null 2>&1

Where <user> is the database user, <password> is the database password and <id> is the correct query id

The command itself works fine as the query gets killed as expected, but it keeps outputting:

Code:

SLEEP(86400)
1

I've tried various ways of calling the command, like wrapping it in `` and $(), but I keep getting the output.

It's probably something simple I'm overlooking, but I can't seem to figure it out.

Turbocapitalist · 10-25-2016, 01:18 PM

Can you post the exact script here, in [code] tags, minus the sensitive parts? It might be the greater than and less than signs < > in the query messing things up.

RagingRaven · 10-25-2016, 04:57 PM

Will do tomorrow, when I'm back at work and have access to the code again.

AwesomeMachine · 10-25-2016, 07:49 PM

I think

Code:

2>&1 &

should work, and maybe a space between -Ae and the opening quote. And you can try placing the semi after the last "&". But I'm not completely positive on that last thing.

ondoho · 10-26-2016, 01:22 AM

> /dev/null 2>&1

afaik this should suppress ALL output, even if the command is faulty?

RagingRaven · 10-26-2016, 02:21 AM

Here's the full script:

Code:

#!/bin/sh

#Get root's path and variables
. /root/.bashrc

errors=0
mysqlUser="<mysqluser>"
mysqlPass="<mysqlpass>"

#lock tables
mysql -u${mysqlUser} -p${mysqlPass} -Ae"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
`sleep 5`

#get processlist
mysql -u${mysqlUser} -p${mysqlPass} -ANe"SHOW PROCESSLIST" | grep "SELECT SLEEP(86400)" > proclist.txt

#get id
SLEEP_ID=`cat proclist.txt | awk '{print $1}'`

#do work on database
#haven't gotten around to this yet.

#remove lock on tables
mysql -u${mysqlUser} -p${mysqlPass} -Ae"KILL QUERY ${SLEEP_ID};" > /dev/null 2>&1

The last line is the line that outputs

Code:

SLEEP(86400)
1

Keep in mind that the command itself seems to work fine, the query gets killed as expected, but it's just the '> /dev/null 2>&1' part that's not working.

Turbocapitalist · 10-26-2016, 02:35 AM

Thanks.

If you make the second line of the script set -e -x it can help with some debugging as it will show the exact line, with variables filled in, before it is run. That will show you which line is actually producing the output in question. My guess is that it might be the table-locking line.

Also, SLEEP_ID can be assigned without "cat"

Code:

SLEEP_ID=$(awk '{print $1}' proclist.txt)

RagingRaven · 10-26-2016, 03:03 AM

Quote:

Originally Posted by Turbocapitalist

Thanks.

If you make the second line of the script set -e -x it can help with some debugging as it will show the exact line, with variables filled in, before it is run. That will show you which line is actually producing the output in question. My guess is that it might be the table-locking line.

Also, SLEEP_ID can be assigned without "cat"

Code:

SLEEP_ID=$(awk '{print $1}' proclist.txt)

I'll edit my script as you said, but I'm pretty sure it's the last line, as when I put echo's before each command the output mentioned comes right after the last echo.

RagingRaven · 10-26-2016, 03:15 AM

Just tried with set -e -x and it is indeed the last line that's outputting.
What I did see though was that the command shown was:

Code:

mysql -u<user> -p<pass> '-AeKILL QUERY <id>;'

Which seems different from what's in my code, not only missing the > /dev/null 2>&1 part, but also having single quotes and missing double quotes:

Code:

mysql -u${mysqlUser} -p${mysqlPass} -Ae"KILL QUERY ${SLEEP_ID};" > /dev/null 2>&1

keefaz · 10-26-2016, 04:58 AM

It's the sleep query running in background that produces this output, see the docs here:

http://dev.mysql.com/doc/refman/5.7/...function_sleep

Code:

 This statement is interrupted using KILL QUERY from another session:

mysql> SELECT SLEEP(1000);
+-------------+
| SLEEP(1000) |
+-------------+
|           1 |
+-------------+

RagingRaven · 10-26-2016, 05:19 AM

@keefaz

Ok that sounds good, but the question is, how would I get rid of the output?

Do I have to do something to the

Code:

mysql -u${mysqlUser} -p${mysqlPass} -Ae"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &

line?

I thought the &-sign at the end ment the command would run in the background and therefor not produce output?
Would adding a > /dev/null 2>&1 at the end of that command work?

Thank you for your answer though.

Turbocapitalist · 10-26-2016, 05:30 AM

Quote:

Originally Posted by RagingRaven

I thought the &-sign at the end ment the command would run in the background

The ampersand makes it run in the background but it will still produce output, even while other programs are running. You can try with some other program besides the database client to experiment.

You'd need to redirect the output of that line (I referred to it as the table-locking line) if you don't want output.

RagingRaven · 10-26-2016, 05:35 AM

I'll give it a go with > /dev/null 2>&1 at the end of that line then.

Edit: no change with this edit, I still get output.

keefaz · 10-26-2016, 06:05 AM

You can put the command in a subshell like

Code:

( command -args "bla" & )

RagingRaven · 10-26-2016, 06:47 AM

What I'm noticing after the last edit is that again the > /dev/null 2>&1 part looks like it gets removed from the command:

The 'set -e -x' output shows the command as:

Code:

mysql -u<user> -p<pass> '-AeFLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)'

Where in the code it is:

Code:

mysql -u${mysqlUser} -p${mysqlPass} -Ae"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" & > /dev/null 2>&1

So it looks to me like the double quotes might have something to do with it, everything behind it appears to get removed.