Mysql Duplicate entry error on ON DUPLICATE KEY UPDATE

Question

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

Answer 1

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.

Answer 2

Are you sure that the primary key is correct? Using this primary key identifies a line also by the value of cnt, which is supposed to be a counter.

I've not tested this, but I think the following query will give the error if you start with an empty table.

INSERT INTO linked_mem
    ( id1, id2, id31, id4, cnt)
VALUES 
    (1, 1, 1, 1, 1),                  // (1, 1, 1, 1, 1) added
    (1, 1, 1, 1, 1),                  // (1, 1, 1, 1, 1) -> (1, 1, 1, 1, 2)
    (1, 1, 1, 1, 1),                  // (1, 1, 1, 1, 1) added (now two rows)
    (1, 1, 1, 1, 1)                   // error
ON DUPLICATE KEY UPDATE cnt = cnt+1

at the fourth row, the (1, 1, 1, 1, 1) would be updated to (1, 1, 1, 1, 2), but this already exists.