bug with statement TYPE attribute?

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};
my $old_types   = $sth->{mysql_type};

print "\ncol_name   col_type   old_type\n";
print "--------   --------   --------\n";
foreach (0..$#{$fields}) {
     printf("%8s   %5d      %5d\n", $fields->[$_], $types->[$_], 
$old_types->[$_]);
}
print "\n";
----------------------


----------------------
   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 results
----------------------
If I run the script from the command line, I get the correct types for 
each field ...

col_name   col_type   old_type
--------   --------   --------
       id       4          3
    class       1        254
  created      11          7


If I run it via a PerlRequire in httpd.conf, I get incorrect field 
types ...

col_name   col_type   old_type
--------   --------   --------
       id      11          7
    class       3          0
  created      12        115

----------------------

I verified that @INC is the same, perl, DBI and DBD::mysql versions 
being used are the same. I have two other machines which I recently 
upgraded to the same versions of perl, apache, mod_perl and all Perl 
modules. On my Mac OS X 10.3.8 machine things work fine, including 
under mod_perl. On my older Linux box (Linux 2.2.22, RH 6.2), which is 
the one running the mysql server, I get the correct types when run from 
the command line, but when run from mod_perl as above, I get ...

col_name   col_type   old_type
--------   --------   --------
       id      12      138876576
    class      12      138876600
  created      12      1952671082

All results are repeatable for me. 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


-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]