TSQLRestServerDB and closed DB connection exception

hnb · 2015-12-04 19:50:32

Hi,

I have big problem with closed connection to DB server with TSQLRestServerDB. When DB server is offline for even short time then my mORMot server can't work correctly anymore :\.

First chance exception at $766EC42D. Exception class EZSQLException with message
'SQL Error: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.'.
Process server.exe (2448)

server can partially work (what is very bad) - error is raised only in threads affected by offline status (some server threads are untouched). I think that we need some "reconnect" system for connections in server threads pools.

mpv · 2015-12-05 08:30:07

On the SynDB level (TSQLDBConnection/TSQLDBConnectionProperties) there is an event OnProcess called with parameter TOnSQLDBProcessEvent. During connection lost it called with speConnectionLost.
But handle of this event is hightly depend on your application logic. For example what can we do if we start database transaction and plane to execute 3 statement and after 2d statement we got a connection lost.
If TSQLDBConnectionProperties.RollbackOnDisconnect is true (default) our two statements are rollbacked and we can't repeat the 3d statement even after reconnect, so we must repeat all 3 statement. But sometimes we can't do it...

So in my server implementation in case of connection lost I simply drop connection from connection pool, and return error to client. Next request to server will create new connection, and if client is lucky and DB server is alive his got a non-error answer.

But for non-transaction system it is possible to write a logic like in SynDB.TQuery.Execute (reconnect and repeat the statement)

Last edited by mpv (2015-12-05 08:45:29)

hnb · 2015-12-07 14:45:31

@mvp thanks for valuable info!

in my situation the best solution is custom Request method (TSQLHtppServer). DB server can be offline even when mORMot server is starting ^^:

type
  TCustomHttpServer = class(TSQLHttpServer)
  protected
    fInitialized: Boolean;

    function Request(Ctxt: THttpServerRequest): cardinal; override;
    procedure TryToInitialize;
  end;

implementation

{ TCustomHttpServer }

function TCustomHttpServer.Request(Ctxt: THttpServerRequest): cardinal;
var
  IgnoreInitialization: Boolean;
begin
  IgnoreInitialization := False;
  try
    if TSQLDBZEOSConnection(aProps.MainConnection).Database.PingServer = -1 then
    begin
      aProps.MainConnection.Disconnect;
      aProps.MainConnection.Connect;
    end;
  except
    IgnoreInitialization := True;
  end;

  if not fInitialized and not IgnoreInitialization then
  begin
    TryToInitialize; // inside TryToInitialize is called aRestServer.CreateMissingTables; and EngineAddIgnoreID etc. 
    FInitialized := True;
  end;

  Result := inherited;
end;

hnb · 2015-12-07 21:42:17

More generic "PingServer" method for example inside TSQLDBConnection should be useful.

mORMot Open Source

#1 2015-12-04 19:50:32

TSQLRestServerDB and closed DB connection exception

#2 2015-12-05 08:30:07

Re: TSQLRestServerDB and closed DB connection exception

#3 2015-12-07 14:45:31

Re: TSQLRestServerDB and closed DB connection exception

#4 2015-12-07 21:42:17

Re: TSQLRestServerDB and closed DB connection exception

Board footer