MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Andrea Forghieri Date:February 13 2002 5:15pm
Subject:Unique indexes and case sensitivity bug(?)
View as plain text  
>Description:
 <MYSQL returns "ERROR 1062: Duplicate entry 'xxxxxx' for key x"
 when creating a UNIQUE KEY on CHAR fields that contain more
 occurencies of the same value written with different cases.
 See example below. >
>How-To-Repeat:
 <Please run these few SQL statements
   CREATE TABLE dummy (a CHAR(1));
   INSERT INTO dummy VALUES('b');
   INSERT INTO dummy VALUES('B');
   CREATE UNIQUE INDEX dummy_idx ON dummy(a);

   Now you should get the error.... >
>Fix:
 <I read carefully the manual and I tried to change the column
 type to CHAR(x) BYNARY
 It seems to me that it is not possible to force MYSQL to treat
 the fields literrally when creating indexes (though it is possible
 in the SELECT statements)
 I haven't find any server variable that concerns this feature.
 More likely than not I am a dumb, and the solution is right
 under my noose, but at the moment I am stucked >

THANX

>Submitter-Id: <a.forghieri@stripped>
>Originator: root
>Organization:
 <Emmegi S.p.A. (www.emmegi.it)>
>MySQL support: [none  ]
>Synopsis: <Unique indexes and case sensitivity bug(?)>
>Severity: <serious>
>Priority: <medium>
>Category: mysql
>Class:  <sw-bug>
>Release: mysql-4.0.1-alpha (Official MySQL RPM)
>Server: /usr/bin/mysqladmin  Ver 8.18 Distrib 3.23.36, for pc-linux-gnu on
i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version  4.0.1-alpha-log
Protocol version 10
Connection  Localhost via UNIX socket
UNIX socket  /var/lib/mysql/mysql.sock
Uptime:   47 min 47 sec

Threads: 6  Questions: 2429  Slow queries: 1  Opens: 69  Flush tables: 2
Open tables: 24  Queries per second avg: 0.847
>Environment:
 <machine, os, target, libraries (multiple lines)>
System: Linux mandrake 2.4.3-20mdksmp #1 SMP Sun Apr 15 22:32:27 CEST 2001
i686 unknown
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i586-mandrake-linux/2.96/specs
gcc version 2.96 20000731 (Linux-Mandrake 8.0 2.96-0.48mdk)
Compilation info: CC='gcc'  CFLAGS='-O6 -fno-omit-frame-pointer -mpentium'
CXX='gcc'
-O6 -fno-omit-frame-pointer            -felide-constructors -fno-exceptions 
-fno-rtti -mpentium'  LDFLAGS=''
LIBC:
lrwxrwxrwx    1 root     root           13 ago 27 00:40 /lib/libc.so.6 ->
libc-2.2.2.so
-rwxr-xr-x    1 root     root      1216268 feb 21  2001 /lib/libc-2.2.2.so
-rw-r--r--    1 root     root     26366908 feb 21  2001 /usr/lib/libc.a
-rw-r--r--    1 root     root          178 feb 21  2001 /usr/lib/libc.so
Configure command:
./configure  --disable-shared --with-mysqld-ldflags=-all-static --with-clien
t-ldflags=-all-static --without-berkeley-db --with-innodb --enable-assembler
 --with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock 
--prefix=/ --with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/us
r/sbin --sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql
 --infodir=/usr/info --includedir=/usr/include --mandir=/usr/man --with-embe
dded-server --enable-thread-safe-client '--with-comment=Official MySQL RPM'




Thread
Unique indexes and case sensitivity bug(?)Andrea Forghieri13 Feb