DBI error 4: statement contains no result

Some errors are really hard to find: They appear only sometimes or only on live systems or within complex source that can't run manually using a debugger. Adding debug output might help, but might also be confusing as the DBI error code 4 "statement contains no result" does.

I was hunting an error in such a script. It didn't do it's job but didn't show any error messages. I added some debug statements and got error 4 "statement contains no result" at many MySQL queries. Google didn't find anything helpful - it's no MySQL error and the DBI documentation doesn't tell anything about it.

I ended up with a small test script to reproduce the error:

#!/usr/bin/perl -l
use DBI;
print my $dbh = DBI->connect("DBI:mysql:host=localhost;database=test","username","password");
print my $sth = $dbh->prepare("INSERT IGNORE INTO foo VALUES(1)");
print "execute: ".$sth->execute();
print DBI->err." ".DBI->errstr;
print "mysql_insert_id: ".$sth->{mysql_insertid};
print DBI->err." ".DBI->errstr;
print "NAME: ".$sth->{NAME};
print DBI->err." ".DBI->errstr;
print "NUM_OF_FIELDS: ".$sth->{NUM_OF_FIELDS};
print DBI->err." ".DBI->errstr;

The script basically connects to my local test MySQL server and prepares a statement handle, printing both. It executes a sample query (which might fail or not, doesn't matter) and requests some additional information about the executed statement. Each step (starting from the execute, because I knew, that at least most of the querys reported as error 4 succeeded) also prints out the DBI error code and error message.

This is the output of the test script:
DBI::db=HASH(0x98c418)
DBI::st=HASH(0x98c0b8)
DBD::mysql::st execute failed: Table 'test.foo' doesn't exist at - line 4.
execute:
1146 Table 'test.foo' doesn't exist
mysql_insert_id: 0
1146 Table 'test.foo' doesn't exist
NAME:
4 statement contains no result
NUM_OF_FIELDS:
4 statement contains no result

The first two lines are the values of $dbh and $sth showing that both succeeded. The execute failed, printed the error message starting with "DBD::mysql::st execute failed" and returnd nothing. DBI->err returns error code 1146 after the execute and also after the ->{mysql_insertid}. The call to $sth->{NAME} should return an array-reference containing the names of the columns returned by the query, but it also mixes up the DBI->err and DBI->errstr values if no column-based reply has been returned by the query.

The project where I discovered this error is using a custom statement module which automatically copies ->{NAME}, ->{NUM_OF_FIELDS} and various other fields from the original DBI statement handle into it's own object and thus mixed up the DBI->err and also $sth->err values of every query.

The sample script above shows the same result, doesn't matter if DBI->err or $sth->err is being used.

The solution: Grab the ->err and ->errstr values right after the execute call and before anything else is done on the $sth.