Running mysql commands inside bash script

Question

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.

just a silly question here...is prefix a variable? or a string value? if a string value, shouldn't it be 'prefix'? Thanks. — arcee123
– arcee123, Commented Jan 9, 2023 at 20:59
it is string I tried that and I get this 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 — zeid10
– zeid10, Commented Jan 9, 2023 at 21:04

arcee123 · Accepted Answer · 2023-01-09 21:50:10Z

1

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

you can push everything into one single line.
try reformatting your script to use EOF tags instead.
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/

edited Jan 9, 2023 at 21:50

answered Jan 9, 2023 at 21:07

arcee123

30514 gold badges61 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

zeid10 Over a year ago

that solved the first error, but now I get this: mysql: option '-e' requires an argument ./s3_clone.sh: line 3: SELECT: command not found ./s3_clone.sh: line 4: where: command not found ./s3_clone.sh: line 5: option: command not found ./s3_clone.sh: line 5: AND: command not found ./s3_clone.sh: line 6: QUERY: command not found

arcee123 Over a year ago

ditch the -e and see what that gives you...

zeid10 Over a year ago

Syntax to use near 'QUERY' at line 1 ./s3_clone.sh: line 3: SELECT: command not found ./s3_clone.sh: line 4: WHERE: command not found ./s3_clone.sh: line 5: option: command not found ./s3_clone.sh: line 5: AND: command not found ./s3_clone.sh: line 6: QUERY: command not found

arcee123 Over a year ago

my bad. only 2 <<'s are needed. sorry, I did it in my head first. :)...editing my answer.

arcee123 Over a year ago

ok. ditch the -e and only use 2 x << in the line. try that.

|

zeid10 · Accepted Answer · 2023-01-10 21:47:36Z

1

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

edited Jan 10, 2023 at 21:47

answered Jan 10, 2023 at 21:29

zeid10

5218 silver badges30 bronze badges

1 Comment

arcee123 Over a year ago

there you go. awesome....this was a good one.

Collectives™ on Stack Overflow

Running mysql commands inside bash script

2 Answers 2

13 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

13 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related