MySQL Bugs: #17985: different characters treated as same cause duplicate key error

Bug #17985	different characters treated as same cause duplicate key error
Submitted:	6 Mar 2006 19:44	Modified:	8 May 2007 17:39
Reporter:	Corin Langosch	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.18	OS:	Linux (linux debian)
Assigned to:		CPU Architecture:	Any

Description:
in germany ä und a are completey different characters. unluckily mysql treats them as same and so reports duplicate key error.

How to repeat:

CREATE TABLE `bb1_wordlist` (
  `word` varchar(50) collate latin1_german1_ci NOT NULL default '',
  UNIQUE KEY `word` (`word`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci ;

INSERT INTO `bb1_wordlist` VALUES ('wärs');
INSERT INTO `bb1_wordlist` VALUES ('wars');

Suggested fix:
treat a different than ä. same for all other umlaute.

Thank you for the bug report. I was unable to repeat that issue testing with current
source server, so looks it was already fixed:

miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 5.0.20-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `bb1_wordlist` (
    ->   `word` varchar(50) collate latin1_german1_ci NOT NULL default '',
    ->   UNIQUE KEY `word` (`word`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci ;
Query OK, 0 rows affected (0.04 sec)

mysql>
mysql> INSERT INTO `bb1_wordlist` VALUES ('wärs');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `bb1_wordlist` VALUES ('wars');
Query OK, 1 row affected (0.00 sec)

mysql> select * from  `bb1_wordlist`;
+-------+
| word  |
+-------+
| wars  |
| wärs |
+-------+
2 rows in set (0.00 sec)

mysql> select * from  `bb1_wordlist` where word='wärs';
+-------+
| word  |
+-------+
| wärs |
+-------+
1 row in set (0.00 sec)

mysql>

Hi.

I habe the same problem. I am using mysql on a freebsd4 server.
I am running the folling server version:

mysql-client-5.0.27 Multithreaded SQL database (client)
mysql-server-5.0.33 Multithreaded SQL database (server)

------------------------

Here is what I tried and what I found on google:

-------------------------

CREATE TABLE `bb1_wordlist` (
  `word` varchar(50) collate latin1_german1_ci NOT NULL default '',
  UNIQUE KEY `word` (`word`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci ;

INSERT INTO `bb1_wordlist` VALUES ('wärs');
INSERT INTO `bb1_wordlist` VALUES ('wars');

ERROR 1062 : Duplicate entry 'wars' for key 1

-----------------------------

CREATE TABLE `bb1_wordlist2` (
  `word` varchar(50) collate latin1_german1_ci NOT NULL default '',
  UNIQUE KEY `word` (`word`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ;

INSERT INTO `bb1_wordlist2` VALUES ('wärs');
INSERT INTO `bb1_wordlist2` VALUES ('wars');

ERROR 1062 : Duplicate entry 'wars' for key 1

-----------------------------

I also tried:

ALTER TABLE bb1_wordlist CHARACTER SET `binary` COLLATE `binary`;

INSERT INTO `bb1_wordlist` VALUES ('wärs');
INSERT INTO `bb1_wordlist` VALUES ('wars');

ERROR 1062 : Duplicate entry 'wars' for key 1

-----------------------------

The bug does not seam to be fixed!

Help needed ;-)

Frank

See bug: http://bugs.mysql.com/bug.php?id=28162.