Microsoft SQL Node Execute none queries

Zetanova · July 17, 2024, 1:40pm

The current ms sql node 1.1 in n8n 1.47.1 has a problem with executing none query commands.

I want to execute backup commands and the result type is not handled correctly.

Command:
BACKUP LOG mydb
TO URL = ‘s3://gateway.storjshare.io/my-bucket/mydb_20240717_02.trn’
WITH FORMAT, MAXTRANSFERSIZE = 10485760, STATS = 10, COMPRESSION, ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = mydb_2024);

Result:

{
  "errorMessage": "Internal error",
  "errorDetails": {},
  "n8nDetails": {
    "n8nVersion": "1.47.1 (Self Hosted)",
    "binaryDataMode": "default",
    "stackTrace": [
      "TypeError: result is not iterable",
      "    at Object.execute (/usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/dist/nodes/Microsoft/Sql/MicrosoftSql.node.js:218:41)",
      "    at processTicksAndRejections (node:internal/process/task_queues:95:5)",
      "    at Workflow.runNode (/usr/local/lib/node_modules/n8n/node_modules/n8n-workflow/dist/Workflow.js:728:19)",
      "    at /usr/local/lib/node_modules/n8n/node_modules/n8n-core/dist/WorkflowExecute.js:664:51",
      "    at /usr/local/lib/node_modules/n8n/node_modules/n8n-core/dist/WorkflowExecute.js:1076:20"
    ]
  }
}

SQL Studio Result is composed of only message output:
100 percent processed.
Processed 8 pages for database ‘mydb’, file ‘mydb_log’ on file 1.
BACKUP LOG successfully processed 8 pages in 0.584 seconds (0.100 MB/sec).

Completion time: 2024-07-17T15:28:14.7160653+02:00

n8n · July 17, 2024, 1:40pm

It looks like your topic is missing some important information. Could you provide the following if applicable.

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

Zetanova · July 17, 2024, 1:42pm

n8n version: 1.47.1
Database (default: SQLite): postgres
n8n EXECUTIONS_PROCESS setting (default: own, main): main
Running n8n via (Docker, npm, n8n cloud, desktop app): Docker
Operating system: ubuntu

ria · July 19, 2024, 3:12pm

Hi @Zetanova

We have just put better error logging in place with a recent release - if you could update to 1.51.0 and try again and see what the error says?

We may not support all SQL admin queries, but need to make sure first if this could really be the issue here.

Thanks

Zetanova · July 21, 2024, 3:01pm

Here here error output, the error message is the same, would be nice to be able to execute no query operations for maintenance and other things.

n8n version

1.50.1 (Self Hosted)

Stack trace

TypeError: result is not iterable at Object.execute (/usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/dist/nodes/Microsoft/Sql/MicrosoftSql.node.js:218:41) at processTicksAndRejections (node:internal/process/task_queues:95:5) at Workflow.runNode (/usr/local/lib/node_modules/n8n/node_modules/n8n-workflow/dist/Workflow.js:725:19) at /usr/local/lib/node_modules/n8n/node_modules/n8n-core/dist/WorkflowExecute.js:673:51 at /usr/local/lib/node_modules/n8n/node_modules/n8n-core/dist/WorkflowExecute.js:1085:20

Jon · July 22, 2024, 4:04pm

Hey @Zetanova,

That looks to be 1.50.1 can you try 1.51.0?

Zetanova · July 22, 2024, 7:28pm

Current latest : 1.50.1
Current next : 1.51.0

Then we need to wait that the version will go into prod.
I need to rebuild the docker image only to add a ssl certificate into the cert store.

Jon · July 23, 2024, 2:21pm

Hey @Zetanova,

Instead of making a new docker image you can now just add your certificates to a volume mounted at /opt/custom-certificates and it should make life a bit easier (more info on that here: Configure n8n to use your own certificate authority | n8n Docs)

I suspect the issue here could be that the underlying package we use doesn’t support running commands or the response is not being handled correctly. I will see if we can set up SQL locally to do some testing as well.

thajesta · July 26, 2024, 8:30pm

Hi,

I get the same error when I use the SQL node for executing an insert query.

This is the workflow I’m using:

n8n version: 1.51.1
Database (default: SQLite): Sql Server
n8n EXECUTIONS_PROCESS setting (default: own, main): main
Running n8n via (Docker, npm, n8n cloud, desktop app): n8n cloud
Operating system: Windows

nineforty5 · July 27, 2024, 4:20am

I get the same error. As a workaround, I just add a second select query after the insert/update and no error is reported.

e.g.

SELECT 1 AS result;

thajesta · July 27, 2024, 5:47am

Hi @nineforty5,

That doesn’t work for me. The only way to fix this, is to set the setting for ‘On Error’ on ‘Continue’. This is for me the only way to let the workflow work without it to stop.

Zetanova · August 5, 2024, 6:16pm

@Jon thanks for the certificate tip, n8n starts with `

Trusting custom certificates from /opt/custom-certificates.
ERROR: Access denied '/opt/custom-certificates'
User settings loaded from: /home/node/.n8n/config
Initializing n8n process
n8n ready on 0.0.0.0, port 5678
...

I exec into the container and checked the permissions (644), i could list the file and read the pem content from the directory

The issue is still persisting in 1.52.2, the command is executed successfully but the node simply does not handle the result type.

@nineforty5 the workaround with a second command does work for sql backup command. It will error on failure and succeed on completion.

{
  "errorMessage": "Internal error",
  "errorDetails": {},
  "n8nDetails": {
    "n8nVersion": "1.52.2 (Self Hosted)",
    "binaryDataMode": "default",
    "stackTrace": [
      "TypeError: result is not iterable",
      "    at Object.execute (/usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/dist/nodes/Microsoft/Sql/MicrosoftSql.node.js:218:41)",
      "    at processTicksAndRejections (node:internal/process/task_queues:95:5)",
      "    at Workflow.runNode (/usr/local/lib/node_modules/n8n/node_modules/n8n-workflow/dist/Workflow.js:728:19)",
      "    at /usr/local/lib/node_modules/n8n/node_modules/n8n-core/dist/WorkflowExecute.js:673:51",
      "    at /usr/local/lib/node_modules/n8n/node_modules/n8n-core/dist/WorkflowExecute.js:1085:20"
    ]
  }
}

Elkozel · October 1, 2024, 9:07pm

I had the same issue with the permissions for certificates, but it turned out that the directory itself (/opt/custom-certificates) that did not have the correct permissions (for me the permissions were ‘root:root’). Please make sure that the mapped volume directory (the ./pki directory in the example) has the correct permissions.