MySQL documentation states:
With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row and 2 if an existing row is updated.
However, while using it, I get a different result. For a same multiple primary unique key, it takes 5 or 6 update before considering like an update and not a new insert. I first thought that it may nodejs with the mysql package which would be bugged?
However, I noticed that through the mysql shell, I get the same type of error:
INSERT INTO table SET `master_id` = 119, `slave_id` = 1221829,
`date` = '2014-09-18', `type` = '42', `threshold` = '18',
`value` = 18.3536, `ratio` = '1.96'
ON DUPLICATE KEY UPDATE value = (value + VALUES(value)) / 2,
ratio = (ratio + VALUES(ratio)) / 2;
Query OK, 1 row affected (0.00 sec)
INSERT INTO alerts SET `master_id` = 119, `slave_id` = 1221829,
`date` = '2014-09-18', `type` = '42', `threshold` = '18',
`value` = 18.3536, `ratio` = '1.96'
ON DUPLICATE KEY UPDATE value = (value + VALUES(value)) / 2,
ratio = (ratio + VALUES(ratio)) / 2;
Query OK, 0 rows affected (0.00 sec)
INSERT INTO alerts SET `master_id` = 119, `slave_id` = 1221829,
`date` = '2014-09-18', `type` = '42', `threshold` = '18',
`value` = 18.3536, `ratio` = '1.96'
ON DUPLICATE KEY UPDATE value = (value + VALUES(value)) / 2,
ratio = (ratio + VALUES(ratio)) / 2;
Query OK, 2 rows affected (0.00 sec)
Is it because it is not advised to use ON DUPLICATE KEY for a multiple primary key as stated below?
In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes.
Or is it because it is the sames values? Or because the interval between two insertions is too short (~ 10 seconds)?
My table has the following primary key: {master_id, slave_id, date, type}
I really don't get it...
