Geek Answers Handbook

Mysql Repeating login error on ON DUPLICATE KEY UPDATE

I am using a memory table . It has several identifiers and a counter of all data - integers. My code updates the counter by 1 if data exists or creates a line with counter = 1 if not.

I am using the following query:

INSERT INTO linked_mem
        ( id1, id2, id31, id4 cnt)
        VALUES (31316, 0, 557158967, 261470594, 1)
        ON DUPLICATE KEY UPDATE cnt= cnt+1

Sometimes (about 5% of the inserts) I get "Duplicate entry" [key numbers] for key 1

What could be the problem? Shouldn't the ON DUPLICATE KEY UPDATE part handle the duplicate key?

Update: adding a table to create a 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
+5
source share
5 answers

This can happen if you update the field with a checkmark UNIQUEand a violation of the second key occurs on UPDATE.

, .

:

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

cnt PRIMARY KEY.

+10

cnt , , / .

+1

, ? cnt, .

, , , , .

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

, (1, 1, 1, 1, 1) (1, 1, 1, 1, 2), .

+1

, , . , ? ? , , ; , PK, .

0

.

. DUPLICATE - :

, .

0

More articles:


All Articles