mysql bash shell script outputting erro

Question

I am trying to create a bash shell script that runs an sql query and later on create a cronjob that runs it at an specific time.

I created my bash script see below

mysql -u $host -D $dbname -u $user -p$password -e $mySqlQuery

I have wrap -u -D -p -e all in variables. I have also change it to and executable file. When i run it. it gives out an output stating. Command not found. can anyone tell the mistake i made?

Below is the bash script

host="host"
user="user"
dbname="database"
password="password"
mySqlQuery = "SELECT *
FROM invoice i
  JOIN item it
    ON it.invoice_id = i.id
  JOIN user u
    ON i.user_id = u.id
  JOIN gateway_response gr
    ON gr.invoice_id = i.id

WHERE  i.created_at >= '2019-03-01 00:00:00' and
i.created_at <= '2019-03-17 23:59:59' and i.status=9"

mysql -u $host -D $dbname -u $user -p$password -e $mySqlQuery

Below is the error i am receiving when i run it.

/home/chris2kus/givingDetectRun.sh: line 8: mySqlQuery: command not found /home/chris2kus/givingDetectRun.sh: line 20: mysql: command not found –

Have you tried to run it using the full path to the mysql command? Ex.: /usr/local/bin/mysql -u $host -D $dbname -u $user -p$password -e $mySqlQuery — Rod Elias, Mar 20 '19 at 19:45
Also, I suggest you to use double quotes around your variable names, i.e., use "$host" instead of just $host — Rod Elias, Mar 20 '19 at 19:48
@RodElias what is the difference wrapping the variables in double quotes? — Chris, Mar 20 '19 at 19:53
Take a look at https://stackoverflow.com/questions/6697753/difference-between-single-and-double-quotes-in-bash — Rod Elias, Mar 20 '19 at 19:55

Rod Elias · Answer 1 · 2019-03-21T11:59:14.057

1

There must be no spaces around the = and the variable name mySqlQuery.

Also, I suggest you wrap your variables around double quotes, i.e., use "$host" instead of just $host.

edited Mar 21 '19 at 11:59

answered Mar 20 '19 at 19:58

Rod Elias

706
6
14

score 0 · Answer 2 · answered Mar 20 '19 at 19:59

You can write a file like this and chmod 755 filename.sh :

#!/bin/bash

host="localhost"
dbname="test"
user="root"
password="xxxxxxxxxx"

mySqlQuery="select * 
from col;"

mysql -u $host -D $dbname -u $user -p$password -e "$mySqlQuery"

Sample

$chmod 755 testmysql.sh
$
$ ./testmysql.sh 
+----+------+------+------+
| id | Col1 | Col2 | Col3 |
+----+------+------+------+
|  1 |    1 |    2 |    3 |
|  2 |    2 |    3 |    4 |
|  3 |    3 |    4 |    5 |
+----+------+------+------+
$

score 0 · Answer 3 · answered Mar 20 '19 at 20:19

For starters, make sure your line 5 looks like mySqlQuery="SELECT..."

(notice no spaces on either side of the assignment operator)

For seconds, try to re-format your entire MySQL query to fit into a single line. (perhaps Heidi since it's a query, since that will keep you at least syntax-wise in the clear of errors)

For thirds, once you confirm that the bash runs as intended, add \n to tell the bash that you're continuing the command in the next row

Prototyping before optimization. Get it running before you get it flying.

mysql bash shell script outputting erro

3 Answers3