0

I need to run a monthly bash script via cron that is related to our company's billing system. This is done with two stored procedures. When I run them via the MySQL console and workbench, they work fine.

I've looked at this article and this is basically the way I do it.

I call via cron, a shell script that looks like this:

mysql -h 192.168.1.1 -u<username> -p<password> mydatabase < /path/to/billing_periods.sql

My text file that has the commands in it looks like this:

call sp_start_billing_period();
call sp_bill_clients();

What happens is that the first query runs, but the second one on the second line, doesn't.

I can make a stored procedure that wraps these two - but I just was hoping to learn why this was happening... Perhaps a mistake I made or a limit in the way you do this..

I also considered doing this (two calls to the MySQL shell):

mysql -h 192.168.1.1 -u<username> -p<password> mydatabase -e "call sp_start_billing_period();"

mysql -h 192.168.1.1 -u<username> -p<password> mydatabase -e "call sp_bill_clients();"
Community
  • 1
  • 1
Rob Watts
  • 103
  • 4

2 Answers2

0

I think you are only allowed to execute a single statement in your input .sql file, see the mysql documentation (manpage) for -e statement.

·   --execute=statement, -e statement
      Execute the statement and quit. The default output format is like that produced with --batch.

The -e is implicit. At least when I do different mysql queries I put them in their own script like you already suggested.

ozborn
  • 980
  • 5
  • 24
0

You could try separating each statement with a semicolon.

mysql -h 192.168.1.1 -u<username> -p<password> mydatabase -e "call sp_start_billing_period();call sp_bill_clients();"

If you have your statements in a file you can do:

while read LINE; do mysql -u<username> -p<password> mydatabase -e"$LINE";echo "-----------";done < statements.sql
Alvaro Flaño Larrondo
  • 5,516
  • 2
  • 27
  • 46