Firebird. Cannot reconnect connection when disconnected by server

[eversun]

Hi !

I still use firebird 2.5 and force disconnection to recompile stored procedures. Older ZEOS versions correctly report this situation, i.e. if I got a error code of 335544721, 335544722 and some more, I just did Connection.Close and Connection.Open to reconnect.

Current version somehow reports different codes for this case - 335544485 or 335544486, but Connection.Close raises exception in CloseAllLinkedComponents, and I cannot do anything with that.

Actually ZEOS 8 reports too often "unsuccessful execution caused by ..." error messages that cannot identify the right error reasons

[marsupilami]

Zeos 8 is supposed to detect situations with lost connections now and handle them accordingly. It should call the OnConnectionLost event handler of the TZConnection object.

[eversun]

Sure, but anyway I should be able to force disconnect - this is not working, as I hang in exceptions cycle

[aehimself]

I don't have the code in front of me at the moment, but does it behave the same if you call .ReConnect instead of .Disconnect and .Connect?

I had my fair share of AVs upon disconnections and therefore memory leaks (if .Disconnect was called by the destructor) but they all slowly disappeared.

Where exactly are you getting in an exception cycle? Can you post a stack trace?

[eversun]

I had tried Reconnect - same situation. The problem is it's trying to close previous transaction that does not exists at the moment, so is raises an exception and function halts.

[marsupilami]

Hmm - I think we are not getting anywhere with this. Could you please try to create a small sample application and an SQL script that demonstrates the problem? This really would help us in debugging the problem.

[eversun]

This simpliest code shows the problem:
program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
System.SysUtils,
ZConnection,
ZDataSet,
ZDbcInterbase6Utils;

var
Connection: TZConnection;
Query: TZQuery;

begin
try
Connection := TZConnection.Create(nil);
Connection.Protocol := 'firebird';
Connection.HostName := '127.0.0.1';
Connection.Database := 'X:\TEST.FDB';
Connection.User := 'SYSDBA';
Connection.Password := 'masterkey';

Query := TZQuery.Create(nil);
Query.Connection := Connection;

Connection.Connect;

Writeln('Now drop connection and press enter');
Readln;

Query.SQL.Text := 'select * from RDB$DATABASE';
try
Query.Open;
except
on E: Exception do Writeln('inner', E.ClassName, ': ', E.Message);
end;

Connection.Reconnect;

except
on E: Exception do Writeln(E.ClassName, ': ', E.Message);
end;
end.


Output is:
Now drop connection and press enter

innerEZIBSQLException: SQL Error: Unsuccessful execution caused by a system error that precludes successful execution of subsequent statements
Code: -902 Message: TRANSACTION STARTED.
EZIBSQLException: SQL Error: Unsuccessful execution caused by a system error that precludes successful execution of subsequent statements
Code: -902 Message: isc_dsql_free_statement

[eversun]

Is there any solution for this problem ? Firebird driver seems to be totally non-functional in the current state, the only way for me is to throw it out and get back to older version...

[marsupilami]

Hello eversun,

please set the FirebirdAPI parameter to legacy. There things work as expected. On the firebird interface based API I get an access violation currently, which I cannot fix easily. Please note: Zeos now raises an EZSQLConnectionLost exception when it detects a connection loss. Please use that to check for a lost connection.

This is the output from a modified version of your program using the legacy API:

D:\Projekte\Zeos\2021-08-26 Firebird + Reconnect\Win32\Debug>Project1.exe
Now drop connection and press enter

innerEZSQLConnectionLost: SQL Error: Unsuccessful execution caused by a system error that precludes successful execution of subsequent statements
Code: -902 Message: TRANSACTION STARTED.; GDS Code: 335544726
Connection successfully reconnected.

D:\Projekte\Zeos\2021-08-26 Firebird + Reconnect\Win32\Debug>

This is the code I used:

Code: Select all

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils,
  ZConnection,
  ZDataSet,
  ZDbcInterbase6Utils;

var
  Connection: TZConnection;
  Query: TZQuery;

