Mysql Duplicate entry error on ON DUPLICATE KEY UPDATE

mysql

Solution

This can happen if you update a field that is marked `UNIQUE` and the second key violation occurs on `UPDATE`.

Update:

From your table structure I can see that it's exactly your case.

That's what happens:

INSERT INTO linked_mem
        ( id1, id2, id31, id4 cnt)
        VALUES (31316, 0, 557158967, 261470594, 1)

-- inserts

INSERT INTO linked_mem
        ( id1, id2, id31, id4 cnt)
        VALUES (31316, 0, 557158967, 261470594, 1)

-- updates `cnt` to 2

INSERT INTO linked_mem
        ( id1, id2, id31, id4 cnt)
        VALUES (31316, 0, 557158967, 261470594, 1)

-- tries again to update `cnt` to 2 and fails

Remove `cnt` from your `PRIMARY KEY` definition.

Problem

I'm using a memory table. It has several ids and a counter. All data as integers. My code updates the counter by 1 if the data exists or creates a line with `counter=1` if not. The query I use is: ``` INSERT INTO linked_mem ( id1, id2, id31, id4, cnt) VALUES (31316, 0, 557158967, 261470594, 1) ON DUPLICATE KEY UPDATE cnt = cnt+1 ``` Occasionally (about 5% of inserts) I get " Duplicate entry '[key numbers]' for key 1 What could be the problem? Isn't the ON DUPLICATE KEY UPDATE part supposed to handle the duplicate key? Update: adding create a table of the real table ``` CREATE TABLE `linked_mem` ( `li_sid` int(10) unsigned NOT NULL default '0', `li_id1` int(10) unsigned NOT NULL default '0', `li_cid1` int(10) unsigned NOT NULL default '0', `li_id2` int(10) unsigned NOT NULL default '0', `li_cid2` int(10) unsigned NOT NULL default '0', `cnt` int(10) unsigned NOT NULL default '1', `li_filter` int(10) unsigned NOT NULL default '0', `li_group` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`li_id1`,`li_sid`,`li_cid1`,`li_cid2`,`li_group`,`cnt`,`li_id2`) ) ENGINE=MEMORY DEFAULT CHARSET=latin1 ```

Original source