Zeos8 Exception after upgrade to Firebird 3.0

[korecek]

Hello,
we would like to ask for some help because we ran into issue when we updated from Firebird 2.5.8 to 3.0.7.
Note: we are using C++ (XE10.4.2)

On first look application did work OK, but after few days it crashed with Exception "close all lob streams before closing the resultset".
Crashes are random on different queries (on 2.5.8 same code did run for months without crash).

Principle of work in our application:
1. To the REST interface comes Request -> create new thread
2. in that thread create new TZConnection then fill with required values(DB name, library, user, ... )
3. then TZconnection->Connect()
4. in same thread create new TZQuery and fill it with needed values (connection TZConnection, SQL, params)
5. then use TZQuery->Open()
6. read returned data (by using TZQuery->First(), TZQuery->Next(), TZQuery->EoF)
7. TZQuery->Close()
8. destroy TZQuery
9. TZconnection->Disconnect()
10. destroy TZconnection
11. send REST response back and close thread

This runs fine for hundreds of queries then once it throws exception ("close all lob streams before closing the resultset") on
point 5. above and then once again on point 9.
This happens even if we loop only a single REST request with same values to run, which results to same query
every time (few hundred times OK and then once exception).

We tried to update Firebird to 3.0.8 and Zeos components to latest (we had version from June 2021), but nothing helped.

Thank you in advance for any help.

Vaclav

vACLAV

[marsupilami]

Hello Vaclav,

close all lob streams before closing the resultset

This sounds like a blob stream is still in use. This can happen for any type of BLOB fields. Maybe some exception gets raised ans a blob stream is not freed as it should? Do you use BLOB streams or do you access BLOBs using .AsString and .AsBytes only?

Best regards,

Jan

[korecek]

Hello,
that the strangest thing, because each pair of TZconnection and TZQuery are created on separated thread by
calling new (that means that each SQL query has their own Zconnection and TZQuery) and crash happens after
some time on TZQuery->Open() a and then on TZconnection->Disconnect() as noted in post before.
In that situation TZconnection->Connect() succeeds but TZQuery->Open() throws exception "close all lob streams
before closing the resultset" and no result is generated (both TZconnection and TZQuery instances are created as new and
never used before)

Example of used SQL (cycled periodically for creation of that exception) with added data types:

Code: Select all

SELECT  ID_DEVICE,                                                                 -- BIGINT
        DEVICE_VENDOR.DV_IDENT_HEX AS DEV_VENDOR,                       -- CHAR(4)
        DEV_SPECS.WS_NAME AS DEV_VERSION,                                     -- VARCHAR(8)
        DEV_SPECS.WS_DEVTYPE AS DEV_TYPE,                                      -- VARCHAR(8)          
        WM_SERIAL_NUMBER AS DEV_SN,                                               -- VARCHAR(15)
        RPACKAGE_TYPEDEF.RT_SETTINGS_TEXT AS DEV_SETTINGS,        -- VARCHAR(100)
        ENCRYPTION.EN_PHP_TEXT AS DEV_ENCRYPTION,                        -- VARCHAR(100)
        DEVICE.WM_ENCRYPTION_KEY AS DEV_ENCRYPTION_KEY,              -- VARCHAR(48)
        DEVICE.WM_STATE AS DEV_STATE,                                              -- VARCHAR(10)
        DEVICE.WM_ACTIVE AS DEV_ACTIVE,                                           -- CHAR(1)
        DEVICE.WM_INSERT_TIME AS INSERT_TIME                                  -- TIMESTAMP
FROM DEVICE
INNER JOIN DEVICE_VENDOR ON DEVICE_VENDOR.ID_DEVICE_VENDOR = DEVICE.WM_DEVICE_VENDOR_ID
INNER JOIN DEV_SPECS ON DEV_SPECS.ID_DEV_SPECS = DEVICE.WM_SPECS_ID
INNER JOIN ENCRYPTION ON ENCRYPTION.ID_ENCRYPTION = DEVICE.WM_ENCRYPTION_ID
INNER JOIN RPACKAGE_TYPEDEF ON RPACKAGE_TYPEDEF.ID_RPACKAGE_TYPEDEF = DEVICE.WM_RPACKAGETYPE_ID
WHERE WM_COMPANY_ID =:COMPANY

Note: We have this problem in FB 3.0.8 not in FB 2.5.8. Application is same only difference is in FB version (i.e. fbclient.dll) .

Thank you in advance for any help.

Best regards,
Vaclav

[aehimself]

I have no experience with FB, but this might be a good starting point.
Maybe something similar has to be changed somewhere in FB LOB streams...?

[marsupilami]

Hello,
that the strangest thing, because each pair of TZconnection and TZQuery are created on separated thread by
calling new (that means that each SQL query has their own Zconnection and TZQuery) and crash happens after
some time on TZQuery->Open() a and then on TZconnection->Disconnect() as noted in post before.
In that situation TZconnection->Connect() succeeds but TZQuery->Open() throws exception "close all lob streams
before closing the resultset" and no result is generated (both TZconnection and TZQuery instances are created as new and
never used before)

