Error Handling and Debugging | MySQL and Perl for the Web

only for RuBoard - do not distribute or recompile

When you write programs, you ll encounter problems, and it s good to know how to get information that can help you pinpoint and resolve those problems. If you don t do something about error checking, your scripts will fail and you won t know why. This section discusses diagnostic and debugging techniques that will be useful to you as you develop MySQL scripts.

One technique that we ve already seen is to use the -w option on the #! line that begins your script:

 #! /usr/bin/perl -w

This puts Perl in warning mode, causing it to issue messages when it finds something about the script that doesn t look quite right. You may want to turn on -w while you re developing a script, and then disable it when you put the script into production use. This is particularly true if you ve eliminated as many warning messages as possible, but some persist. For example, some warnings may result from code within external modules that you re using, and you can t do anything about those. Other warnings are harmless, but annoying. If you fetch a row that contains columns that are NULL, the corresponding values in the row structure will be undef. Some operations, such as trying to print those values, result in Use of uninitialized value warnings. Removing the -w option silences the warnings. As a general rule, however, I recommend using it if at all possible.

To perform a syntax check on a script without actually executing it, use the following command. Perl will look through the script and issue warnings for errors or other things it doesn t like:

 % perl -wc script.pl

Another helpful debugging technique is one we ve already been using:

 use strict;

Turning on strict mode helps you find things such as misspellings of variable names that Perl otherwise would not warn you about. You ll find a use strict statement in the source for almost all the scripts written in this chapter and for every script in the following chapters.

DBI Error-Processing and Tracing Facilities

DBI provides its own mechanism for handling errors, which is controlled through PrintError and RaiseError, two attributes that you can specify when you connect to the MySQL server. If PrintError is enabled, DBI methods print messages using warn() when an error occurs, in addition to returning a status value indicating an error. If RaiseError is enabled, DBI methods print messages using die() when an error occurs. This means that when you enable RaiseError, you don t need to check the return value of DBI calls for an error if a call returns, you can assume that it succeeded. (RaiseError is kind of a blunt tool because scripts just squawk and die on an error when you turn it on. Nevertheless, it has the important virtue of preventing one of the most common problems in database programming neglecting to check for errors on a uniform basis.)

By default, DBI scripts run with PrintError enabled and RaiseError disabled. To change the value of either or both, enable or disable them when you invoke connect(). For example, you can reverse the default values by disabling PrintError and enabling RaiseError like so:

 $dbh = DBI->connect ($dsn, $user_name, $password,                      {PrintError => 0, RaiseError => 1});

If you leave RaiseError disabled, you should check the result of DBI method calls. If you also disable PrintError, as shown here, you may want to print your own error messages:

 $dbh = DBI->connect ($dsn, $user_name, $password, {PrintError => 0})      or die "Cannot connect to server: $DBI::errstr ($DBI::err)\n";

The example uses $DBI::errstr and $DBI::err, two DBI variables that contain the MySQL error message and numeric error code when an error occurs. (If a call succeeds, the variables contain the empty string and 0, respectively, to indicate no error. ) There is also a $DBI::state variable that contains a standard five-character SQL error state code, but it s generally not nearly as useful as $DBI::errstr and $DBI::err. Whichever error variables you want to use, you should retrieve their values immediately after an error occurs. If you invoke another DBI method in the meantime, the error variables will be reset to reflect the result of the more recent call.

Use of RaiseError and PrintError in This Book

Unless otherwise indicated, scripts in this book run with PrintError disabled and RaiseError enabled so that errors in DBI calls will cause scripts to terminate automatically with an error message.

