Log Error in OnConnectionLost

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
FredS
Posts: 208
Joined: Mon 10 Nov 2014 17:52

Log Error in OnConnectionLost

Post by FredS » Thu 25 Apr 2019 17:21

When MSSQL restarts the original Error would be something like: "TCP Provider: An existing connection was forcibly closed by..", how can I log this inside OnConnectionLost?
The Exception passed in as Sender has no InnerException nor contains any relevant info in its Message.

Code: Select all

procedure TDataDM.ConConnectionLost(Sender: TObject; Component: TComponent; ConnLostCause: TConnLostCause; var RetryMode: TRetryMode);
var e : EUpdateError absolute Sender;
const cConnectionLost = 'Connection Lost';
begin
  Log(cConnectionLost + ': ' + e.Message);
  RetryMode := TRetryMode.rmReconnectExecute;
end;

FredS
Posts: 208
Joined: Mon 10 Nov 2014 17:52

Re: Log Error in OnConnectionLost

Post by FredS » Thu 25 Apr 2019 21:52

In the 'FailOver' demo the Kill Session SQL does not work for FB3.

Stellar
Devart Team
Posts: 304
Joined: Tue 03 Oct 2017 11:00

Re: Log Error in OnConnectionLost

Post by Stellar » Fri 26 Apr 2019 13:04

The exception object is not passed to the OnConnectionLost Event. To capture the server connection break, you can try processing the OnError event for TUniConnection.
In the demo project, the session deletion method is implementedf for InterBase. You can learn more about how to delete a session in FireBird 3 in the documentation:
https://firebirdsql.org/file/documentat ... ttach-kill

FredS
Posts: 208
Joined: Mon 10 Nov 2014 17:52

Re: Log Error in OnConnectionLost

Post by FredS » Fri 26 Apr 2019 15:35

Stellar wrote:
Fri 26 Apr 2019 13:04
..you can try processing the OnError event for TUniConnection.
I already have that set and nothing is being logged.
The obvious issue here is that if another error occurs we get support requests with something like

Code: Select all

 'Dataset was not open'
without knowing what really occurred.

FredS
Posts: 208
Joined: Mon 10 Nov 2014 17:52

Re: Log Error in OnConnectionLost

Post by FredS » Fri 26 Apr 2019 19:17

Stellar wrote:
Fri 26 Apr 2019 13:04
You can learn more about how to delete a session in FireBird 3 in the documentation:
https://firebirdsql.org/file/documentat ... ttach-kill
I managed to achieve disconnection using:

Code: Select all

      dbFirebird:  begin
         CurConID := Con.QuerySingle('SELECT CURRENT_CONNECTION FROM RDB$Database');
         KillCon.ExecSQL('DELETE FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = :ATTACHMENT_ID', [CurConID]);
       end;
See Edit below, earlier I wrote:However this did NOT trigger 'ConnectionLost' but ended in an unhandled exception:

Code: Select all

raised exception class EIBCError with message 'connection shutdown'.
Restarting the FB3 Server gave me this instead, and still no 'ConnectionLost' :

Code: Select all

 raised exception class EIBCError with message 'Error reading data from the connection.'.
EDIT:
Turns out the above errors occur with FB3 only if 'DisconnectedMode=FALSE'. The dbs I've tested MSSQL, MARIADB, MYSQL all work fine in this state. Bug or architecture?

Another thing that popped up is that one cannot use the default connection in 'FailOver' to connect to FB3. With 'Pooling' Active it raises:

Code: Select all

Error: Can't perform operation on connected connection. 
DisconnectedMode and no Pooling?

ViktorV
Devart Team
Posts: 2723
Joined: Wed 30 Jul 2014 07:16

Re: Log Error in OnConnectionLost

Post by ViktorV » Tue 30 Apr 2019 13:02

1. The OnConnectionLost event occurs only when the following conditions are fulfilled:
- a fatal error occurs;
- there are no opened transactions in a connection that are not ReadOnlyReadCommitted (if connection has at least one opened transaction, which is not ReadCommitedReadOnly, FailOver does not execute. All ReadCommitedReadOnly transaction are restored with FailOver operation);
- there are no opened and non-fetched datasets;
- there are no explicitly prepared datasets or SQLs.
In the UniDAC FailOver demo, the sample does not use the ReadOnlyReadCommitted, but the ReadCommitted transaction, so the OnConnectionLost event does not work. You can change the transaction type for TUniConnection to ReadOnlyReadCommitted, and use a separate transaction of type ReadCommitted to change the data of all datsets. To do this, you can use the TUniQuery.UpdateTransaction property.
2. We will investigate the cause of the error "Can't perform the operation" and let you know the result.

FredS
Posts: 208
Joined: Mon 10 Nov 2014 17:52

Re: Log Error in OnConnectionLost

