Checking for Errors

2.3.1 Problem

Something went wrong with your program and you don't know what.

2.3.2 Solution

Everybody has problems getting programs to work correctly. But if you don't anticipate difficulties by checking for errors, you make the job a lot harder. Add some error-checking code so your programs can help you figure out what went wrong.

2.3.3 Discussion

You now know how to connect to the MySQL server. It's also a good idea to know how to check for errors and how to retrieve MySQL-related error information from the API, so that's what we'll cover next. When errors occur, MySQL provides a numeric error code and a corresponding descriptive text error message. The recipes in this section show how to access this information. You're probably anxious to see how to do more interesting things (such as issue queries and get back the results), but error checking is fundamentally important. Programs sometimes fail, especially during development, and if you don't know how to determine why failures occur, you'll be flying blind.

The example programs in this section show how to check for errors, but will in fact execute without any problems if your MySQL account is set up properly. Thus, you may have to modify the examples slightly to force errors to occur so that the error-handling statements are triggered. For example, you can change a connection-establishment call to supply a bad password. This will give you a feel for how the code acts when errors do occur.

A general debugging aid that is not specific to any API is to check the MySQL query log to see what queries the server actually is receiving. (This requires that you have query logging turned on and that you have access to the log on the MySQL server host.) The log often will show you that a query is malformed in a particular way and give you a clue about why your program is not constructing the proper query string. If you're running a script under a web server and it fails, check the server's error log.

2.3.4 Perl

The DBI module provides two attributes that control what happens when DBI method invocations fail:

• PrintError, if enabled, causes DBI to print an error message using warn( ).
• RaiseError, if enabled, causes DBI to print an error message using die( ); this terminates your script.

By default, PrintError is enabled and RaiseError is disabled, so a script continues executing after printing a message if errors occur. Either or both attributes can be specified in the connect( ) call. Setting an attribute to 1 or 0 enables or disables it, respectively. To specify either or both attributes, pass them in a hash reference as the fourth argument to the connect( ) call. (The syntax is demonstrated shortly.)

The following code uses the default settings for the error-handling attributes. This results in a warning message if the connect( ) call fails, but the script will continue executing:

my $dbh = DBI->connect ($dsn, "cbuser", "cbpass");

However, because you really can't do much if the connection attempt fails, it's often prudent to exit instead after DBI prints a message:

my $dbh = DBI->connect ($dsn, "cbuser", "cbpass") or exit;

To print your own error messages, leave RaiseError disabled and disable PrintError as well. Then test the results of DBI method calls yourself. When a method fails, the $DBI::err and $DBI::errstr variables will contain the MySQL numeric error code and descriptive error string, respectively:

my $dbh = DBI->connect ($dsn, "cbuser", "cbpass", {PrintError => 0})
 or die "Connection error: $DBI::errstr ($DBI::err)
";

If no error occurs, $DBI::err will be 0 or undef, and $DBI::errstr will be the empty string or undef.

When checking for errors, you should access these variables immediately after invoking the DBI method that sets them. If you invoke another method before using them, their values will be reset.

The default settings (PrintError enabled, RaiseError disabled) are not so useful if you're printing your own messages. In this case, DBI prints a message automatically, then your script prints its own message. This is at best redundant, and at worst confusing to the person using the script.

If you enable RaiseError, you can call DBI methods without checking for return values that indicate errors. If a method fails, DBI prints an error and terminates your script. If the method returns, you can assume it succeeded. This is the easiest approach for script writers: let DBI do all the error checking! However, if PrintError and RaiseError both are enabled, DBI may call warn( ) and die( ) in succession, resulting in error messages being printed twice. To avoid this problem, it's best to disable PrintError whenever you enable RaiseError. That's the approach generally used in this book, as illustrated here:

my $dbh = DBI->connect ($dsn, "cbuser", "cbpass",
 {PrintError => 0, RaiseError => 1});

If you don't want the all-or-nothing behavior of enabling RaiseError for automatic error checking versus having to do all your own checking, you can adopt a mixed approach. Individual handles have PrintError and RaiseError attributes that can be enabled or disabled selectively. For example, you can enable RaiseError globally by turning it on when you call connect( ), then disable it selectively on a per-handle basis. Suppose you have a script that reads the username and password from the command-line arguments, then loops while the user enters queries to be executed. In this case you'd probably want DBI to die and print the error message automatically if the connection fails (there's not much you can do if the user doesn't provide a valid name and password). After connecting, on the other hand, you wouldn't want the script to exit just because the user enters a syntactically invalid query. It would be better for the script to trap the error, print a message, then loop to get the next query. The following code shows how this can be done (the do( ) method used in the example executes a query and returns undef to indicate an error):

my $user_name = shift (@ARGV);
my $password = shift (@ARGV);
my $dbh = DBI->connect ($dsn, $user_name, $password,
 {PrintError => 0, RaiseError => 1});
