UPDATE SQL Query returning misleading response

Question

I am doing a simple UPDATE SQL Query into a database using nodejs as shown below:

conn.query("UPDATE room SET `join_id`=? WHERE `id`=?", [join, cfid], function (e, r) {
    console.log(e, r);
    //e null; r object;
});

The response r from the query returns this:

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

Yet after the OKPacket response, I see no change in the database and therefore the rest of my code malfunctions. I've spent hours researching on this issue and found just a question from two years ago which was not concluded: here

Answer 1

My guess is that you have autocommit turned off, perhaps in the MySQL server's my.cnf file or some other way. To check, run code along the lines of this:

let sql = "SHOW GLOBAL variables LIKE 'autocommit'";
connection.query(sql, function(err, result) {
  console.log(result, 'global');
});

let sql2 = "SHOW variables LIKE 'autocommit'";
connection.query(sql2, function(err, result) {
  console.log(result, 'local');
});

Your console should output

[ RowDataPacket { Variable_name: 'autocommit', Value: 'ON' } ] 'global'
[ RowDataPacket { Variable_name: 'autocommit', Value: 'ON' } ] 'local'

But if autocommit is 'OFF', you have your answer.