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:

mysql@gossdevmydb0119.abn-sjl.ea.com:~
-> 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'

mysql@gossdevmydb0119.abn-sjl.ea.com:~
-> mysql --skip-column-names --raw --host=localhost --port=3306 --user=user --password=pass -e 'show variables where variable_name = "max_connections"'
+-----------------+------+
| max_connections | 2000 |
+-----------------+------+
Community
  • 1
  • 1
MrDuk
  • 16,578
  • 18
  • 74
  • 133

2 Answers2

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

nu11p01n73R
  • 26,397
  • 3
  • 39
  • 52
  • 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 Oct 16 '14 at 15:24
  • @MrDuk but the link says to use a different quoting technique? have you already tried this one, if so i may remove the answer. – nu11p01n73R Oct 16 '14 at 15:36
  • Here since you use `"` within another `"` the quoting ends abrubtly causing the error. – nu11p01n73R Oct 16 '14 at 15:37
  • Right, so that's the problem I'm trying to overcome here – MrDuk Oct 16 '14 at 15:41