ROLLBACK - Unable to perform operation using terminated connection

💬 Workflows
, , , ,
1

I've tried everything I can think of and I still get intermittent errors in my workflow of "Error evaluating query5: ROLLBACK - Unable to perform operation using terminated connection."

I've created a brand new resource and user to my Snowflake Database so this is the ONLY thing that is running. Regardless of if I have 2 queries or 1 in the workflow that use this user I am still getting the error. I know it's not a timeout issue because the entire workflow (with the error) is only taking 10 seconds to complete. The first query using the resource runs without fail, but the second query fails regularly.

Any help would be appreciated! Will try to jump into the next office hours as well.

2

Thank you for coming to office hours, Kirk! Posting our conversation so we can continue it here:

My teammate wrote this JS snippet (to use in a JS Query) up for another user running into the same issue trying to run a bulk insert statement with an array column in Snowflake:

const jsonData = mergeAPI.data

let values = "";

jsonData.forEach(row => {

  let rowValues = "";

  Object.keys(row).forEach(key => {
    let value = row[key];

    if (typeof value === 'number') {
      value = value; // no quotes 
    } else if (typeof value === 'string') {
      value = `'${value}'`; // single quotes
    } else if (value instanceof Date) {
      value = value.toISOString().slice(0,10); // date format
    } else {
      value = JSON.stringify(value); // other types
    }

    rowValues += value + ",";

  });

  rowValues = rowValues.slice(0,-1); // remove trailing comma

  values += `(${rowValues}),\n`;

});

values = values.slice(0, -2); 

const columns = Object.keys(jsonData[0]).join(",");

const query = `INSERT INTO merge.balance_sheets (${columns}) VALUES ${values}`;


return query

where merge.balance_sheets is the table name

1 Like
3

And then you ran into this error:

{"data":null,"error":{"error":true,"message":"SQL compilation error:\nsyntax error line 1 at position 79 unexpected '\"attempt_hourly_limit\"'...}}}
4

As a note, the above JS will require disabling prepared statements on your resource. If you’re security conscious, you may want to create a separate, identical resource for this, and lock the permissions down the permissions, with the disabled prepared statements ticked.

Let me know if that’ll work for you for the time being until we fix this bulk insert bug!

5

Hi any updates on this bulk insert bug? Some of my users are getting the ROLLBACK - Unable to perform operation using terminated connection error in a web app when bulk uploading data (uses a GUI bulk insert query under the hood). Disabling prepared statements is not an option for me. Thank you!

6

Hey @sgodoshian - thanks for reaching out. I don't have anything concrete to share, unfortunately; we haven't been able to consistently isolate the issue, as there are a few different confounding variables.

Does the table you're updating have an array column? Or are you using a dynamic table name in the query?

Depending on the number of records that you have, the best workaround might be looping through your data and triggering an update query for each.