Bug #118977 for DBD-mysql: zerofill

Sat Nov 26 11:08:46 2016 knarf [...] knarf.de - Ticket created

Subject:	zerofill
Date:	Sat, 26 Nov 2016 17:08:26 +0100
To:	bug-DBD-mysql [...] rt.cpan.org
From:	Frank Bartels <knarf [...] knarf.de>

Hi, I use perl v5.24.1 together with p5-DBD-mysql-4.040. I have a script that used to work for 10 years or more but now broke because of the recent changes: "Fix integer types when server side prepare statements are enabled" I do something like this: my $query = $dbh->prepare("SELECT Albums.Id,Tracks.Id,Albums.Artist,Albums.Title,Albums.Cddb,Albums.Genre,Albums.Year,Tracks.Trackname,Tracks.Tracknum,Tracks.Length,Tracks.VBR,Tracks.Bitrate FROM Tracks,Albums WHERE Tracks.Albumref=Albums.Id ORDER BY Tracks.Id"); $query->execute; while ( ( $AlbumId, $TrackId, $DB{TPE1}, $DB{TALB}, $DB{CDDB}, $DB{TCON}, $DB{TYER}, $DB{TIT2}, $DB{TRCK}, $DB{TLEN}, $DB{VBR}, $DB{BITRATE} ) = $query->fetchrow_array ) { Albums.Id is "smallint(5) unsigned zerofill" in mysql 5.5.53. Before 4.040 $AlbumId was "00001". With 4.040 $AlbumId changed to "1". I know this is not the best design. :) I fixed it doing $AlbumId = sprintf("%05s", $AlbumId); for now. Do you plan to bring back the old behaviour? Thanks, Knarf

Download smime.p7s
application/x-pkcs7-signature 3.8k

Message body not shown because it is not plain text.

Tue Nov 29 16:30:30 2016 pali [...] cpan.org - Cc PALI added

Tue Nov 29 16:35:08 2016 pali [...] cpan.org - Correspondence added

On Sob Nov 26 11:08:46 2016, knarf@knarf.de wrote: Show quoted text

> Hi, > > I use perl v5.24.1 together with p5-DBD-mysql-4.040. > > I have a script that used to work for 10 years or more but now broke > because of the recent changes: "Fix integer types when server side > prepare statements are enabled" > > I do something like this: > > my $query = $dbh->prepare("SELECT > Albums.Id,Tracks.Id,Albums.Artist,Albums.Title,Albums.Cddb,Albums.Genre,Albums.Year,Tracks.Trackname,Tracks.Tracknum,Tracks.Length,Tracks.VBR,Tracks.Bitrate > FROM Tracks,Albums WHERE Tracks.Albumref=Albums.Id ORDER BY > Tracks.Id"); > $query->execute; > > while ( ( $AlbumId, $TrackId, $DB{TPE1}, $DB{TALB}, $DB{CDDB}, > $DB{TCON}, > $DB{TYER}, $DB{TIT2}, $DB{TRCK}, $DB{TLEN}, $DB{VBR}, $DB{BITRATE} > ) = $query->fetchrow_array ) { > > Albums.Id is "smallint(5) unsigned zerofill" in mysql 5.5.53. > > Before 4.040 $AlbumId was "00001". With 4.040 $AlbumId changed to > "1". > > I know this is not the best design. :) > > I fixed it doing > > $AlbumId = sprintf("%05s", $AlbumId); > > for now. > > Do you plan to bring back the old behaviour? > > Thanks, > Knarf

Hi! ZEROFILL mysql attribute is not supported by DBD::mysql yet. You had just luck that if worked and probably worked only for non-server-side prepared statements. Anyway, for supporting ZEROFILL in DBD::mysql I see there only one solution: Tell mysql to return column as string, not integer. That can be quite ineffective, but probably only possible solution right now... Here is patch for it: https://github.com/perl5-dbi/DBD-mysql/pull/75 I would be happy if you test it.

Tue Nov 29 16:35:08 2016 The RT System itself - Status changed from 'new' to 'open'

Thu Dec 01 03:22:02 2016 MICHIELB [...] cpan.org - Correspondence added

RT-Send-CC:

pali [...] cpan.org

This is now merged in https://github.com/perl5-dbi/DBD-mysql/commit/dc4d40b1df2f05b9e23105ab6d7b98c77eb318de Thanks Pali for the patch, and thanks Knarf for the report! -- Michiel

Thu Dec 01 03:22:13 2016 MICHIELB [...] cpan.org - Status changed from 'open' to 'resolved'

Thu Dec 01 03:22:13 2016 MICHIELB [...] cpan.org - Taken

Sat Jul 01 05:21:51 2017 pali [...] cpan.org - Correspondence added

Reopening, fix was reverted in 4.043.

Sat Jul 01 05:22:02 2017 pali [...] cpan.org - Status changed from 'resolved' to 'open'

Tue Nov 14 02:23:53 2017 MICHIELB [...] cpan.org - Correspondence added

applied to master again in https://github.com/perl5-dbi/DBD-mysql/commit/0200483708e46d16293868dd974b91ec51637e11

Tue Nov 14 02:23:54 2017 MICHIELB [...] cpan.org - Status changed from 'open' to 'resolved'