Post by FredS » Tue 30 Apr 2019 18:28

During today's testing I found a couple of inconsistencies, the fix was to sync the checkbox settings with the DM before connect:

Code: Select all

procedure TMainForm.ConnectionBeforeConnect(Sender: TObject);
begin
  FShouldNotUpdateControls := True;
  try
    DM.Connection.Options.LocalFailover := cbFailover.Checked;
    DM.Connection.Pooling := cbPooling.Checked;
    DM.quDetail.CachedUpdates := cbCachedUpdates.Checked;
    DM.quDetail.Options.LocalMasterDetail := cbLocalMasterDetail.Checked;
    DM.quDetail.SpecificOptions.Values['FetchAll'] := BoolToStr(cbFetchAll.Checked, True);
    DM.Connection.Options.DisconnectedMode := cbDisconnectedMode.Checked;
    DM.quMaster.Debug := cbDebug.Checked;
    meLog.Lines.Add(Format('Failover: %d, Pooling: %d, CachedUpdates: %d, LocalMaster: %d, FetchAll: %d, DisconnectedMode: %d',
                     [Ord(cbFailover.Checked), Ord(cbPooling.Checked), Ord(cbCachedUpdates.Checked),
                      Ord(cbLocalMasterDetail.Checked), Ord(cbFetchAll.Checked),Ord(cbDisconnectedMode.Checked)]));
  finally
    FShouldNotUpdateControls := False;
  end;
end;
After that I reran my tests and below are the log entries. For my purpose only the top row of the master was altered and posted.

Code: Select all

Failover: 1, Pooling: 1, CachedUpdates: 0, LocalMaster: 0, FetchAll: 1, DisconnectedMode: 0
Provider: SQL Server
Transaction.IsolationLevel: 0
11:15:32 AM Session was killed
11:15:38 AM  - Connection lost during SQL execution
Provider: SQL Server
Transaction.IsolationLevel: 0
==========================================

Failover: 1, Pooling: 1, CachedUpdates: 0, LocalMaster: 0, FetchAll: 1, DisconnectedMode: 0
Provider: MySQL
Transaction.IsolationLevel: 0
11:16:31 AM Session was killed
11:16:36 AM  - Connection lost during SQL execution
Provider: MySQL
Transaction.IsolationLevel: 0
==========================================

Failover: 1, Pooling: 0, CachedUpdates: 0, LocalMaster: 0, FetchAll: 1, DisconnectedMode: 0
Provider: InterBase
Transaction.IsolationLevel: 0
11:18:51 AM Session was killed
connection shutdown
As you can see the only change in settings is Pooling due to the error reported before.
Yet Interbase errors with connection shutdown without firing the ConnectionLost Event.

ViktorV
Devart Team
Posts: 2723
Joined: Wed 30 Jul 2014 07:16

Re: Log Error in OnConnectionLost

Post by ViktorV » Thu 02 May 2019 06:46

This is the correct behavior of UniDAC. As we have already written the OnConnectionLost event does not generate if the sample does not use the ReadOnlyReadCommitted, but the ReadCommitted transaction. To solve the issue please use the ReadOnlyReadCommitted transaction in your sample.

FredS
Posts: 208
Joined: Mon 10 Nov 2014 17:52

Re: Log Error in OnConnectionLost

Post by FredS » Thu 02 May 2019 18:15

ViktorV wrote:
Thu 02 May 2019 06:46
..please use the ReadOnlyReadCommitted transaction in your sample.
OK, that of course is a ReadOnly transaction and completely alters the behavior in comparison to the other dbs.

But I'm still not clear how to use it, I get this when I try to assign it:

Code: Select all

DM.Connection.DefaultTransaction.IsolationLevel := iblReadOnlyReadCommitted;
[dcc32 Error] Main.pas(402): E2010 Incompatible types: 'TCRIsolationLevel' and 'TIBCIsolationLevel'
From https://www.devart.com/ibdac/docs/devar ... nlevel.htm:
iblReadOnlyReadCommitted : Enables the transaction to see all committed data in the database with read-only access mode.

ViktorV
Devart Team
Posts: 2723
Joined: Wed 30 Jul 2014 07:16

Re: Log Error in OnConnectionLost

Post by ViktorV » Fri 03 May 2019 11:00

Since UniDAC is a universal set of components designed to work with various DBMS, the property TUniTransaction.IsolationLevel requires the implementation of transaction isolation levels that are common to various DBMS.
You can use the following code to solve your task:

Code: Select all

DM.Connection.DefaultTransaction.IsolationLevel := ilReadCommitted;
DM.Connection.DefaultTransaction.ReadOnly := True;
The TCRIsolationLevel is defined in the CRAccess.pas module. To solve the issue you can add the CRAccess unit to the USES clause of your unit.

Post Reply