Unexpected error occurred while running query

[benbarry]

I have an app to import a CSV file and run multiple queries to import things to MongoDB for each row. In this particular test CSV file there are ~70 rows, and it pretty consistently fails to make it through all of them (in the error below it made it to ~30 before failing). So, I know this query works, because it did it 30 times without issue. It's not critical issue, because I built the app to skip rows already imported if it failed previously—so I can eventually get the whole sheet imported fine. It's just annoying, because I'm uncertain what the issue is.

This is the error I get:

My questions are:

• Is this an issue with Retool or with Mongo?
• Anything I can do to fix it?

[ScottR]

There is ongoing maintenance at the moment....https://status.retool.com/

[benbarry]

Ok. Hope that's all that it is. Guess I'll wait a few days and see.

It's not always the same query that fails. I just ran the import test again, and it made it 33 before throwing this:

[ScottR]

[benbarry]

These queries are working ~99% of the time. I'm uncertain if they would have previously worked 100% of the time, because I'm building this right now.

I can't read the details in that screenshot, but I've always used the "new" IP addresses.

[Kabirdas]

Hey @benbarry!

Unfortunately, this error message is not the most descriptive and I'm not seeing much more on my end

So, some general context questions:

1. Do you happen to have logs from the MongoDB side?
2. How is your DB being hosted?
3. Are you seeing failures specifically when you run this set of upserts or is it also happening with other queries?
4. Do smaller upserts function more consistently or do you see approximately the same error rate?

[benbarry]

The Mongo database is currently on an M0 cluster while in dev, so it seems that I can't download logs. I'm not sure if there is another way to access them, but I'll research it a bit.

I have noticed a few sporadic failures of other single queries today, but I haven't been doing much other than working on this import function.

[benbarry]

@Kabirdas I'm not sure if this tells you anything more. It failed again just now with a slightly different error.

[Kabirdas]

@benbarry thanks for that additional context! Taking a look at this with some other members of the team, will report back if we find a solution or need any more info.

[Kabirdas]

Hey @benbarry!

The team has done some more investigation into this and has potential targets for implementing a fix. However, due to the upcoming holiday code freeze, they won't be implemented until the start of the new year.

If you find this behavior to be urgently blocking and need a fix before then let me know and I can try to look into other solutions with you

[benbarry]

@Kabirdas thanks for the update. I've implemented a way to restart my script if it fails and continue from where it was at before failing. It's a bit annoying, but getting the job done, so not urgently blocking. Have a good holiday!

[Kabirdas]

Hey @benbarry! Just want to circle back here to report that we've made some changes to help reduce occurrences of this error. Can you let me know if it's still popping up for you?

[benbarry]

Excellent. I was able to muddle through everything I needed to import at the time, but there will be more in the future. However, I'll try and run a test batch in the next day or two and follow up about how it goes.

[benbarry]

@Kabirdas I finally got around to running a test batch. It still seems to consistently fail randomly and unexpectedly.

Here are screenshots from two failures. First run made it through 24 records before failing. Second made it through 16.

[dzear]

Hi, to piggyback on this, we also see these random errors on random queries with errors like "Unexpected error occurred while running query" and "request to http://a70beb220e29f4cc8be1d24af3159c5c-f33a0ad608976d7c.elb.us-west-2.amazonaws.com/api/runQuery failed, reason: read ECONNRESET". Something seems to occasionally fail on Retool's backend.

These failure are all going to a GCP MySQL database.

Is there a way to automatically retry a query on failure in Retool?

[Kabirdas]

Hey! Thanks for reporting back @benbarry! I'm doing some digging on our end on this and will update here as I find more information or have more questions.

In response to your question @dzear, it's possible to add event handlers to run in if your query fails. You can add a debounce to the trigger so that it only retries every X milliseconds:

You might also want to add a retry counter as a temp state so that it will only retry a certain number of times:

And then reset the temp state if the query is successful or the number of retries has been exceeded:

It's also possible to do all of the above with a single debounced script:

const retry_count = query1_retries.value;
if(retry_count < 5){
  query1.trigger();
  query1_retries.setValue(retry_count + 1);
}else{
  utils.showNotification({title: "Query failed", notificationType: "error"});
  query1_retries.setValue(0);
}

Hopefully, that helps for now!
retry_query.json

[dzear]

Thanks @Kabirdas! It would be awesome if Retool could add retry query under the query's advanced options so we didn't have to do all this boilerplate on all of our queries

[Kabirdas]

Definitely! This is something that has been requested before, if it's picked up and included I'll let you know here