Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

Same DBI version gives different output on different machines

by samarzone (Pilgrim)
on Jan 25, 2010 at 11:19 UTC ( #819455=perlquestion: print w/ replies, xml ) Need Help??
samarzone has asked for the wisdom of the Perl Monks concerning the following question:

Problem: Same version of DBI returns different values on different servers

Description: I have a table named csresult with negative value stored in count column. When I try to fetch the value I get different results on different machines. On one machine it returns correct values, i.e. -1 but on another machine it returns the value 4294967295 (which I suppose is the number after conversing it into an unsigned value.) Similarly I get 4294967294 for -2. DBI version is same on both the machines however perl and linux versions differ. I got no help/hint on googling.

Can someone please explain this behaviour?

Different outputs are following

Mysql values/configuration (Output was same when I connected to the mysql server from both the machines) :-

<blockquote> mysql> select count from csresult where sig = '024b1680d57f61df3b3236f +0c9321ce7'; +-------+ | count | +-------+ | -1 | +-------+ 1 row in set (0.00 sec) mysql> show create table csresult\G *************************** 1. row *************************** Table: csresult Create Table: CREATE TABLE `csresult` ( `sig` varchar(32) NOT NULL default '', `count` int(10) default NULL, `results` mediumblob, `extrainfo` text, PRIMARY KEY (`sig`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.01 sec) mysql> show indexes from csresult\G *************************** 1. row *************************** Table: csresult Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: sig Collation: A Cardinality: 204 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: 1 row in set (0.00 sec) mysql> select version(); +------------+ | version() | +------------+ | 4.1.16-log | +------------+ 1 row in set (0.00 sec) </blockquote>

Contents of perl script(dbi-test.pl):-

#!/usr/bin/perl -w use strict; use DBI; my $dbh = DBI->connect("dbi:mysql:db:dbserver", "user", "password") or + die "could not connect\n"; my $sth = $dbh->prepare("Select count from csresult where sig = '024b1 +680d57f61df3b3236f0c9321ce7'"); $sth->execute(); while(my $data = $sth->fetchrow_hashref()) { print "count:$data->{count}\n"; }

Output on first machine:-

<blockquote>&#91;samar@malena perl&#93;$ uname -a Linux malena.XXX 2.6.18-128.el5 #1 SMP Wed Jan 21 10:41:14 EST 2009 x8 +6_64 x86_64 x86_64 GNU/Linux &#91;samar@malena perl&#93;$ perl -version This is perl, v5.8.8 built for x86_64-linux-thread-multi Copyright 1987-2006, Larry Wall Perl may be copied only under the terms of either the Artistic License + or the GNU General Public License, which may be found in the Perl 5 source ki +t. Complete documentation for Perl, including FAQ lists, should be found +on this system using "man perl" or "perldoc perl". If you have access to + the Internet, point your browser at http://www.perl.org/, the Perl Home Pa +ge. &#91;samar@malena perl&#93;$ perl -MDBI -e 'print "$DBI::VERSION\n"' 1.57 &#91;samar@malena perl&#93;$ perl dbi-test.pl count:-1 </blockquote>

Output on second machine:-

<blockquote>&#91;samar@maria perl&#93;$ uname -a Linux maria.XXX 2.6.9-22.0.1.XXX #1 SMP Mon Dec 5 17:33:30 IST 2005 x8 +6_64 x86_64 x86_64 GNU/Linux &#91;samar@maria perl&#93;$ perl -version This is perl, v5.8.3 built for x86_64-linux-thread-multi Copyright 1987-2003, Larry Wall Perl may be copied only under the terms of either the Artistic License + or the GNU General Public License, which may be found in the Perl 5 source ki +t. Complete documentation for Perl, including FAQ lists, should be found +on this system using `man perl' or `perldoc perl'. If you have access to + the Internet, point your browser at http://www.perl.com/, the Perl Home Pa +ge. &#91;samar@maria perl&#93;$ perl -MDBI -e 'print "$DBI::VERSION\n"' 1.57 &#91;samar@maria perl&#93;$ perl dbi-test.pl count:4294967295 </blockquote>

Thanks in advance

Comment on Same DBI version gives different output on different machines
Select or Download Code
Re: Same DBI version gives different output on different machines
by Corion (Pope) on Jan 25, 2010 at 11:34 UTC

    I would blame Perl 5.8.3, as some later Perls had fixes for sign-extensions, as the 64-bit age wasn't that present when 5.8.3 was current.

Re: Same DBI version gives different output on different machines
by moritz (Cardinal) on Jan 25, 2010 at 11:34 UTC
    Probably more important than the DBI version is the version of the DBD::mysql module, which handles the data exchange with mysql.

    Also notice that count is a keyword in SQL (as in SELECT COUNT(*) FROM ..), so you're on the safe side when you quote the column name:

    Select `count` from ...
    Perl 6 - links to (nearly) everything that is Perl 6.
      Thanks for your hint (DBD::mysql). It seems I am closer to the reason (but more confused).

      Machine-1 (with correct output) had DBD::mysql version 4.005 and machine-2 (with incorrect output) had DBD::version 3.0001_x.

      There is an entry in change-log of DBD::mysql 4.001 which says -

      Fix handling of negative integers bound to a column marked as SQL_INTEGER.
      [rt.cpan.org #18976], patch from Mike Schilli.

      We planned to install 4.00 (to check incorrect value) and then 4.001 (to check correct value) to make sure that the mentioned change is the one we are looking for. But we faced some strange thing. The script gave the desired output even with DBD::mysql version 4.00. We uninstalled it and installed 3.0005 and it also gave the desired output. Finally we installed 3.0000 but everything was working fine now. We manually removed DBD/mysql.pm and the mysql.so files installed with the module and reinstalled the 3.0000 version but unlike previously now we are getting the desired output. Although this solves the problem on our development servers I am keen to find out the reason.

Re: Same DBI version gives different output on different machines
by Krambambuli (Deacon) on Jan 25, 2010 at 11:39 UTC
    I'd suspect that the two machines do have different architectures, the one with the incorrect results being on 64 bits.

    That won't help too much - but maybe you can try further steps then to track the problem down, maybe to an library that isn't the right one for the given architecture.

    Update: Sorry, I haven't noticed the uname -a in your original post, showing that the two machines are both x86-64. Still, it has to be something about the 64 bits libraries that differ on one machine.


    Krambambuli
    ---

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://819455]
Approved by jettero
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (5)
As of 2014-07-25 00:32 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (167 votes), past polls