Bug #70020 Incorrect unique constraint
Submitted: 13 Aug 2013 20:40 Modified: 14 Sep 2013 5:26
Reporter: Alexander Yerenkow Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:5.5.28 OS:Any
Assigned to: CPU Architecture:Any

[13 Aug 2013 20:40] Alexander Yerenkow
Description:
Unique constraint somehow treats '.' and '. ' as same string.

How to repeat:
CREATE TABLE `temp` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `content` varchar(50) CHARACTER SET utf8 NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `content_uniq` (`content`)
);

INSERT INTO `temp` (`content`) VALUES ('.');
INSERT INTO `temp` (`content`) VALUES ('. ');

Suggested fix:
Fix uniqueness checks/index/engine
[14 Aug 2013 5:26] MySQL Verification Team
It doesn't make much sense to do this, but it has been documented as such.

http://dev.mysql.com/doc/refman/5.6/en/char.html

"For those cases where trailing pad characters are stripped or comparisons ignore them, if a column has an index that requires unique values, inserting into the column values that differ only in number of trailing pad characters will result in a duplicate-key error. For example, if a table contains 'a', an attempt to store 'a ' causes a duplicate-key error."

You could use VARBINARY(50) in order to insert those values uniquely.
[15 Sep 2013 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".