BigQuery insert does not show error when failing due to bad credentials (Service Account)

[pinkasey]

Describe the bug
BigQuery node cannot be used with "Service Account" credentials, at least not the "Create" operation.
When I try to use it with "Service Account" credentials, the node succeeds even though the data is not inserted.
The http response from Google in this case is 401, with an error message in the body, e.g:

{
  "error": {
    "code": 401,
    "message": "API keys are not supported by this API. Expected OAuth2 access token or other authentication credentials that assert a principal. See https://cloud.google.com/docs/authentication",
    "errors": [
      {
        "message": "Login Required.",
        "domain": "global",
        "reason": "required",
        "location": "Authorization",
        "locationType": "header"
      }
    ],
    "status": "UNAUTHENTICATED",
    "details": [
      {
        "@type": "type.googleapis.com/google.rpc.ErrorInfo",
        "reason": "CREDENTIALS_MISSING",
        "domain": "googleapis.com",
        "metadata": {
          "method": "google.cloud.bigquery.v2.TableDataService.InsertAll",
          "service": "bigquery.googleapis.com"
        }
      }
    ]
  }
}

(I tested it here)

To Reproduce
Steps to reproduce the behavior:

1. Create a workflow with a BigQuery node, as per documentation
2. set the BigQuery node to use Service Account credentials
3. run the node
4. See "success" - but nothing is written to BigQuery table

Expected behavior
I expect the error received from BigQuery to show in n8n

Environment (please complete the following information):

• OS: Debian 11
• n8n Version 0.193.3
• Node.js Version 16.17.0
• Database system SQLite
• Operation mode own

Additional context
Add any other context about the problem here.

[Joffcom]

Hey @pinkasey,

Thanks for reporting this one, I will take a proper look shortly and get back to you.

[Joffcom]

Hey @pinkasey,

I have just given this one a go and I am not able to reproduce the same thing. Do you happen to have a screenshot of what you are seeing in n8n? I have managed to find another issue where a successful create option doesn't show any output which I am about to put in a fix for.

[pinkasey]

I've recreated the issue on my server, this is what I'm seeing:

Note that there are 2 mistakes in this pictures that should cause an error:

1. the credentials
2. the "Columns" field, which I intentionally filled with rubbish. The BigQuery table has mandatory columns, BTW.

[pinkasey]

I've checked the logs too - no errors.
Here's the workflow as json:

here's the workflow I used for testing this:

{
  "name": "Bad BigQuery Node",
  "nodes": [
    {
      "parameters": {},
      "id": "3472055b-6dbb-4301-bfd4-1d45516ac7f1",
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        240,
        300
      ]
    },
    {
      "parameters": {
        "content": "There are 2 problems with this BigQuery node:\n\n1. The credentials - BigQuery does not work with Service-Account credentials (limitation set by Google). I tested this with valid Service-Account credentials.\n\n2. The \"Columns\" field, which I intentionally filled with rubbish (`asdf`). The BigQuery table has mandatory columns, so BigQuery is bound to fail this call.",
        "height": 171.23762376237647,
        "width": 774.9434229137192
      },
      "id": "0a2718b3-1b2d-4341-9776-c194323c2d3d",
      "name": "_QUICKSTART_NOTE_",
      "type": "n8n-nodes-base.stickyNote",
      "typeVersion": 1,
      "position": [
        139.1937765205099,
        116.75388967468177
      ]
    },
    {
      "parameters": {
        "authentication": "serviceAccount",
        "projectId": "n8n-animals",
        "datasetId": "autopilot",
        "tableId": "segment_sizes",
        "columns": "asdf",
        "options": {}
      },
      "id": "a5901413-98b7-44fc-82e5-a8a2f35d950a",
      "name": "Google BigQuery",
      "type": "n8n-nodes-base.googleBigQuery",
      "typeVersion": 1,
      "position": [
        460,
        300
      ],
      "credentials": {
        "googleApi": {
          "id": "4",
          "name": "Google account Big Query"
        }
      }
    }
  ],
  "pinData": {},
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Google BigQuery",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  },
  "active": false,
  "settings": {},
  "id": 3,
  "meta": {
    "instanceId": "dbf1e1c27e0aa552ba9dd5d1a5efec41b35d15523cb28b14de34056eb55c6d4d"
  },
  "tags": []
}

[Joffcom]

Hey @pinkasey,

It is hard to see what is wrong with the credentials from the screenshot when I tested with bad credentials I was seeing an error message.

Would you be able to try with the PR above and see if it changes anything for you? In my testing it brought the behaviour back to 0.192.0.

[pinkasey]

I can try,
might take me a few days to get to it.

I'm a bit curious to see what do you see in this scenario - can you share a screenshot?
If it's not too much trouble

[pinkasey]

Just dropping in to say that I've just upgraded to the latest n8n (0.194.0),
and it's a great improvement for this issue!

Now there's a response when something goes wrong.

There's still a problem though, although much smaller -
When BigQuery returns an error, n8n treats it as success:

Here's the output:

For me this is not a problem,
my setup is correct and I'll never get this error.

[pinkasey]

here's an example error output:
(caused by not passing mandatory columns, and instead passing just one invalid column - asdf - as shown in above screenshot)

[
  {
    "kind": "bigquery#tableDataInsertAllResponse",
    "insertErrors": [
      {
        "index": 0,
        "errors": [
          {
            "reason": "invalid",
            "location": "",
            "debugInfo": "",
            "message": "Missing required fields: Msg_0_CLOUD_QUERY_TABLE.segment_id, Msg_0_CLOUD_QUERY_TABLE.segment_size, Msg_0_CLOUD_QUERY_TABLE.segment_title, Msg_0_CLOUD_QUERY_TABLE.timestamp."
          }
        ]
      }
    ]
  }
]

[Joffcom]

That looks somewhat better, I will check to see if that is how we have we always handled the response before the 193 change that didn't product any output.