Bug #93315 Error while Updating values to NULL
Submitted: 23 Nov 2018 11:23 Modified: 5 Dec 2018 0:08
Reporter: Chirag Shah (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:Connector for Node.js Severity:S2 (Serious)
Version:8.0.13 OS:Any
Assigned to: Rui Quelhas CPU Architecture:Any
Tags: MySQL, NodeJS, XDevAPI

[23 Nov 2018 11:23] Chirag Shah
Description:
I am unable to update values to NULL using Connector for Node.js

This is my code block
-----
let UpdateData = async (tableName, setValues, where) => {
  try {
    let data = await GetTable(tableName); //Here it will get return me table object.
    let { table, session } = data;
    let updateTable = table.update().where(where); 
    //here multiple set values available
    setValues.forEach(e => {
      updateTable = updateTable.set(e.column, e.value);
    });

    let update = await updateTable.execute();
    session.close();
    return {
      result: GetResult(update)
    };
  } catch (ex) {
    console.log(ex);
    return null;
  }
};
-----
{ Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',`isActive`=TRUE WHERE (`id` = 1)' at line 1
    at SqlResultHandler.BaseHandler.(anonymous function) (D:\CodeBase\GitKraken\BitBucket\MSSP\Vue\vue-apexa-portal\node_modules\@mysql\xdevapi\lib\Protocol\ResponseHandlers\BaseHandler.js:113:19)
    at Array.entry (D:\CodeBase\GitKraken\BitBucket\MSSP\Vue\vue-apexa-portal\node_modules\@mysql\xdevapi\lib\Protocol\ResponseHandlers\BaseHandler.js:90:29)
    at WorkQueue.process (D:\CodeBase\GitKraken\BitBucket\MSSP\Vue\vue-apexa-portal\node_modules\@mysql\xdevapi\lib\WorkQueue.js:75:19)
    at Client.handleServerMessage (D:\CodeBase\GitKraken\BitBucket\MSSP\Vue\vue-apexa-portal\node_modules\@mysql\xdevapi\lib\Protocol\Client.js:222:21)
    at Client.handleNetworkFragment (D:\CodeBase\GitKraken\BitBucket\MSSP\Vue\vue-apexa-portal\node_modules\@mysql\xdevapi\lib\Protocol\Client.js:266:14)
    at TLSSocket.stream.on.data (D:\CodeBase\GitKraken\BitBucket\MSSP\Vue\vue-apexa-portal\node_modules\@mysql\xdevapi\lib\Protocol\Client.js:72:36)
    at emitOne (events.js:116:13)
    at TLSSocket.emit (events.js:211:7)
    at addChunk (_stream_readable.js:263:12)
    at readableAddChunk (_stream_readable.js:250:11)
  info:
   { severity: 0,
     code: 1064,
     sqlState: '42000',
     msg: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \',`isActive`=TRUE WHERE (`id` = 1)\' at line 1' } }

How to repeat:
table.update().where(`id=1`).set("name",null).set("isActive",1).execute();

Here I tried null and undefined both values but it didn't work.
I don't know how to update value with NULLinto database using this connector.

Suggested fix:
null/undefined from JS can automatically convert to NULL into MySQL.
[5 Dec 2018 0:08] Philip Olson
Posted by developer:
 
Fixed as of the upcoming MySQL Connector/Node.js 8.0.14 release, and here's the changelog entry:

Attempting to use false-like values such as 0, false, null, and undefined
would emit errors when inserting a new table row. Additionally, now
boolean values become numeric values (true=1, false=0) while null and
undefined are converted to MySQL's NULL type.

Thank you for the bug report.