MySQL UNIQUE error on 2 different UTF-8 string?

Question

I create a tool to leech users for my xenforo forum but I have a mistake with 2 users, such as sample below.

CREATE TABLE IF NOT EXISTS `xf_user` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `email` varchar(120) NOT NULL,
  `gender` enum('','male','female') NOT NULL DEFAULT '' COMMENT 'Leave empty for ''unspecified''',
  `custom_title` varchar(50) NOT NULL DEFAULT '',
  `language_id` int(10) unsigned NOT NULL,
  `style_id` int(10) unsigned NOT NULL COMMENT '0 = use system default',
  `timezone` varchar(50) NOT NULL COMMENT 'Example: ''Europe/London''',
  `visible` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT 'Show browsing activity to others',
  `user_group_id` int(10) unsigned NOT NULL,
  `secondary_group_ids` varbinary(255) NOT NULL,
  `display_style_group_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'User group ID that provides user styling',
  `permission_combination_id` int(10) unsigned NOT NULL,
  `message_count` int(10) unsigned NOT NULL DEFAULT '0',
  `conversations_unread` smallint(5) unsigned NOT NULL DEFAULT '0',
  `register_date` int(10) unsigned NOT NULL DEFAULT '0',
  `last_activity` int(10) unsigned NOT NULL DEFAULT '0',
  `trophy_points` int(10) unsigned NOT NULL DEFAULT '0',
  `alerts_unread` smallint(5) unsigned NOT NULL DEFAULT '0',
  `avatar_date` int(10) unsigned NOT NULL DEFAULT '0',
  `avatar_width` smallint(5) unsigned NOT NULL DEFAULT '0',
  `avatar_height` smallint(5) unsigned NOT NULL DEFAULT '0',
  `gravatar` varchar(120) NOT NULL DEFAULT '' COMMENT 'If specified, this is an email address corresponding to the user''s ''Gravatar''',
  `user_state` enum('valid','email_confirm','email_confirm_edit','moderated','email_bounce') NOT NULL DEFAULT 'valid',
  `is_moderator` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `is_admin` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `is_banned` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `like_count` int(10) unsigned NOT NULL DEFAULT '0',
  `warning_points` int(10) unsigned NOT NULL DEFAULT '0',
  `is_staff` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`username`),
  KEY `email` (`email`),
  KEY `user_state` (`user_state`),
  KEY `last_activity` (`last_activity`),
  KEY `message_count` (`message_count`),
  KEY `trophy_points` (`trophy_points`),
  KEY `like_count` (`like_count`),
  KEY `register_date` (`register_date`),
  KEY `staff_username` (`is_staff`,`username`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3;

INSERT INTO xf_user (`username`, `email`, `gender`, `custom_title`, `language_id`, `style_id`, `timezone`, `visible`, `user_group_id`, `secondary_group_ids`, `display_style_group_id`, `permission_combination_id`, `message_count`, `conversations_unread`, `register_date`, `last_activity`, `trophy_points`, `alerts_unread`, `avatar_date`, `avatar_width`, `avatar_height`, `gravatar`, `user_state`, `is_moderator`, `is_admin`, `is_banned`, `like_count`, `warning_points`, `is_staff`) VALUES 
(N'BoyBuon1994', 'user608@email', 'male', '', 2, 0, 'Asia/Bangkok', 1, 2, '', 2, 2, 0, 0, 1403776680, 1403776680, 0, 0, 0, 0, 0, '', 'valid', 0, 0, 0, 0, 0, 0),
(N'BoyBuồn1994', 'user635@email', 'male', '', 2, 0, 'Asia/Bangkok', 1, 2, '', 2, 2, 0, 0, 1403776680, 1403776680, 0, 0, 0, 0, 0, '', 'valid', 0, 0, 0, 0, 0, 0);

In the above example, N'BoyBuon1994' and N'BoyBuồn1994' are interpreted as the same, but they are not. Someone help me.

dont post you db here, provide people with a SQLFIDDLE to help you — Dwza
– Dwza, Commented Jun 27, 2014 at 5:48

StanislavL · Accepted Answer · 2014-06-27 05:44:13Z

1

You should use different collation

ALTER TABLE xf_user
MODIFY COLUMN `username` varchar(50) COLLATE utf8_bin DEFAULT NULL;

From http://forums.mysql.com/read.php?22,394026,394256

answered Jun 27, 2014 at 5:44

StanislavL

57.5k9 gold badges75 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dwza · Accepted Answer · 2014-06-27 05:56:55Z

0

you should use " instead of ' here because you have singlequote in your string

(N'BoyBuon1994',....
(N'BoyBuồn1994',....

try

("N'BoyBuon1994",
("N'BoyBuồn1994",

i quess they are the same because of the db's given charset.

try to change the charset (utf8 to utf8_bin). i think he interprets ồ as o

you can also change the charset for a single field in db if you like the keep utf8 like it is. And with single field i mean field like username

edited Jun 27, 2014 at 5:56

answered Jun 27, 2014 at 5:51

Dwza

6,5657 gold badges45 silver badges79 bronze badges

Collectives™ on Stack Overflow

MySQL UNIQUE error on 2 different UTF-8 string?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related