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?