Skip Menu |
 

This queue is for tickets about the DBD-mysql CPAN distribution.

Report information
The Basics
Id: 80394
Status: resolved
Priority: 0/
Queue: DBD-mysql

People
Owner: MICHIELB [...] cpan.org
Requestors: arnold.obdeijn [...] gmail.com
thierryv [...] mac.com
Cc: pali [...] cpan.org
AdminCc:

Bug Information
Severity: (no value)
Broken in: 4.022
Fixed in: 4.040



Download (untitled) / with headers
text/plain 458b
When server side prepared statements are enabled, negative integer values are returned as large positive integers: -1 becomes 4294967293 -2 becomes 4294967294 -3 becomes 4294967295 etc.. It seemed like this was an issue before 4.001. In the release notes of 4.001 it states: "Fix handling of signed integer values when using server-side prepared statements (they were being forced to unsigned values)." Either the problem was never solved, or it is back.
Subject: Negative integer turns positive with mysql_server_prepare
Download (untitled) / with headers
text/plain 6.5k
Dear all, To my greatest astonishment, I run into this case where a "-8" signed SMALLINT came back as "2^32 - 8" if mysql_server_prepare is turned on. check the SELECT yCoord AS 'Y' output in the three examples below. The last one is where the problem shows up. I hope this can help you. Best regards, Thiery I did compile Perl myself and all the CPAN modules. DBD::mysql -- 4.022 DBI -- 1.622 $ perl -v This is perl 5, version 14, subversion 2 (v5.14.2) built for x86_64-linux-thread-multi $ perl -V:config_args config_args='-des -Dprefix=/opt/perl -Acflags=-fPID -DPIC -m64 -Aldflags=-L/usr/lib64 -L/lib64 -lresolv -lnsl -ldl -lm -lcrypt -lutil -lc -Duse64bitint -Duseshrplib -Dusemultiplicity -Duselargefiles -Dusethreads - Duseithreads -Dmyhostname=localhost -Dperladmin=root@localhost'; Show quoted text
mysql> SELECT c.xCoord AS 'X', c.yCoord AS 'Y', c.dieId, a.headNum, a.siteNum, a.numInfo, a.hardBin, a.softBin, b.docType, UNIX_TIMESTAMP(b.startT) AS startT,
-> IF(d.keyId IS NULL,0, d.keyId) AS 'keyId', -> IF(d.keyData IS NULL,'null', d.keyData) AS 'CPSN', -> IF(e.keyStatus IS NULL,'', e.keyStatus) AS 'State', -> IF(a.keyId IS NULL,'None', IF(a.partFlagB3 = 0,'Pass',IF(a.realPRR = true,'Fail','Fake'))) AS 'Status' -> FROM (DieInfo AS a, STDFInfo AS b, Die AS c, LotWaf AS l) -> LEFT JOIN MACKey AS d -> ON a.keyId = d.keyId -> LEFT JOIN MACKeyHist AS e -> ON (a.keyId = e.keyId AND e.dieInfoId = a.dieInfoId) -> WHERE a.STDFId = b.STDFId -> AND b.lwId = l.lwId -> AND a.dieId = c.dieId -> AND l.lotId = 'ABCDEFG' -> AND l.subLotId = '21'; +-----+-----+-------+---------+---------+---------+---------+---------+ | X | Y | dieId | headNum | siteNum | numInfo | hardBin | softBin | +-----+-----+-------+---------+---------+---------+---------+---------+ | 69 | -9 | 1 | 1 | 27 | 1 | 6 | 138 | | 69 | -8 | 2 | 1 | 28 | 1 | 1 | 1 | | 68 | -8 | 3 | 1 | 29 | 1 | 1 | 1 | | 67 | -8 | 4 | 1 | 30 | 1 | 1 | 1 | | 73 | -10 | 5 | 1 | 20 | 1 | 1 | 1 | | 72 | -10 | 6 | 1 | 21 | 1 | 1 | 1 | | 70 | -9 | 7 | 1 | 24 | 1 | 1 | 1 | | 71 | -9 | 8 | 1 | 25 | 1 | 1 | 1 | | 72 | -9 | 9 | 1 | 26 | 1 | 1 | 1 | | 73 | -9 | 10 | 1 | 27 | 1 | 1 | 1 | | 73 | -8 | 11 | 1 | 28 | 1 | 1 | 1 | | 72 | -8 | 12 | 1 | 29 | 1 | 1 | 1 | ... With mysql_server_prepare disabled, my application's trace reports: [26611]: Found a row with: [69,-9] as <STDF> s<27> h<1> n<1> <1340349441> <> <None> [26611]: Found a row with: [69,-8] as <STDF> s<28> h<1> n<1> <1340349441> <ASSIGNED> <Pass> [26611]: Found a row with: [68,-8] as <STDF> s<29> h<1> n<1> <1340349441> <ASSIGNED> <Pass> [26611]: Found a row with: [67,-8] as <STDF> s<30> h<1> n<1> <1340349441> <ASSIGNED> <Pass> [26611]: Found a row with: [73,-10] as <STDF> s<20> h<1> n<1> <1340349441> <ASSIGNED> <Pass> [26611]: Found a row with: [72,-10] as <STDF> s<21> h<1> n<1> <1340349441> <ASSIGNED> <Pass> [26611]: Found a row with: [70,-9] as <STDF> s<24> h<1> n<1> <1340349441> <ASSIGNED> <Pass> [26611]: Found a row with: [71,-9] as <STDF> s<25> h<1> n<1> <1340349441> <ASSIGNED> <Pass> ... With mysql_server_prepare ENABLED, my application's trace reports "4294967287" instead of "-8": [26477]: Found a row with: [69,4294967287] as <STDF> s<27> h<1> n<1> <1340349441> <> <None> [26477]: Found a row with: [69,4294967288] as <STDF> s<28> h<1> n<1> <1340349441> <ASSIGNED> <Pass> [26477]: Found a row with: [68,4294967288] as <STDF> s<29> h<1> n<1> <1340349441> <ASSIGNED> <Pass> [26477]: Found a row with: [67,4294967288] as <STDF> s<30> h<1> n<1> <1340349441> <ASSIGNED> <Pass> [26477]: Found a row with: [73,4294967286] as <STDF> s<20> h<1> n<1> <1340349441> <ASSIGNED> <Pass> [26477]: Found a row with: [72,4294967286] as <STDF> s<21> h<1> n<1> <1340349441> <ASSIGNED> <Pass> [26477]: Found a row with: [70,4294967287] as <STDF> s<24> h<1> n<1> <1340349441> <ASSIGNED> <Pass> [26477]: Found a row with: [71,4294967287] as <STDF> s<25> h<1> n<1> <1340349441> <ASSIGNED> <Pass> [26477]: Found a row with: [72,4294967287] as <STDF> s<26> h<1> n<1> <1340349441> <ASSIGNED> <Pass> [26477]: Found a row with: [73,4294967287] as <STDF> s<27> h<1> n<1> <1340349441> <ASSIGNED> <Pass> [26477]: Found a row with: [73,4294967288] as <STDF> s<28> h<1> n<1> <1340349441> <ASSIGNED> <Pass> ... CREATE TABLE `Die` ( `dieId` INT unsigned NOT NULL AUTO_INCREMENT, `dieTypeId` SMALLINT unsigned NOT NULL, `xCoord` SMALLINT signed NOT NULL, `yCoord` SMALLINT signed NOT NULL, ... my $stm = qq { SELECT c.xCoord AS 'X', c.yCoord AS 'Y', c.dieId, a.headNum, a.siteNum, a.numInfo, a.hardBin, a.softBin, b.docType, UNIX_TIMESTAMP(b.startT) AS startT, IF(d.keyId IS NULL,0, d.keyId) AS 'keyId', IF(d.keyData IS NULL,'null', d.keyData) AS 'CPSN', IF(e.keyStatus IS NULL,'', e.keyStatus) AS 'State', IF(a.keyId IS NULL,'None', IF(a.partFlagB3 = 0,'Pass',IF(a.realPRR = true,'Fail','Fake'))) AS 'Status' FROM (DieInfo AS a, STDFInfo AS b, Die AS c, LotWaf AS l) LEFT JOIN MACKey AS d ON a.keyId = d.keyId LEFT JOIN MACKeyHist AS e ON (a.keyId = e.keyId AND e.dieInfoId = a.dieInfoId) WHERE a.STDFId = b.STDFId AND b.lwId = l.lwId AND a.dieId = c.dieId AND l.lotId = $inp->{lotId} AND l.subLotId = $inp->{subLotId} }; &tracing (8, " Step 1: Running this query: <$stm>"); my $sth = $dbmyh->prepare($stm); $DBI::err && ( &tracerr (1, " Error processing request: $DBI::err ($DBI::errstr)."), return (0)); $sth->execute(); $DBI::err && ( &tracerr (1, " Error processing request: $DBI::err ($DBI::errstr)."), return (0)); ($DBI::rows == 0) && ( &tracing (2, " Error it is not possible to have ($DBI::rows) of information returned for <$STIFInfo->{waferId}>."), return (0)); while (my $ref = $sth->fetchrow_hashref()) { $DBI::err && ( &tracerr (1, " Error processing request: $DBI::err ($DBI::errstr)."), return (0)); &tracing (8, " Found a row with: [$ref->{'X'},$ref->{'Y'}] as <$ref->{'docType'}> s<$ref->{'siteNum'}> h<$ref->{'headNum'}> n<$ref->{'numInfo'}> <$ref->{'startT'}> <$ref->{'State'}> <$ref->{'Status'}>");
Download (untitled) / with headers
text/plain 6.8k
On Štv Okt 25 12:44:15 2012, TVNshack wrote: Show quoted text
> Dear all, > > To my greatest astonishment, I run into this case where a "-8" signed > SMALLINT came back as "2^32 - 8" if mysql_server_prepare is turned on. > > check the SELECT yCoord AS 'Y' output in the three examples below. The > last one is where the problem shows up. > > I hope this can help you. > > Best regards, > Thiery > > > > I did compile Perl myself and all the CPAN modules. > > DBD::mysql -- 4.022 > DBI -- 1.622 > > $ perl -v > This is perl 5, version 14, subversion 2 (v5.14.2) built for x86_64- > linux-thread-multi > > $ perl -V:config_args > config_args='-des -Dprefix=/opt/perl -Acflags=-fPID -DPIC -m64 > -Aldflags=-L/usr/lib64 -L/lib64 -lresolv -lnsl -ldl -lm -lcrypt -lutil > -lc -Duse64bitint -Duseshrplib -Dusemultiplicity -Duselargefiles > -Dusethreads - > Duseithreads -Dmyhostname=localhost -Dperladmin=root@localhost'; > >
> mysql> SELECT c.xCoord AS 'X', c.yCoord AS 'Y', c.dieId, a.headNum, > mysql> a.siteNum, a.numInfo, a.hardBin, a.softBin, b.docType, > mysql> UNIX_TIMESTAMP(b.startT) AS startT,
> -> IF(d.keyId IS NULL,0, d.keyId) AS 'keyId', > -> IF(d.keyData IS NULL,'null', d.keyData) AS 'CPSN', > -> IF(e.keyStatus IS NULL,'', e.keyStatus) AS 'State', > -> IF(a.keyId IS NULL,'None', IF(a.partFlagB3 = > 0,'Pass',IF(a.realPRR = true,'Fail','Fake'))) AS 'Status' > -> FROM (DieInfo AS a, STDFInfo AS b, Die AS c, LotWaf AS l) > -> LEFT JOIN MACKey AS d > -> ON a.keyId = d.keyId > -> LEFT JOIN MACKeyHist AS e > -> ON (a.keyId = e.keyId AND e.dieInfoId = a.dieInfoId) > -> WHERE a.STDFId = b.STDFId > -> AND b.lwId = l.lwId > -> AND a.dieId = c.dieId > -> AND l.lotId = 'ABCDEFG' > -> AND l.subLotId = '21'; > > +-----+-----+-------+---------+---------+---------+---------+--------- > + > | X | Y | dieId | headNum | siteNum | numInfo | hardBin | softBin > | > +-----+-----+-------+---------+---------+---------+---------+--------- > + > | 69 | -9 | 1 | 1 | 27 | 1 | 6 | 138 > | > | 69 | -8 | 2 | 1 | 28 | 1 | 1 | 1 > | > | 68 | -8 | 3 | 1 | 29 | 1 | 1 | 1 > | > | 67 | -8 | 4 | 1 | 30 | 1 | 1 | 1 > | > | 73 | -10 | 5 | 1 | 20 | 1 | 1 | 1 > | > | 72 | -10 | 6 | 1 | 21 | 1 | 1 | 1 > | > | 70 | -9 | 7 | 1 | 24 | 1 | 1 | 1 > | > | 71 | -9 | 8 | 1 | 25 | 1 | 1 | 1 > | > | 72 | -9 | 9 | 1 | 26 | 1 | 1 | 1 > | > | 73 | -9 | 10 | 1 | 27 | 1 | 1 | 1 > | > | 73 | -8 | 11 | 1 | 28 | 1 | 1 | 1 > | > | 72 | -8 | 12 | 1 | 29 | 1 | 1 | 1 > | > ... > > > With mysql_server_prepare disabled, my application's trace reports: > > [26611]: Found a row with: [69,-9] as <STDF> s<27> h<1> n<1> > <1340349441> <> <None> > [26611]: Found a row with: [69,-8] as <STDF> s<28> h<1> n<1> > <1340349441> <ASSIGNED> <Pass> > [26611]: Found a row with: [68,-8] as <STDF> s<29> h<1> n<1> > <1340349441> <ASSIGNED> <Pass> > [26611]: Found a row with: [67,-8] as <STDF> s<30> h<1> n<1> > <1340349441> <ASSIGNED> <Pass> > [26611]: Found a row with: [73,-10] as <STDF> s<20> h<1> n<1> > <1340349441> <ASSIGNED> <Pass> > [26611]: Found a row with: [72,-10] as <STDF> s<21> h<1> n<1> > <1340349441> <ASSIGNED> <Pass> > [26611]: Found a row with: [70,-9] as <STDF> s<24> h<1> n<1> > <1340349441> <ASSIGNED> <Pass> > [26611]: Found a row with: [71,-9] as <STDF> s<25> h<1> n<1> > <1340349441> <ASSIGNED> <Pass> > ... > > > With mysql_server_prepare ENABLED, my application's trace reports > "4294967287" instead of "-8": > > > [26477]: Found a row with: [69,4294967287] as <STDF> s<27> h<1> n<1> > <1340349441> <> <None> > [26477]: Found a row with: [69,4294967288] as <STDF> s<28> h<1> n<1> > <1340349441> <ASSIGNED> <Pass> > [26477]: Found a row with: [68,4294967288] as <STDF> s<29> h<1> n<1> > <1340349441> <ASSIGNED> <Pass> > [26477]: Found a row with: [67,4294967288] as <STDF> s<30> h<1> n<1> > <1340349441> <ASSIGNED> <Pass> > [26477]: Found a row with: [73,4294967286] as <STDF> s<20> h<1> n<1> > <1340349441> <ASSIGNED> <Pass> > [26477]: Found a row with: [72,4294967286] as <STDF> s<21> h<1> n<1> > <1340349441> <ASSIGNED> <Pass> > [26477]: Found a row with: [70,4294967287] as <STDF> s<24> h<1> n<1> > <1340349441> <ASSIGNED> <Pass> > [26477]: Found a row with: [71,4294967287] as <STDF> s<25> h<1> n<1> > <1340349441> <ASSIGNED> <Pass> > [26477]: Found a row with: [72,4294967287] as <STDF> s<26> h<1> n<1> > <1340349441> <ASSIGNED> <Pass> > [26477]: Found a row with: [73,4294967287] as <STDF> s<27> h<1> n<1> > <1340349441> <ASSIGNED> <Pass> > [26477]: Found a row with: [73,4294967288] as <STDF> s<28> h<1> n<1> > <1340349441> <ASSIGNED> <Pass> > ... > > > CREATE TABLE `Die` ( > `dieId` INT unsigned NOT NULL AUTO_INCREMENT, > `dieTypeId` SMALLINT unsigned NOT NULL, > `xCoord` SMALLINT signed NOT NULL, > `yCoord` SMALLINT signed NOT NULL, > ... > > > > my $stm = qq { > SELECT c.xCoord AS 'X', c.yCoord AS 'Y', c.dieId, a.headNum, > a.siteNum, a.numInfo, a.hardBin, a.softBin, b.docType, > UNIX_TIMESTAMP(b.startT) AS startT, > IF(d.keyId IS NULL,0, d.keyId) AS 'keyId', > IF(d.keyData IS NULL,'null', d.keyData) AS 'CPSN', > IF(e.keyStatus IS NULL,'', e.keyStatus) AS 'State', > IF(a.keyId IS NULL,'None', IF(a.partFlagB3 = > 0,'Pass',IF(a.realPRR = true,'Fail','Fake'))) AS 'Status' > FROM (DieInfo AS a, STDFInfo AS b, Die AS c, LotWaf AS l) > LEFT JOIN MACKey AS d > ON a.keyId = d.keyId > LEFT JOIN MACKeyHist AS e > ON (a.keyId = e.keyId AND e.dieInfoId = a.dieInfoId) > WHERE a.STDFId = b.STDFId > AND b.lwId = l.lwId > AND a.dieId = c.dieId > AND l.lotId = $inp->{lotId} > AND l.subLotId = $inp->{subLotId} > }; > &tracing (8, " Step 1: Running this query: <$stm>"); > my $sth = $dbmyh->prepare($stm); > $DBI::err && ( &tracerr (1, " Error processing request: $DBI::err > ($DBI::errstr)."), return (0)); > $sth->execute(); > $DBI::err && ( &tracerr (1, " Error processing request: $DBI::err > ($DBI::errstr)."), return (0)); > ($DBI::rows == 0) && ( &tracing (2, " Error it is not possible to > have ($DBI::rows) of information returned for <$STIFInfo-
> >{waferId}>."), return (0));
> > while (my $ref = $sth->fetchrow_hashref()) { > $DBI::err && ( &tracerr (1, " Error processing request: $DBI::err > ($DBI::errstr)."), return (0)); > &tracing (8, " Found a row with: [$ref->{'X'},$ref->{'Y'}] as > <$ref->{'docType'}> s<$ref->{'siteNum'}> h<$ref->{'headNum'}> n<$ref-
> >{'numInfo'}> <$ref->{'startT'}> <$ref->{'State'}> <$ref- > >{'Status'}>");
I guess this could be fixed by patch in https://rt.cpan.org/Ticket/Display.html?id=118823#txn-1684314
Fixed in 4.040. Finally!


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.