help! where is this bug?!

[Ray Zimmerman <rz...@cornell.edu>]

I need some help with a hard-to-locate (for me) bug. After some major 
upgrades of OS, perl, apache, etc a mod_perl app that worked fine 
before began complaining that "You have an error in your SQL syntax". I 
found it was due to incorrect quoting based on incorrect mysql column 
types returned by DBI/DBD::mysql. Strangely, a stand-alone script 
returns the correct column types, but the same script executed by 
mod_perl gives incorrect types. I should mention that aside from this I 
haven't noticed any other problems with the mod_perl installation.

Here are the details ...

Environment:
	perl-5.8.6
	apache-1.3.33
	mod_perl-1.29
	Linux 2.6.9 (RHEL 4 WS) Intel
	DBI-1.48 (also tried 1.45, 1.47)
	DBD-mysql-2.9004
connecting to:
	mysql-4.0.16 (running on a different Intel Linux box)

----------------------
   the script
----------------------
use strict;
use DBI;

my $DBI_DSN     = 'DBI:mysql:database=MyDb;mysql_client_found_rows=0';
my $DBI_USER    = 'MyUser';
my $DBI_PASS    = 'MyPassword';
my $DBI_OPT     = { PrintError => 0, RaiseError => 1, AutoCommit => 1 };
my $dbh     = DBI->connect( $DBI_DSN, $DBI_USER, $DBI_PASS, $DBI_OPT );
my $sql     = 'SELECT * FROM C_Object WHERE 1 = 0';
my $sth     = $dbh->prepare( $sql );
my $rv      = $sth->execute;
my $fields  = $sth->{NAME};
my $types   = $sth->{TYPE};

foreach (0..$#{$fields}) {
     printf("%8s : %d\n", $fields->[$_], $types->[$_]);
}
----------------------


----------------------
   the table
----------------------
CREATE TABLE C_Object (
   id int(11) NOT NULL auto_increment,
   class char(255) default NULL,
   created timestamp(14) NOT NULL,
   PRIMARY KEY  (id)
) TYPE=MyISAM;
----------------------


----------------------
   the result
----------------------
If run the script from the command line, I get the correct types for 
each field ...
       id : 4
    class : 1
  created : 11

If run it via a PerlRequire in httpd.conf, I get incorrect field types 
...
       id : 11
    class : 3
  created : 12
----------------------

I verified that @INC is the same, perl, DBI and DBD::mysql versions 
being used are the same. I have two other boxes which I recently 
upgraded to the same versions of perl, apache, mod_perl and all Perl 
modules. Both return the correct types when run from the command line. 
When run from mod_perl as above, the first, an older Intel Linux box, 
gave me all columns as type 12 (also incorrect). The other machine, a 
Mac OS X laptop, gave the correct types under mod_perl.

So can anyone duplicate this? Have any ideas where to go from here to 
try to track it down?

Ray Zimmerman
Director, Laboratory for Experimental Economics and Decision Research
428-B Phillips Hall, Cornell University, Ithaca, NY 14853
phone:  (607) 255-9645