Is there a way to get the mysql cmdline client to abort as soon as it encounters an error, and to return a non-zero exit status to the controlling shell script.
Basically I want to be able to have a shell script like:
mysql -A --batch <<END_SQL
UPDATE table1...
UPDATE table2...
...
END_SQL
if [ $? -ne 0 ]
then
# Error handling
fi
With oracle I just had to put WHENEVER SQLERROR EXIT FAILURE at the top of the sql commands. Is there a mysql equivalent? Not found anything in google.
By default, the mysql client already does exit when it encounters an error.
You can make the mysql client not exit when it encounters an error if you use the --force option.
$ echo "select * from nonexistanttable ; select now()" | mysql test
ERROR 1146 (42S02) at line 1: Table 'test.nonexistanttable' doesn't exist
$ echo $?
1
Notice this does not return the result of now(). It exited first.
$ echo "select * from nonexistanttable ; select now()" | mysql --force test
ERROR 1146 (42S02) at line 1: Table 'test.nonexistanttable' doesn't exist
now()
2017-11-12 22:46:30
$ echo $?
0
Ah, there it is.
This question is sort of the opposite of MySQL: ignore errors when importing?
It's not widely known that this is the behavior of the mysql client. The only clue would have been to read about the --force option in this manual page: https://dev.mysql.com/doc/refman/5.7/en/mysql-command-options.html
