CODEWITHSUNDEEP
mysqlnode.js
mattr
mattr

Reputation: 5518

Should "ON DUPLICATE KEY UPDATE" throw an error if it fails to update a UNIQUE KEY?

I'm having an issue where when I use "ON DUPLICATE KEY UPDATE" with a UNIQUE field there is a silent failure. Here is the scenerio:

1) I have a mapping table like this:

CREATE TABLE index_user_username (
    username VARCHAR(255) NOT NULL UNIQUE,
    id INT NOT NULL UNIQUE,
    PRIMARY KEY (username, id)
);

notice username is marked as UNIQUE

2) I insert a few entries:

mysql> INSERT INTO index_user_username (username,id) VALUES ('dude', 1) ON DUPLICATE KEY UPDATE username=VALUES(username);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO index_user_username (username,id) VALUES ('man', 2) ON DUPLICATE KEY UPDATE username=VALUES(username);
Query OK, 1 row affected (0.00 sec)

here is my sanity check on the db:

mysql> SELECT * FROM index_user_username;
+----------+----+
| username | id |
+----------+----+
| dude     |  1 |
| man      |  2 |
+----------+----+
2 rows in set (0.00 sec)

3) I update one of the rows as one of the other's username but i dont get a failure (I get "Query OK"):

mysql> INSERT INTO index_user_username (username,id) VALUES ('dude', 2) ON DUPLICATE KEY UPDATE username=VALUES(username);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM index_user_username;
+----------+----+
| username | id |
+----------+----+
| dude     |  1 |
| man      |  2 |
+----------+----+
2 rows in set (0.00 sec)

So my main question is, shouldn't this throw an error? And if not, any suggestions on options for work arounds (also, see note below)? Also, I'm running version 5.6.13

Clarification: the "id" field maps to another table. this is a unique username-to-userId mapping table, essentially.

as a side note: I did notice that in the command-line I get a response telling me how many rows are affected and thought that I could do some checking and throw an error in my code if 0 affected rows comes up. However, I implemented this all using the node.js module (https://github.com/felixge/node-mysql) and it seems to return the same response for all of the above INSERT statements:

{ fieldCount: 0,
    affectedRows: 1,
    insertId: 0,
    serverStatus: 2,
    warningCount: 0,
    message: '',
    protocol41: true,
    changedRows: 0 }

Any help figuring out why this seems to be giving me the wrong feedback would be helpful as well.

Upvotes: 0

Views: 2828

Answers (3)

mattr
mattr

Reputation: 5518

Ok after some investigation, I think i found my answer. From the mysql docs on "INSERT ... ON DUPLICATE KEY UPDATE Syntax"

In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes.

(I assume it is partially because of the ambiguity in multiple matches on UNIQUEs like in my case)

Also, from the node.js side of things, I believe I found a work-around that allows me to figure out what happened after the update and respond accordingly. I found some issues/pulls that describe similar problems I was having, for example:

which pointed me to blacklisting FOUND_ROWS:

So, given:

CREATE TABLE index_user_username (
    username VARCHAR(255) NOT NULL UNIQUE,
    id INT NOT NULL UNIQUE,
    PRIMARY KEY (username, id)
);

if I have the following script:

var mysql      = require('mysql');
var connection = mysql.createConnection('mysql://root:rootpass@localhost/user?flags=-FOUND_ROWS');
connection.connect()
connection.query('INSERT INTO index_user_username (username,id) VALUES (\'dude\', 1) ON DUPLICATE KEY UPDATE username=VALUES(username)', function(err,result) {
  console.log('1:',result.affectedRows)
  connection.query('INSERT INTO index_user_username (username,id) VALUES (\'man\', 2) ON DUPLICATE KEY UPDATE username=VALUES(username)', function(err,result) {
    console.log('2:',result.affectedRows)
    connection.query('INSERT INTO index_user_username (username,id) VALUES (\'dude\', 2) ON DUPLICATE KEY UPDATE username=VALUES(username)', function(err,result) {
      console.log('3:',result.affectedRows)
      connection.query('INSERT INTO index_user_username (username,id) VALUES (\'dude\', 3) ON DUPLICATE KEY UPDATE username=VALUES(username)', function(err,result) {
        console.log('4:',result.affectedRows)
        connection.end()
      })
    })
  })
})

I get the output:

1: 1
2: 1
3: 0
4: 0

which is what I need, where previously I was getting:

1: 1
2: 1
3: 1
4: 1

Which didnt give me feeback about what just happened.

Upvotes: 0

user207421
user207421

Reputation: 311018

The ON DUPLICATE KEY clause has no need to update the unique key. It's already there. Just remove that column from the update.

Upvotes: 0

Mihai
Mihai

Reputation: 26794

But it did update with the same name.Unique key only prevents two identical values and not an update with the same value.Dont you mean

ON DUPLICATE KEY UPDATE id=VALUES(id)

Upvotes: 0

Related Questions