Intermittent BigQuery failures

[stephenplusplus]

Our system test which validates that we return a PartialFailureError when invalid data is inserted into a table.

The schema states that the name property must be a string.

Our request body looks like this:

{
  "rows": [
    {
      "json": {
        "name": "dave"
      }
    },
    {
      "json": {
        "name": 11
      }
    }
  ]
}

The response is either:

// incorrect (frequent)
{
  kind: 'bigquery#tableDataInsertAllResponse'
}

// correct (infrequent)
{
  "kind": "bigquery#tableDataInsertAllResponse",
  "insertErrors": [
    {
      "index": 1,
      "errors": [
        {
          "reason": "invalid",
          "location": "name",
          "debugInfo": "generic::invalid_argument: Conversion from int64 to string is unsupported.",
          "message": "Conversion from int64 to string is unsupported."
        }
      ]
    },
    {
      "index": 0,
      "errors": [
        {
          "reason": "stopped"
        }
      ]
    }
  ]
}

The first I noticed this error was Friday, 3/3/17.

@tswast any ideas? Thanks!

[tswast]

Nothing obvious to me. I'll pass this on to the BQ eng team.

[Splaktar]

Yes! This seems like what we're seeing as well!

Ever since we've updating to the google-cloud-node version that first introduced PartialFailureError we've been having problems. The docs are very insufficient in how to handle these cases.

The BigQuery table docs are not correct with regards to the Promise handling:

table.insert(rows).then(function(data) {
  var insertErrors = data[0];
  var apiResponse = data[1];
});

Should be:

table.insert(rows).then(function(data) {
  var apiResponse = data[0];
  var insertErrors = data[1];
});

That said, why would you handle errors in the then/success function and not in catch()?

[Splaktar]

Here's what we're seeing in our logs:

(node:22) UnhandledPromiseRejectionWarning: Unhandled promise rejection (rejection id: 8): PartialFailureError: A failure occurred during this request.

data: [{"kind":"bigquery#tableDataInsertAllResponse"}]
apiResponse: {"kind":"bigquery#tableDataInsertAllResponse"}
insertErrors: undefined

I have been pulling my hair out trying to figure out how to get the insertErrors and then what should I do if there is an error?

With google-cloud-node v0.41.2, all of our BigQuery inserts succeeded. Now just by upgrading to google-cloud-node v0.47.0, v0.48.0, or v0.49.0, we're seeing a bunch of PartialFailureErrors. It doesn't make any sense at all to us.

[stephenplusplus]

Well, I can say it hasn't been exactly obvious if a partial failure is an "error" in terms of how Node callbacks/promises historically have worked. A partial failure could mean 1/1000 requests failed, so communicating the whole API call as a failure can be misleading.

Is it possible in versions prior to when we introduced PartialFailureError, you were considering the lack of presence of an "err" argument as a 100% success?

[stephenplusplus]

This is a Trobleshooting Errors document we link to in our docs: https://cloud.google.com/bigquery/troubleshooting-errors

[Splaktar]

We've been seeing the PartialFailureErrors since 2/19 and had to roll back to the old google-cloud-node since it was causing our GAE Flex to spin up 20 instances and peg them all to 100% CPU. We've since fixed the CPU/instance issue, but we're still seeing a lot of PartialFailureErrors and we never get any kind of indication why anything failed or what rows failed since insertErrors is never defined.

Our old code did this:

public insert(rows: T): Promise<boolean> {
    return new Promise((resolve, reject) =>
      this.table.insert(rows, {}, (error) => {
        error ? reject(error) : resolve(true);
      })
    );
  }

Then on the Promise we have:

.catch((error) => {
  throw new Error(`[${os.hostname()}] Error inserting into bigQuery for key: "${listing.key}": ${error}`);
});

This seems like it would report to us any and all errors previously (which we did not see).

[stephenplusplus]

It doesn't look like that old code would have returned errors, since they were previously returned through the 'insertErrors' argument and not through the 'err' argument. The err, back then, was for request failures like 404, 503, but didn't include insertErrors.

So, I think like you said, you're also experiencing these intermittent failures, but they are coming from BQ's API and not ours. Unless there's another way we're supposed to be sending the requests than we do: https://github.com/GoogleCloudPlatform/google-cloud-node/blob/master/packages/bigquery/src/table.js#L1059

[Splaktar]

The BigQuery table docs have this if (err.name === 'PartialFailureError') { which doesn't seem to make sense to me given the data structure:

    {
      kind: string,
      insertErrors: [
        {
          index: number,
          errors: [
            {
              reason: string,
              location: string,
              debugInfo: string,
              message: string
            }
          ]
        }
      ]
    }

[Splaktar]

It doesn't look like that old code would have returned errors, since they were previously returned through the 'insertErrors' argument and not through the 'err' argument. The err, back then, was for request failures like 404, 503, but didn't include insertErrors.

Ah, that's good to know! Thank you. Good in the way that we're not crazy, but not so good in that we've always been loosing data... ugh.

[Splaktar]

The BigQuery table docs also refer to the following:

// insertErrors.errors (object[]):
// insertErrors.errors[].row (original row object passed to `insert`)
// insertErrors.errors[].errors[].reason
// insertErrors.errors[].errors[].message

But insertErrors.errors[i].row doesn't seem to be valid since errors only contain:

           {
              reason: string,
              location: string,
              debugInfo: string,
              message: string
            }