Q:

Mysql Duplicate entry error on ON DUPLICATE KEY UPDATE

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

A:

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.

