0

I have a database table with an unique index idx_1(USER_ID, COURSE_ID), however I still found some entries with the same USER_ID and COURSE_ID (both not null). And the other columns of the duplicated rows are not the same. such as addtime (default current timestamp), and updatetime (default current timestamp on update current timestamp) of the duplicated 2 rows have a difference of 1 second:

SELECT * FROM user_course WHERE COURSE_ID='COURSEID1' AND USER_ID='USERID1';

----------------------------------------------------------------
UUID | COURSE_ID | USER_ID | ADDTIME         | UPDATETIME
----------------------------------------------------------------
abc1 |COURSEID1  | USERID1 | 2019-01-01 00:00| 2019-01-01 00:00


SELECT * FROM user_course WHERE UUID='abc1" OR UUID='abc2';

----------------------------------------------------------------
UUID | COURSE_ID | USER_ID | ADDTIME         | UPDATETIME
----------------------------------------------------------------
abc1 |COURSEID1  | USERID1 | 2019-01-01 00:00| 2019-01-01 00:00
----------------------------------------------------------------
abc2 |COURSEID1  | USERID1 | 2019-01-01 00:01| 2019-01-01 00:01

when I query the data with USER_ID and COURSE_ID of one of the duplicated rows, only one row returned, however I can get the rows with duplicated values when query the table with the primary key.

So my question is: Is this a bug of MySQL? and how can I avoid this error when working with MySQL?

server info

OS: CentOS 6

MySQL: MySQL 5.6

  • Did you define your index as unique?. See 13.1.13 CREATE INDEX Syntax :: Unique Indexes and dbfiddle. – wchiquito Sep 29 at 8:25
  • Are they strings with spaces on the end? – Colin 't Hart Sep 29 at 21:20
  • No, there's no space tailed. When compared with python, the returned value is True. It seems that they are exactly the same. And the index is unique index. @Colin'tHart – Woods Chen Sep 30 at 2:35
  • 1
    Can you post the result of: SHOW CREATE TABLE user_course; – Jesus Uzcanga Sep 30 at 18:47
  • Provide SELECT HEX(course_id), hex(userid1) FROM ... – Rick James Oct 10 at 3:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.