Dbo - reconnection to DB server after connection is lost

Added by Sergey Petrov 4 months ago

Hi!

By the moment, the reconnection to DB server after connection is lost implemented for Postgres and MySql backends.

1) Is there a special reason that for MS SQL backend this functional is not implemented?
2) Is it acceptable to implement checkConnection function like below and call it in functions: executeSql, startTransaction, commitTransaction, rollbackTransaction, MSSQLServerStatement constructor, MSSQLServerStatement::execute (similar to MySql backend approach)?

void Wt::Dbo::backend::MSSQLServer::checkConnection()
{
    if (!impl_ || !impl_->env || !impl_->dbc)
        return;

    try
    {
        // ping MS SQL Server by execution 'SELECT 1' query
        SQLHSTMT s;
        SQLRETURN rc = SQLAllocHandle(SQL_HANDLE_STMT, impl_->dbc, &s);
        handleErr(SQL_HANDLE_DBC, impl_->dbc, rc);
        SQLWCHAR wstr[] = L"SELECT 1";
        rc = SQLPrepareW(s, wstr, 8);
        handleErr(SQL_HANDLE_STMT, s, rc);

        rc = SQLExecute(s);
        SQLFreeStmt(s, SQL_CLOSE);
        SQLFreeHandle(SQL_HANDLE_STMT, s);
        handleErr(SQL_HANDLE_STMT, s, rc);
    }
    catch (MSSQLServerException& )
    {
        try
        {
            // do reconnect
            clearStatementCache();
            impl_->reset(); // free SQLHENV env, SQLHDBC dbc, SQLHSTMT stmt
            impl_->connect();
        }
        catch(MSSQLServerException& e)
        {
            throw MSSQLServerException("checkConnection: Error when reconnecting: " + std::string(e.what()));
        }
    }
}

Remark: 'SELECT 1' query chosen because of https://stackoverflow.com/questions/2440060/whats-the-best-way-to-test-sql-server-connection-programmatically

3) Checking the connection is alive for Postgres backend seems to be a bit incorrect:

  if (PQstatus(conn_) != CONNECTION_OK)  {
    LOG_WARN("connection lost to server, trying to reconnect...");
    if (!reconnect()) {
      throw PostgresException("Could not reconnect to server...");
    }
  }

because of this may require to call pg_ping (see https://stackoverflow.com/questions/14294696/postgresql-pqstatus-always-return-connection-ok)


Replies (2)

RE: Dbo - reconnection to DB server after connection is lost - Added by Roel Standaert 4 months ago

I think none of the implementations had automatic reconnects before. Those were added later for specific backends. That's the reason why it's not implemented for SQL Server.

Could you turn this into an issue with a patch or a pull request? That'll make it easier to review.

This article also suggests that recent versions of SQL Server and its ODBC driver can do automatic reconnects: https://docs.microsoft.com/en-us/sql/connect/odbc/windows/connection-resiliency-in-the-windows-odbc-driver?view=sql-server-ver15

RE: Dbo - reconnection to DB server after connection is lost - Added by Sergey Petrov 4 months ago

Thanks a lot for the answer!

I did know about SQL Server automatic reconnect feature, so my reconnect implementation is not appropriate.
However, from https://docs.microsoft.com/en-us/sql/connect/odbc/windows/connection-resiliency-in-the-windows-odbc-driver?view=sql-server-ver15 article - ConnectRetryCount is limited to 255.
That means when connection attempts will be over the connection will completely broken and may require manual reconnect (for infinite reconnect attempt case, as was done for Postgres ans MySql backends).

(1-2/2)