CODEWITHSUNDEEP
mysqlsql
user784637
user784637

Reputation: 16092

Why am I getting " Duplicate entry" errors when inserting the DISTINCT values into a table?

I'm trying to insert the distinct values of one field of the table requests into the only field of the table pastmonth

CREATE TABLE `pastmonth` (
  `video_id` char(5) DEFAULT NULL,
  PRIMARY KEY (`video_id`)
);

INSERT INTO pastmonth (video_id)
SELECT DISTINCT (video_id)
FROM requests
WHERE exec_datetime >= NOW() - 60*24*60*60;

However I get this error

Error Code: 1062. Duplicate entry 'abcde' for key 'PRIMARY'

I don't believe there are duplicate entries for the value abcde in the table requests because the following query indicates there is only 1 entry with that but it should not matter if there was because I'm SELECTING the DISTINCT values anyways.

SELECT COUNT(*) FROM requests WHERE video_id = 'abcde';

COUNT(*)
1

I have a non-unique index on requests.video_id but I think that is irrelevant to the problem I have. Is it possible that I have a corrupt btree index?

EDIT

CREATE TABLE `requests` (
  `request_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `video_id` char(5) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `exec_datetime` datetime DEFAULT NULL,
  PRIMARY KEY (`request_id`),
  KEY `exec_datetime` (`exec_datetime`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

Upvotes: 2

Views: 580

Answers (1)

Ja͢ck
Ja͢ck

Reputation: 173542

In the requests table, the video_id field is defined as:

`video_id` char(5) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,

But in the new table, it's defined as:

`video_id` char(5)

There might be a difference in character set and corresponding collation. Make sure both fields have exactly the same character set and collation.

Upvotes: 1

Related Questions