Question 21354 in Mysql Question posted in
The official MySQL documentation can be found here.

Running mysql commands inside bash script

I am trying to execute a mysql command inside a bash script but every time I try to execute it fails for some reason. I have tried several ways and none seemed to work(e.g: <<QUERY…;QUERY)
My select is the following but I get an error:

#!/bin/bash
mysql -utesting -pMypass -hlocalhost -D test DB -e "

SELECT value FROM h6_options

where module=cloud

AND `option`=prefix;"

I get the following error.
ERROR 1064 (42000) at line 3: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '='prefix'' at line 5

Any help is appreciated.
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Turns out the issue was the backticks. I had to escape them in order to not evaluate the line.

    <<QUERY
SELECT value FROM h6_options

WHERE `option`="prefix"

AND module="cloud"
QUERY

  2. 0

    You are using a multi-line SQL statement. this means you have two options:

    1. you can push everything into one single line.
    2. try reformatting your script to use EOF tags instead.
    3. any option I didn’t think of that the smart people here can consider…

    here’s an example:

    mysql -u USER -pPASSWORD <<EOF
SQL_QUERY 1
SQL_QUERY 2
SQL_QUERY N
EOF

    so for you, I would do this:

    mysql -utesting -pMypass -hlocalhost -D test DB <<EOF
SELECT value FROM h6_options
where module=cloud
AND `option`='prefix';
EOF

    Note: I don’t like using the word EOF….for me, I like the word SQL or QUERY. because EOF means end of file. The code is identical, so you can use the word of choice:

    mysql -utesting -pMypass -hlocalhost -D test DB <<QUERY
SELECT `value` FROM `h6_options`
where `module`='cloud'
AND `option`='prefix';
QUERY

    Source: https://www.shellhacks.com/mysql-run-query-bash-script-linux-command-line/

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search