Can Ecto or TDS retry queries if TCP connection closes or network error or DBConnection.ConnectionError?

Does Ecto or TDS have a setting to retry SQL queries if the TCP connection closes?

Does DBConnection have settings to retry when it fails?

Do I have to wrap with try catch and handle errors myself?

My queries intermittently fail about once every couple of minutes. I have a Docker container created with mix phx.gen.release --docker running on Azure App Service for Linux.

ARG DEBIAN_VERSION=bullseye-20230612-slim

2023-08-29T14:54:56.513337330Z ** (exit) an exception was raised:
2023-08-29T14:54:56.513342430Z     ** (DBConnection.ConnectionError) bad return value: {:error, :closed}
2023-08-29T14:54:56.513348130Z         (db_connection 2.5.0) lib/db_connection.ex:1286: DBConnection.handle_common_result/3
2023-08-29T14:54:56.513353330Z         (db_connection 2.5.0) lib/db_connection.ex:1410: DBConnection.run_prepare/4
2023-08-29T14:54:56.513359630Z         (db_connection 2.5.0) lib/db_connection.ex:1422: DBConnection.run_prepare_execute/5
2023-08-29T14:54:56.513365530Z         (db_connection 2.5.0) lib/db_connection.ex:1527:
2023-08-29T14:54:56.513386731Z         (db_connection 2.5.0) lib/db_connection.ex:656: DBConnection.parsed_prepare_execute/5
2023-08-29T14:54:56.513392331Z         (db_connection 2.5.0) lib/db_connection.ex:648: DBConnection.prepare_execute/4
2023-08-29T14:54:56.513397631Z         (tds 2.3.2) lib/tds.ex:70: Tds.query/4
2023-08-29T14:54:56.513402231Z         (ecto_sql 3.10.1) lib/ecto/adapters/sql.ex:955: Ecto.Adapters.SQL.struct/10

I have constant logs of TCP connections closing when the server is idling with a new Phoenix app configured with --database mssql and 10 pool connections.

2023-08-30T12:20:22.976702606Z 12:20:22.976 [error] GenServer #PID<0.6690.0> terminating
2023-08-30T12:20:22.976756307Z ** (stop) :tcp_closed
2023-08-30T12:20:22.976772707Z Last message: {:tcp_closed, #Port<0.1567>}
2023-08-30T12:20:33.958430086Z 12:20:33.957 [error] GenServer #PID<0.6723.0> terminating
2023-08-30T12:20:33.958483187Z ** (stop) :tcp_closed
2023-08-30T12:20:33.958490487Z Last message: {:tcp_closed, #Port<0.1579>}
2023-08-30T12:20:39.852832431Z 12:20:39.852 [error] GenServer #PID<0.6712.0> terminating
2023-08-30T12:20:39.852870331Z ** (stop) :tcp_closed
2023-08-30T12:20:39.852874331Z Last message: {:tcp_closed, #Port<0.1575>}

There is not a built in retry. I think (based on your other forum posts) that the path you should pursue is figuring out why you are getting these tcp failures.

It hasn’t been easy figuring out the tcp failures. I’ve been searching all week.

Understood, but retries aren’t that easy either. For simple queries sure, but it’s gonna get pretty messy with transactions and stuff.

I found the TCP failure problem. My local container and Azure container connect to Azure SQL Server differently that TDS or DBConnection doesn’t know how to handle.

Excellent! Super happy to see the end of that line of questioning.