1

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.

Sodved
  • 8,428
  • 2
  • 31
  • 43

1 Answers1

2

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

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828