Describe the problem/error/question
I’m trying to use query parameters within my MySQL node called “Upsert lead”.
However, it seems that the “Query Parameters” setting has some bugs:
-
nullvalues are being converted to''(empty strings) in the output query instead of staying asnull, even when the “Replace Empty Strings with NULL” option is enabled.
In fact, this setting doesn’t seem to work at all. -
Fields appear to shift positions when there are empty fields in between.
-
If none of the fields are
nullor empty, everything works as expected.
What is the error message (if any)?
INSERT INTO ld_leads (
email,
campaign_id,
first_name,
last_name,
company_id,
job_title
) VALUES (
'[email protected]',
1,
1,
'',
'',
''
)
ON DUPLICATE KEY UPDATE
campaign_id = IFNULL(NULLIF('', ''), campaign_id),
first_name = IFNULL(1, first_name),
last_name = IFNULL(1, last_name),
company_id = IFNULL(NULLIF('', ''), company_id),
job_title = IFNULL(NULLIF('', ''), job_title);
Please share your workflow
{
"nodes": [
{
"parameters": {
"operation": "executeQuery",
"query": ,
"options": {
"queryReplacement":
"replaceEmptyStrings": true
}
},
"type": "n8n-nodes-base.mySql",
"typeVersion": 2.5,
"position": [
448,
-48
],
"id": "b0d880ea-86f8-478e-8748-2cbbc4491990",
"name": "Upsert lead",
"credentials": {
"mySql": {
"id": "1jPH9m4KLMjZ6Qnk",
"name": "MySQL | atspub_dev"
}
}
},
{
"parameters": {
"operation": "select",
"table": {
"__rl": true,
"value": "ld_leads",
"mode": "list",
"cachedResultName": "ld_leads"
},
"returnAll": true,
"where": {
"values": [
{
"column": "email",
"value": "={{ $('Sub-Flow Trigger').item.json.email }}"
}
]
},
"options": {
"detailedOutput": false
}
},
"type": "n8n-nodes-base.mySql",
"typeVersion": 2.5,
"position": [
672,
-144
],
"id": "3b4c902f-a22c-4fd8-b1f3-d2123015a864",
"name": "Return row",
"credentials": {
"mySql": {
"id": "1jPH9m4KLMjZ6Qnk",
"name": "MySQL | atspub_dev"
}
}
},
{
"parameters": {
"workflowInputs": {
"values": [
{
"name": "update_empty_fields",
"type": "boolean"
},
{
"name": "campaign_id",
"type": "number"
},
{
"name": "email"
},
{
"name": "first_name"
},
{
"name": "last_name"
},
{
"name": "company_id",
"type": "number"
},
{
"name": "job_title"
}
]
}
},
"type": "n8n-nodes-base.executeWorkflowTrigger",
"typeVersion": 1.1,
"position": [
0,
-144
],
"id": "7a931d60-b697-4988-806f-2f77b3342615",
"name": "Sub-Flow Trigger"
},
{
"parameters": {
"conditions": {
"options": {
"caseSensitive": true,
"leftValue": "",
"typeValidation": "strict",
"version": 2
},
"conditions": [
{
"id": "5d9f9cd1-20e2-4f1e-85a3-f9b00c0f8cba",
"leftValue": "={{ $json.update_empty_fields }}",
"rightValue": "",
"operator": {
"type": "boolean",
"operation": "true",
"singleValue": true
}
}
],
"combinator": "and"
},
"options": {}
},
"type": "n8n-nodes-base.if",
"typeVersion": 2.2,
"position": [
224,
-144
],
"id": "a348f793-b116-4175-9c86-826dd0a788ba",
"name": "Update empty fields?"
},
{
"parameters": {
"operation": "upsert",
"table": {
"__rl": true,
"value": "ld_leads",
"mode": "list",
"cachedResultName": "ld_leads"
},
"dataMode": "defineBelow",
"columnToMatchOn": "email",
"valueToMatchOn": "={{ $json.email }}",
"valuesToSend": {
"values": [
{
"column": "campaign_id",
"value": "={{ $json.campaign_id }}"
},
{
"column": "first_name",
"value": "={{ $json.first_name }}"
},
{
"column": "last_name",
"value": "={{ $json.last_name }}"
},
{
"column": "company_id",
"value": "={{ $json.company_id }}"
},
{
"column": "job_title",
"value": "={{ $json.job_title }}"
}
]
},
"options": {
"replaceEmptyStrings": true
}
},
"type": "n8n-nodes-base.mySql",
"typeVersion": 2.5,
"position": [
448,
-240
],
"id": "49ef0397-5de5-42a5-8aa7-7bd5c4a5278e",
"name": "Upsert lead | Update empty fields",
"credentials": {
"mySql": {
"id": "1jPH9m4KLMjZ6Qnk",
"name": "MySQL | atspub_dev"
}
}
}
],
"connections": {
"Upsert lead": {
"main": [
[
{
"node": "Return row",
"type": "main",
"index": 0
}
]
]
},
"Return row": {
"main": [
[]
]
},
"Sub-Flow Trigger": {
"main": [
[
{
"node": "Update empty fields?",
"type": "main",
"index": 0
}
]
]
},
"Update empty fields?": {
"main": [
[
{
"node": "Upsert lead | Update empty fields",
"type": "main",
"index": 0
}
],
[
{
"node": "Upsert lead",
"type": "main",
"index": 0
}
]
]
},
"Upsert lead | Update empty fields": {
"main": [
[
{
"node": "Return row",
"type": "main",
"index": 0
}
]
]
}
},
"pinData": {
"Sub-Flow Trigger": [
{
"update_empty_fields": false,
"campaign_id": 1,
"email": "[email protected]",
"first_name": "",
"last_name": "",
"company_id": 1,
"job_title": ""
}
]
},
"meta": {
"templateCredsSetupCompleted": true,
"instanceId": "642fefb0ab25e779a8a9e8ccbe2dd6f06e6f7878da88ff09da8e1f54695ffea6"
}
}
Share the output returned by the last node
INSERT INTO ld_leads (
email,
campaign_id,
first_name,
last_name,
company_id,
job_title
) VALUES (
'[email protected]',
1,
1,
'',
'',
''
)
ON DUPLICATE KEY UPDATE
campaign_id = IFNULL(NULLIF('', ''), campaign_id),
first_name = IFNULL(1, first_name),
last_name = IFNULL(1, last_name),
company_id = IFNULL(NULLIF('', ''), company_id),
job_title = IFNULL(NULLIF('', ''), job_title);
Information on your n8n setup
- n8n version: 1.105.4
- Database (default: SQLite): Default
- n8n EXECUTIONS_PROCESS setting (default: own, main): No idea what this is
- Running n8n via (Docker, npm, n8n cloud, desktop app): Docker
- Operating system: Ubuntu 22.04