begin
  try
    Connection := TZConnection.Create(nil);
    Connection.Protocol := 'firebird';
    Connection.HostName := 'localhost';
    Connection.Database := 'c:\program files (x86)\topsales\datenbank\topsales.fdb';
    Connection.LibraryLocation := 'd:\projekte\topsales\additional\firebird-embedded-3.0.7\fbclient.dll';
    Connection.ClientCodepage := 'UTF8';
    Connection.User := 'SYSDBA';
    Connection.Password := 'masterkey';
    Connection.Properties.Add('FirebirdAPI=legacy');

    Query := TZQuery.Create(nil);
    Query.Connection := Connection;

    Connection.Connect;

    Writeln('Now drop connection and press enter');
    Readln;

    Query.SQL.Text := 'select * from RDB$DATABASE';
    try
      Query.Open;
    except
      on E: Exception do
        Writeln('inner', E.ClassName, ': ', E.Message);
    end;

    Connection.Reconnect;
    Writeln('Connection successfully reconnected.');
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
end.

Error messages on the Firebird and Interbase drivers are not optimal again (yet). We are working on this.

Also you can enable your program to compile with Zeos 7.2 and Zeos 8.0. I am not sure, if the release notes contain the relevant chapter about that (yet).

[marsupilami]

Note: It seems that I found the issue. The interface based API now behaves like the legacy API. PLease check out the latest SVN version. Git should be synchronized tomorrow.

Best regards,

Jan

[eversun]

Hi ! Sorry for a long delay. I am currently with build 7741.
Problem is still active. Once an #335544856 (connection shutdown) happens in Query.Open, I call to Connection.Reconnect - but it still fails when TZAbstractDbcConnection.Close CloseRegisteredStatements call.

SQL Error: connection shutdown; GDS Code: 335544856; Unsuccessful execution caused by a system error that precludes successful execution of subsequent statements '#$D#$A'Code: -902 Message: isc_dsql_free_statement'

And if I try to make a query with this connection again, now it's AV at zero address.

function TZAbstractRODataset.CreateStatement(const SQL: string; Properties: TStrings)...
at line
TxnCon := Txn.GetConnection;

[marsupilami]

Hello eversun,

do you still use the sample code from 2021-06-22? As posted by me on 2021-08-26, I think your sample code now works correctly with Zeos? If you changed your sample code, please share it again.

Best regards,

Jan

[brick08]

i have problem with lost connection too. but a have connect to mssql.
I copyed your code and little changed:

Code: Select all

program project1;

{$mode objfpc}{$H+}

uses
  {$IFDEF UNIX}
  cthreads,
  {$ENDIF}
  Classes,
  SysUtils,
  ZConnection,
  ZDataSet,
  ZDbcInterbase6Utils,
  zcomponent;

var
  Connection: TZConnection;
  Query: TZQuery;

begin
  try
    Connection := TZConnection.Create(nil);
    Connection.Protocol := 'mssql';
    Connection.HostName := 'TECHSRVGEN';   //change
    Connection.Database := 'AdventureWorks2019';
    Connection.LibraryLocation := 'libsybdb-5.dll';
    Connection.ClientCodepage := 'UTF8';
    Connection.User := 'sa';
    Connection.Password := 'password';   //change

    Query := TZQuery.Create(nil);
    Query.Connection := Connection;

    Connection.Connect;
    Writeln('Revision: 7772');
    Writeln('Now drop connection and press enter');
    Readln;

    Query.SQL.Text := 'select * from AWBuildVersion';
    try
      Query.Open;
      Writeln(Query.Fields[1].AsString);
    except
      on E: Exception do
        Writeln('inner', E.ClassName, ': ', E.Message);
    end;

    Connection.Reconnect;
    Writeln('Connection successfully reconnected.');
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
end.

At first run I don't droped connection. reconnect succesfull, but were some memory leaks. (1 screen)
At second run I dropped connection before Query.open and get AccessVioletion (screen 2)
At the third I started the program without connection and get error (screen 3)

[marsupilami]

Hello brick,

I will have to see for the first screenshot.

The second and third screenshot don't show an error that is related to connection lost problems but rather to TDS_MAX_CONN. I seem to remember that TDS_MAX_CONN problems were reported before too when memory was leaked or things like that...

best regards,

Jan

[brick08]

I am attaching screenshots related to connection loss error.
Error AV occurs on the line 1470 in module ZDbcConnection:

Code: Select all

procedure TZAbstractDbcConnection.CloseRegisteredStatements;
var I: Integer;
begin
  for i := fRegisteredStatements.Count-1 downto 0 do begin
    //try
      IZStatement(fRegisteredStatements[i]).Close; //there AV
    //except end;
  end;
end;