MySQL Bugs: #62877: mysql client return code

Bug #62877	mysql client return code
Submitted:	22 Oct 2011 11:18	Modified:	16 Oct 2012 4:50
Reporter:	Dimitriy A	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	5.5.13, 5.5.17	OS:	Linux (SuSE 64-bit)
Assigned to:		CPU Architecture:	Any
Tags:	client, error code, return code

Description:
I would normally expect mysql client to return an error code other than 0 executing a SQL statement with an error.

How to repeat:
# Return code of 0 is provided when SQL Statement is executed without error:
> echo 'select 1' | mysql
> echo $?
0

# Return code of 0 is provided again, even though the SQL Statement Executed with an error:
> echo 'XYZ' | mysql
> echo $?
0

Suggested fix:
Return error code of non-zero value when a statement fails to execute.

What exact version, 5.5.x, do you use? I do not see this problem with 5.5.17 on Mac:

macbook-pro:5.5 openxs$ echo 'select 1' | bin/mysql -uroot test 
1
1
macbook-pro:5.5 openxs$ echo $?
0
macbook-pro:5.5 openxs$ echo 'XYZ' | bin/mysql -uroot test 
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'XYZ' at line 1
macbook-pro:5.5 openxs$ echo $?
1
macbook-pro:5.5 openxs$ bin/mysql --version
bin/mysql  Ver 14.14 Distrib 5.5.17, for osx10.5 (i386) using readline 5.1

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.13-log |
+------------+

shell> mysql --version
/path/mysql  Ver 14.14 Distrib 5.5.13, for linux2.6 (x86_64) using readline 5.1

Please, check with a newer version, 5.5.17.

Tried with newer version. Same behavior.

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.17-log |
+------------+

shell> mysql --version
/path/mysql  Ver 14.14 Distrib 5.5.17, for linux2.6 (x86_64) using readline 5.1

Tried eliminating options from mysql client and narrowed it down to "--force" option. Although "--force" means "Continue even if we get an SQL error.", I would still expect an error code of greater than 0, when one or more SQL statements fail.

Indeed, with --force it always returns 0 status code. I think this is by design. But your expectations are reasonable, so I'd say we have a valid feature request here.

Indeed, with --force it always returns 0 status code. I think this is by design. But your expectations are reasonable, so I'd say we have a valid feature request here.

After analysis, we don't think this feature request is reasonable. From Georgi Kodinov:

mysql is not executing a single statement. For instance, it is not clear what we should return if we have two statements piped into mysql and one fails and the other doesn't. 

As seen from the experiments it already returns non-zero if it can't connect.