2

I am trying to execute a mysql query inside my shell script, which is not executing . Here is what i am trying to do

query="\"select count(*) from information_schema.tables where TABLE_SCHEMA not in ('mysql','performance_schema','information_schema') and engine!='INNODB';\""

echo $query

"select count(*) from information_schema.tables where TABLE_SCHEMA not in ('mysql','performance_schema','information_schema') and engine!='INNODB';"

Inside my shell script , i am executing below which is not working. It is not giving any error but simply not considering -e option and listing down all the valid options for mysql client.

mysql -u$username -p$password -e$query

I even validated below in my shell prompt

echo "mysql -uroot -p -e$query"
mysql -uroot -p -e"select count(*) from information_schema.tables where TABLE_SCHEMA not in ('mysql','performance_schema','information_schema') and engine!='INNODB';"

which is perfectly fine and executes without any issues. I am not sure what to do now. please help.Thanks

3
  • 1
    Why do you put quotes inside your query string?
    – mustaccio
    Jun 8, 2020 at 15:03
  • @mustaccio : because i wanted to construct and execute my sql query as like i would manually do in shell prompt. But even without qoutes also its not working Jun 8, 2020 at 15:07
  • It is quite possible. But it is easy to be tripped up by quoting and escaping.
    – Rick James
    Jun 10, 2020 at 19:21

1 Answer 1

1

You need to do it like this:

query="select count(*) from information_schema.tables where TABLE_SCHEMA not in ('mysql','performance_schema','information_schema') and engine!='INNODB'"
mysql -uroot -p -e"${query}"

or

query="select count(*) from information_schema.tables"
query="${query} where TABLE_SCHEMA not in"
query="${query} ('mysql','performance_schema','information_schema')"
query="${query} and engine!='INNODB'"
mysql -uroot -p -e"${query}"

Double quotes do not need to be part of the query variable

2
  • Thank you so much . This will help me in lot of automation. Jun 9, 2020 at 9:32
  • @udhayandharmalingam if my answer was helpful, please mark it as accepted by clicking the check mark. Jun 9, 2020 at 15:59

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.