Troubleshooting Perl DBI

Objective

To diagnose problems arising from use of the Perl DBI module

Symptoms

Most of the major problems that can occur when using the Perl DBI module cause a meaningful error message to be generated. Typically this is written to STDERR (but see below for how this could have been overridden). Scripts which run as daemons may redirect STDERR to a logfile, to syslog, or (less helpfully) to /dev/null. Similar considerations apply to scripts that are called by daemons (such as CGI scripts).

Investigation

Strategy

There are five possibilities to consider:

1. the DBI module is not being called in the manner that you intended, or
2. the DBI module is not delivering the intended SQL statements to the database, or
3. the SQL statements are not having the desired effect when they are executed by the database, or
4. the DBI module is not correctly returning the result set to the client program, or
5. the client program is misinterpreting the result set.

If an error message is generated then it is likely that both the point of failure and the cause of failure will be obvious. Otherwise, it will be necessary to trace the query through its various stages of processing to determine where it deviates from what is expected.

Ensure that error messages are visible

The DBI module can report errors in several different ways:

• If $dbh is the connection handle then $dbh->err will be true if the most recent substantive operation failed, otherwise it will be undefined or false. If $dbh->err is true then $dbh->errstr should contain a human-readable error message.
• If the PrintError option is set (as it is by default) then errors are additionally reported using the Perl warn function.
• If the RaiseError option is set then errors additionally cause an exception to be thrown.

The first method relies on the Perl script explicitly checking $dbh->err after each operation. In a large program there is an obvious risk of errors being missed if you rely on this method.

Warnings are printed to STDERR unless you have overridden this behaviour by installing a warning handler in $SIG{__WARN__}. Exceptions are printed to STDERR and cause the program to terminate, unless they are caught by eval.

In normal operation a good combination of settings is to enable RaiseError (to ensure that no errors are missed) but disable PrintError (to prevent errors from being reported twice). However for troubleshooting purposes you may wish to enable both RaiseError and PrintError to gain assurance that no errors are being silently swallowed by eval.

Another useful option is PrintWarn. This governs the behaviour of DBI warnings (as opposed to errors), causing them to be reported using the Perl warn function. It is enabled automatically if $^W is set (for example by use warnings).

If STDERR has been redirected then you can find out where to by looking in /proc (see these instructions).

View queries as they reach the database server

There are a number of reasons why the SQL statement delivered to the database could differ from what was intended. Two of the more likely causes are:

• binding the wrong value to a placeholder, or
• incorrectly assembling an SQL statement from fragments.

For most types of database you can check whether anything of this nature has happened by instructing the database server to log all queries as they are received. To do this for PostgreSQL or MySQL see:

• Log all queries to a PostgreSQL server
• Log all queries to a MySQL server

Note that if placeholder substitution is performed by the database server (as opposed to the DBD backend) then it may not have occurred at the point where the queries are logged.

Test queries using a command-line database client

It is advisable to check at an early stage that the SQL statement you are attempting to execute has the intended behaviour. Most database systems provide some form of command-line client which can be used for this purpose. Where possible you should connect using the same account on the database server as used by the Perl script.

For example, to connect to a PostgreSQL database called finance on the server db.example.com as the user user you could use the command:

psql -d finance -h db.example.com -U user

You should then be prompted for the password. Similarly, for a MySQL database:

mysql -D finance -h db.example.com -u user -p

Note the option -p. Without this you will not be given the opportunity to enter a password, and therefore the connection attempt will probably fail.

SQLite does not have a database server but it does have a client. Supposing that the database pathname is /var/lib/finance/finance.db a suitable command for SQLite version 2 would be:

sqlite /var/lib/finance/finance.db

and similarly for SQLite version 3:

sqlite3 /var/lib/finance/finance.db

Note that all of the above database clients require SQL statements to be terminated by a semicolon (unlike the DBI module).

Dumping the data structure returned by the DBI module

There are many ways in which the DBI module can return data. Possible formats include:

• an array (selectrow_array),
• a reference to an array (selectrow_arrayref),
• a reference to a hash (selectrow_hashref),
• a reference to an array to a reference to an array (selectall_arrayref), or
• a reference to a hash to a reference to a hash (selectall_hashref).

If these structures are not interpreted correctly by the client program then the most likely outcome is a result of undef with little indication of where the error occurred. It may therefore be helpful to inspect the structure before any processing takes place. You can do this using the module Data::Dump following the instructions given here.

For example, the following code fragment dumps the result of a call to selectall_arrayref:

use DBI;
use Data::Dump qw(dump);

my $dbh = DBI->connect('dbi:Pg:dbname=finance;host=db.example.com','user','xyzzy',{AutoCommit=>1,RaiseError=>1,PrintError=>0});
my $result = $dbh->selectall_arrayref('SELECT name,salary FROM employees');
dump $result;

Tracing the DBI module

If you suspect a problem within the DBI module then this can be investigated using the trace function:

$dbh->trace(5);

The argument is a trace level in the range 1 to 15 which specifies the amount of detail required. Note that tracing the DBI module is not an effective method for debugging SQL statements and should rarely be necessary.

• Conventions
• Privacy
• Legal