TFDQuery Async execution aborts with out of memory

[Clément 148]

Hello,

 

I'm using Delphi 10.4.2 with Firedac against SQL Server northwind database.
I setup the components to execute asynchronously and assigned the OnError event.
But the out of memory is not cached and I don't have a clue on how/where to catch it

 

I'm running this query "select * from categories, orders, employees" to simulate the error a user is reporting.
The application (32 bit version) memory goes to 1.7Gb and raises the out or memory.
The problem is not the query per se, I'm just trying to catch the out of memory error.

 

Here's some code:

function TSQLTaskWorker.DoCreateQry(const aSQL: String;
  aFDConnection: TFDConnection): TFDQuery;
begin
   Result := TFDQuery.Create(nil);
   if Assigned(aFDConnection) then
      Result.Connection := aFDConnection
   else
      Result.Connection := fFDConnection;

   Result.FetchOptions.Mode := fmAll;
   Result.FetchOptions.Items :=[fiBlobs];
   Result.FetchOptions.Cache :=[fiBlobs];
   Result.FetchOptions.AutoFetchAll := afAll;
   Result.FetchOptions.RecsMax := 100000;
   Result.SQL.Text := aSQL;
end;


procedure TSQLTaskWorker.DoSetConnection(aTaskConnection: TSQLTaskConnection);
begin
    { ... Setting up fFDConnection ... }
      fQryAsync:= DoCreateQry('', fFDConnection );
      fQryAsync.ResourceOptions.CmdExecMode := amAsync;
      fQryAsync.AfterOpen    := event_QueryAsyncAfterOpen;
      fQryAsync.AfterExecute := event_QueryAsyncAfterExecute;
      fQryAsync.OnError      := event_QueryAsyncError;
end;


procedure TSQLTaskWorker.DoRunSQLAsync(aSQLTaskRequest: TSQLTaskQueryRequest);
begin
   fQryAsync.SQL.Text := aSQLTaskRequest.SQL;
   fQryAsync.Tag := NativeUInt( lTaskAsyncQueryResponse );
   fQryAsync.OpenOrExecute;
end;

When DoRunSQLAsync is called, fQryAsync.OpenOrExecute is called and returns immediately as expected. I can follow the memory building up until the out of memory is raised.
The error is raise before calling any events, and afterward no event is called either. I can't find a way to trap such error. Here's the call stack:

Since I'm not able to handle properly this error, my application becomes unstable and must be closed.

 

Any ideas?

Clément

 

[Lajos Juhász 293]

6 hours ago, Clément said:

Since I'm not able to handle properly this error, my application becomes unstable and must be closed.

 

Any ideas?

You're reaching the memory limit of the 32 bit applications. You can choose between to switch to 64 bit or to limit the query.

[Clément 148]

Hello Lajos,

 

The application in question is a query tool, so the user can test or write queries against several SQL Engines. If  the user wrote is doing "something wrong" , I must catch the error and display it nicely. This uncatched out of memory is making the application unstable.

 

When using "amAsync", Firedac spawns a thread to fetch the data, and returns immediately the control to the app. That's fine.
The issue of retrieving such volume of records is to trigger the out of memory inside that thread and not surfacing it. At least, so far, I couldn't find a way to catch it.

I'm working on plan B and monitoring the memory used. If the memory gets close enough to the limit, I will abort the job. At least the application will remain stable.
 

[Anders Melander 1782]

Out of memory errors in threaded applications can be really hard to recover safely from. It largely depends on allocation pattern and the nature of the out of memory error. In single threaded applications it's easier but there's still no guarantee that it can be done safely.

The problem is that once you run out of memory you might not have enough memory to report and handle the problem.

 

Luckily an out of memory error is often caused by to trying to allocate a big hunk of memory and if that allocation fails then there's still memory available, just not enough to satisfy that particular allocation request.

 

One "trick" that I've used occasionally is to allocate a large recovery block when the application starts. If an out of memory occurs then this block is deallocated, the allocation is retried and the user is notified that they're about to run out of memory. You will need a custom memory manager (or just a wrapper around the existing one) in order for this to work and multi threading might get in the way.

 

Or you could just recompile for 64-bit 🙂

 

With regard to not being able to handle the error in the async execution; Couldn't you just execute the query in your own thread instead and handle the problem there?

[Clément 148]

47 minutes ago, Anders Melander said:

Or you could just recompile for 64-bit 🙂

This is my "cKwel" query tool available for free in 32bit and 64bits. The user downloaded the 32bit version because he is using 32bit library to connect to his database.🤨
 

47 minutes ago, Anders Melander said:

With regard to not being able to handle the error in the async execution; Couldn't you just execute the query in your own thread instead and handle the problem there?

The SQL Query is already running in it's background thread. FireDAC accepts the following Execution modes:

amBlocking	The calling thread and GUI are blocked until an action is finished.
amNonBlocking	The calling thread is blocked until an action is finished. The GUI is not blocked.
amCancelDialog	The calling thread and GUI are blocked until an action is finished. FireDAC shows a dialog, allowing to cancel an action.
amAsync	The calling thread and GUI are not blocked. The called method returns immediately.

 

 TFDQuery is created using amBlocking. In this mode, just placing TFDquery.Open in an exception block solves the problem.
But, in this mode the user have to wait until the end of the execution and there's no way to Cancel the query (calling abortJob)
Since I'm already in a background thread, amNonBlocking and amCancelDialog are not an option. Which leaves me with amAsync.
The "Cancel Query" button is working. But I can't catch the out of memory error, and who knows what other exceptions?

 

 

 

 

Edited October 7, 2021 by Clément

[Anders Melander 1782]

I don't have time right now to check the FireDAC source but it seems strange if FD has been designed so you can only cancel a non-blocking query. The natural pattern would be to execute a blocking query in a thread and cancel that from another thread (e.g. the main thread).

 

Anyway, it seems you can cancel the query at the connection level via TFDConnection.AbortJob.

[Clément 148]

I will try to call AbortJob from the main thread. My TFDConnection is in another thread, so it should work.

[Anders Melander 1782]

Well I got curious and peaked at the source anyway. It seems you can call AbortJob on the query even while it's blocked.

Also, TFDConnection.AbortJob just iterates the connected datasets and calls AbortJob on each of them.

[Clément 148]

Good news! I'm calling TFDConnection.AbortJob from an instance in the main thread, and it is aborting the query running in another thread! How cool is that!
Also I manage to catch the out of memory error in my default Query execution flow!

The application remains stable even after the out of memory

[Wloochacz 2]

OK, great, but...

In this kind of application, fetching all data from the server and all BLOBs is definitely not a good solution...

When I write a query, I don't always want to fetch all the data from the server - the first 100 records are enough for me (sure, you can move such a constant to the application configuration), and the rest on demand.

Therefore, it might be better to set up FDQuery this way:

   Result.FetchOptions.Mode := fmOnDemand;
   Result.FetchOptions.Items :=[];
   Result.FetchOptions.Cache :=[];

Besides, if I write such a query and I get an "out of memory" error message I really won't know what went wrong?
Where did it run out of memory? On the server? In the application? If in the application, why?

 

Oh well, and most importantly overall - if you always fetch all the data, the query execution time will be long.
Or at least it will look like that, but in fact the execution of the query is say 0.1 sec and retrieving all the data is 10 sec.