Firedac Connection Pooling not honored by ORM Session, MySql closes connection after 8 hours

[Todd Flora]

We have a thread that runs perpetually checking for messages on a message bus. When a message is available it consumes the message and then uses an ORM Session to write the resulting data to disk. When the thread starts up it creates a Firedac connection and an associated ORM Session. It does not set the firedac connection.connected property to true but allows the Orm Session to do this when needed.

The Firedac connection definition defines the connection type as pooled and therefore according to the Firedac documentation, whenever the connection.Connected is set to true an active connection in the pool will be retrieved if one exists, otherwise it will create a new connection as long as the pool limit is not exhausted.

See the following: for a description of connection pooling http://docwiki.embarcadero.com/RADStudio/Berlin/en/Multithreading_(FireDAC)

When Connection.Connected is set to false the connection will be retired back to the Pool and exist in the pool for a time before being cleaned up after a period of inactivity. This is typical resource pooling behavior and is desirous for us so that we can maintain a small group of connections that can be used by a larger group of process threads.

Additionally not holding connection open for long periods of time resolves issues with databases that cleanup connections after a period of inactivity. For instance MySql will automatically kill connections that have been inactive for more than 8 hours and then subsequent calls to a TFDConnection that has been cleaned up by MySql will cause Server Gone errors and cannot be recovered from except to destroy and recreate the connection.,

We noticed right away that it seems the ORM Session Firedac adapter opens a connection when it needs one but never closes the connection. Therefore the connection is never put back into the Pool and thus cannot be reused by another thread. This has caused us a couple of issues.

1) We need to maintain the pool size as large as our process thread pools size since each thread effectively needs a connection that is dedicated to the thread.

2) As previously mentiond, In MySql a database connection is closed after 8 hours of inactivity, and this is happening regularly for our perpetually running threads. The TFDManager handles this issue with MySql because it cleans up inactive connections after 90 seconds, but because the connection is never closed and effectively put back into the pool it is suffering from a MySql specific connection gone exception after 8 hours.

IMO, The Firedac Adapter of Marshmallow should only use a connection for as long as it needs it and should close the connection once it is done. For instance in a transaction situation, once the transaction is rolled back or committed it should close the connection. In a non transactional situation, it should open the connection, perform the query or action and then disconnect, so that Firedac Pooling can be used as the functionality is defined.

[Stefan Glienke]

The ORM does at no point call Connect on the IDBConnection - this is typically done implicitly by the DB access components when a query/dataset is being opened.

So my suggestion is to just call session.Connection.Disconnect after you are done with your work - this should be perfectly save as you have a session/connection per thread.

[Todd Flora]

In the Firerdac adapter, when Starting a transaction connected on the underlying Firedac connection is set to true.

function TFireDACConnectionAdapter.BeginTransaction: IDBTransaction;
var
  transaction: TFDTransaction;
begin
  if Assigned(Connection) then
  try
    Connection.Connected := True;
    if not Connection.InTransaction or Connection.TxOptions.EnableNested then
    begin
      transaction := TFDTransaction.Create(nil);
      transaction.Connection := Connection;
      transaction.StartTransaction;
      Result := TFireDACTransactionAdapter.Create(transaction, ExceptionHandler,
        True);
    end
    else
      raise EFireDACAdapterException.Create('Transaction already started, and EnableNested transaction is false');
  except
    raise HandleException;
  end
  else
    Result := nil;
end;

Later on when the transaction is rolled back or committed the connection is not closed. Seems easily remedied with the following one line change to the following two methods. I am testing this now.

procedure TFireDACTransactionAdapter.Commit;
begin
  if Assigned(Transaction) then
  try
    Transaction.Commit;
    Transaction.Connection.Close;  <--- Added
  except
    raise HandleException;
  end;
end;

procedure TFireDACTransactionAdapter.Rollback;
begin
  if Assigned(Transaction) then
  try
    Transaction.Rollback;
    Transaction.Connection.Close;  <--- Added
  except
    raise HandleException;
  end;
end;

[Stefan Glienke]

This does not work so simple.

When nested transactions are enabled a transaction commit or rollback must not close the connection. Also it is not guaranteed that when a transaction commits or rollbacks there are no other queries open.

As I said if you know that you safely can close the connection - then just call .Connection.Disconnect.

[Stefan Glienke]

We discussed this internally and came to the conclusion that we are not changing anything here because of the reasons I explained earlier.

If you need additional functionality you can always inherit or write new adapters that fit your needs - the one we provide aim at common usage and are not designed to handle corner case scenarios (that we often cannot even test easily).