MySQL Bugs: #35634: 'mysql' client: 'source' should stop after first failed command

Bug #35634	'mysql' client: 'source' should stop after first failed command
Submitted:	28 Mar 2008 8:54	Modified:	1 Oct 2008 15:08
Reporter:	Guilhem Bichot	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S4 (Feature request)
Version:	5.1-bk	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
if I have a few lines in SQL in a script and do

mysql < thescript

then mysql will stop execution of the entire script after getting any
error (like duplicate key, too long query, anything). I consider it a
good behaviour. But if I instead use, after starting the mysql client:

source thescript

then it will happily try to execute the lines after the failing one.

In my case, the script had a certain logic; it was the output of
mysqlbinlog; one statement failed to execute (was longer than
max_allowed_packet), but the next statements, short enough, were
executed. Of course, those next statements did no good, they had
dependencies on the failed statement (they executed wrongly because
the failed statement hadn't run).
To give you an example, originally this was run:

CREATE TABLE t(a int, b blob, unique(a));
INSERT INTO t VALUES(1, smallblob);
UPDATE t SET a=2, b=largeblob;
INSERT INTO t VALUES(1, othersmallblob);
UPDATE t SET a=5 WHERE a=1;

in the end of the original run (with big max_allowed_packet), we had
in table: (5, othersmallblob).
In the run from mysqlbinlog, with default, smaller max_allowed_packet,
because the first UPDATE was longer than max_allowed_packet, it
failed, then INSERT failed (duplicate key), UPDATE updated the wrong
row: so we have in table: (5,smallblob). Note close to anything
correct, and we even have spoiled our data.

If I do
mysql -e "source thescript"
it does stop at first error.

The key to this is probably that in interactive mode (I typed "source
thescript" in interactive mode, no "-e" or "<" on the command-line),
"mysql" assumes that I am entering the queries one by one and can thus
stop when I see a problem. But "source" is never really interactive:
it takes line after line without prompting you, when you see an error
it's too late, it has already started executing the next statement.

Ok, there is option --batch, that gives the non-interactive behaviour
for "source", but it's not that friendly, you don't get any prompt:
[INS 22:29 /m/mysql-5.1-maint/mysql-test $] ../client/mysql -uroot -S var/tmp/master.sock -v -B
<this is all that you get on screen, it's waiting for your commands>

And anyway, the current behaviour of "source" in interactive mode is a
problem and the existence of --batch is not a good answer. Default
behaviour should be the safe one.

How to repeat:
Create this kind of SQL file
create table t(a int);
insert into t values(10)); # a syntax error
insert into t values(11);

it will not stop on first error.

Suggested fix:
treat "source" as reading from stdin: stop at first error.

Why don't we want to offer users a choice in the matter?

Clarification from Jon: he meant that there are cases where an error in the execution of the script is not a real issue (for example if the script is just a bunch of INSERTs to produce an arbitrary data load), and where it would be annoying if the entire script aborted at first error.
He suggests that the behaviour to stop at first error become default but with a command-line option to revert to the no-stop behaviour (Jon suggests the option could allow the user to choose what class of statements should not cause a stop: INSERT, UPDATE, DELETE... - though Guilhem thinks this is then a separate feature request as the one here).

Thank you for a reasonable feature request.

In mysql-maria now, in 6.0-maria in a few days, and later in 6.0-main, there is
'mysql --abort-source-on-error'
which very probably is what was requested (to be tested).
Monty implemented it.

If you would like MySQL command line client to stop after the first failure, please use \e and \run instead of source.

Expanding on Konstantin's advice: one can do:
\e (this creates a tmp file and opens it with $EDITOR)
then import your SQL script into the tmp file in the editor (:r in vi, insert-file in Emacs, copy-paste, etc)
then exit your editor: \e automatically loads the content of this temporary file in memory ready to execute, you just need to type ";" or \go to execute it.

The \e option doesn't seem to be a very practical example when the source file is large e.g. when restoring a backup which can be many many GB's

Adding an abort on error option would be much more preferable.

I found a temporary workaround for this problem.
First open a connection with --skip-reconnect

Second after the query, which on generating an error should stop execution insert the query:

kill connection if(@@error_count>0,connection_id(),0);

or equivalent:

kill if(@@error_count>0,connection_id(),0);

Hopefully thread id 0 cannot exist... (can it?)

this will generate an error which is not so nice, but will exit on the preceeding query's error...

downside: it cannot be run in other forms than source otherwise it will exit after the first test.