Example of used SQL (cycled periodically for creation of that exception) with added data types:

Code: Select all

SELECT  ID_DEVICE,                                                                 -- BIGINT
        DEVICE_VENDOR.DV_IDENT_HEX AS DEV_VENDOR,                       -- CHAR(4)
        DEV_SPECS.WS_NAME AS DEV_VERSION,                                     -- VARCHAR(8)
        DEV_SPECS.WS_DEVTYPE AS DEV_TYPE,                                      -- VARCHAR(8)          
        WM_SERIAL_NUMBER AS DEV_SN,                                               -- VARCHAR(15)
        RPACKAGE_TYPEDEF.RT_SETTINGS_TEXT AS DEV_SETTINGS,        -- VARCHAR(100)
        ENCRYPTION.EN_PHP_TEXT AS DEV_ENCRYPTION,                        -- VARCHAR(100)
        DEVICE.WM_ENCRYPTION_KEY AS DEV_ENCRYPTION_KEY,              -- VARCHAR(48)
        DEVICE.WM_STATE AS DEV_STATE,                                              -- VARCHAR(10)
        DEVICE.WM_ACTIVE AS DEV_ACTIVE,                                           -- CHAR(1)
        DEVICE.WM_INSERT_TIME AS INSERT_TIME                                  -- TIMESTAMP
FROM DEVICE
INNER JOIN DEVICE_VENDOR ON DEVICE_VENDOR.ID_DEVICE_VENDOR = DEVICE.WM_DEVICE_VENDOR_ID
INNER JOIN DEV_SPECS ON DEV_SPECS.ID_DEV_SPECS = DEVICE.WM_SPECS_ID
INNER JOIN ENCRYPTION ON ENCRYPTION.ID_ENCRYPTION = DEVICE.WM_ENCRYPTION_ID
INNER JOIN RPACKAGE_TYPEDEF ON RPACKAGE_TYPEDEF.ID_RPACKAGE_TYPEDEF = DEVICE.WM_RPACKAGETYPE_ID
WHERE WM_COMPANY_ID =:COMPANY

This is really strange: Your table declaration doesn't use any blob type fields? Could you maybe try to put together a working example that raises the problem - including data?

Note: We have this problem in FB 3.0.8 not in FB 2.5.8. Application is same only difference is in FB version (i.e. fbclient.dll) .

Ok - that makes some sense: In Zeos 8 we start using the new interface based API for Firebird if possible. This interface based API is available since Firebird 3.0. So if you use Firebird 2.5 Zeos will use the old API whereas it will use the new API on Firebird 3.0 by default.
You can force the use of the old API by either setting the protocol to "interbase" or by setting the "FirebirdAPI" parameter to "legacy". Maybe the problems will be solved for you if you try these things.

Best regards,

Jan

[korecek]

Hello.
Thanks for your assistance and sorry for late response (because of some project testing).
Your suggestion with using settings "FirebirdAPI=legacy" was very helpful and application now runs fine without these crashes.
Additionally we would like to note that when crash (close all lob streams before closing the resultset) happens (using standard
"FirebirdAPI=interface") then in log of Firebird server is this:

TESTGW1 Thu Jan 19 12:26:02 2022
Operating system call CryptAcquireContext failed. Error code -2146893795
TESTGW1 Thu Jan 19 12:26:02 2022
Authentication, client plugin:
operating system directive CryptAcquireContext failed
unknown Win32 error -2146893795

So could that be that there is being some problem with concurrent access to database, because our application works by connecting
multiple users independently on each other to database (that why we give each of them separate thread with TZconnection
and TZQuery created as new). We are noting this because when we do testing by running application ("FirebirdAPI=interface") where
we wait for result of previous request and then sending another and let this run indefinitely it doesn't seem to crash.

We will try to provide the example but it will take some time because as it is now we are bit in time pressure with other projects.

Thank you for your help.

Best regards,
Vaclav

[marsupilami]

Hello Vaclav,

Your suggestion with using settings "FirebirdAPI=legacy" was very helpful and application now runs fine without these crashes.

That is good to know

We will try to provide the example but it will take some time because as it is now we are bit in time pressure with other projects.

That would really help in tracking down the problem Take your time

Best regards,

Jan

[korecek]

Hello.
After a longer time here we have prepared a promised example.

1. demo_example.7z is source code of the example in RAD XE 10.4.2 (note: demo_example\Win32\Debug is configuration
file demo_example.ini, which should be placed by generated exe and in it should be configured database parameters and port of REST)
2. demodb.7z file contains demodb.fbk backup of database (Usr: SYSDBA Pwd:masterkey), Firebird 3.0.8 x64. It should be restored
into demodb.fdb.

Test setup:
We use add-on Tab Reloader in Firefox and create 10 tabs (5 sec reload) with this call http://localhost:8080/get/device/all exception then should happen in relatively short time.
Default settings of Firebird API is Interface, if needed to change it into legacy then it must be done in file dm_DBconnInstance on component ZConnectionFbDBtrans.

