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