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.
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