MySQL Bugs: #70020: Incorrect unique constraint

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

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

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.

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".