Trouble updating rows in node-mysql

Question

I've just started with node-mysql, and have created a database 'users'. I can select and create just fine, following instructions from the repository. The database currently has values

[ { id: 1, user_login: 'mwilson' },
  { id: 2, user_login: 'newbie' } ]

and fields [id, user_login, user_nicename].

However when I run the update function to update the second user, my results show me that I have 1 row matched but 0 rows changed, and SELECT confirms that there was no update.

My code:

connection.query('UPDATE users SET user_nicename = ? WHERE user_login = ?', ['New User','newbie'],
 function(err, results) {
  if(err) {
    console.log("Failed on query",err);
  } else {
  console.log(results);

Results:

{ fieldCount: 0,
  affectedRows: 1,
  insertId: 0,
  serverStatus: 34,
  warningCount: 0,
  message: '(Rows matched: 1  Changed: 0  Warnings: 0',
  protocol41: true,
  changedRows: 0 }

And after connection.query('SELECT id, user_login FROM users'):

[ { id: 1, user_login: 'mwilson' },
  { id: 2, user_login: 'newbie' } ]

The closest other question I could find to mine is Node.js mysql query syntax issues UPDATE WHERE. My code follows the format given by the first answer; other attempts gave query errors. I also saw a post from someone that got this result when trying to increment a NULL field.

If I use WHERE id = 2 I get the same error. My configuration parameters use an account I've given permissions to (GRANT ALL PRIVILEGES ON testdb.* TO 'dev'@'%' IDENTIFIED BY 'dev';)

Any thoughts on how I can update my update?

EDIT: To form the database I ran:

CREATE TABLE users( 
   id int auto_increment primary key, 
   user_login varchar(25), 
   user_nicename varchar(75) 
);

So by default the user fields aside from id are NULL.

You're updating `user_nicename` which you're not showing in your select or sample data. What value does that field have before/after the update? — Joachim Isaksson, Dec 06 '13 at 05:36
Just NULL. I only ran `'INSERT INTO users SET ?', {user_login: 'newbie'}` to create the second row. — arilaan, Dec 06 '13 at 05:45
Yes, but you're not showing `user_nicename` after the update, your `SELECT` only selects `id` and `user_login`. — Joachim Isaksson, Dec 06 '13 at 07:11
Wow! There it is. By changing my SELECT to * it works. I did check mysql before and the nicename hadn't been updated; now after running that select the result shows the change (Rows matched: 1 Changed: 1 Warnings: 0'). Thanks! — arilaan, Dec 06 '13 at 07:37

score 0 · Accepted Answer · answered Dec 06 '13 at 07:46

Following a comment I ran SELECT *, rather than just SELECT id, user_login, before UPDATE. It's interesting because my select and update scripts are in different scripts (and thus different connections). I would have thought I had just missed the update (of course!), but when I ran UPDATE again with no changes my results were

Rows matched: 1  Changed: 1  Warnings: 0

Trouble updating rows in node-mysql

1 Answers1