Dynamic MySQL Query Alignment Issue

Questions
1

I am attempting to use a MySQL node to perform 5 separate queries, each on a different database, immediately after an If node. As expected, 6 items pass into the If node and only 5 follow the true path. However, what I am seeing is that the query for the second item is attempting to run on the database for the first item, which creates a MySQL error because the tables can be different between databases. You won’t be able to see the setup of the MySQL credential, so I will share a screenshot using the first item’s data. I am using env variables for some values so they won’t show in the UI, but they are the same each time.

Using the first item as an example, the database name is ‘bthree’ and the query is ‘SELECT COUNT() AS ‘VHOS Units’ FROM company WHERE process = ‘Relationship Management’
', which should be valid. That database table does exist and the query runs in workbench. But what appears to be happening is that the database used is ‘bthree’ but it is attempting to run the query from the second item, which is 'SELECT COUNT() AS ‘VHOS Units’ FROM cases WHERE stage NOT IN (‘Closed’,‘Resolved’)’ and because no cases table exists on the bthree database, an error results. This is odd to me because the correct query is displayed in the preview below the n8n Query input (see screenshot).

Is there a way to force this module and the credential to use the same item’s values? I suspect that something is causing the query and the database to be misaligned. I have got this working using a Batch node after the Split Items node, but I’m trying to simplify and just use the output of the Split Items node. My understanding is that each item should be processed separately (not necessarily sequentially though?)

What is the error message (if any)?

" Problem in node ‘Get VHOS Usage‘

Table ‘bthree.cases’ doesn’t exist"

Here is the credential setup…

2023-09-18_17-54
2023-09-18_17-54874×736 32 KB

Share the output returned by the last node

ERROR: Table ‘bthree.cases’ doesn’t exist

Details

Stack

Error: Table 'bthree.cases' doesn't exist
    at PromiseConnection.query (/usr/local/lib/node_modules/n8n/node_modules/mysql2/promise.js:93:22)
    at /usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/dist/nodes/MySql/MySql.node.js:252:39
    at Array.map (<anonymous>)
    at Object.execute (/usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/dist/nodes/MySql/MySql.node.js:250:42)
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at Workflow.runNode (/usr/local/lib/node_modules/n8n/node_modules/n8n-workflow/dist/Workflow.js:653:28)
    at /usr/local/lib/node_modules/n8n/node_modules/n8n-core/dist/WorkflowExecute.js:590:53

Information on your n8n setup

  • n8n version: 0.221.2
  • Database (default: SQLite): MySQL
  • n8n EXECUTIONS_PROCESS setting (default: own, main):
  • Running n8n via (Docker, npm, n8n cloud, desktop app): Docker
  • Operating system: Linux
2

Hi @hndmn :wave:

Could you try a split in batches loop, splitting the items into batches of 1? That might fix this up :eyes:

3

I think so, yes, but the workflow becomes a bit unwieldy especially when I add more logic branches. I’d much prefer to get it working this way (or with a code node if Split items is not the best option). I’m also wondering if I can explicitly reference an item in the MySql and related credential, like {{ $json[0]… }} or {{ $json[$currentItem]… }} to be certain both are using the intended values.

I appreciate the assistance.