Skip Menu |
 

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

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

People
Owner: Nobody in particular
Requestors: TCOHEN [...] cpan.org
Cc: pali [...] cpan.org
AdminCc:

Bug Information
Severity: Important
Broken in: 4.028
Fixed in: 4.041_01



Subject: Integer column returned in stringified form
Download (untitled) / with headers
text/plain 432b
While trying to convert results into JSON data, I noticed that integer values where being considered string values. Using the attached code I noticed that while $sth->{mysql_is_num} is saying the value is numeric, the stored result isn't, this is the output: Row arrayref: SV = PV(0x256a5a0) at 0x2547b88 REFCNT = 1 FLAGS = (POK,pPOK) PV = 0x2104fb0 "1"\0 CUR = 1 LEN = 10 mysql_is_num: $VAR1 = [ 1 ];
Subject: test_integer.pl
Download test_integer.pl
text/x-perl 420b
#!/usr/bin/perl use Modern::Perl; use Data::Dumper; use DBI; use Devel::Peek 'Dump'; my $dbh = DBI->connect( "dbi:mysql:database=koha_kohadev;host=localhost;port=3306", "koha_kohadev", "password" ); my $sth = $dbh->prepare("SELECT borrowernumber FROM borrowers LIMIT 1"); $sth->execute; print "Row arrayref:\n"; Dump( $sth->fetchrow_arrayref->[0] ); print "mysql_is_num:\n"; warn Dumper( $sth->{mysql_is_num} ); 1;
I forgot to add context information. This is a Debian 8 (amd64) box, running MariaDB (mysql Ver 15.1 Distrib 10.0.28-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2) And this is some more comprehensive information I get from DBIx::Class ($schema->storage->_describe_connection()) as per ribasushi's suggestion. DBD "DBD::mysql", DBD_VER 4.041, DBIC_DRIVER "DBIx::Class::Storage::DBI::mysql", DBIC_DSN "dbi:mysql:database=koha_kohadev;host=localhost;port=3306", DBIC_VER 0.082840, DBI_VER 1.631, SQL_CURSOR_COMMIT_BEHAVIOR "2 (SQL_CB_PRESERVE)", SQL_CURSOR_ROLLBACK_BEHAVIOR "2 (SQL_CB_PRESERVE)", SQL_CURSOR_SENSITIVITY "0 (SQL_UNSPECIFIED)", SQL_DATA_SOURCE_NAME "dbi:mysql:database=koha_kohadev;host=localhost;port=3306", SQL_DBMS_NAME "MySQL", SQL_DBMS_VER "5.5.5-10.0.28-MariaDB-0+deb8u1", SQL_DEFAULT_TXN_ISOLATION "2 (SQL_TXN_READ_COMMITTED)", SQL_DRIVER_NAME "libmyodbc3.so", SQL_DRIVER_ODBC_VER 03.51, SQL_DRIVER_VER "04.41.0000", SQL_EXPRESSIONS_IN_ORDERBY "Y", SQL_GROUP_BY "3 (SQL_GB_NO_RELATION)", SQL_IDENTIFIER_CASE "4 (SQL_IC_MIXED)", SQL_IDENTIFIER_QUOTE_CHAR "`", SQL_MAX_CATALOG_NAME_LEN 64, SQL_MAX_COLUMN_NAME_LEN 64, SQL_MAX_IDENTIFIER_LEN 64, SQL_MAX_TABLE_NAME_LEN 192, SQL_MULTIPLE_ACTIVE_TXN "Y", SQL_MULT_RESULT_SETS "Y", SQL_NEED_LONG_DATA_LEN "N", SQL_NON_NULLABLE_COLUMNS "1 (SQL_NNC_NON_NULL)", SQL_ODBC_VER 03.80, SQL_QUALIFIER_NAME_SEPARATOR ".", SQL_QUOTED_IDENTIFIER_CASE "3 (SQL_IC_SENSITIVE)", SQL_TXN_CAPABLE "3 (SQL_TC_DDL_COMMIT)", SQL_TXN_ISOLATION_OPTION "0x0000000F (SQL_TXN_READ_UNCOMMITTED SQL_TXN_READ_COMMITTED SQL_TXN_REPEATABLE_READ SQL_TXN_SERIALIZABLE)"
Download (untitled) / with headers
text/plain 593b
On Wed 18 Jan 2017 11:01:05, TCOHEN wrote: Show quoted text
> While trying to convert results into JSON data, I noticed that integer > values where being considered string values. Using the attached code I > noticed that while $sth->{mysql_is_num} is saying the value is > numeric, the stored result isn't, this is the output: > > Row arrayref: > SV = PV(0x256a5a0) at 0x2547b88 > REFCNT = 1 > FLAGS = (POK,pPOK) > PV = 0x2104fb0 "1"\0 > CUR = 1 > LEN = 10 > mysql_is_num: > $VAR1 = [ > 1 > ];
Can you please share the CREATE TABLE statement of your 'borrowers' table? -- Michiel
Download (untitled) / with headers
text/plain 1003b
On Wed Jan 18 11:01:05 2017, TCOHEN wrote: Show quoted text
> While trying to convert results into JSON data, I noticed that integer > values where being considered string values. Using the attached code I > noticed that while $sth->{mysql_is_num} is saying the value is > numeric, the stored result isn't, this is the output: > > Row arrayref: > SV = PV(0x256a5a0) at 0x2547b88 > REFCNT = 1 > FLAGS = (POK,pPOK) > PV = 0x2104fb0 "1"\0 > CUR = 1 > LEN = 10 > mysql_is_num: > $VAR1 = [ > 1 > ];
Just to note that POK means Pointer and actually means how is value stored internally. Number can be stored in perl differently, as native process integer, as native processor float or as pointer (to char*). All those representations are numbers and equivalent (just some can overflow/underflow if are too small/long...). See perlnumber. So FLAGS does not tell you if scalar contains number. You should use e.g. looks_like_number() see: http://perldoc.perl.org/Scalar/Util.html#looks_like_number
Download (untitled) / with headers
text/plain 8.2k
On Thu Jan 19 03:03:51 2017, MICHIELB wrote: Show quoted text
> > Can you please share the CREATE TABLE statement of your 'borrowers' table?
CREATE TABLE `borrowers` ( -- this table includes information about your patrons/borrowers/members `borrowernumber` int(11) NOT NULL auto_increment, -- primary key, Koha assigned ID number for patrons/borrowers `cardnumber` varchar(16) default NULL, -- unique key, library assigned ID number for patrons/borrowers `surname` mediumtext NOT NULL, -- patron/borrower's last name (surname) `firstname` text, -- patron/borrower's first name `title` mediumtext, -- patron/borrower's title, for example: Mr. or Mrs. `othernames` mediumtext, -- any other names associated with the patron/borrower `initials` text, -- initials for your patron/borrower `streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's primary address `streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's primary address `address` mediumtext NOT NULL, -- the first address line for your patron/borrower's primary address `address2` text, -- the second address line for your patron/borrower's primary address `city` mediumtext NOT NULL, -- the city or town for your patron/borrower's primary address `state` text default NULL, -- the state or province for your patron/borrower's primary address `zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's primary address `country` text, -- the country for your patron/borrower's primary address `email` mediumtext, -- the primary email address for your patron/borrower's primary address `phone` text, -- the primary phone number for your patron/borrower's primary address `mobile` varchar(50) default NULL, -- the other phone number for your patron/borrower's primary address `fax` mediumtext, -- the fax number for your patron/borrower's primary address `emailpro` text, -- the secondary email addres for your patron/borrower's primary address `phonepro` text, -- the secondary phone number for your patron/borrower's primary address `B_streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's alternate address `B_streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's alternate address `B_address` varchar(100) default NULL, -- the first address line for your patron/borrower's alternate address `B_address2` text default NULL, -- the second address line for your patron/borrower's alternate address `B_city` mediumtext, -- the city or town for your patron/borrower's alternate address `B_state` text default NULL, -- the state for your patron/borrower's alternate address `B_zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's alternate address `B_country` text, -- the country for your patron/borrower's alternate address `B_email` text, -- the patron/borrower's alternate email address `B_phone` mediumtext, -- the patron/borrower's alternate phone number `dateofbirth` date default NULL, -- the patron/borrower's date of birth (YYYY-MM-DD) `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table, includes the code of the patron/borrower's home branch `categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table, includes the code of the patron category `dateenrolled` date default NULL, -- date the patron was added to Koha (YYYY-MM-DD) `dateexpiry` date default NULL, -- date the patron/borrower's card is set to expire (YYYY-MM-DD) `gonenoaddress` tinyint(1) default NULL, -- set to 1 for yes and 0 for no, flag to note that library marked this patron/borrower as having an unconfirmed address `lost` tinyint(1) default NULL, -- set to 1 for yes and 0 for no, flag to note that library marked this patron/borrower as having lost their card `debarred` date default NULL, -- until this date the patron can only check-in (no loans, no holds, etc.), is a fine based on days instead of money (YYY-MM-DD) `debarredcomment` VARCHAR(255) DEFAULT NULL, -- comment on the stop of the patron `contactname` mediumtext, -- used for children and profesionals to include surname or last name of guarentor or organization name `contactfirstname` text, -- used for children to include first name of guarentor `contacttitle` text, -- used for children to include title (Mr., Mrs., etc) of guarentor `guarantorid` int(11) default NULL, -- borrowernumber used for children or professionals to link them to guarentors or organizations `borrowernotes` mediumtext, -- a note on the patron/borrower's account that is only visible in the staff client `relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarentor `sex` varchar(1) default NULL, -- patron/borrower's gender `password` varchar(60) default NULL, -- patron/borrower's encrypted password `flags` int(11) default NULL, -- will include a number associated with the staff member's permissions `userid` varchar(75) default NULL, -- patron/borrower's opac and/or staff client log in `opacnote` mediumtext, -- a note on the patron/borrower's account that is visible in the OPAC and staff client `contactnote` varchar(255) default NULL, -- a note related to the patron/borrower's alternate address `sort1` varchar(80) default NULL, -- a field that can be used for any information unique to the library `sort2` varchar(80) default NULL, -- a field that can be used for any information unique to the library `altcontactfirstname` varchar(255) default NULL, -- first name of alternate contact for the patron/borrower `altcontactsurname` varchar(255) default NULL, -- surname or last name of the alternate contact for the patron/borrower `altcontactaddress1` varchar(255) default NULL, -- the first address line for the alternate contact for the patron/borrower `altcontactaddress2` varchar(255) default NULL, -- the second address line for the alternate contact for the patron/borrower `altcontactaddress3` varchar(255) default NULL, -- the city for the alternate contact for the patron/borrower `altcontactstate` text default NULL, -- the state for the alternate contact for the patron/borrower `altcontactzipcode` varchar(50) default NULL, -- the zipcode for the alternate contact for the patron/borrower `altcontactcountry` text default NULL, -- the country for the alternate contact for the patron/borrower `altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower `smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on) `sms_provider_id` int(11) DEFAULT NULL, -- the provider of the mobile phone number defined in smsalertnumber `privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history `privacy_guarantor_checkouts` tinyint(1) NOT NULL DEFAULT '0', -- controls if relatives can see this patron's checkouts `checkprevcheckout` varchar(7) NOT NULL default 'inherit', -- produce a warning for this patron if this item has previously been checked out to this patron if 'yes', not if 'no', defer to category setting if 'inherit'. `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- time of last change could be useful for synchronization with external systems (among others) `lastseen` datetime default NULL, -- last time a patron has been seed (connected at the OPAC or staff interface) UNIQUE KEY `cardnumber` (`cardnumber`), PRIMARY KEY `borrowernumber` (`borrowernumber`), KEY `categorycode` (`categorycode`), KEY `branchcode` (`branchcode`), UNIQUE KEY `userid` (`userid`), KEY `guarantorid` (`guarantorid`), KEY `surname_idx` (`surname`(255)), KEY `firstname_idx` (`firstname`(255)), KEY `othernames_idx` (`othernames`(255)), KEY `sms_provider_id` (`sms_provider_id`), CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`), CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`), CONSTRAINT `borrowers_ibfk_3` FOREIGN KEY (`sms_provider_id`) REFERENCES `sms_providers` (`id`) ON UPDATE CASCADE ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Download (untitled) / with headers
text/plain 1.1k
On Thu Jan 19 07:29:02 2017, PALI wrote: Show quoted text
> On Wed Jan 18 11:01:05 2017, TCOHEN wrote:
> > While trying to convert results into JSON data, I noticed that > > integer > > values where being considered string values. Using the attached code > > I > > noticed that while $sth->{mysql_is_num} is saying the value is > > numeric, the stored result isn't, this is the output: > > > > Row arrayref: > > SV = PV(0x256a5a0) at 0x2547b88 > > REFCNT = 1 > > FLAGS = (POK,pPOK) > > PV = 0x2104fb0 "1"\0 > > CUR = 1 > > LEN = 10 > > mysql_is_num: > > $VAR1 = [ > > 1 > > ];
> > Just to note that POK means Pointer and actually means how is value > stored internally. Number can be stored in perl differently, as native > process integer, as native processor float or as pointer (to char*). > All those representations are numbers and equivalent (just some can > overflow/underflow if are too small/long...). See perlnumber. > > So FLAGS does not tell you if scalar contains number. You should use > e.g. looks_like_number() see: > http://perldoc.perl.org/Scalar/Util.html#looks_like_number
Ok, I was looking at the line PV: address "1", because in DBD::Pg something similar displays IV: ... 1
Download (untitled) / with headers
text/plain 723b
On Štv Jan 19 14:29:26 2017, TCOHEN wrote: Show quoted text
> On Thu Jan 19 03:03:51 2017, MICHIELB wrote:
> > > > Can you please share the CREATE TABLE statement of your 'borrowers' > > table?
> > CREATE TABLE `borrowers` ( -- this table includes information about > your patrons/borrowers/members > `borrowernumber` int(11) NOT NULL auto_increment,
In this case DBD::mysql should already uses mysql integers for data transfters when prepared statements are enabled. And since 4.039 perl scalars should have set IOK flags (to have similar output when prepared statements are enabled and disabled). Can you check that you have at least DBD::mysql version 4.039? And can you check output with enabling server side prepared statements?
Download (untitled) / with headers
text/plain 1.8k
On Štv Jan 19 14:34:07 2017, TCOHEN wrote: Show quoted text
> On Thu Jan 19 07:29:02 2017, PALI wrote:
> > On Wed Jan 18 11:01:05 2017, TCOHEN wrote:
> > > While trying to convert results into JSON data, I noticed that > > > integer > > > values where being considered string values. Using the attached > > > code > > > I > > > noticed that while $sth->{mysql_is_num} is saying the value is > > > numeric, the stored result isn't, this is the output: > > > > > > Row arrayref: > > > SV = PV(0x256a5a0) at 0x2547b88 > > > REFCNT = 1 > > > FLAGS = (POK,pPOK) > > > PV = 0x2104fb0 "1"\0 > > > CUR = 1 > > > LEN = 10 > > > mysql_is_num: > > > $VAR1 = [ > > > 1 > > > ];
> > > > Just to note that POK means Pointer and actually means how is value > > stored internally. Number can be stored in perl differently, as > > native > > process integer, as native processor float or as pointer (to char*). > > All those representations are numbers and equivalent (just some can > > overflow/underflow if are too small/long...). See perlnumber. > > > > So FLAGS does not tell you if scalar contains number. You should use > > e.g. looks_like_number() see: > > http://perldoc.perl.org/Scalar/Util.html#looks_like_number
> > Ok, I was looking at the line PV: address "1", because in DBD::Pg > something similar displays IV: ... 1
Just different internal storage... Anyway I tested your table with engineering 4.041_1 version and I'm getting: SV = PVIV(0x1af3580) at 0x1c61bc8 REFCNT = 1 FLAGS = (IOK,pIOK) IV = 1 PV = 0x1de0f70 "1"\0 CUR = 1 LEN = 16 Some changes to FLAGS was done already in commit 702744529a9ff240b00af67983535e65822f3103 (for 4.039) and other big changes are in 87c9819084501577eda82e0c29d28a92ed21a601 (engineering 4.041_1) where is also changed structures for prepared statement and utf-8 support. So please test engineering 4.041_1 version.
Download (untitled) / with headers
text/plain 745b
On Thu Jan 19 14:57:15 2017, PALI wrote: Show quoted text
> Just different internal storage... > > Anyway I tested your table with engineering 4.041_1 version and I'm > getting: > > SV = PVIV(0x1af3580) at 0x1c61bc8 > REFCNT = 1 > FLAGS = (IOK,pIOK) > IV = 1 > PV = 0x1de0f70 "1"\0 > CUR = 1 > LEN = 16 > > Some changes to FLAGS was done already in commit > 702744529a9ff240b00af67983535e65822f3103 (for 4.039) and other big > changes are in 87c9819084501577eda82e0c29d28a92ed21a601 (engineering > 4.041_1) where is also changed structures for prepared statement and > utf-8 support. > > So please test engineering 4.041_1 version.
I tried with that version: $ sudo cpanm -i git://github.com/perl5-dbi/DBD-mysql.git@4.041_01 ... and it worked!
Great, closing! -- Michiel
Reopening, fix was reverted in 4.043.


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.