Is there a bug with mysql's (or DBD's) handling of umlaute in a Primary Key?

Question

I have something that I don't understand.

Here is a piece of code:

use warnings;
use strict;

my $db_name = 'xxx';
my $user    = 'xxx';
my $pw      = 'xxx';

use DBI;

my $dbh = DBI->connect("DBI:mysql:$db_name", $user, $pw) 
          || die "Could not connect to database: $DBI::errstr";


$dbh -> do('drop table umlaute;');


$dbh -> do(
  "create table umlaute (
     txt varchar(3)  primary key -- will not work if primary key enabled
   )") ||  die "could not create table";


$dbh -> begin_work;
my $sth = $dbh -> prepare ("insert into umlaute values (?)") or die "prepare failed";

for my $a ( 'a' .. 'z' , 'ä', 'ö', 'ü') {
for my $b ( 'a' .. 'z' , 'ä', 'ö', 'ü') {
for my $c ( 'a' .. 'z' , 'ä', 'ö', 'ü') {

  $sth -> execute ("$a$b$c") or die "could not insert $a$b$c"; 

}}}

$dbh->commit;

# check count
my $cnt = $dbh -> selectrow_arrayref("select count(*) from umlaute")->[0];

if ($cnt != 29*29*29) {
   print "$cnt != 29*29*29\n";
}
else {
  print "$cnt\n";
}

$dbh -> do('drop table performance_insert;');

It will work as I think it should when there is no primary key on the table umlaute. Yet, if I 'enable' the primary key, it will fail. Interestingly, the script manages to insert the ä and the ö but not the ü.

Any hints to why this is not working are appreciated. It's most probably something I don't spot, so four eyes see more than two.

Answer 1

Thi seems to be an encoding problem, you should read the manual section Character Set Support to fix this. Try to set the encoding to UTF-8.

Answer 2

The column most likely has the latin1_swedish_ci collation. Declare a different collation when creating the table, for example latin1_bin. Character sets and collations are worth understanding, as it could save you effort later on if you find that you need to support characters outside of the latin1 character set.