$dbh->{RaiseError} = 0; # disable automatic termination on error
print "Enter queries to be executed, one per line; terminate with Control-D
";
while (<>) # read and execute queries
{
 $dbh->do ($_) or warn "Query failed: $DBI::errstr ($DBI::err)en";
}
$dbh->{RaiseError} = 1; # re-enable automatic termination on error

If RaiseError is enabled, you can trap errors without terminating your program by executing code within an eval block. If an error occurs within the block, eval fails and returns a message in the $@ variable. Typically, eval is used something like this:

eval
{
 # statements that might fail go here...
};
if ($@)
{
 print "An error occurred: $@
";
}

This technique is commonly used, for example, to implement transactions. (See Chapter 15.) Using RaiseError in combination with eval differs from using RaiseError alone in the following ways:

• Errors terminate only the eval block, not the entire script.
• Any error terminates the eval block, whereas RaiseError applies only to DBI-related errors.

When you use eval with RaiseError enabled, be sure to disable PrintError. Otherwise, in some versions of DBI, an error may simply cause warn( ) to be called without terminating the eval block as you expect.

In addition to using the error-handling attributes PrintError and RaiseError, you can get lots of useful information about your script's execution by turning on DBI's tracing mechanism. Invoke the trace( ) method with an argument indicating the trace level. Levels 1 to 9 enable tracing with increasingly more verbose output, and level 0 disables tracing:

DBI->trace (1); # enable tracing, minimal output
DBI->trace (3); # elevate trace level
DBI->trace (0); # disable tracing

Individual database and statement handles have trace( ) methods, too. That means you can localize tracing to a single handle if you want.

