| Bug #93343 | Error while updating TINYINT value using either true/false or 1/0 | ||
|---|---|---|---|
| Submitted: | 26 Nov 2018 10:56 | Modified: | 28 Nov 2018 9:54 |
| Reporter: | Chirag Shah (OCA) | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | Connector for Node.js | Severity: | S2 (Serious) |
| Version: | 8.0.13 | OS: | Windows |
| Assigned to: | Rui Quelhas | CPU Architecture: | Any |
| Tags: | MySQL, node.js, XDevAPI | ||
[27 Nov 2018 18:16]
Rui Quelhas
Hi Chirag,
thanks for the report and for your interest in MySQL and Connector/Node.js
A few of things about what you are experiencing.
First of all, it's weird that insert() is working for all those scenarios. Looks like there is a bug when you try to call insert(false) or insert([false]).
It should result in the following error:
Error: invalid input expression
This is sort of what is also happening with update(). Using update().set('isActive', true) or update().set('isActive', 1) seems to work though.
On the same page, insert().values(true)/insert().values(1)/insert().values([true]) also works.
I believe your example is sort of "upside down". Can you confirm that?
On the other hand, values like '0', '1', 'true' or 'false' are regular JavaScript strings, so they should not work when the column is a TINYINT. From what I can tell, there's no reason to have special treatment for them. For a comprehensive list of how each JS type matches a MySQL data type, please check the "Column Types" table available in the docs (https://dev.mysql.com/doc/dev/connector-nodejs/8.0/tutorial-Working_with_Tables.html).
[28 Nov 2018 5:15]
Chirag Shah
Hi Rui,
I refer "Column Types" in link that you given.
As per "Column Types" I need to use Number for TINYINT.
So I use following two examples.
My Code
-----
Insert
---
let obj = { columns: [ 'name', 'isActive' ],values: [ 'Chirag', 1 ] };
let {columns, values} = obj;
table.insert(columns).values(values).execute();
Result:
This one executes without any issue.
Update
---
let obj = { setValues: [ { column: 'name', value: 'Chirag' }, { column: 'isActive', value: 0 } ], where: 'id = 5' };
let {setValues, where} = obj;
let updateTable = table.update().where(where);
setValues.forEach(e => {
updateTable = updateTable.set(e.column, e.value);
});
let update = await updateTable.execute();
Result:
{ 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 'WHERE (`id` = 5)' 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 \'WHERE (`id` = 5)\' at line 1' } }
My query
---
So if as per "Column Types" I need to use Number type only, should I stop trying(I am not saying using because I want to make sure how many possibilities are working) other types(like String or Boolean)?
Even using Number(in Node.JS) for TINYINT(in MySQL), I am unable to update value using update().set().
[28 Nov 2018 9:54]
Rui Quelhas
Hi Chirag,
as discussed on the community Slack.
Inserting and updating falsy values (0 or false) seems to not work as expected, and it's a bug. The same can't be said for truthy values (1 or true), which seem to work fine.
Regarding values such as "true", "false". Those are JavaScript Strings which and we can't expect them to be coerced into a TINYINT or some other JavaScript Number matching MySQL data type. "0" and "1" seem to be coerced at the plugin level though, so there's nothing the client can't do about it.
One example of why that's a bad idea is, for instance, if instead of using a TINYINT to represent boolean values, you use a ENUM('true', 'false'). While inserting or updating values, the client does not have a clue about the column metadata, so it can't coerce on-demand.
I'll be closing this one since you already created a new bug report (BUG#93381) that focuses on the specific issue at hand, which is the fact that falsy values don't work with insert() and update().
Thanks again.
Description: Column Type: TINYINT I use this column type to store Boolean values into table. While Inserting ----------- table.insert(['isActive']).values([false]).execute(); //use either true or false table.insert(['isActive']).values([0]).execute(); //use either 0 or 1 table.insert(['isActive']).values(['0']).execute(); //use either '0' or '1' These all three working perfectly. but when I try to update column value, I can update using only '0' which is in string format. It gives me error when I try to update with value false or 0 which is in boolean and number format respectively. 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 'WHERE (`id` = 19)' 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 \'WHERE (`id` = 19)\' at line 1' } } How to repeat: table.update().where('id = 19').set('isActive', true).execute(); // not working table.update().where('id = 19').set('isActive', 1).execute(); //not working table.update().where('id = 19').set('isActive', '1').execute(); //working perfectly Suggested fix: As it is working in insert() operation I think it should same work in update() operation as well. It should able to accept 0,false,'0' all three types of values.