Bug #93710 for DBD-mysql: Error in DBD:mysql retrieving FLOATs when the flag 'mysql_server_prepare=1' is set

I have encountered what I believe to be an error in DBD::mysql. This error only seems to occur when the flag 'mysql_server_prepare=1' is set. If this is true, then FLOATs returned from the database will have slightly different values than when the flag is not set. I suppose it would be fairly straightforward to just round off the values of all FLOATs, but since this rather unexpected bug arises from a change which would seem to have no relevance, it is a bit disconcerting. It would be nice to know if this bug is on the roadmap to be fixed any time soon. Thanks again for any help or insight that you can provide. Doug Hershberger Below I give instructions for reproducing this error. All commands and output where generated on computers running Red Hat Enterprise Linux r6.4. This was tested on 64 bit physical boxes as well as computers running as 64 bit virtual machines hosted on a Hyper-V server. This was tested with DBD::mysql v4.013, v4.020 and v4.026; DBI c1.609 and v1.631; mySQL v5.5.17-log and v5.1.71 although not all combinations of those were tried. #Setting up the database for the test case mysql -uroot -p CREATE USER 'chester'@'localhost' IDENTIFIED BY 'myTestPW'; GRANT ALL ON test.* TO 'chester'@'localhost'; use test; CREATE TABLE preparedTest (name VARCHAR(20), floatVar FLOAT, doubVar DOUBLE); INSERT INTO preparedTest (name, floatVar, doubVar) VALUES ("example", .96, .96); Show quoted text
#Output which shows the table structure +---------+----------+---------+ | name | floatVar | doubVar | +---------+----------+---------+ | example | 0.96 | 0.96 | +---------+----------+---------+ #A dump of this data is attached and provided below so that you can recreate the database #Put the dump into test-file.sql #load the database into mySQL mysql test -uchester -pmyTestPW < test-file.sql #Perl code to test this error is attached and provided below. #put that code in the file preparedTest.pl perl preparedTest.pl #returns the following results 1..6 DBI::VERSION: 1.609 DBD::mysql::VERSION: 4.026 Running tests with no prepared statements. Name:example ok 1 - Got the right row with no prepared statements. 0.96 ok 2 - Float values work with no prepared statements. 0.96 ok 3 - Double values work with no prepared statements. Running tests with prepared statements. Name:example ok 4 - Got the right row with prepared statements. 0.959999978542328 not ok 5 - Float values work with prepared statements. # Failed test 'Float values work with prepared statements.' # at ./preparedTest.pl line 31. 0.96 ok 6 - Double values work with prepared statements. # Looks like you failed 1 test of 6. ########### #Here is a dump of the data you can put this in a file called test-file.sql: ########### use test; -- MySQL dump 10.13 Distrib 5.1.73, for redhat-linux-gnu (x86_64) -- -- Host: localhost Database: test -- ------------------------------------------------------ -- Server version 5.1.73 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `preparedTest` -- DROP TABLE IF EXISTS `preparedTest`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `preparedTest` ( `name` varchar(20) DEFAULT NULL, `floatVar` float DEFAULT NULL, `doubVar` double DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `preparedTest` -- LOCK TABLES `preparedTest` WRITE; /*!40000 ALTER TABLE `preparedTest` DISABLE KEYS */; INSERT INTO `preparedTest` VALUES ('example',0.96,0.96); /*!40000 ALTER TABLE `preparedTest` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; ########### #END of SQL data dump test-file.sql ########### ########### #Enter this code into preparedTest.pl ########### #!/usr/bin/perl use strict; use DBI; # load DBI database interaction routines print "DBI::VERSION: $DBI::VERSION\n"; use DBD::mysql; use Test::Simple tests => 6; print "DBD::mysql::VERSION: " . $DBD::mysql::VERSION . "\n\n"; #First without the prepare statement my $dbh=DBI->connect('DBI:mysql:database=test;host=localhost', 'chester', 'myTestPW', {'RaiseError' => 1}) or die "ConnectionEror: $DBI::errstr\n"; testIt($dbh, "no prepared statements"); #Now with the prepare statement $dbh=DBI->connect('DBI:mysql:database=test;host=localhost;mysql_server_prepare=1', 'chester', 'myTestPW', {'RaiseError' => 1}) or die "ConnectionEror: $DBI::errstr\n"; testIt($dbh, "prepared statements"); sub testIt { my $dbh = shift; my $condition = shift; my $sth = $dbh->prepare("SELECT * FROM preparedTest;"); $sth->execute or die "SQL Error: $DBI::errstr\n"; my @rows = $sth->fetchrow_array(); my $name = $rows[0]; print "Running tests with " . $condition . ".\n"; print "Name:"; print $name . "\n"; ok($name eq "example", "Got the right row with " . $condition . "."); my $floater = $rows[1]; print $floater . "\n"; #I'm a bit surprised this comparison works for any case because I thought that you # can't get correct results using == with FLOATs due to internal limitations. But I # guess this is just another thing that Perl automagically does for you ok($floater == .96, "Float values work with " . $condition . "."); my $double = $rows[2]; print $double . "\n"; ok($double == .96, "Double values work with " . $condition . "."); print "\n\n"; $sth->finish(); $dbh->disconnect(); } ########### #END code for preparedTest.pl ########### Thanks -- Doug Hershberger Associate Director, Bioinformatics [cid:663C72F1-22F0-47E4-825A-1E4FB7E35B7C] 16121 Lee Road Ft. Myers, FL 33912 P 239.284.1115 C 650.898.7819 F 239.284.1199 Skype doug.hershberger.algenol