Breaking changes in node-red-node-mysql dependency

[aitor-develop]

Current Behavior

node-red-node-mysql returns parsed JSON instead of string when column contains JSON-formatted text

I noticed this issue after rebuilding my Node-RED Docker container. My application had been working correctly, but after recreating the container and reinstalling dependencies, the node-red-node-mysql node started returning different data for the same query, despite using the same version of the Node-RED MySQL node.

Specifically, when a database column contains JSON data stored as a string, the node now returns a parsed JavaScript object instead of the original string value. This broke parts of my existing flow that expected the column value to remain a string.

The behavior appears to be related to a change in the mysql2 dependency. While investigating, I found that mysql2 introduced a breaking change regarding JSON handling, apparently: jsonStrings: true config option should be active

Related issue:
sidorares/node-mysql2#4402

Expected Behavior

node-red-node-mysql returns data from a column/table with no parsing

Steps To Reproduce

Perform a Select of a column that contains a stringified json

Environment

• Node-RED version: 5.5.0
• Node.js version: 24

[hardillb]

This needs moving to node-red/node-red-nodes

/cc @dceejay

[knolleary]

I will transfer this issue over to nodes repo as it isn't a core Node-RED issue.

[dceejay]

OK - for now I have pinned back the mysql2 dependency so should now revert.
released as v3.0.1.
Will look at the option to see if it makes sense to expose as an option or just turn off by default and let users use JSON node to parse later if needed.

[dceejay]

OK - have now added the jsonString:true to force the old behaviour with the latest lib.
v3.0.2
(and hard pinned the lib version so it won't change again randomly unless we change it manually)

[aitor-develop]

Thanks Dave!

[arturv2000]

@dceejay I had the opposite behaviour... updated my Node-Red instances to the latest version of Node-Red and the modules in use including node-red-node-mysql from 3.0.0to 3.0.2 and my flows where broken because the MYSQL JSON Columns where no longer being parsed as a JSON Object.

Had to uninstall current version and force the installation of 3.0.0 in order to have the flows working. To maintain the old behaviour it seems that the jsonStrings needs to be set to false instead of true. Changing this directly on the file inside the folder node-red-node-mysql the behaviour is as expected.

[dceejay]

Hmm

looking deeper at the code and the mysql2 discussion - it seems that the oldest default behaviour prior to them adding jsonStrings (PR #2642) was to actually returned parsed data - and so their current default jsonStrings:false (which I now just override) should return the same...

so @aitor-develop - I'm now in a quandary ... I'm not sure how it wasn't returning parsed JSON for you unless there was a mistake in their parser and it just failing for you.

@arturv2000 - is a simpler workaround just to add a JSON node after the node, to then parse the returned data string ?

I guess the real answer is to make it a customisable toggle - which I will try to do soon.

[arturv2000]

In my case the JSON is only one of "columns" returned from the query. Eventually a JSON node could maybe work (not sure), since I am feeding the query result to a function node I could do that "work" inside the function node. But honestly want to avoid that since I have some quite number of query's / functions that "rely" on the MySQL JSON Columns data already be parsed since that was the original behaviour.

One possible option would be to add a setting flag like there is for the connectionLimit something like:

jsonStrings: RED.settings.mysqlJsonStrings ?? false,

The default could be false to keep with the previous behaviour to avoid breaking flows and allows the user to override via the settings file.

[dceejay]

Actually the flag is (IMHO) confusing... you set it to true to NOT parse strings. But there is a gotcha - this is only for mysql type of JSON - if you store a JSON string in a text field type then they are NOT parsed by default...

So if I add a checkbox to set the flag - I think that would just be even more confusing - as then the user would expect it to parse all the time - whether a JSON or string type and would be confused when it didn't.

So yes - I need some more thinking.

[aitor-develop]

so @aitor-develop - I'm now in a quandary ... I'm not sure how it wasn't returning parsed JSON for you unless there was a mistake in their parser and it just failing for you.

I've been using this node for quite a few years now (around 3+ years always with MariaDB 10), and I've always received JSON values as strings. I only noticed the change because, after installing the node in a new nodered instance, some of my flows unexpectedly broke. Is it possible that parsing was failing all this time and I simply wasn't aware of it? Maybe....

Anyway, I think the node should respect the database column types rather than inspect and transform the content itself.

For example, if a column uses a native JSON type (such as in MySQL), returning a JSON object makes sense. However, if a column is VARCHAR, TEXT, or LONGTEXT and happens to contain a stringified JSON document, I would expect it to be returned as a string.

In my case, I'm using MariaDB, which doesn't have a native JSON type, so JSON data is effectively stored as text. Because of that, I naturally expect to get strings back exactly as they are stored and decide myself if and when to parse them within the flow.

This approach also preserves the distinction between actual JSON columns and text columns that merely contain JSON-formatted content. The node returns data according to the schema, and any additional parsing can be done explicitly by the user when needed.

For that reason, I'd prefer not to add another checkbox if possible. From my perspective, the node's responsibility is to return the data according to the database type

[dceejay]

@aitor-develop - so in that case then I should revert it to defaults... jsonStrings: false - which supposedly does try to decode any JSON types - and allegedly always has... until you said it didn't...

Net - for now I'm going to "revert" it to false which is supposedly what it always has been.

Happy to keep trying to understand what has changed in your environment but for now let's revert.