We hope this will help.

Best regards,
Vaclav

[korecek]

Hello.
We would like to ask if anything changed in this matter and if you found posted demo helpful in solving this problem. Or are we doing something wrong in using ZeosLib components in our example and that's why it does not work and crashes into AV.

Additionally:
We have upgraded to latest ZeosLib (snapshot from 20.8.2022) and we tried to setting FirebirdAPI=Interface and the problem mentioned above still persists but setting FirebirdAPI=legacy continues to work fine.

Thank you in advance for any help.

Best regards,
Vaclav

[marsupilami]

Hello Vaclav,

I am sorry to say that it seems I simply forgot to take a look. I downloaded and unpacked your example right now. But riught after downloading my problems started
I had hoped for an example that I could translate to Delphi easily. But your example application looks quite complex to me. When I try to compile it, I get errors about missing *.lib files. The ones from Jedi and Zeos probably are fixable if I follow the installation routines.
Note: I don't use C++ Builder. I just pay for it - just in case
Problem is that it also seems to depend on the DataSnap server components? At least it misses DataSnapCommon.lib and DataSnapServer.lib. I am pretty sure, these are not part of my RAD Studio Professional license.
Do you have any chance to create an even more simple demo?

Best regards,

Jan

[korecek]

Hello Jan,
we are sorry to hear that you encountered these issues (we apologise but we didn't realise that REST/DataSnap is not part of other versions - we let it to be created by project wizard).
With this post, we are adding new files in which there is a new example where DataSnap is exchanged for threads.
Again it seems to work with FirebirdAPI=legacy but in FirebirdAPI=Interface result is after some time:

First chance exception at $00000000. Exception class $C0000005 with message 'access violation at 0x00000000: read of address 0x00000000'.

then

First chance exception at $75FACC12. Exception class EZSQLException with message 'close all lob streams before closing the resultset'.

which happens to be raised twice.

Note: demo_example2.ini file in archive should be moved to exe and configured.

We hope this will help.

Best regards,
Vaclav

[marsupilami]

Hello Vaclav,

I checked your source and it should compile on my computer. The problem is, you use Zeos in a way that is not supported. You have one connection in the main Thread and give references to that connection to each thread in TfrmMain::createThreads:

Code: Select all

      thdWork.reset(new TthdWork(5, ZConnectionFbDB, true));

So all threads share the same TZConnection object - which is out of specification. Per Zeos specification each thread needs its own TZConnection object. This includes the firebird and interbase drivers: They are not meant to work when used this way. It is pure luck that they work in your environment.

Could you please check what happens, if each thread uses its own TZConnection object?

From your previous example project I understand that you work in a web based environment. If you need a lot of connections that are essentially configured in the same way, you might want to check what TZConnectionGroup and TZGroupedConnection can do for you. TZConnectionGroup essentially is a template for configuring Zeos connections that TZGroupedConnection can use to get its configuration from. Unfortunately this doesn't include all properties yet because no one took the time to redo them properly.

If you don't want to open connections and destroy them all the time, you could think about using the pooled driver that impleemnts a connection pool behind the scenes. For using it with firebird use "pooled.firebird" as the driver name. Other options should be documented in the source code

Best regards,

Jan

[korecek]

Hello Jan,
Thanks for your fast response but we think that there is a little misunderstanding about ZConnectionFbDB.
The ZConnectionFbDB is not used in threads for connection but only for getting the parameters of connection (read-only and it does not change because it is read from ini file only on start so we think that it should be thread safe) as you can see in constructor of data module in dm_DBconnInstance.cpp

Code: Select all

   ZConnectionFbDBtrans->Database        = ZConnectionFbDBmain->Database;
   ZConnectionFbDBtrans->HostName        = ZConnectionFbDBmain->HostName;
   ZConnectionFbDBtrans->Port            = ZConnectionFbDBmain->Port;
   ZConnectionFbDBtrans->Protocol        = ZConnectionFbDBmain->Protocol;
   ZConnectionFbDBtrans->LibraryLocation = ZConnectionFbDBmain->LibraryLocation;
   ZConnectionFbDBtrans->User            = ZConnectionFbDBmain->User;
   ZConnectionFbDBtrans->Password        = ZConnectionFbDBmain->Password;
   ZConnectionFbDBtrans->Properties      = ZConnectionFbDBmain->Properties;

and this module is called new for each connection in example:

Code: Select all

    psadcDBconnInstance.reset(new TdmDBconnInstance(NULL, ZConnectionFbDBthread));

and this one is then used in query to database and on finish it is deleted:

Code: Select all

    psadcDBconnInstance.reset();

So we think that we are doing it in a way of create -> open -> close -> destroy of connection.

Please correct us if we are wrong but we think that this is done in thread safe manner because ZConnectionFbDB from main form is never used for opening connections in threads and in threads its is only used for read-only purposes. If you think it would make a difference then we could move the passed parameters to separate structure/class a then pass pointer on this to threads and not the pointer on ZConnectionFbDB from main form.

Best regards,
Vaclav