execute mysql query in shell script

Question

Suppose i have 2 databases nm and nm_history. I need to get records from both databases.if i execute query in mysql, its working fine. But if i execute that query in bin/bash it returns following error

**

mysql: option '-e' requires an argument
./get_rec.sh: line 4: subscriber_2: command not found
./get_rec.sh: line 4: a_party: command not found
./get_rec.sh: line 4: subscriber_2: command not found
./get_rec.sh: line 4: prov_channel: command not found
./get_rec.sh: line 4: subscriber_2: command not found
./get_rec.sh: line 4: created: command not found
./get_rec.sh: line 4: svc_mgmt_07: command not found
./get_rec.sh: line 4: created: command not found
./get_rec.sh: line 4: subscriber_2: command not found
./get_rec.sh: line 4: svc_mgmt_07: command not found
./get_rec.sh: line 4: subscriber_2: command not found
./get_rec.sh: line 4: a_party: command not found
./get_rec.sh: line 4: svc_mgmt_07: command not found
./get_rec.sh: line 4: msisdn: command not found
./get_rec.sh: line 4: svc_mgmt_07: command not found
./get_rec.sh: line 4: action_type: command not found
./get_rec.sh: line 4: SELECT nm.., nm.., nm.. AS Created, nm_history.. AS terminate FROM nm. INNER JOIN nm_history.  ON nm.. = nm_history..
WHERE nm_history..=2: command not found

**

here is the script:

mysql -uUser -pPassword -hHostDB -e

The query:

SELECT S.`a_party`, S.`prov_channel`, S.`created` AS Created,M.`created` AS terminate FROM nm.`subscriber_1` S INNER JOIN nm_history.`svc_mgmt_06` M ON S.`a_party` = M.`msisdn` 
WHERE M.`action_type`=2;**

Nicop · Accepted Answer · 2014-07-03 05:12:45Z

11

The -e argument needs an inline argument, as followed:

mysql -uUSER -pPASS -hHOST -e "SELECT * FROM db.table;"

answered Jul 3, 2014 at 5:12

Nicop

3141 silver badge6 bronze badges

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

Comments

kiba · Accepted Answer · 2014-07-03 05:18:00Z

7

you should write query string in shell script without backticks (`):

mysql -uUser -pPassword -hHostDB -e"SELECT S.a_party, S.prov_channel, S.created AS Created,M.created AS terminate 
FROM nm.subscriber_1 S 
INNER JOIN nm_history.svc_mgmt_06 M ON S.a_party = M.msisdn
WHERE M.action_type=2"

answered Jul 3, 2014 at 5:18

kiba

1,6551 gold badge11 silver badges7 bronze badges

1 Comment

Amarjit Singh Over a year ago

how can I use an environment variable in -e argument?

Ôrel · Accepted Answer · 2020-06-12 17:32:40Z

I had the exact same problem and what fixed it was that I changed the double quotation marks for single quotation marks

Like this:

alexander@linux:~> mysql -u alexmack -pXXXXXXX -D eliminacion -e "SELECT `NOMBRE`,`FECHA_TERMINO`, DATE_ADD(`FECHA_TERMINO`, INTERVAL 1 DAY) 'FECHA_BLOQUEO',DATE_ADD(`FECHA_TERMINO`, INTERVAL 10 DAY) 'FECHA_BORRADO' FROM `excolaboradores` WHERE ESTADO = 'habilitado';"
NOMBRE: orden no encontrada
FECHA_TERMINO: orden no encontrada
FECHA_TERMINO: orden no encontrada
FECHA_TERMINO: orden no encontrada
excolaboradores: orden no encontrada
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ', DATE_ADD(, INTERVAL 1 DAY) 'FECHA_BLOQUEO',DATE_ADD(, INTERVAL 10 DAY) 'FECHA_' at line 1
alexander@linux:~> mysql -u alexmack -pXXXXXXX -D eliminacion -e 'SELECT `NOMBRE`,`FECHA_TERMINO`, DATE_ADD(`FECHA_TERMINO`, INTERVAL 1 DAY) 'FECHA_BLOQUEO',DATE_ADD(`FECHA_TERMINO`, INTERVAL 10 DAY) 'FECHA_BORRADO' FROM `excolaboradores` WHERE ESTADO = "habilitado";'
+---------+---------------+---------------+---------------+
| NOMBRE  | FECHA_TERMINO | FECHA_BLOQUEO | FECHA_BORRADO |
+---------+---------------+---------------+---------------+
| test001 | 2020-06-15    | 2020-06-16    | 2020-06-25    |
| test002 | 2020-06-18    | 2020-06-19    | 2020-06-28    |
| test003 | 2020-06-20    | 2020-06-21    | 2020-06-30    |
+---------+---------------+---------------+---------------+

Collectives™ on Stack Overflow

execute mysql query in shell script

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related