Skip Menu |
 

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

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

People
Owner: CAPTTOFU [...] cpan.org
Requestors: Manuel.Jeckelmann [...] swisscom.com
Cc:
AdminCc:

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



Subject: mysql_server_prepare=1 conflicts with BIT-type in DBD::mysql
Date: Wed, 21 Aug 2013 16:23:00 +0000
To: <bug-DBD-mysql [...] rt.cpan.org>
From: <Manuel.Jeckelmann [...] swisscom.com>
Download (untitled) / with headers
text/plain 1.9k
Hi everyone, I found an incompatibility between the mysql_server_prepare-flag and BIT-typed MySQL-content. *REPRODUCE * o DB: -- Database: `test` -- Table structure for table `t_binary_test` CREATE TABLE IF NOT EXISTS `t_binary_test` ( `id_binary_test` bigint(20) NOT NULL auto_increment, `flags` bit(32) NOT NULL, PRIMARY KEY (`id_binary_test`), KEY `flags` (`flags`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ; INSERT INTO `t_binary_test` (`id_binary_test`, `flags`) VALUES (2, b'10'), (1, b'1'); o Perl code: #!/bin/perl -w use DBI; use Data::Dumper; my $driver_opts = ""; $driver_opts .= "mysql_server_prepare=1;"; my $dsn = "dbi:mysql:database=test;host=host.localdomain.net;port=3306;"; $dsn .= $driver_opts; my $dbh = DBI->connect($dsn, 'user', 'password', {RaiseError => 1, AutoCommit => 1, InactiveDestroy => 0,}); my $sth = $dbh->prepare("SELECT id_binary_test,flags FROM t_binary_test"); $sth->execute() or die("Execute failed: ".$DBI::errstr); my $r = $sth->fetchrow_hashref(); print Dumper $r; __END__ * DESCRIPTION * This code is expected to return the first line of the table and print it using Data::Dumper, producing the output: $VAR1 = { 'flags' => '', 'id_binary_test' => '1' }; However, $r (which is printed) will be undef. There are two ways to get around the undef-output: 1.) Comment out the line, setting the mysql_server_prepare-flag: #$driver_opts .= "mysql_server_prepare=1;"; You will get the expected output. 2.) This is rather a workaround than a solution: Change the query, such that the bit-typed field gets returned as a BIN-fomatted string. So: "SELECT id_binary_test,BIN(flags) FROM t_binary_test" * VERSIONS * - OS: CentOS release 6.4 (Final); Linux 2.6.32-358.6.2.el6.x86_64 #1 SMP Thu May 16 20:59:36 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux - Perl: v5.10.1 (*) built for x86_64-linux-thread-multi - MySQL-Server: 5.0.95-log - DBD::mysql: 4.023 - DBI: 1.628 Keep up the good work!! MJ
Hi Manuel, Based on your code sample I created a test, see attached. If I run it on the current DBD::mysql I get these results - as far as I can see the whole fetchrow_hashref is not executing. 1..22 ok 1 - create table for prepare ok 2 not ok 3 - fetchrow_hashref for prepare # Failed test 'fetchrow_hashref for prepare' # at t/rt88006-bit-prepare.t line 43. not ok 4 - id_binary test contents # Failed test 'id_binary test contents' # at t/rt88006-bit-prepare.t line 44. # got: undef # expected: '1' not ok 5 - flags has contents # Failed test 'flags has contents' # at t/rt88006-bit-prepare.t line 45. ok 6 ok 7 - fetchrow_hashref for prepare with BIN() ok 8 - id_binary test contents ok 9 - flags has contents ok 10 ok 11 ok 12 - create table for noprepare ok 13 ok 14 - fetchrow_hashref for noprepare ok 15 - id_binary test contents ok 16 - flags has contents ok 17 ok 18 - fetchrow_hashref for noprepare with BIN() ok 19 - id_binary test contents ok 20 - flags has contents ok 21 ok 22 # Looks like you failed 3 tests of 22. @patrick do you have an idea why?
Subject: rt88006-bit-prepare.t
#!/usr/bin/perl use strict; use warnings; use vars qw($test_dsn $test_user $test_password); use Carp qw(croak); use DBI; use Test::More; use lib 't', '.'; require 'lib.pl'; plan tests => 22; for my $scenario (qw(prepare noprepare)) { my $dbh; my $dsn = $test_dsn; $dsn .= ';mysql_server_prepare=1;' if $scenario eq 'prepare'; eval {$dbh = DBI->connect($dsn, $test_user, $test_password, { RaiseError => 1, AutoCommit => 1})}; if ($@) { plan skip_all => "ERROR: $DBI::errstr. Can't continue test"; } my $create = <<EOT; CREATE TEMPORARY TABLE `dbd_mysql_rt88006_bit_prep` ( `id_binary_test` bigint(20) NOT NULL auto_increment, `flags` bit(32) NOT NULL, PRIMARY KEY (`id_binary_test`), KEY `flags` (`flags`) ) EOT ok $dbh->do($create),"create table for $scenario"; ok $dbh->do("INSERT INTO `dbd_mysql_rt88006_bit_prep` (`id_binary_test`, `flags`) VALUES (2, b'10'), (1, b'1')"); my $sth = $dbh->prepare("SELECT id_binary_test,flags FROM dbd_mysql_rt88006_bit_prep"); $sth->execute() or die("Execute failed: ".$DBI::errstr); ok (my $r = $sth->fetchrow_hashref(), "fetchrow_hashref for $scenario"); is ($r->{id_binary_test}, 1, 'id_binary test contents'); ok ($r->{flags}, 'flags has contents'); ok $sth->finish; $sth = $dbh->prepare("SELECT id_binary_test,BIN(flags) FROM dbd_mysql_rt88006_bit_prep"); $sth->execute() or die("Execute failed: ".$DBI::errstr); ok ($r = $sth->fetchrow_hashref(), "fetchrow_hashref for $scenario with BIN()"); is ($r->{id_binary_test}, 1, 'id_binary test contents'); ok ($r->{'BIN(flags)'}, 'flags has contents'); ok $sth->finish; ok $dbh->disconnect; }
Download (untitled) / with headers
text/plain 1.2k
I'll have to dig into this issue. On Tue Oct 15 11:19:48 2013, MICHIELB wrote: Show quoted text
> Hi Manuel, > > Based on your code sample I created a test, see attached. > > If I run it on the current DBD::mysql I get these results - as far as > I can see the whole fetchrow_hashref is not executing. > > 1..22 > ok 1 - create table for prepare > ok 2 > not ok 3 - fetchrow_hashref for prepare > # Failed test 'fetchrow_hashref for prepare' > # at t/rt88006-bit-prepare.t line 43. > not ok 4 - id_binary test contents > # Failed test 'id_binary test contents' > # at t/rt88006-bit-prepare.t line 44. > # got: undef > # expected: '1' > not ok 5 - flags has contents > # Failed test 'flags has contents' > # at t/rt88006-bit-prepare.t line 45. > ok 6 > ok 7 - fetchrow_hashref for prepare with BIN() > ok 8 - id_binary test contents > ok 9 - flags has contents > ok 10 > ok 11 > ok 12 - create table for noprepare > ok 13 > ok 14 - fetchrow_hashref for noprepare > ok 15 - id_binary test contents > ok 16 - flags has contents > ok 17 > ok 18 - fetchrow_hashref for noprepare with BIN() > ok 19 - id_binary test contents > ok 20 - flags has contents > ok 21 > ok 22 > # Looks like you failed 3 tests of 22. > > > @patrick do you have an idea why?
Download (untitled) / with headers
text/plain 260b
Show quoted text
> On Tue Oct 15 11:19:48 2013, MICHIELB wrote:
> > > > Based on your code sample I created a test, see attached.
I added this as a TODO-marked test to the repository now: https://github.com/perl5-dbi/DBD-mysql/commit/c9c07773847b69fa73314704da23709903dad180
RT-Send-CC: pali [...] cpan.org
Download (untitled) / with headers
text/plain 129b


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.