Chapter 6. Error Handling

Detecting Errors

Most of the MySQL functions will return some indication that an error has occurred. For instance, mysql_query will fail and return nonzero if it can not contact the database, the query is in error, or there is a permissions error. mysql_store_result will fail and return NULL if it can not allocate any memory. Each function's error determination is listed in more detail in Appendix A.

Extended Error Information

There are two things you can determine about an error. You can retrieve a human-readable string which explains what the error is. You can also retrieve the error number. To retrieve the error string, use char *mysql_error(MYSQL *mysql) which will return the last error that occurred. Use unsigned int mysql_error(MYSQL *mysql) to retrieve the last error number.

The error messages will be returned in the language determined when MySQL was compiled. Check the installation documentation for more information on how to change the error language.

You may see the list of error codes in errmsg.h in the MySQL include directory. These are the errors that the MySQL client code may produce. The server may return an error code about a query. In this case, the error code is defined in a file called derror.h in the /sql directory of the MySQL source distribution. Unfortunately, this header was not included with the client include header, so you will have to manually copy the file to where you need it or hard-code the error numbers.

A good example of an error message that can be returned by the server is ER_DUP_ENTRY (1062). You will get this error if you try to insert a row that has a duplicate key entry based on a PRIMARY index or a UNIQUE index.

Reconnecting Example

Here is an example of how to effectively use error numbers. This function is a wrapper around the standard mysql_real_query function that will reconnect to the server if the connection was lost.

MYSQL mysql;
//set these externally
char * user;
char * password;
char * db;

void report_error()
{
char * error_message;

error_message = mysql_error(&mysql);
if(error_message && *error_message) {
fprintf(stderr,"db error:
   [%u] %s",mysql_errno(&db_handle),error_message);
}
}

int connect()
{
if(!(mysql_connect(&mysql,NULL,user,password))) {
report_error();
return -1;
}

if(mysql_select_db(&mysql,db)) {
report_error();
return -1;
}
return 0;
}

int safe_mysql_real_query(const
   char *query,unsigned int query_length)
{
int rc;
int count;

rc = mysql_real_query(&mysql,query,query_length);
if(rc) {
//error
switch(mysql_errno(&db_handle)) {
case CR_SERVER_GONE_ERROR:
case CR_SERVER_LOST:
//try to reconnect a few times
count = 0;
do {
rc = connect();
if(rc) {
if(count>5) {
//give up
break;
}
sleep(1);
}
count++;
} while(rc);
if(!rc) {
//now that we are reconnected, try the query again
rc = mysql_real_query(&mysql,query,query_length);
//don't reconnect again if this fails.  could cause infinite loop
}
return rc;
default:
//unknown failure
return rc;
}  //end switch
}  //end if rc
return 0;
}

Previous | Table of Contents | Next