graphql-engine: [Microsoft][ODBC Driver 17 for SQL Server]The connection is broken and recovery is not possible. The connection is marked by the client driver as unrecoverable. No attempt was made to restore the connection.

Version Information

Server Version: v2.10.0 CLI Version (for CLI related issue): v2.10.0

What is the current behaviour?

We have Hasura hosted on a Cloud Run on Google Cloud Platform connected with a Postgres and a SQL Server databases. From time to time, Hasura is losing connection with the SQL Server database and unable to reconnect again.

We are receiving this exception message:

[Microsoft][ODBC Driver 17 for SQL Server]The connection is broken and recovery is not possible. The connection is marked by the client driver as unrecoverable. No attempt was made to restore the connection.

What is the expected behaviour?

We would like to Hasura reconnect with SQL Server automatically, so we don’t have to release a new revision on Cloud SQL to recover the connection.

Please provide any traces or logs that could help here.

This is the log we have from Cloud Run:

{
  "insertId": "xxxxxxxxxxxxxxxxxxxxxxxxxxxx",
  "jsonPayload": {
    "level": "error",
    "detail": {
      "error": "database query error",
      "internal": {
        "exception": {
          "type": "unsuccessful_return_code",
          "message": "[Microsoft][ODBC Driver 17 for SQL Server]The connection is broken and recovery is not possible. The connection is marked by the client driver as unrecoverable. No attempt was made to restore the connection."
        },
        "query": "BEGIN TRANSACTION"
      },
      "path": "$",
      "code": "unexpected"
    },
    "timestamp": "2022-11-18T11:49:34.097+0000",
    "type": "event-trigger"
  },
  "resource": {
    "type": "cloud_run_revision",
    "labels": {
      "configuration_name": "hasura-prd",
      "service_name": "hasura-prd",
      "location": "southamerica-east1",
      "revision_name": "hasura-prd-xxxxxxxxxxxxxxxxxxxxxxxxxxxx",
      "project_id": "xxxxxxxxxxxxxxxxxxxxxxxxxxxx"
    }
  },
  "timestamp": "2022-11-18T11:49:35.103839Z",
  "labels": {
    "instanceId": "xxxxxxxxxxxxxxxxxxxxxxxxxxxx"
  },
  "logName": "xxxxxxxxxxxxxxxxxxxxxxxxxxxx",
  "receiveTimestamp": "2022-11-18T11:49:35.195698667Z"
}

Keywords

sql server connection broken

About this issue

Original URL
State: open
Created 2 years ago
Comments: 17 (5 by maintainers)

Most upvoted comments

Great, thanks!

Assuming it makes it to your production instance and you’re monitoring machine statistics, it would be great to know if there’s major changes to CPU load, memory usage, or network traffic anywhere.

If you still have issues, please let me know.

SamirTalwar on Aug 1, 2023

@SamirTalwar thanks for the guidance. Very helpful. I’ve updated the setting – found in databases.yaml as mentioned – to disable pool settings.

I’m hoping my staging hasura instance has enough traffic to replicate the issue I’m seeing in production (happened again a few days ago). If I don’t see any issues on the staging database over the coming days, I’ll make the same change to my production server. I’ll keep this thread updated with my progress & findings.

lucasnad27 on Jul 31, 2023

Some update from us to help you debug this.

We migrated our Hasura deployment from Cloud Run to GKE, both were using GCP VPN to connect to the database. All hasura configuration stayed the same.

We have been 22 days without a problem now.

tadeumaia on Jul 27, 2023

Happy to do so Samir. I haven’t used the CLI much, but should be able to get this done by EOW. I’ll let you know when I’m able to flip the bit and hopefully I can re-produce the issue in the coming weeks 🤞🏻

lucasnad27 on Jul 26, 2023

Hello, I work with @julianomcl

Thanks for all the work you already did debugging this issue, but we are still having this issue.

Hasura is connected to two DB’s one MSSQL and one POSTGRES, these problems only happen on the MSSQL database and locks all queries, after a container restart at cloud run the error is resolved and everything goes back to normal until we get hit by the error again.

Could you please provide us with some guidance on how to further debug this?

Here is some more information that could help:

Connection String Driver={ODBC Driver 17 for SQL Server};Server=cloud-sql-internal-hostname:1433;Database=ourdb;Uid=user;Pwd=pwd;

Hasura Settings HASURA_GRAPHQL_ENABLED_LOGTYPES | startup,http-log,query-log,websocket-log,webhook-log HASURA_GRAPHQL_DEV_MODE | false HASURA_GRAPHQL_METADATA_DATABASE_URL | Our postgressdb HASURA_GRAPHQL_ENABLE_CONSOLE | true HASURA_GRAPHQL_ENABLED_APIS | graphql, metadata

Cloud Run settings resources: limits: cpu: 2000m memory: 1G startupProbe: timeoutSeconds: 240 periodSeconds: 240 failureThreshold: 1 tcpSocket: port: 8080

tadeumaia on May 30, 2023