bug with statement TYPE attribute?
am 16.03.2005 17:49:04 von Ray ZimmermanI 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/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org