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