MySql "INSERT … ON DUPLICATE KEY UPDATE" still inserting duplicate records. What am I missing?

Question

I have a simple table set up with two columns, each column is a key value. the values stored in each field are varchar(45) representing an email address and a keyword. It is possible that the information collected may duplicate itself as it is related to site browsing data collection. To avoid duplicate entries, I used tried to use INSERT IGNORE into, REPLACE into, and finally I'm trying the following:

insert into <table name> (user_email, key_token) values ('<email>@<this>.com', 'discountsupplies') on duplicate key update user_email='<email>@<this>.com',key_token='discountsupplies';

but I am still seeing duplicate records being inserted into the table. The SQL that generated the table:

DROP TABLE IF EXISTS `<database name>`.`<table name>` ;

CREATE  TABLE IF NOT EXISTS `<database name>`.`<table name>` (
  `user_email` VARCHAR(45) NOT NULL ,
  `key_token` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`user_email`, `key_token`) )
ENGINE = InnoDB;

While I saw several questions that were close to this one, I did not see any that addressed why this might be happening, and I'd like to figure out what I'm not understanding about this behavior. Any help is appreciated.

Answer 1

You have a composite primary key on both columns.

This means that it's the combination of the fields is UNIQUE, not each field as is.

Thes data are possible in the table:

[email protected]  1
[email protected]  1
[email protected]  2

, since no combination of (user_email, key_token) repeats in the table, while user_email and key_token as themselves can repeat.

If you want each separate column to be UNIQUE, define the UNIQUE constraints on the fields:

CREATE  TABLE IF NOT EXISTS `<database name>`.`<table name>` (
  `user_email` VARCHAR(45) NOT NULL ,
  `key_token` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`user_email`, `key_token`),
  UNIQUE KEY (user_email),
  UNIQUE KEY (key_token)
)
ENGINE = InnoDB;

Update

Having duplicates in a column marked as UNIQUE would be a level 1 bug in MySQL.

Could you please run the following queries:

SELECT  user_email
FROM    mytable
GROUP BY
        user_email
HAVING  COUNT(*) > 1

SELECT  key_token
FROM    mytable
GROUP BY
        key_token
HAVING  COUNT(*) > 1

and see if they return something?

Answer 2

PRIMARY KEY (user_email,key_token) means a combination of both will be unique but if you also want individual email and key_tokens to be unique you have to use UNIQUE seperately for each column..

PRIMARY KEY ('user_email', 'key_token'),
  UNIQUE KEY (user_email),
  UNIQUE KEY (key_token)

Answer 3

As an addendum, After adding the UNIQUE KEY statements, I went back and tried both REPLACE and INSERT IGNORE to achieve my goal, and none of these options is excluding duplicate entries.

Also adding: UNIQUE INDEX (user_email, key_token) doesn't seem to help either.

I'm going to do this check via a manual look-up routine until I can figure this out. If I find an answer I'll be happy to update the post.

Answer 4

Added Unique Index lines below the original create table statement -

-- -----------------------------------------------------
-- Table `<db name>`.`<table name>`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `<db name>`.`<table name>` ;

CREATE  TABLE IF NOT EXISTS `<db name>`.`<table name>` (
  `user_email` VARCHAR(45) NOT NULL ,
  `key_token` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`user_email`, `key_token`),
  UNIQUE KEY (user_email),
  UNIQUE KEY (key_token)
  )

ENGINE = InnoDB;

CREATE UNIQUE INDEX ix_<table name>_useremail on `<db name>`.`<table name>`(user_email);
CREATE UNIQUE INDEX ix_<table name>_keytoken on `<db name>`.`<table name>`(key_token);

it seems to be ok (no errors when creating tables during the source step), but I'm still getting duplicates when running the on duplicate query.

Answer 5

final solution for now: query table to get list of key_tokens by user_email, test current key_token against list entries, if found don't insert. Not optimal or pretty, but it works....