MySQL Bugs: #35584: Returned error value for mysql_query() not matching #define's in mysql/errmsg.h

Bug #35584	Returned error value for mysql_query() not matching #define's in mysql/errmsg.h
Submitted:	26 Mar 2008 19:14	Modified:	2 Apr 2008 23:36
Reporter:	Michael Darling	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	mysql-devel - 5.0.22-2.2.el5_1.1.x86_64	OS:	Any (CentOS v5.1)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
I believe mysql_query() in the C API is returning an improper value.

Please forgive me if I am mistaken somehow.  I am NOT looking for support for my program.  I found this issue (that I consider a bug), and changed my program so it functions properly with the unexpected return value.

After upgrading servers, several of my programs started receiving a value of "1" from mysql_query().

As mysql_query is defined in the documentation, there are five possible return codes: <success> (0), CR_COMMANDS_OUT_OF_SYNC (2014), CR_SERVER_GONE_ERROR (2006), CR_SERVER_LOST (2013), CR_UNKNOWN_ERROR (2005).  (The numbers after each return code are #define's in mysql/errmsg.h.

The return value "1" is not documented in the C API documentation, or #define'ed in mysql/errmsg.h.

I upgraded from an old server running MySQL 3.25.58 server/client, to a new server running MySQL 5.0.22 server/client.

I understand that upgrading from MySQL 4.1 to 5.0 changes the default of the mysql structure's reconnect flag from 0 to 1 -- to avoid dangerous situations.  MySQL making the default 0 makes sense to me.

Setting the reconnect flag to 1 after calling mysql_real_connect() made my programs start working properly again.

Obviously, I have an issue somewhere that is causing a program that completes entirely in less than 0.03 seconds to break it's database connection.  That's a separate issue for me to look into independently.

The only reason I'm writing this bug is that it seems that mysql_query() is returning an undocumented value.

How to repeat:
I hope that mysql_query() is returning hard coded integers, or using #defines other than in mysql/errmsg.h, so that anyone who breaks their database connection can repeat this.  I am not sure, however.

Suggested fix:
Returning properly documented values.

(Just fixing my typo in the version field, of ^4 when I meant to type 64)

Many thanks for writing a bug report.

Please let me explain why this is not a bug in my next section. I agree with you that our documentation isn't very clear here. I set this to verified as feature request for our documentation team that they can change the documentation at this section into a better understandable documentation.

You misunderstood the documentation:

The function: mysql_query() just returns '0' for success and something else for 'error'. Often this could be '1'.
But the '1' is not the error code number.
You only get the error code number when you call mysql_error().

Susanne, thank you for your explanation and verifying this as a feature request.

You are right.  Now that I look at the documentation again, both the original programmer and I believed that the return value would either be zero or one of the listed errors.

Because of our incorrect belief, our programs were written to abort when mysql_query() returned CR_COMMANDS_OUT_OF_SYNC or CR_UNKNOWN_ERROR, and call mysql_ping() and run the query again (up to three times) if it returned CR_SERVER_GONE_ERROR or CR_SERVER_LOST.

Now that I know what the functions actually return in error situations, I can properly modify our programs to work with how the API is currently implemented.

I think that modifying the documentation would make the situation much clearer.

However, I think that if this goes beyond modifying documentation, and that mysql_query(), and possibly other functions that act the same way, are changed to return the error codes instead of simply a non-zero return, programs would be able to test against a well defined set of error codes, and use integers rather than string comparisons in their tests.

The behavior noted in this report is true for C API functions in general, not just mysql_query(),
so no change is needed for mysql_query().  The behavior is described in the overview section that indicates how the API is to be used:

http://dev.mysql.com/doc/refman/5.0/en/c-api-function-overview.html

Which says:

"For detecting and reporting errors, MySQL provides access to error information by means of the mysql_errno() and mysql_error() functions. These return the error code or error message for the most recently invoked function that can succeed or fail, allowing you to determine when an error occurred and what it was."

And more detail is given here:

http://dev.mysql.com/doc/refman/5.0/en/mysql-errno.html

So, I believe no change is needed.