Skip Menu |
 

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

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

People
Owner: Nobody in particular
Requestors: samborsky_d [...] yahoo.com
Cc:
AdminCc:

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



Subject: is_utf8 is not set when CONCAT() is used in SQL query
Download (untitled) / with headers
text/plain 901b
It seems that this bug was not reported before. The problem occurs when columns are combined with CONCAT() function, then DBD::mysql doesn't set is_utf8 flag on the output column. See the script attached. Many thanks in advance! Observed output: =============== UTF-8 is broken if we CONCAT() two fields: name = 1135 Düsseldorf; is_utf8 = name = 1160 Москва; is_utf8 = UTF-8 is good if we just take `name` field: name = Düsseldorf; is_utf8 = 1 name = Москва; is_utf8 = 1 Expected output: =============== UTF-8 isn't broken even if we CONCAT() two fields: name = 1135 Düsseldorf; is_utf8 = 1 name = 1160 Москва; is_utf8 = 1 UTF-8 is good if we just take `name` field: name = Düsseldorf; is_utf8 = 1 name = Москва; is_utf8 = 1 Software versions: ================= MySQL version = 5.1.32-community $DBI::VERSION = 1.611 $DBD::mysql::VERSION = 4.013
Subject: concat_utf8.pl
Download concat_utf8.pl
text/x-perl 1.5k
#! /usr/bin/perl -w use utf8; use strict; use DBI; binmode STDOUT, ":utf8"; binmode STDERR, ":utf8"; my $dbh = DBI->connect("DBI:mysql:database=test;", 'user', 'password', {RaiseError => 1, AutoCommit => 0, mysql_auto_reconnect => 1, mysql_enable_utf8 => 1, on_connect_do => [ 'set names utf8' ], }); $dbh or die $!; $SIG{__DIE__} = sub {$dbh->disconnect}; =pod Fill some data in a two-column table: DROP TABLE IF EXISTS `cities`; CREATE TABLE `cities` ( `year` int(11) DEFAULT NULL, `name` char(80) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `cities` (`year`, `name`) VALUES (1135, 'Düsseldorf'), (1160, 'Москва'); =cut printf "\nUTF-8 is broken if we CONCAT() two fields:\n"; my $sql = "SELECT CONCAT(`year`, ' ', `name`) AS 'name' FROM `cities`"; my $sth = $dbh->prepare($sql); $sth->execute(); while (my $hsh = $sth->fetchrow_hashref) {# trouble is here --> printf "name = %s; is_utf8 = %s\n", $hsh->{name}, utf8::is_utf8($hsh->{name}); } print "\nUTF-8 is good if we just take `name` field:\n"; $sql = "SELECT `name` AS 'name' FROM `cities`"; $sth = $dbh->prepare($sql); $sth->execute(); while (my $hsh = $sth->fetchrow_hashref) {# now it's OK --> printf "name = %s; is_utf8 = %s\n", $hsh->{name}, utf8::is_utf8($hsh->{name}); } print "\nSoftware versions:\n"; $sql = 'SELECT @@version'; $sth = $dbh->prepare($sql); $sth->execute(); printf "MySQL version = %s\n\$DBI::VERSION = %s\n\$DBD::mysql::VERSION = %s\n", $sth->fetchrow_array, $DBI::VERSION, $DBD::mysql::VERSION;
From: samborsky_d [...] yahoo.com
Download (untitled) / with headers
text/plain 317b
Sorry, it seems to be a quirk of MySQL: CONCAT(1234, ' ', 'string') gives a BINARY result (sic!). Then DBD::mysql process that value as a binary string and no utf8 flag is set. Numbers should be cast explicitly in order to produce CHAR: CONCAT(CAST(1234 AS CHAR), ' ', 'string') Please, close this ticket. Thanks!
closing per reporters request.


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.