CODEWITHSUNDEEP
bashshelldelimitermysql
MrDuk
MrDuk

Reputation: 18242

Using multiple string delimiters in command causing parsing issues with mysql client / bash script

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 |
+-----------------+------+

Upvotes: 0

Views: 504

Answers (2)

user2350426
user2350426

Reputation:

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")"

Upvotes: 2

nu11p01n73R
nu11p01n73R

Reputation: 26667

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

Upvotes: 1

Related Questions