search

perl5-dbi / DBD-mysql

MySQL driver for the Perl5 Database Interface (DBI)
https://metacpan.org/module/DBD::mysql
Other
62 stars 71 forks source link

Integer column returned in stringified form [rt.cpan.org #119904] #212

Open mbeijen opened 6 years ago

mbeijen commented 6 years ago

Migrated from rt.cpan.org#119904 (status was 'open')

Requestors:

Attachments:

From tcohen@cpan.org on 2017-01-18 16:01:05:

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
        ];

From tcohen@cpan.org on 2017-01-18 17:38:10:

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)"

From michielb@cpan.org on 2017-01-19 08:03:51:

On Wed 18 Jan 2017 11:01:05, 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
>         ];

Can you please share the CREATE TABLE statement of your 'borrowers' table?
--
Michiel

From pali@cpan.org on 2017-01-19 12:29:02:

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

From tcohen@cpan.org on 2017-01-19 19:29:26:

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, -- 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;

From tcohen@cpan.org on 2017-01-19 19:34:07:

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

From pali@cpan.org on 2017-01-19 19:56:36:

On Å tv Jan 19 14:29:26 2017, TCOHEN wrote:
> 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?

From pali@cpan.org on 2017-01-19 19:57:15:

On Å tv Jan 19 14:34:07 2017, TCOHEN wrote:
> 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.

From tcohen@cpan.org on 2017-01-20 15:24:08:

On Thu Jan 19 14:57:15 2017, PALI wrote:
> 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!

From michielb@cpan.org on 2017-01-21 21:26:21:

Great, closing!

--
Michiel

From pali@cpan.org on 2017-07-01 09:18:16:

Reopening, fix was reverted in 4.043.
eserte commented 5 years ago

I am wondering whether this issue is fixed --- with recent DBD::mysql 4.050 I see for fetched int values the following SV flags:

FLAGS = (IOK,pIOK)
tomascohen commented 4 years ago

What are the regression tests for this particular bug fixed in 4.041_1?