0

I'm trying to do the following:

OUTPUT=$(su - mysql -c "mysql --skip-column-names --raw --host=localhost --port=3306 --user=user --password=pass -e 'show variables where variable_name = "max_connections"'")

And execute this via a shell script on the local box... however, I get this returned: ERROR 1054 (42S22) at line 1: Unknown column 'max_connections' in 'where clause'

If I just do the command locally on the box, without bash:

sudo su - mysql -c "mysql --skip-column-names --raw --host=localhost --port=3306 --user=user --password=pass -e 'show variables where variable_name = "max_connections"'"

I get the output expected... so it's like maybe bash is parsing out my delimiters wrong? A related question revealed that I couldn't use single-quote separators for my variable_name, which looks like what bash is attempting to parse my " as?

Update: To demonstrate the issue with single-quotes:

[email protected]:~
-> mysql --skip-column-names --raw --host=localhost --port=3306 --user=user --password=pass -e 'show variables where variable_name = 'max_connections''
ERROR 1054 (42S22) at line 1: Unknown column 'max_connections' in 'where clause'

[email protected]:~
-> mysql --skip-column-names --raw --host=localhost --port=3306 --user=user --password=pass -e 'show variables where variable_name = "max_connections"'
+-----------------+------+
| max_connections | 2000 |
+-----------------+------+
Improve this question

2 Answers 2

Reset to default
2

If you do need quotes for the mysql statement, then you may use \". Look at the difference:

echo "mysql --raw -e 'show vars where var_name = "max_connections"'"
echo "mysql --raw -e 'show vars where var_name = \"max_connections\"'"

But, please!: Do not try to run such complex commands in one string, it is difficult to understand and more difficult to maintain. Divide and conquer, look at this equivalent code:

#!/bin/bash

User="user"
Pass="pass"
Options="--skip-column-names --raw --host=localhost --port=3306"
MoreOptions="--user=$User --password=$Pass"
RunStatement="-e 'show variables where variable_name = \"max_connections\"'"

echo "mysql --skip-column-names --raw --host=localhost --port=3306 --user=user --password=pass -e 'show variables where variable_name = "max_connections"'"
echo "mysql --skip-column-names --raw --host=localhost --port=3306 --user=user --password=pass -e 'show variables where variable_name = \"max_connections\"'"
echo "mysql $Options $MoreOptions $RunStatement"

Which of the three echo commands is easier to read and understand?

Your script command would become (provided vars are set as above) something like:

OUTPUT="$(su - mysql -c "mysql $Options $MoreOptions $RunStatement")"
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Use proper quoting

OUTPUT=$(su - mysql -c "mysql --skip-column-names --raw --host=localhost --port=3306 --user=user --password=pass -e 'show variables where variable_name = 'max_connections'' ")

quote max_connections in ' ' single quotes

The mysql expect quoted string, but bash removes the quoting you have provided

To understand better consider

$echo " "hello " "
 hello  
$echo " 'hello' "
 'hello'

see the quoting preserved in the second echo

Improve this answer

4 Comments

I appreciate the answer, but if you'll look at the linked question... wrapping max_connections in single-quotes isn't an option here..
@MrDuk but the link says to use a different quoting technique? have you already tried this one, if so i may remove the answer.
Here since you use " within another " the quoting ends abrubtly causing the error.
Right, so that's the problem I'm trying to overcome here

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.