Trace output normally goes to your terminal (or, in the case of a web script, to the web server's error log). You can write trace output to a specific file by providing a second argument indicating a filename:

DBI->trace (1, "/tmp/trace.out");

If the trace file already exists, trace output is appended to the end; the file's contents are not cleared first. Beware of turning on a file trace while developing a script, then forgetting to disable the trace when you put the script into production. You'll eventually find to your chagrin that the trace file has become quite large. (Or worse, a filesystem will fill up and you'll have no idea why!)

2.3.5 PHP

In PHP, most functions that can succeed or fail indicate what happened by means of their return value. You can check that value and take action accordingly. Some functions also print a warning message when they fail. (mysql_connect( ) and mysql_select_db( ) both do this, for example.) Automatic printing of warnings can be useful sometimes, but if the purpose of your script is to produce a web page (which is likely), you may not want PHP to splatter these messages into the middle of the page. You can suppress such warnings two ways. First, to prevent an individual function call from producing an error message, put the @ warning-suppression operator in front of its name. Then test the return value and deal with errors yourself. That was the approach used for the previous section on connecting to the MySQL server, where connect.php printed its own messages:

if (!($conn_id = @mysql_connect ("localhost", "cbuser", "cbpass")))
 die ("Cannot connect to server
");
print ("Connected
");
if (!@mysql_select_db ("cookbook", $conn_id))
 die ("Cannot select database
");

Second, you can disable these warnings globally by using the error_reporting( ) function to set the PHP error level to zero:

error_reporting (0);

However, be aware that by turning off warnings this way, you won't get any notification for things that are wrong with your script that you really should know about, such as parse errors caused by malformed syntax.

To obtain specific error information about failed MySQL-related operations, use mysql_errno( ) and mysql_error( ), which return a numeric error code and descriptive error string. Each function takes an optional connection identifier argument. if you omit the identifier, both functions assume you want error information for the most recently opened connection. However, prior to PHP 4.0.6, both functions require that there is a connection. For older versions of PHP, this requirement makes the error functions useless for reporting problems with the connection-establishment routines. (If mysql_connect( ) or mysql_pconnect( ) fail, mysql_errno( ) and mysql_error( ) return 0 and the empty string, just as if no error had occurred.) To work around this, you can use the PHP global variable $php_errormsg instead, as shown in the following example. The code shows how to print error messages, both for failed connection attempts and for errors that occur subsequent to a successful connection. For problems connecting, it attempts to use mysql_errno( ) and mysql_error( ) if they return useful information. Otherwise, it falls back to using $php_errormsg:

if (!($conn_id = @mysql_connect ("localhost", "cbuser", "cbpass")))
{
 # If mysql_errno( )/mysql_error( ) work for failed connections, use
 # them (invoke with no argument). Otherwise, use $php_errormsg.
 if (mysql_errno ( ))
 {
 die (sprintf ("Cannot connect to server: %s (%d)
",
 htmlspecialchars (mysql_error ( )),
 mysql_errno ( )));
 }
 else
 {
 die ("Cannot connect to server: "
 . htmlspecialchars ($php_errormsg) . "
");
 }
}
print ("Connected
");
if (!@mysql_select_db ("cookbook", $conn_id))
{
 die (sprintf ("Cannot select database: %s (%d)
",
 htmlspecialchars (mysql_error ($conn_id)),
 mysql_errno ($conn_id)));
}

The htmlspecialchars( ) function escapes the <, >, and & characters so they display properly in web pages. It's useful here when displaying error messages because we don't know what particular characters a message contains.

Use of $php_errormsg requires the track_errors variable to be enabled in your PHP initialization file. On my system, that file is /usr/local/lib/php.ini. Locate the file on your system, then make sure the track_errors line looks like this:

track_errors = On;

If you change the track_errors setting and you're using PHP as an Apache module, you'll need to restart Apache to make the change take effect.

2.3.6 Python

Python programs signal errors by raising exceptions, and handle errors by catching exceptions in an except block. To obtain MySQL-specific error information, name an exception class and provide a variable to receive the information. Here's an example:

try:
 conn = MySQLdb.connect (db = "cookbook",
 host = "localhost",
 user = "cbuser",
 passwd = "cbpass")
 print "Connected"
except MySQLdb.Error, e:
 print "Cannot connect to server"
 print "Error code:", e.args[0]
 print "Error message:", e.args[1]
 sys.exit (1)

If an exception occurs, the first and second elements of e.args will be set to the numeric error code and descriptive error message, respectively. (Note that the Error class is accessed through the MySQLdb driver module name.)

2.3.7 Java

Java programs handle errors by catching exceptions. If you simply want to do the minimum amount of work, print a stack trace to inform the user where the problem lies:

catch (Exception e)
{
 e.printStackTrace ( );
}

The stack trace shows the location of the problem, but not necessarily what the problem is. It may not be all that meaningful except to you, the program's developer. To be more specific, you can print the error message and code associated with an exception:

• All Exception objects support the getMessage( ) method. JDBC methods may throw exceptions using SQLException objects; these are like Exception objects but also support getErrorCode( ) and getSQLState( ) methods.
• For MySQL errors, getErrorCode( ) and getMessage( ) return the numeric error code and descriptive error string.
• getSQLState( ) returns a string that provides error values defined according to the XOPEN SQL specification (which you may or may not find useful).
• You can also get information about non-fatal warnings, which some methods generate using SQLWarning objects. SQLWarning is a subclass of SQLException, but warnings are accumulated in a list rather than thrown immediately, so they don't interrupt your program and you can print them at your leisure.

The following example program, Error.java, demonstrates how to access error messages by printing all the error information it can get its hands on. It attempts to connect to the MySQL server and prints exception information if the attempt fails. Then it issues a query and prints exception and warning information if the query fails:

// Error.java - demonstrate MySQL error-handling

import java.sql.*;

public class Error
{
 public static void main (String[ ] args)
 {
 Connection conn = null;
 String url = "jdbc:mysql://localhost/cookbook";
 String userName = "cbuser";
 String password = "cbpass";

 try
 {
 Class.forName ("com.mysql.jdbc.Driver").newInstance ( );
 conn = DriverManager.getConnection (url, userName, password);
 System.out.println ("Connected");
 tryQuery (conn); // issue a query
 }
 catch (Exception e)
 {
 System.err.println ("Cannot connect to server");
 System.err.println (e);
 if (e instanceof SQLException) // JDBC-specific exception?
 {
 // print general message plus any database-specific message
 // (note how e is cast from Exception to SQLException to
 // access the SQLException-specific methods)
 System.err.println ("SQLException: " + e.getMessage ( ));
 System.err.println ("SQLState: "
 + ((SQLException) e).getSQLState ( ));
 System.err.println ("VendorCode: "
 + ((SQLException) e).getErrorCode ( ));
 }
 }
 finally
 {
 if (conn != null)
 {
 try
 {
 conn.close ( );
 System.out.println ("Disconnected");
 }
 catch (SQLException e)
 {
 // print general message plus any
 // database-specific message
 System.err.println ("SQLException: " + e.getMessage ( ));
 System.err.println ("SQLState: " + e.getSQLState ( ));
 System.err.println ("VendorCode: " + e.getErrorCode ( ));
 }
 }
 }
 }

 public static void tryQuery (Connection conn)
 {
 Statement s = null;

 try
 {
 // issue a simple query
 s = conn.createStatement ( );
 s.execute ("USE cookbook");
 s.close ( );

 // print any accumulated warnings
 SQLWarning w = conn.getWarnings ( );
 while (w != null)
 {
 System.err.println ("SQLWarning: " + w.getMessage ( ));
 System.err.println ("SQLState: " + w.getSQLState ( ));
 System.err.println ("VendorCode: " + w.getErrorCode ( ));
 w = w.getNextWarning ( );
 }
 }
 catch (SQLException e)
 {
 // print general message plus any database-specific message
 System.err.println ("SQLException: " + e.getMessage ( ));
 System.err.println ("SQLState: " + e.getSQLState ( ));
 System.err.println ("VendorCode: " + e.getErrorCode ( ));
 }
 }
}