Database and statement handles have their own PrintError and RaiseError attributes. You can use them to modify DBI s error-processing behavior on a handle-specific basis if you like. If you want to change the current database, but not die or print a message if the current MySQL user has no permission to use the database, for example, you could use a function such as the following one. It attempts to select the database, but is silent if an error occurs and indicates by its return value whether the attempt succeeded:

 sub change_database  { my ($dbh, $db_name) = @_;  my ($old_pe, $old_re);  my $result;      $old_pe = $dbh->{PrintError};   # save current attribute values      $old_re = $dbh->{RaiseError};      $dbh->{PrintError} = 0;         # disable both attributes      $dbh->{RaiseError} = 0;      $result = $dbh->do ("USE $db_name");      $dbh->{PrintError} = $old_pe;   # restore attribute values      $dbh->{RaiseError} = $old_re;      return (defined ($result));     # return true if USE succeeded  }

Another DBI debugging aid is its tracing facility. This can provide a lot of information about what your script is doing. DBI >trace( 1) turns on tracing with minimal verbosity (the maximum trace level is 9). DBI->trace(0) turns off tracing. You can trace individual handles, too. For example, $sth->trace(n ) sets the statement handle $sth to trace level n .

Monitoring Query Execution

A problem that occurs quite often during script development and debugging is that queries generated by the script are malformed. Unfortunately, when you construct queries programmatically, it s not always obvious from looking at the code what is actually being sent to the MySQL server, and that makes it difficult to determine how to fix the problem. You can do several things to obtain query-execution information.

If MySQL logging is turned on and you have access to the server s log file, you can run the following command in a Telnet window to monitor what queries the server is running. Simple inspection of the log file often is sufficient to see what s wrong with queries that are failing:

 % tail -f mysql_log_file

Not everybody has access to the server logs, however. Another way to find out what your queries look like is to print them before executing them:

 $stmt = qq{ ... your query here... };  warn "Query: $stmt\n";  $sth = $dbh->prepare ($stmt);

That technique isn t so useful if you re using placeholders, though; all you ll see is the string with the ? placeholder characters intact. You may find it more informative to turn on tracing. Then you ll see the actual query strings that are sent to the server after the data values have been substituted into the query. (You ll need a trace level of at least 2.)

Diagnostic messages normally go to the standard error output, which is your terminal for command-line scripts and the Apache error log for Web scripts. For lines in the error log, it can be difficult to determine which script produced them. You can alleviate this problem by using the CGI::Carp module to cause diagnostics to be prefixed with a time stamp and the name of the originating script. Include a statement such as this in your script after the use CGI line:

 use CGI::Carp;

You do have some latitude to modify where diagnostic output goes:

Trace output (for either command-line or Web scripts) can be sent to a named file by specifying a filename argument when you enable tracing. The following statement specifies that trace output should be written to /tmp/myscript.err :
```
 DBI->trace (1, "/tmp/myscript.err"); 
```
DBI opens trace files in append mode, so any previous contents of the file won t be lost. On the other hand, you need to be careful to remove the trace call from your script when you ve eliminated the problem you re trying to solve. If you forget, the trace file becomes larger each time your script runs. Also, if you trace a Web script to a file, be aware that if multiple clients request the script at the same time, the output from the different invocations will be intertwined, making it difficult to tell what s going on.
Web script diagnostics can be directed to your browser by turning on fatalsToBrowser :
```
 use CGI::Carp qw(fatalsToBrowser); 
```

Here s a short script, fatals.pl, that illustrates how fatalsToBrowser works. Invoke it from your browser to see what happens when it reaches the die() call:

 #! /usr/bin/perl -w  # fatals.pl - show effect of fatalsToBrowser  use strict;  use CGI qw(:standard);  use CGI::Carp qw(fatalsToBrowser);  print header (),      start_html ("fatalsToBrowser Demonstration");      h2("fatalsToBrowser Demonstration");  die "This is an error message\n";  print end_html ();  exit (0);

Enabling fatalsToBrowser is useful mostly during script development because it enables you to see error output immediately in your browser window without having to check the Apache error log. When a script goes into production use, it may be best to disable it. At that point, you want any error messages to go to the error log, not to remote clients who most likely would be confused by them.

only for RuBoard - do not distribute or recompile