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

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: 1@example. Com 1 2@example. Com 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 ``.

`` ( `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?

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 2@example.

Com 1 2@example. Com 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 ``.

`` ( `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?

Thank you for the assistance. I just tried adding the UNIQUE KEY () statements and resetting the test database to try this. I am afraid I am still getting duplicates.

Your example above is right on as far as what I am trying to achieve in the table with the data I have. – jonny Oct 8 '09 at 14:02 Tried both queries - I did get values back for both queries, but I did not get all the email addresses in the table nor did I get all the keywords - just some of each. – jonny Oct 8 '09 at 15:29 You didn't create two separate UNIQUE KEYs, you creates a single composite one.

Issue this: CREATE UNIQUE INDEX ix_mytable_useremail ON mytable (user_email) – Quassnoi Oct 8 '09 at 15:33.

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

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.

– Quassnoi Oct 8 '09 at 14:51 This is the first appearing snippet 'cut and pasted' from the table (email has been modified from original address for privacy) that demonstrates the duplication. The third record gets added even though the first record is clearly in the table. Melanies@gmail.

Com tempurpedic-beds melanies@gmail. Com child-safety-gates [email protected] tempurpedic-beds – jonny Oct 8 '09 at 15:23 1 You need to create two separate UNIQUE KEYs: CREATE UNIQUE INDEX ix_mytable_useremail ON mytable (user_email), CREATE UNIQUE INDEX ix_mytable_keytoken ON mytable (key_token) – Quassnoi Oct 8 '09 at 15:29.

Added Unique Index lines below the original create table statement - -- ----------------------------------------------------- -- Table ``. `` -- ----------------------------------------------------- DROP TABLE IF EXISTS ``. `` ; CREATE TABLE IF NOT EXISTS ``.

`` ( `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__useremail on ``. ``(user_email); CREATE UNIQUE INDEX ix__keytoken on ``. ``(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.

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

Quassnoi provided lots of help - had I gotten the situation with my table to work as I'd envisioned I'd have chosen one of his answers as final solution. This is what I ended up using so i'm choosing this as the non-optimal, but working solution. – jonny Oct 13 '09 at 15:08.

You have a composite primary key on both columns.

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

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.