Hello -
I am running N8N self hosted version 1.56.2 with MySQL node version 2.4 (Latest) and I am having issues with the MySQL Execute node. When I run the node below, I am getting null values passed in the middle of the query. Any reason why it is replacing $19 with null? If I run the update in MySQL directly it seems to work OK.
Thank you
It looks like your topic is missing some important information. Could you provide the following if applicable.
Hello @n8n_rookie ,
Thanks for bringing to our attention your issue.
I believe the issue you are facing is because $ may be used to represent parameters passed into. So $19 is being tried to be replaced and as it does not exist it may result as null.
Can you try to escape it? If you try to escape it with backtick `, any luck on doing it?
I was checking the code from GIt n8n/packages/nodes-base/nodes/MySql/v2/actions/common.descriptions.ts at 7fd0c71bdc0e3d15902d100368ffe777ccf81adb · n8n-io/n8n · GitHub
This is where I am basing my assumptions to check and validate.
I hope this points to the right way. Let us know if this helps.
Cheers!
Wow GREAT CATCH - I will try that out. Curious is that a bug or something standard? I haven’t seen an backtick in SQL like that before except when a table name has spaces
Hey @n8n_rookie ,
Thanks for replying back.
Try not only with backtick but also backslash . We would need to check further to understand if this is a bug or if the Node expect the users to escape characters when needed.
We look forward to hearing from you.
Flavio
Unfortunately still no luck. I am doing the swap in the MySQL node by instead of using:
{{ $json.message.content }}
I am using (assuming $ value should be something like $754.39):
{{ $json.message.content.replaceAll(‘$’, ‘$$’) }} ---- This one will put NULL.39 instead of $754.39
{{ $json.message.content.replaceAll(‘$’, '$') }} ---- This one will put NULL.39 …
{{ $json.message.content.replaceAll(‘$’, ‘$’) }} ---- This one will put NULL.39 …
Again, I appreciate any help! I realize there is a sloppy workaround available (I could put a placeholder and than replace the placeholder text later on) but I really think N8N can probably handle this a better way
Thanks for coming back and for sharing the detailed steps you have tried.
I was actually thinking in a different way(I should have provided details, my bad).
Can you try to add the escape like my example below:
{{ $json.message.content.replace(/\$/g, '\\$') }}
I tested this in a javascript online editor and it replaced “Iphone only $19.88” for “Iphone only \$19.88”.
This is what I think that could help.
Another option, instead of escape the $ character would be to use Parameterized Queries(But I am not sure we can use it via n8n by heart, need to be checked). That would mean to use something like:
INSERT INTO your_table (column_name) VALUES (?)
and then pass the string "“Iphone only $19.88” as value.
Hmmm so odd, even with:
{{ $json.message.content.replace(/$/g, ‘\$’) }}
I am still getting:
NULL.39
To make sure we are on the right path, I tried:
{{ $json.message.content.replace(/$/g, ‘TESTTESTTEST’) }}
and this does indeed put TESTTESTTEST754.39 (which is expected!)
Hello @n8n_rookie ,
Thanks for the quick attempted.
I quickly compared and I see that in the previous comment my suggestion was missing one extra backslash. CAN you try:
{{ $json.message.content.replace(/$/g, ‘\\$’) }}
Adding the image of my test for reference here:
Error: invalid syntax at Expression.renderExpression
hmmm… I need to keep messing with this something seems off
So I am assuming you tried with the 2 backslash as per my last suggestion and now you got this different error.
Can you share a simplified workflow INPUT ==> MySQL that the issue happens? So we can have extra pair of eyes to check if something is missing?
I will see if I can reproduce something based on the image already shared. But having your workflow would be more precise. Don’t give up!
Flavio
Thank you! Giving your advice (including not giving up!) got it to work!
The long term solution is to use Paramter Queries like you hinted towards originally.
Here is a VERY simple example where I am skipping not important details:
Using MySQL execute SQL, change the options to be Query parameters, include the expression that you want to include (no need to worry about special characters!) and in the Query include a parameter (IE: $1 for the first parameter) and it JUST WORKS! Magic.
Shoutout to @Flavio_Orfano for all the help!
Great news you shared! I am super happy the discussions we had here helped you to narrow down to the solution.
And thank you for sharing the details so others facing similar matter can benefit from it.
All the best and I wish you all a great week ahead.
Flavio
I also solved this issue by using query parameters (which are safer anyway), but I wanted to point out there is definitely something weird happening when you try to escape parameters ($1, etc) in SQL queries. Is there anyway to fix that?
For example, if you try to insert a cash amount as text like “$13” into an SQL table, n8n will replace that text with NULL. I tried all the suggestions listed in the thread - backticks, backslashes, etc - but none of them worked. The only thing I could get to work was separating the dollar sign from the number and then using REPLACE to put the text back. So you’d use something like: REPLACE('$ 13', '$ ', '$')
Here’s an example workflow:
Is there anyway to escape these query parameters in SQL queries?
