Firebird. Cannot reconnect connection when disconnected by server

The offical for ZeosLib 7.3 Report problems, ask for help, post proposals for the new version of Zeoslib 7.3/v8
Quick Info:
-We made two new drivers: odbc(raw and unicode version) and oledb
-GUID domain/field-defined support for FB
-extended error infos of Firebird
-performance ups are still in queue
In future some more feature will arrive, so stay tuned and don't hassitate to help
eversun
Fresh Boarder
Fresh Boarder
Posts: 20
Joined: 28.01.2020, 09:31

Firebird. Cannot reconnect connection when disconnected by server

Post by 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
Platinum Boarder
Platinum Boarder
Posts: 1956
Joined: 17.01.2011, 14:17

Re: Firebird. Cannot reconnect connection when disconnected by server

Post by 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
Fresh Boarder
Fresh Boarder
Posts: 20
Joined: 28.01.2020, 09:31

Re: Firebird. Cannot reconnect connection when disconnected by server

Post by eversun »

Sure, but anyway I should be able to force disconnect - this is not working, as I hang in exceptions cycle
User avatar
aehimself
Zeos Dev Team
Zeos Dev Team
Posts: 796
Joined: 18.11.2018, 17:37
Location: Hungary

Re: Firebird. Cannot reconnect connection when disconnected by server

Post by 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?
Delphi 12.2, Zeos 8 from latest GIT snapshot
Using:
- MySQL server 8.0.18; libmysql.dll 8.0.40 x64 5.7.19 x68, libmariadb.dll 3.3.11
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.15
- MSSQL 2012, 2019; sybdb.dll FreeTDS_3102
- SQLite 3.47
eversun
Fresh Boarder
Fresh Boarder
Posts: 20
Joined: 28.01.2020, 09:31

Re: Firebird. Cannot reconnect connection when disconnected by server

Post by 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
Platinum Boarder
Platinum Boarder
Posts: 1956
Joined: 17.01.2011, 14:17

Re: Firebird. Cannot reconnect connection when disconnected by server

Post by 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
Fresh Boarder
Fresh Boarder
Posts: 20
Joined: 28.01.2020, 09:31

Re: Firebird. Cannot reconnect connection when disconnected by server

Post by 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
Fresh Boarder
Fresh Boarder
Posts: 20
Joined: 28.01.2020, 09:31

Re: Firebird. Cannot reconnect connection when disconnected by server

Post by 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
Platinum Boarder
Platinum Boarder
Posts: 1956
Joined: 17.01.2011, 14:17

Re: Firebird. Cannot reconnect connection when disconnected by server

Post by 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
Platinum Boarder
Platinum Boarder
Posts: 1956
Joined: 17.01.2011, 14:17

Re: Firebird. Cannot reconnect connection when disconnected by server

Post by 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
Fresh Boarder
Fresh Boarder
Posts: 20
Joined: 28.01.2020, 09:31

Re: Firebird. Cannot reconnect connection when disconnected by server

Post by 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
Platinum Boarder
Platinum Boarder
Posts: 1956
Joined: 17.01.2011, 14:17

Re: Firebird. Cannot reconnect connection when disconnected by server

Post by 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
Fresh Boarder
Fresh Boarder
Posts: 14
Joined: 13.01.2021, 03:27

Re: Firebird. Cannot reconnect connection when disconnected by server

Post by 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)
You do not have the required permissions to view the files attached to this post.
marsupilami
Platinum Boarder
Platinum Boarder
Posts: 1956
Joined: 17.01.2011, 14:17

Re: Firebird. Cannot reconnect connection when disconnected by server

Post by 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
Fresh Boarder
Fresh Boarder
Posts: 14
Joined: 13.01.2021, 03:27

Re: Firebird. Cannot reconnect connection when disconnected by server

Post by 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;   
You do not have the required permissions to view the files attached to this post.
Post Reply